« Bare Bones OS for MySQL? | Main | Guide to Incompatibilities when Upgrading MySQL to Version 5.0 »
September 30, 2006
Guide to Incompatibilities when Upgrading MySQL to Version 4.1
For folks who are looking at upgrading MySQL to a newer version there is good documentation in the MySQL docs (for example, upgrading from 4.0. to 4.1). My (limited) experience is that folks tend to focus on moving the data between versions, and don't always think about the other ramifications involved in upgrading to a new major version of MySQL.
You'll note that in the upgrade instructions the first step is focused on the behavior of MySQL:
Check the items in the change lists found later in this section to see whether any of them might affect your applications. Note particularly any that are marked Incompatible change. These result in incompatibilities with earlier versions of MySQL and you should consider the implications of these incompatibilities before you upgrade. Note particularly the items under "Server Changes" that related to changes in character set support.
The placement of this item first on the list is significant. The effort to accomodate behavioral changes in MySQL may be more significant than ensuring the data is properly copied between versions. I bumped into one of these recently when going through an upgrade from 4.0 to 4.1. The data appeared to have transferred correctly but in testing the application I started getting periodic internal server errors. Several hours of debugging and I was at the SQL layer looking at a problem caused by this:
Warning: Incompatible change: TIMESTAMP is now returned as a string of type 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported.
The trick is, unless you are the only coder and have a small code base that you understand very well t's not clear how these changes will affect your application. You might think that a timestamp formatting change won't make a difference, but it does.
So in an effort to at least be aware of what to be on the lookout for, here's the complete list of incompatibility warnings from the MySQL 4.1 docs. I want to note that I'm not sure on the criteria for a compatibility change warning, and just because these items are flagged as compatibility changes doesn't mean you won't be affected by some of the other changes or bug fixes. So these are the biggies, but it's wise to take a few minutes and read all of the release notes to see if anything else raises a red flag for you.
Se here are the items pulled from the MySQL 4.1 release docs that are marked Warning: Incompatible change, followed by the full version number in parens:
- TIMESTAMP is now returned as a string of type 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported. (4.1.0)
- Renamed the C API mysql_prepare_result() function to mysql_get_metadata() because the old name was confusing. (4.1.1)
- Client authentication now is based on 41-byte passwords in the user table, not 45-byte passwords as in 4.1.0. Any 45-byte passwords created for 4.1.0 must be reset after running the mysql_fix_privilege_tables script. (4.1.1)
- Handling of the FLOAT and DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of FLOAT(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 4.1.2 and that contain floating-point data not strictly legal for the column type, you should alter the data types of those columns. (4.1.2)
- String comparison now works according to the SQL standard. Because we have that 'a' = 'a ' then from it must follow that 'a' > 'a\t'. (The latter was not the case before MySQL 4.1.2.) To implement it, we had to change how storage engines compare strings internally. As a side effect, if you have a table where a CHAR or VARCHAR column in some row has a value with the last character less than ASCII(32), you have to repair this table. CHECK TABLES tells you if this problem exists. (4.1.2)
- Renamed 10 prepared statements C API functions. (4.1.2)
- The signature of the mysql_stmt_prepare() function was changed to int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create a MYSQL_STMT handle, you should use the mysql_stmt_init() function, not mysql_stmt_prepare(). (4.1.2)
- The Type output column for SHOW TABLE STATUS now is labeled Engine. (4.1.2)
- 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. (4.1.3)
- The timezone system variable has been removed and replaced by system_time_zone. (4.1.3)
- 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. (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. (4.1.13)
Hope that helps someone. It provides a nice snapshot of what to be worried about when going back to upgrading that application to work with MySQL 4.1.
Posted by mike at September 30, 2006 7:28 AM