From time to time I have been changing the column datatype for different reasons. Majorly being for performance and efficiency and some other time based on how the mysql engines would be strong on some types vs the other
here is the simple command i use for changing the column datatype
alter table table_name modify column column_name datatypegoeshere
ALTER TABLE members MODIFY COLUMN date_created TIMESTAMP;
It is customary sometimes to share the part of the database from sandbox to a fellow developer or to access it from the other machine – umm.. is it not how the servers are doing it?? what are you talking about 😉
Anyway here is a simple note to make it happen.1. We need to bind the machine name for the server. to do that
open your my.cnf – inside the /etc/mysql/my.cnf for debianish machines and search for the
[mysqld] and add the following
bind-address = your ip address goes here
you can find your ip address from
then save the file and restart your mysql
sudo /etc/init.d/mysql restart
There are ways to assign for a specified user from the specified ip addresses as well.
Like if you have database db1 and user1 from ip address 18.104.22.168 then
grant select, insert, update on db1.* to 'user1'@'22.214.171.124'
I was reinstalling my mac machine and wanted to install my goodies [PHP, Apache, MySQL] on it and I come up with the newer version error when i try to install mysql.
here is what I gather from different sites and blogs:
Go to the terminal and issue these commands
sudo rm /usr/local/mysql*
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm /etc/my.cnf
sudo rm -rf /var/db/receipts/com.mysql.*
This has taken care of the error for me
The thing is, you would have sql that have LIMIT appended at the end like
select * from sometable limit 25
but what if you wanted to know how many rows would have been returned if it was not limited?
the trivial approach is
select count(*) from sometable
and then issuing query with limit on it.
This is slower solution.
here is the way to do it in a better way
select sql_calc_found_rows * from sometable limit 25;
Right after execution the select with sql_calc_found_rows, we would get the ‘would have been’ value on the second query.
Mysql comes with a handy tool for dumping database, like from remote server to local machine or vice versa.
Sometimes you might want to dump some tables but not the other here you go!
mysqldump -h[HOST] -u[USERNAME] --port=[PORT] -p --ignore [ignoretablename] databasename > databasename.sql
The database.sql would contain all the schema + data.
Then import it as
mysql -h[host] -u[username] -p databasename < databasename.sql
You can provide -p[password] or if you leave it as -p you would be prompted for password later which is a cool way to proceed.
How to enable/add unicode character in hibernate with mysql
Unicode is ruling, it would come handy when working with non ASCII characters like when dealing with Chinese or Ethiopian alphabet amharic characters.
With internalization being the main concern in softwares, bit it website or mobile application, there is a chance you will need unicode in your application.
Hibernate is an ORM, object relational mapper, that is being used with Java and .net. It will allow to abstract all the database related stuffs with simple interface. In this unicode hibernate tutorial, I will show how you can insert, select update and delete records with unicode.
Why do I get No provider for Entity manager Error and how should I fix it
I will show the fix from the hibernate side for MySQL. But keep in mind that unicode characters have to be enabled on the database side as well with proper encoding.
See how you can avoid could not open hibernate session error
The solution for having unicode characters to be recognized as they are passing though the hibernate world would be done on the config file.
Enabling unicode character in hibernate
Here are the steps to follow:
1. go to your
2. make your the connection url something like:
jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8 – make sure the the ampersand is the html-encoded ampersand.
Do you know you can have boilerplate java app with maven?
Find k complementary numbers – algorithm in Java
Have you wondered how to update/insert NULL as value to the existing row in mysql? Too simple:
UPDATE table_name SET field_name = NULL WHERE some_criteria
Watch! there is no any quote on the NULL part, if you do so, it would be inserted/updated as string with value “NULL”
Using if / else on the mysql would be much easier to express with simple query. Her we go:
Assume you have the following table:
| Field | Type | Null | Key | Default | Extra |
| file_id | int(11) | NO | PRI | NULL | auto_increment |
| file_path | text | NO | | NULL | |
| file_name | varchar(120) | NO | MUL | NULL | |
| file_size | bigint(20) | YES | | NULL | |
| file_content | text | NO | MUL | NULL | |
Then you want to collect records either by file_id or by file_size based on the file_size, if the file size above some value, check the name of the file to contain xls, otherwise, consider only files that have file id more than y
select * from file where if (file_size > x, file_name like '%.xls', file_id > y)
One of the fun things in php-mysql is working with Unicode characters. It is so cooked that, we should almost eat those with a little bit adjustment.
I am not going to tutor on how to use php-mysql unicode stuff – there are a bunch of ’em out there.
Rather I would discuss an error that would be common while doing your stuff..
Once you configure everything and put your unicode to mysql, you might see the output of it being questionmark on the browser – but you see the correct unicode in the database through the phpmyadmin.. it is this single line of code you would ever need…
mysql_query(“SET NAMES ‘utf8′”, conn); where the conn is the result of your connection – mysql_connect();
You can modify it accordingly, for example I am using an object oriented one so I can issue the query from my object – you would need to do this only once.
Especially when you move mysql from Window [see related] to Linux using the data file copy, you might face such and error telling you one of your table’s .frm file is not found.
In this case either of the two can solve the problem.
1. be aware that the .frm, for that matter both MDI and MYI files are case sensitive to linux – as it is for all files. So, if on windows you have test.frm and your application access them as TesT.frm, you may not have problem, but it is a major one on Linux.
2. The other is group and ownership issue, make sure the files are under mysql on both ownership and group – unless you have other configuration for that.
TO change ownership use
sudo chmod -R mysql /path/to/data/files
and for group
sudo chgrp -R mysql /path/to/data/files
Hope it would help somebody.