Built For Designers & Developers

MySQL: Changes Introduced In MySQL 5.5

With the migration to MySQL 5.5 there are some new features that have been introduced, where some aren’t compatible with the previous MySQL 5.1 server that was running on our servers. 

Default storage engine

With MySQL 5.5 InnoDB has become default the storage engine instead of MyISAM storage engine. This is NOT the case for our servers, all of our shared,reseller and managed VPS servers still utilize MyISAM as the default storage engine.

Improved performance

Improved performance is due to the optimizations in MySQL to better utilize multi core CPUs alongside with improving InnoDB locking and memory management. Since all of our servers have multiple cores available to services running on them, it is important that they can fully utilize them and provide maximum efficiency.

 SQL changes

• INTO clauses are no longer accepted in nested SELECT statements. Modify the SQL statements to not contain the clause.
• MySQL usernames are now changed to allow for more then 7 characters.
• Alias declarations outside table_reference are not allowed for multiple-table DELETE statements. Modify those statements to use aliases only inside the table_reference part.
• Alias resolution does not require qualification and alias reference should not be qualified with the database name.
• New reserved words:
  • GENERAL
  • IGNORE_SERVER_IDS
  • MASTER_HEARTBEAT_PERIOD
  • MAXVALUE
  • RESIGNAL
  • SIGNAL
  • SLOW

Numeric calculations

On the numeric side, the server includes a new a library for conversions between strings and numbers, dtoa.

This library provides the basis for an improved conversion between string or DECIMAL values and approximate-value (FLOAT or DOUBLE) numbers. Also, all numeric operators and functions on integer, floating-point and DECIMAL values throw an out of range error (ER_DATA_OUT_OF_RANGE) rather than returning an incorrect value or NULL.

If an application relies on previous numeric results, it may have to be adjusted to the new precision or behavior.

Unicode support

The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP), introducing the utf16, utf32 and utf8mb4 charsets.

If you are considering upgrading from utf8 to utf8mb4 to take advantage of the supplementary characters, you may have to adjust the size of the fields and indexes in the future. See http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html.

Upgrading to utf8mb4 will not take place unless you explicitly change the charset, i.e. with a ALTER TABLE… statement

Indexes

The stopword file is loaded and searched using latin1 if the character_set_server is ucs2, utf16, or utf32. If any table was created with FULLTEXT indexes while the server character set was ucs2, utf16, or utf32, it should be repaired using this statement REPAIR TABLE tbl_name QUICK;

Deprecated features

MySQL 5.5 introduces a number of deprecated features that would be removed in future MySQL releases. Certain features are provided with alternatives that can be used, other features would be fully removed.

• Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.
• The YEAR(2) data type. YEAR(2) columns in existing tables are treated as before, but YEAR(2) in new or altered tables are converted to YEAR(4). For more information about YEAR(4) please visit http://dev.mysql.com/doc/refman/5.5/en/migrating-to-year4.html

For a in depth review of the changes introduced in MySQL 5.5 please visit: http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html