« Guide to Incompatibilities when Upgrading MySQL to Version 4.1 | Main | Building an Aux Input Cable for Bose SoundDock »
September 30, 2006
Guide to Incompatibilities when Upgrading MySQL to Version 5.0
The obvious follow-up post to the Guide to Incompatibilities when Upgrading MySQL to Version 4.1
is a look at the incompatibilities when moving to version 5.0.
Again, I emphasize the importance of being educated about how upgrading MySQL may affect your application. These are the obvious, flagged items, but I recommend reading all of the 5.0 release notes.
Here are the items from the MySQL 5.0 release notes that are marked Warning: Incompatible change:
- C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. (5.0.1)
- SHOW STATUS now shows the session (thread-specific) status variables and SHOW GLOBAL STATUS shows the status variables for the whole server. (5.0.2)
- A consequence of the change in handling of the DECIMAL and NUMERIC fixed-point data types is that the server is more strict to follow standard SQL. For example, a data type of DECIMAL(3,1) stores a maximum value of 99.9. Previously, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. Now the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 5.0.3 and that contain floating-point data not strictly legal for the data type, you should alter the data types of those columns. (5.0.3 & 4.1.2)
- For user-defined functions, exact-value decimal arguments such as 1.3 or DECIMAL column values were passed as REAL_RESULT values prior to MySQL 5.0.3. As of 5.0.3, they are passed as strings with a type of DECIMAL_RESULT. (5.0.3)
- The C API ER_WARN_DATA_TRUNCATED warning symbol was renamed to WARN_DATA_TRUNCATED. (5.0.3)
- MyISAM and InnoDB tables created with DECIMAL columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump such tables with mysqldump before upgrading, and then reload them after upgrading. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (5.0.6)
- The behavior of LOAD DATA INFILE and SELECT ... INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values both are empty. Formerly, a column was read or written the display width of the column. For example, INT(4) was read or written using a field with a width of 4. Now columns are read and written using a field width wide enough to hold all values in the field. However, data files written before this change was made might not be reloaded correctly with LOAD DATA INFILE for MySQL 4.1.12 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE. (5.0.6 & 4.1.12)
- Previously, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format. (5.0.8 & 4.1.13)
- The namespace for triggers has changed. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used). (5.0.10)
- Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to JOIN. (5.0.12)
- A lock wait timeout caused InnoDB to roll back the entire current transaction. Now it rolls back only the most recent SQL statement. (5.0.13)
- For BINARY columns, the pad value and how it is handled has changed. The pad value for inserts now is 0x00 rather than space, and there is no stripping of the pad value for selects. (5.0.15)
- The CHAR() function now returns a binary string rather than a string in the connection character set. An optional USING charset clause may be used to produce a result in a specific character set instead. Also, arguments larger than 256 produce multiple characters. They are no longer interpreted modulo 256 to produce a single character each. (5.0.15)
- The InnoDB storage engine no longer ignores trailing spaces when comparing BINARY or VARBINARY column values. This means that (for example) the binary values 'a' and 'a ' are now regarded as unequal any time they are compared, as they are in MyISAM tables. (5.0.19)
Posted by mike at September 30, 2006 8:51 PM