MySQL

MySQL is Going to be Upgraded to MySQL5.5

Posted on

As a continued effort to provide the latest software for both security and performance, we are upgrading MySQL from version 5.1.57 to 5.5.25a over the next few weeks. Below is a brief summary of the MySQL changes; all information here is sourced from the official MySQL documentation. However, for more in-depth information please visit the official page here: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

We expect a very low impact for our customers and their websites; fewer than 0.1% of websites hosted by us will be affected. Any possible problems are existing issues that will now cause an error to occur where previously the error was dropped silently. Please read on for a technical overview of potential errors and how they could affect 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 highlighted here, please get in touch with our support staff. If you do not see any errors then you will not need to make any changes. Thanks!

This entry was posted in Web Hosting, WebMe News. Bookmark the permalink.

One Response to MySQL is Going to be Upgraded to MySQL5.5

  1. Friv says:

    Mysql5.5 is strongly, cache better.

Leave a Reply to Friv Cancel reply

Your email address will not be published. Required fields are marked *