MySQL: from utf8 to utf8mb4

After my last upgrade from MySQL 5.1 to MySQL 5.5 to use utf8mb4 instead of utf8, I noticed that some accented characters were being stored as question mark.

To fix this problem, I had to update the character set of the database, tables and columns and make sure that the my.cnf contains the good charset.

Server Character Set and Collation

In /etc/mysql/my.cnf file make sure you have the following:

[mysqld]
# ...
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci
# ...

Display the current character set

To see the current character set, issue the following command:

For Schemas:

SELECT default_character_set_name FROM information_schema.SCHEMATA S
WHERE schema_name = "schemaname";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` C
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

Alter database character set:

Use the following command:

alter database schemaname CHARACTER SET = utf8mb4;

Alter table and column character set

ALTER TABLE tablename DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY columname LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

ALTER TABLE tablename CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;
ALTER TABLE tablename MODIFY columnname longtext CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

Finally, the output of SHOW VARIABLES LIKE 'character_set%'; should at least look like to the following:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------------+
| Variable_name            | Value                                                              |
+--------------------------+--------------------------------------------------------------------+
| character_set_client     | utf8                                                               |
| character_set_connection | utf8                                                               |
| character_set_database   | utf8mb4                                                            |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8                                                               |
| character_set_server     | utf8mb4                                                            |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /usr/local/appservers/mysql-5.5.21-linux2.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)

If you are using MySQL with a Java application, you must upgrade your mysql-connector-java to the latest version, at the moment of writing, it is 5.1.18 or at least to the 5.1.14

Here is the maven dependency:

 mysql
 mysql-connector-java
 5.1.18

Resources: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html

MySQL 5.5 upgrade on Debian Squeeze (2/2)

In the precedent post we have seen how to install and run MySQL 5.5 on a Debian server that was already running MySQL 5.1.
In this post we will go farther and see how we make an init script to start/stop MySQL
Configuration files and scripts are available for download on our git repository at bitbucket

‣ Copy and update Debian configuration files:

Copy both debian.cnf and debian-start from your old /etc/mysql folder or download them from our repository.

/etc# cp mysql/debian.cnf mysql-5.5/
/etc# cp mysql/debian-start mysql-5.5/

Fix paths in debian-start file:

sed -i "s/\/etc\/mysql/\/etc\/mysql-5.5/" /etc/mysql-5.5/debian-start
sed -i "s/\/etc\/init.d\/mysql/\/etc\/init.d\/mysql-5.5/" /etc/mysql-5.5/debian-start
sed -i "s/\/usr\/bin/\/usr\/local\/appservers\/mysql-5.5\/bin/g" /etc/mysql-5.5/debian-start

Fix path into the debian.cnf file.

sed -i "s/\/var\/run\/mysqld\/mysqld.sock/\/var\/run\/mysqld-5.5\/mysqld-5.5.sock/g" /etc/mysql-5.5/debian.cnf
sed -i "s/\/usr$/\/usr\/local\/appservers\/mysql-5.5/g" /etc/mysql-5.5/debian.cnf

‣ make symlink for binary ( to make 'kill' command working )

ln -s /usr/local/appservers/mysql-5.5/bin/mysqld /usr/sbin/mysqld-5.5

‣ Copy your /etc/init.d/mysql to /etc/init.d/mysql-5.5 and fix the init script

Update paths accordingly or use the bash script (update-mysql-init.d) containing sed commands which is available for download in the repository also.

‣ Add the debian-sys-maint user

Please, use the create_debian-sys-maint_user.sql script available on the repository to create debian-sys-maint user.
Make sure to provide the script with the debian-sys-maint password as argument. The password can be found in /etc/mysql-5.5/debian.cnf

Now we can start|stop mysqld 5.5 with the following command:

#start the server
/etc/init.d/mysql-5.5 start

#stop the server
/etc/init.d/mysql-5.5 stop

‣ Make sure we can connect to your server

/usr/local/appservers/mysql-5.5/bin/mysql -S/var/run/mysqld-5.5/mysqld-5.5.sock -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

‣ Configure MySQL 5.5 to start when system boots

Debian uses a Sys-V like init system for executing commands when the system runlevel changes - for example at bootup and shutdown time.

# update-rc.d mysql-5.5 defaults

check that the command was successful:

/etc$ find rc* -regex .*mysql.*

rc0.d/K01mysql-5.5
rc0.d/K02mysql

rc1.d/K01mysql-5.5  
rc1.d/K02mysql

rc2.d/S20mysql-5.5
rc2.d/S20mysql

rc3.d/S20mysql-5.5
rc3.d/S20mysql

rc4.d/S20mysql-5.5
rc4.d/S20mysql

rc5.d/S20mysql-5.5
rc5.d/S20mysql

rc6.d/K01mysql-5.5
rc6.d/K02mysql

Now, launch the server with /etc/init.d/mysql-5.5 start then reboot the server to make sure that MySQL 5.5 will start too.

If the server didn't start at boot time, you should start looking in /var/log/syslog and in log file /usr/local/appservers/mysql-5.5/data/host_name.err

‣ Create an alias to connect to MySQL 5.5

Added this alias to your bash.bashrc file (make it system wide)

alias mysql-5.5='/usr/local/appservers/mysql-5.5/bin/mysql -S /var/run/mysqld-5.5/mysqld-5.5.sock '

You can now connect to your server by opening a new terminal and issuing:

mysql-5.5 -uroot -p

‣ Change root password

Before we continue, let's change the root password. More information here

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');

Try to connect to make sure that password was changed. You'll have to use the -p parameter.

/usr/local/appservers/mysql-5.5/bin/mysql \
-S/var/run/mysqld-5.5/mysqld-5.5.sock \
-uroot -p

If you are using mysql-query-browser, you must go to advanced setting of the "create connection" window, and specify this socket /var/run/mysqld-5.5/mysqld-5.5.sock

‣ Drop test database

mysql> Drop database test;

‣ Delete anonymous account

DROP USER ''@'localhost';
DROP USER ''@'host_name';

select host, user, password from user;

‣ Finally, import you data to the new server

To keep this post simple, we will see how to import one database only

First dump the database from the old MySQL 5.1 server (to dump all databases replace the --databases flag by --all-databases):

mysqldump -u root -p --databases my_db > /home/backups/my_db_dump.sql

Then, import it into the new MySQL 5.5 server:

mysql -uroot -p -S /var/run/mysqld-5.5/mysqld-5.5.sock < /home/backups/my_db_dump.sql

In my case, I had to create a user to be associated with this db:

CREATE USER 'reda'@'localhost' IDENTIFIED BY 'my_pass';
GRANT ALL ON my_db.* TO 'reda'@'localhost';

CREATE USER 'reda'@'127.0.0.1' IDENTIFIED BY 'my_pass';
GRANT ALL ON my_db.* TO 'reda'@'127.0.0.1';

‣ Conclusion

During these two posts we have seen how to install MySQL 5.5 on a Debian Squeeze server that was already running a MySQL 5.1 server.

We did an init.d script to start and stop MySQL as any other service. We was able to make it start at boot time and stop at system shutdown.

If you need to move from utf8 to utf8mb4, you may find the next post useful.

Configuration files and scripts are available for download on our git repository at bitbucket

‣Resources:

http://www.dotdeb.org/2012/02/09/mysql-5-5-20/
http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/
http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/
http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/#comment-193000

MySQL 5.5 upgrade on Debian Squeeze (1/2)

Since a few days, I started to see the following error in the log file:

Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x9C\xF0\x9F...' for column 'message' at row 1

After some googling, I found an answer on SO. It seems that the problem was caused by some characters  like an 'emoji' character aka japanese smiley face.

There are known issues storing 4byte utf characters in some versions of MySQL that were fixed by using utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length.

This has been fixed on MySQL 5.5.3 and we will see in this post series how to run different versions of MySQL on the same Debian server.
In this first post, we will install the server, of course we will keep the old instance of MySQL running. We will make sure that the new server can be started before we will move on to the next post that describe how to make an init script to start/stop your new MySQL server with grace.

Configuration files and scripts are available for download on our git repository at bitbucket

But wait, why not just upgrade to the new version with aptitude?

Because, there are other applications using the current MySQL server that I don't want to disturb.

If you don't have this constraint or if you are starting from scratch, I recommend installing the latest version of MySQL using aptitude with dotdeb.org because in Debian Squeeze, the current version of MySQL is (and it remains forever) the 5.1.x

Installing a fresh MySQL 5.5 server:

‣ make sure you have a full safe backup of all of your data and configuration files.

$ cp -r /var/lib/mysql/* /home/backup/var_lib_mysql/
$ mysqldump -u root -p --all-databases /home/backup/mysql/dump.sql

‣ Install the asynchronous I/O library

This is so that we can take advantage of the asynchronous I/O capability in the new InnoDB plugin that ships with MySQL 5.5

# aptitude install libaio-dev

‣ Download the latest version of MySQL

Get MySQL-5.5.21-1.linux2.6.x86_64.tar from their website . Note that this is the Linux Generic version and not the debian one. If you are using a 32bits OS choose mysql-5.5.21-linux2.6-i686.tar.gz

‣ Untar and move mysql folder to /usr/local/appservers/

Create also a symbolic link as follows:

/usr/local/appservers#ln -s mysql-5.5.21-linux2.6-x86_64 mysql-5.5

‣ Set the correct file and directory permissions on the MySQL installation directory

Setting correct permissions is very important, make sure that all the files except those under the data directory are owned by root. The data directory has to be owned by the user mysql.

 chown -R root:root mysql-5.5/*
 chown -R mysql:mysql mysql-5.5/data

‣ Get your my.cnf configuration file

Copy the sample MySQL configuration file to the etc directory or use your old my.cnf

 $ mkdir /etc/mysql-5.5
 $ cp /usr/local/appservers/mysql-5.5/support-files/my-large.cnf /etc/mysql-5.5/my.cnf

You can checkout my configuration file which contains some optimization, especially if you don't use MyIsam engine (I'm using Innodb engine exclusively) so I deleted some useless options like: key_buffer that was defined by the debian version of my.cnf

The datadir option which is where MySQL store data, was set to a directory located on a partition with enough space to hold your data

datadir = /usr/local/appservers/mysql-5.5/data

For performance issue, query logging was disabled:

general_log and general_log_file

Finally, I included some additional configuration files (lower_case_table_names.cnf, mysqld_safe_syslog.cnf)

!includedir /etc/mysql-5.5/conf.d/

It is worth noting, that every option is defined on the MySQL website

‣ Create /var/run/mysqld-5.5

#mkdir /var/run/mysqld-5.5
#chown -R mysql /var/run/mysqld-5.5/
#ls -l /var/run/mysql*
#drwxr-xr-x 2 mysql       root        4096 mar 30  2010 mysqld
#drwxr-xr-x 2 mysql       root        4096 mar  6 01:08 mysqld-5.5
#

‣ Initialize the MySQL data directory and create the system tables

/usr/local/appservers/mysql-5.5/scripts#./mysql_install_db --user=mysql \
 --datadir=/usr/local/appservers/mysql-5.5/data/ \
 --basedir=/usr/local/appservers/mysql-5.5 \
 --defaults-file=/etc/mysql-5.5/my.cnf

‣ You can now start the server

/usr/local/appservers/mysql-5.5/bin/mysqld_safe \
 --defaults-file=/etc/mysql-5.5/my.cnf \
 --basedir=/usr/local/appservers/mysql-5.5

‣ Connect to your server

/usr/local/appservers/mysql-5.5/bin/mysql -S/var/run/mysqld-5.5/mysqld-5.5.sock -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

‣ Stop the server

mysqladmin --defaults-file=/etc/mysql-5.5/my.cnf shutdown

Success, we have been able to install a new version of MySQL server on a machine that was running an old instance of MySQL.
Now, jump to the next post to see how to use /etc/init.d/mysql-5.5 start|stop command and how to make your new server start at system boot.