As part of maintaining the latest software helping security and performance an MySQL upgrade is planned from version 5.1.57 to 5.5.25a. The upgrade will take place over the next few weeks.

Below is a brief summary of the MySQL changes. The full documentation can be read here:

http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

It is expected that there will be less than 0.1% of websites will be effected by these changes. Any possible problems are existing issues that will cause an error to occur where before the error was dropped silently.

The following is an overview of potential errors and how they could effect you.

Changes to TIMESTAMP

This is the support for display width, any SQL containing “TIMESTAMP(N)” will now cause an error. In previous versions this was silently ignored and then deprecated, this is now a syntax error. Below are 2 examples:

Example on v5.5 (raises error 1064)
mysql> create table `table1` (ts TIMESTAMP);
Query OK, 0 rows affected (0.13 sec)
mysql> create table `table2` (ts TIMESTAMP(2));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(2))' at line 1

Example on v5.1 (silently ignored)
mysql> create table `table1` (ts TIMESTAMP);
Query OK, 0 rows affected (0.03 sec)
mysql> create table `table2` (ts TIMESTAMP(2));
Query OK, 0 rows affected, 1 warning (0.31 sec)
mysql> show create table `table2`\G
*************************** 1. row ***************************
Table: table2
Create Table: CREATE TABLE `table2` (
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Changes to reserved words

Any table/column names and similar (eg functions/triggers) should always be quoted with backticks (`), this has been the case for many years. If you have a table or column name that matches a reserved word you may get unexpected behavior if not quoted with backticks. It is recommended to check the following page for any new reserved words: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

CREATE TABLE IF NOT EXISTS … SELECT

The changes here are beyond the scope of this blog entry; you can see the full detailed changes here:
http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html

“out of range” error (ER_DATA_OUT_OF_RANGE)

This error is now thrown if a numeric operation has a result that out of the range of the datatype being assigned to. Previous behavior was to silently enter NULL or an incorrect value. An example is given in their docs (http://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html), though be careful if you plan on relying on this behaviour.

Nested select statement

Nested select statements are no longer supported with the SELECT … INTO syntax. For more information on this please see the official documentation.

Aliases in DELETE statements

When using aliases in DELETE some syntax has been removed, you should check any DELETE statements that use table aliases.

If you are receiving MySQL errors relating to any issues related to the MySQL upgrade, please get in touch with support. If you do not see any errors then you will not need to make any changes.