Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x9C\xF0\x9F...' for column 'message' at row 1After 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?
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 libraryThis 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 MySQLGet 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 directorySetting 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 fileCopy 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.cnfYou 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/dataFor performance issue, query logging was disabled:
general_log and general_log_fileFinally, 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 shutdownSuccess, 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.