Upgrading from Mysql 5.0 to 5.5

Having dealt with Application server upgrades, library upgrades and more in the past, I have to say that this has been one of the smoother component upgrades I have dealt with so far.

Here are some brief findings and code changes I had to go through to get our applications upgraded from Mysql 5.0[58]/ Mysql 5.0[77] to Mysql 5.5[15]

1. INNODB definition

a. The TYPE attribute is unsupported as of Mysql 5.5

b. Schema needs to be updated to use the ENGINE instead.

c. Example: ENGINE=InnoDB (unsupported: TYPE=InnoDB)

2. Timestamp

a. In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silenty ignored beginning with MySQL 4.1. This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5. These changes in behavior can lead to two problem scenarios when trying to use TIMESTAMP(N) columns with a MySQL 5.5 or later server:

i. When importing a dump file (for example, one created using mysqldump) created in a MySQL 5.0 or earlier server into a server from a newer release series, a CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) causes the import to fail with a syntax error.

ii. To fix this problem, edit the dump file in a text editor to replace any instances of TIMESTAMP(N) with TIMESTAMP prior to importing the file. Be sure to use a plain text editor for this, and not a word processor; otherwise, the result is almost certain to be unusable for importing into the MySQL server.

3. GROUP_CONCAT(N)

GROUP_CONCAT when used with Integers used to give us back a byte [] as of Mysql 5.0. Mysql 5.5 treats this differently and does an auto conversion to String. So any code that expected a byte [] will have to be updated.

Here is an example:

SELECT GROUP_CONCAT(ROW_NUMBER ORDER BY ROW_NUMBER) AS ROW_NUMBER FROM test_table;

TO get this to work consistently we will have to make code changes that involve Casting object types in SQL and always returning a String. Any code that read them back as a byte [] will have to be refactored to use a String instead.

For example:

SELECT CAST(GROUP_CONCAT(ROW_NUMBER ORDER BY ROW_NUMBER) AS BINARY) AS ROW_NUMBER FROM test_table;

4 Mysqldump – This has been largely backward compatible, except for one of the options that we have been using, which is -–all. This option was deprecated a while ago and somewhere between 5.1.x and 5.5.x this was taken out. The recommendation was to use –create-options instead.

Apart from these we have had challenges to migrate customers from one installation to the other but that goes without saying. There are enough documentation out there that help with these.

Here are some performance benchmarks using supersmack:


The 5.0.92 Percona machine seems to have much higher wait IO and lower throughput:
bash-3.2# super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=2ms min=0ms avg= 1ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 12 0 1740.80
update_index 300000 18 0 1740.80


MySQL 5.5.15
bash-3.2# /usr/local/bin/super-smack update-select.smack 30 10000
Query Barrel Report for client smacker
connect: max=15ms min=0ms avg= 5ms from 30 clients
Query_type num_queries max_time min_time q_per_s
select_index 300000 3 0 5209.22
update_index 300000 12 0 5209.22

bash-3.2# mysql --version
mysql Ver 14.12 Distrib 5.0.92, for unknown-linux-gnu (x86_64) using readline 5.1
bash-3.2# mysqladmin status
Uptime: 99573 Threads: 6 Questions: 973831 Slow queries: 0 Opens: 1290 Flush tables: 1 Open tables: 320 Queries per second avg: 9.780

bash-3.2# mysql --version
mysql Ver 14.14 Distrib 5.5.15, for Linux (x86_64) using EditLine wrapper
bash-3.2# mysqladmin status
Uptime: 99247 Threads: 6 Questions: 1822824 Slow queries: 0 Opens: 1686 Flush tables: 1 Open tables: 96 Queries per second avg: 18.366


Its certainly fast and much improved InnoDB comes as a relief. Performance Schema is exciting too although it will be interesting to see if it adds up to any overhead.