Skip navigation links

Additional languages
2.18.3 Copying MySQL Databases to Another Machine »
Section Navigation      [Toggle]

2.18.2. Upgrading from MySQL 4.1 to 5.0

Note

It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, you should protect your data by making a backup. MySQL recommends that you dump and reload your tables from any previous version to upgrade to 5.0.

In general, you should do the following when upgrading from MySQL 4.1 to 5.0:

  • Read all the items in the following sections to see whether any of them might affect your applications:

  • Note particularly any changes that are marked Known issue or Incompatible change. These incompatibilities with earlier versions of MySQL may require your attention before you upgrade.

    Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If any upgrade issue applicable to your installation involves an incompatibility that requires special handling, follow the instructions given in the incompatibility description. Often this will involve a dump and reload, or use of a statement such as CHECK TABLE or REPAIR TABLE.

    A dump and reload is done like this:

    • Dump the affected tables with mysqldump before upgrading

    • Reload the dump file (for example, with mysql) to recreate the tables after upgrading

    Any procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See Section 12.5.2.6, “REPAIR TABLE Syntax”.

  • After you upgrade to a new version of MySQL, run mysql_upgrade (see Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”). This program will check your tables, and repair them if necessary. It will also update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. (Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.)

  • If you are running MySQL Server on Windows, see Section 2.9.14, “Upgrading MySQL on Windows”.

  • MySQL 5.0 adds support for stored procedures. This support requires the mysql.proc table. To create this table, you should run the mysql_upgrade program as described in Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • MySQL 5.0 adds support for views. This support requires extra privilege columns in the mysql.user and mysql.db tables. To create these columns, you should run the mysql_upgrade program as described in Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • If you are using replication, see Section 15.3.3, “Upgrading a Replication Setup”, for information on upgrading your replication setup.

Several visible behaviors have changed between MySQL 4.1 and MySQL 5.0 to make MySQL more compatible with standard SQL. These changes may affect your applications.

The following lists describe changes that may affect applications and that you should watch out for when upgrading to MySQL 5.0.

Server Changes:

  • Incompatible change: The indexing order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, TEXT indexes are compared as space-padded at the end (just as MySQL sorts CHAR, VARCHAR and TEXT fields). If you have a index on a TEXT column, you should run CHECK TABLE on it. If the check reports errors, rebuild the indexes: Dump and reload the table if it is an InnoDB table, or run OPTIMIZE TABLE or REPAIR TABLE if it is a MyISAM table.

  • Incompatible change. For BINARY columns, the pad value and how it is handled has changed as of MySQL 5.0.15. The pad value for inserts now is 0x00 rather than space, and there is no stripping of the pad value for retrievals. For details, see Section 10.4.2, “The BINARY and VARBINARY Types”.

  • Incompatible change: As of MySQL 5.0.3, the server by default no longer loads user-defined functions (UDFs) unless they have at least one auxiliary symbol (for example, an xxx_init or xxx_deinit symbol) defined in addition to the main function symbol. This behavior can be overridden with the --allow-suspicious-udfs option. See Section 20.2.2.6, “User-Defined Function Security Precautions”.

  • Incompatible change: As of MySQL 5.0.13, InnoDB rolls back only the last statement on a transaction timeout. In MySQL 5.0.32, a new option, --innodb_rollback_on_timeout, causes InnoDB to abort and roll back the entire transaction if a transaction timeout occurs (the same behavior as in MySQL 4.1).

  • Incompatible change: For ENUM columns that had enumeration values containing commas, the commas were mapped to 0xff internally. However, this rendered the commas indistinguishable from true 0xff characters in the values. This no longer occurs. However, the fix requires that you dump and reload any tables that have ENUM columns containing true 0xff in their values: Dump the tables using mysqldump with the current server before upgrading from a version of MySQL 5.0 older than 5.0.36 to version 5.0.36 or newer.

  • Incompatible change: The update log has been removed in MySQL 5.0. If you had enabled it previously, you should enable the binary log instead.

  • Incompatible change: Support for the ISAM storage engine has been removed in MySQL 5.0. If you have any ISAM tables, you should convert them before upgrading. For example, to convert an ISAM table to use the MyISAM storage engine, use this statement: 

    ALTER TABLE tbl_name ENGINE = MyISAM;

    Use a similar statement for every ISAM table in each of your databases.

  • Incompatible change: Support for RAID options in MyISAM tables has been removed in MySQL 5.0. If you have tables that use these options, you should convert them before upgrading. One way to do this is to dump them with mysqldump, edit the dump file to remove the RAID options in the CREATE TABLE statements, and reload the dump file. Another possibility is to use CREATE TABLE new_tbl ... SELECT raid_tbl to create a new table from the RAID table. However, the CREATE TABLE part of the statement must contain sufficient information to re-create column attributes as well as indexes, or column attributes may be lost and indexes will not appear in the new table. See Section 12.1.9, “CREATE TABLE Syntax”.

    The .MYD files for RAID tables in a given database are stored under the database directory in subdirectories that have names consisting of two hex digits in the range from 00 to ff. After converting all tables that use RAID options, these RAID-related subdirectories still will exist but can be removed. Verify that they are empty, and then remove them manually. (If they are not empty, there is some RAID table that has not been converted.)

  • Incompatible change: Beginning with MySQL 5.0.42, when a DATE value is compared with a DATETIME value, the DATE value is coerced to the DATETIME type by adding the time portion as 00:00:00. Previously, the time portion of the DATETIME value was ignored, or the comparison could be performed as a string comparison. To mimic the old behavior, use the CAST() function as shonw in this example: SELECT date_col = CAST(NOW() AS DATE) FROM table;.

  • As of MySQL 5.0.25, the lc_time_names system variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. See Section 9.8, “MySQL Server Locale Support”.

  • In MySQL 5.0.6, binary logging of stored routines and triggers was changed. This change has implications for security, replication, and data recovery, as discussed in Section 17.5, “Binary Logging of Stored Programs”.

  • As of MySQL 5.0.28, mysqld_safe no longer implicitly invokes mysqld-max if it exists. Instead, it invokes mysqld unless a --mysqld or --mysqld-version option is given to specify another server explicitly. If you previously relied on the implicit invocation of mysqld-max, you should use an appropriate option now.

SQL Changes:

  • Important note: Prior to MySQL 5.0.46, the parser accepted invalid code in SQL condition handlers, leading to server crashes or unexpected execution behavior in stored programs. Specifically, the parser allowed a condition handler to refer to labels for blocks that enclose the handler declaration. This was incorrect because block label scope does not include the code for handlers declared within the labeled block.

    As of 5.0.46, the parser rejects this invalid construct, but if you upgrade in place (without dumping and reloading your databases), existing handlers that contain the construct still are invalid even if they appear to function as you expect and should be rewritten.

    To find affected handlers, use mysqldump to dump all stored functions and procedures, triggers, and events. Then attempt to reload them into an upgraded server. Handlers that contain illegal label references will be rejected.

    For more information about condition handlers and writing them to avoid invalid jumps, see Section 12.8.4.2, “DECLARE for Handlers”.

  • Incompatible change: If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different non-conflicting name. If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a non-conflicting name, or change calls to the function so that they use a database qualifier (that is, use db_name.func_name() syntax). See Section 8.2.3, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

  • Incompatible change: The parser accepted statements that contained /* ... */ that were not properly closed with */, such as SELECT 1 /* + 2. As of MySQL 5.0.50, statements that contain unclosed /*-comments now are rejected with a syntax error.

    This fix has the potential to cause incompatibilities. Because of Bug#26302, which caused the trailing */ to be truncated from comments in views, stored routines, triggers, and events, it is possible that objects of those types may have been stored with definitions that now will be rejected as syntactically invalid. Such objects should be dropped and re-created so that their definitions do not contain truncated comments. If a stored object definition contains only a single statement (does not use a BEGIN ... END block) and contains a comment within the statement, the comment should be moved to follow the statement or the object should be rewritten to use a BEGIN ... END block. For example, this statement: 

    CREATE PROCEDURE p() SELECT 1 /* my comment */ ;

    Can be rewritten in either of these ways: 

    CREATE PROCEDURE p() SELECT 1; /* my comment */
CREATE PROCEDURE p() BEGIN SELECT 1 /* my comment */ ; END;

  • Incompatible change: 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, LEFT JOIN, and so forth.

    These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see Section 12.2.8.1, “JOIN Syntax”.

  • Incompatible change: The namespace for triggers has changed in MySQL 5.0.10. 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).

    After upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them or DROP TRIGGER will not work after the upgrade. Here is a suggested procedure for doing this:

    1. Upgrade to MySQL 5.0.10 or later to be able to access trigger information in the INFORMATION_SCHEMA.TRIGGERS table. (It should work even for pre-5.0.10 triggers.)

    2. Dump all trigger definitions using the following SELECT statement: 

      SELECT CONCAT('CREATE TRIGGER ', t.TRIGGER_SCHEMA, '.', t.TRIGGER_NAME,
              ' ', t.ACTION_TIMING, ' ', t.EVENT_MANIPULATION, ' ON ',
              t.EVENT_OBJECT_SCHEMA, '.', t.EVENT_OBJECT_TABLE,
              ' FOR EACH ROW ', t.ACTION_STATEMENT, '//' )
INTO OUTFILE '/tmp/triggers.sql'
FROM INFORMATION_SCHEMA.TRIGGERS AS t;

      The statement uses INTO OUTFILE, so you must have the FILE privilege. The file will be created on the server host. Use a different filename if you like. To be 100% safe, inspect the trigger definitions in the triggers.sql file, and perhaps make a backup of the file.

    3. Stop the server and drop all triggers by removing all .TRG files in your database directories. Change location to your data directory and issue this command: 

      shell> rm */*.TRG

    4. Start the server and re-create all triggers using the triggers.sql file. For the file created earlier, use these commands in the mysql program: 

      mysql> delimiter // ;
mysql> source /tmp/triggers.sql //

    5. Use the SHOW TRIGGERS statement to check that all triggers were created successfully.

  • Incompatible change: As of MySQL 5.0.15, the CHAR() function returns a binary string rather than a string in the connection character set. An optional USING charset_name 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. These changes may cause some incompatibilities:

    • CHAR(ORD('A')) = 'a' is no longer true: 

      mysql> SELECT CHAR(ORD('A')) = 'a';
+----------------------+
| CHAR(ORD('A')) = 'a' |
+----------------------+
|                    0 |
+----------------------+

      To perform a case-insensitive comparison, you can produce a result string in a non-binary character set by adding a USING clause or converting the result: 

      mysql> SELECT CHAR(ORD('A') USING latin1) = 'a';
+-----------------------------------+
| CHAR(ORD('A') USING latin1) = 'a' |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
mysql> SELECT CONVERT(CHAR(ORD('A')) USING latin1) = 'a';
+--------------------------------------------+
| CONVERT(CHAR(ORD('A')) USING latin1) = 'a' |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+

    • CREATE TABLE ... SELECT CHAR(...) produces a VARBINARY column, not a VARCHAR column. To produce a VARCHAR column, use USING or CONVERT() as just described to convert the CHAR() result into a non-binary character set.

    • Previously, the following statements inserted the value 0x00410041 ('AA' as a ucs2 string) into the table: 

      CREATE TABLE t (ucs2_column CHAR(2) CHARACTER SET ucs2);
INSERT INTO t VALUES (CHAR(0x41,0x41));

      As of MySQL 5.0.15, the statements insert a single ucs2 character with value 0x4141.

  • Incompatible change: By default, integer subtraction involving an unsigned value should produce an unsigned result. Tracking of the “unsignedness” of an expression was improved in MySQL 5.0.13. This means that, in some cases where an unsigned subtraction would have resulted in a signed integer, it now results in an unsigned integer. One context in which this difference manifests itself is when a subtraction involving an unsigned operand would be negative.

    Suppose that i is a TINYINT UNSIGNED column and has a value of 0. The server evaluates the following expression using 64-bit unsigned integer arithmetic with the following result: 

    mysql> SELECT i - 1 FROM t;
+----------------------+
| i - 1                |
+----------------------+
| 18446744073709551615 |
+----------------------+

    If the expression is used in an UPDATE t SET i = i - 1 statement, the expression is evaluated and the result assigned to i according to the usual rules for handling values outside the column range or 0 to 255. That is, the value is clipped to the nearest endpoint of the range. However, the result is version-specific:

    • Before MySQL 5.0.13, the expression is evaluated but is treated as the equivalent 64-bit signed value (–1) for the assignment. The value of –1 is clipped to the nearest endpoint of the column range, resulting in a value of 0: 

      mysql> UPDATE t SET i = i - 1; SELECT i FROM t;
+------+
| i    |
+------+
|    0 |
+------+

    • As of MySQL 5.0.13, the expression is evaluated and retains its unsigned attribute for the assignment. The value of 18446744073709551615 is clipped to the nearest endpoint of the column range, resulting in a value of 255: 

      mysql> UPDATE t SET i = i - 1; SELECT i FROM t;
+------+
| i    |
+------+
|  255 |
+------+

    To get the older behavior, use CAST() to convert the expression result to a signed value: 

    UPDATE t SET i = CAST(i - 1 AS SIGNED);

    Alternatively, set the NO_UNSIGNED_SUBTRACTION SQL mode. However, this will affect all integer subtractions involving unsigned values.

  • Incompatible change: Before MySQL 5.0.13, NOW() and SYSDATE() return the same value (the time at which the statement in which the function occurs begins executing). As of MySQL 5.0.13, SYSDATE() returns the time at which it executes, which can differ from the value returned by NOW(). For information about the implications for binary logging, replication, and use of indexes, see the description for SYSDATE() in Section 11.6, “Date and Time Functions” and for SET TIMESTAMP in Section 12.5.4, “SET Syntax”. To restore the former behavior for SYSDATE() and cause it to be an alias for NOW(), start the server with the --sysdate-is-now option (available as of MySQL 5.0.20).

  • Incompatible change: Before MySQL 5.0.13, GREATEST(x,NULL) and LEAST(x,NULL) return x when x is a non-NULL value. As of 5.0.3, both functions return NULL if any argument is NULL, the same as Oracle. This change can cause problems for applications that rely on the old behavior.

  • Incompatible change: Before MySQL 4.1.13/5.0.8, 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.

  • Incompatible change: In MySQL 4.1.12/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/5.0.6 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. For more information, see Section 12.2.6, “LOAD DATA INFILE Syntax”.

  • Incompatible change: The implementation of DECIMAL has changed in MySQL 5.0.3. You should make your applications aware of this change. For information about this change, and about possible incompatibilities with old applications, see Section 11.13, “Precision Math”, in particular, Section 11.13.2, “DECIMAL Data Type Changes”.

    DECIMAL columns are stored in a more efficient format. To convert a table to use the new DECIMAL type, you should do an ALTER TABLE on it. (The ALTER TABLE also will change the table's VARCHAR columns to use the new VARCHAR data type properties, described in a separate item.)

    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. Before MySQL 5.0.3, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. As of MySQL 5.0.3, 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. For example: 

    ALTER TABLE tbl_name MODIFY col_name DECIMAL(4,1);

    The behavior used by the server for DECIMAL columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but you have DECIMAL columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer DECIMAL format, dump them with mysqldump and reload them.

  • Incompatible change: MySQL 5.0.3 and up uses precision math when calculating with DECIMAL and integer columns (64 decimal digits) and for rounding exact-value numbers. Rounding behavior is well-defined, not dependent on the implementation of the underlying C library. However, this might result in incompatibilities for applications that rely on the old behavior. (For example, inserting .5 into an INT column results in 1 as of MySQL 5.0.3, but might be 0 in older versions.) For more information about rounding behavior, see Section 11.13.4, “Rounding Behavior”, and Section 11.13.5, “Precision Math Examples”.

  • Incompatible change: 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. (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.) If you have such tables, check and repair them with mysql_upgrade after upgrading. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • Incompatible change: 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. If you upgrade to 5.0.3 and find that your UDF now receives string values, use the initialization function to coerce the arguments to numbers as described in Section 20.2.2.3, “UDF Argument Processing”.

  • Incompatible change: Before MySQL 5.0.2, SHOW STATUS returned global status values. The default as of 5.0.2 is to return session values, which is incompatible with previous versions. To issue a SHOW STATUS statement that will retrieve global status values for all versions of MySQL, write it like this: 

    SHOW /*!50002 GLOBAL */ STATUS;

  • Incompatible change: User variables are not case sensitive in MySQL 5.0. In MySQL 4.1, SET @x = 0; SET @X = 1; SELECT @x; created two variables and returned 0. In MySQL 5.0, it creates one variable and returns 1. Replication setups that rely on the old behavior may be affected by this change.

  • Some keywords are reserved in MySQL 5.0 that were not reserved in MySQL 4.1. See Section 8.3, “Reserved Words”.

  • The LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section 12.6.2.2, “LOAD DATA FROM MASTER Syntax”, for recommended alternatives.

  • As of MySQL 5.0.3, trailing spaces no longer are removed from values stored in VARCHAR and VARBINARY columns. The maximum lengths for VARCHAR and VARBINARY columns in MySQL 5.0.3 and later are 65,535 characters and 65,535 bytes, respectively.

    When a binary upgrade (filesystem-level copy of data files) to MySQL 5.0 is performed for a table with a VARBINARY column, the column is space-padded to the full allowable width of the column. This causes values in VARBINARY columns that do not occupy the full width of the column to include extra trailing spaces after the upgrade, which means that the data in the column is different.

    In addition, new rows inserted into a table upgraded in this way will be space padded to the full width of the column.

    This issue can be resolved as follows:

    1. For each table containing VARBINARY columns, execute the statement 

      ALTER TABLE table_name ENGINE=engine_name;

      where table_name is the name of the table and engine_name is the name of the storage engine currently used by table_name. In other words, if the table named mytable uses the MyISAM storage engine, then you would use this statement: 

      ALTER TABLE mytable ENGINE=MYISAM;

      This rebuilds the table so that it uses the 5.0 VARBINARY format.

    2. Then you must remove all trailing spaces from any VARBINARY column values. For each VARBINARY column varbinary_column, you should perform the following statement (where table_name is the name of the table containing the VARBINARY column): 

      UPDATE table_name SET varbinary_column = RTRIM(varbinary_column);

      This is necessary and safe because trailing spaces are stripped before 5.0.3, meaning that any trailing spaces are erroneous.

    This problem does not occur (and thus these two steps are not required) for tables upgraded using the recommended procedure of dumping tables prior to the upgrade and reloading them afterwards.

    Note

    If you create a table with new VARCHAR or VARBINARY columns in MySQL 5.0.3 or later, the table will not be usable if you downgrade to a version older than 5.0.3. Dump the table with mysqldump before downgrading and reload it after downgrading.

  • Comparisons made between FLOAT or DOUBLE values that happened to work in MySQL 4.1 may not do so in 5.0. Values of these types are imprecise in all MySQL versions, and you are strongly advised to avoid such comparisons as WHERE col_name=some_double, regardless of the MySQL version you are using. See Section B.1.5.8, “Problems with Floating-Point Comparisons”.

  • As of MySQL 5.0.3, BIT is a separate data type, not a synonym for TINYINT(1). See Section 10.1.1, “Overview of Numeric Types”.

  • MySQL 5.0.2 adds several SQL modes that allow stricter control over rejecting records that have invalid or missing values. See Section 5.1.7, “SQL Modes”, and Section 1.7.6.2, “Constraints on Invalid Data”. If you want to enable this control but continue to use MySQL's capability for storing incorrect dates such as '2004-02-31', you should start the server with --sql_mode="TRADITIONAL,ALLOW_INVALID_DATES".

  • As of MySQL 5.0.2, the SCHEMA and SCHEMAS keywords are accepted as synonyms for DATABASE and DATABASES, respectively. (While “schemata” is grammatically correct and even appears in some MySQL 5.0 system database and table names, it cannot be used as a keyword.)

  • As of MySQL 5.0.25, TIMESTAMP columns that are NOT NULL now are reported that way by SHOW COLUMNS and INFORMATION_SCHEMA, rather than as NULL.

C API Changes:

  • Incompatible change: Because the MySQL 5.0 server has a new implementation of the DECIMAL data type, a problem may occur if the server is used by older clients that still are linked against MySQL 4.1 client libraries. If a client uses the binary client/server protocol to execute prepared statements that generate result sets containing numeric values, an error will be raised: 'Using unsupported buffer type: 246'

    This error occurs because the 4.1 client libraries do not support the new MYSQL_TYPE_NEWDECIMAL type value added in 5.0. There is no way to disable the new DECIMAL data type on the server side. You can avoid the problem by relinking the application with the client libraries from MySQL 5.0.

  • Incompatible change: The ER_WARN_DATA_TRUNCATED warning symbol was renamed to WARN_DATA_TRUNCATED in MySQL 5.0.3.

  • The reconnect flag in the MYSQL structure is set to 0 by mysql_real_connect(). Only those client programs which did not explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (due to the fact that table locks, temporary tables, user variables, and session variables are lost after reconnection).

MySQL Enterprise.  MySQL Enterprise subscribers will find more information about upgrading in the Knowledge Base articles found at Upgrading. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.


User Comments

Posted by Mike Krejci on October 24 2005 8:14pm[Delete] [Edit]

I upgraded from 4.1 to 5.0 without only a small problem. If you store any AES_ENCRYPT data, do a mysqldump --hex-blob of the data before you upgrade and then restore the tables from that. The upgrade seemed to destroy the aes encrypted data in the fields and it was no longer able to be decyrpted.

Posted by Silvan Mühlemann on January 20 2006 6:40am[Delete] [Edit]

I upgraded from MySQL 4.0.25 to 5.0.18 without dumping the data with mysqldump and reimporting. (With 4 GB of data it would have taken forever)
The only problems I encountered was with tables containing fulltext indexes. CHECK TABLE reported those tables as crashed. Even REPAIR TABLE or OPTIMIZE TABLE wouldn't help.
I had to explicitly drop the fulltext indexes and add them again.

Posted by [name withheld] on January 22 2006 8:02pm[Delete] [Edit]

In MySQL 4.0.x, the following SQL sentences retrieve the same dataset ('Table' is a table, 'col' is a DATETIME datatype):
select * from Table where col=''
select * from Table where col=0
select * from Table where col='0000-00-00'

However, the join using the empty string (select * from Table where col='') isn't working in version 5.0.

Posted by Chris Lewis on February 8 2006 5:00am[Delete] [Edit]

An interesting side effect of the JOIN ... USING (...) changes which I didn't find an explanation for in the documentation: In 4.1 and earlier, USING(...) only paid attention to columns in the preceding joined table, so you could get away with:

DoubleJoinedTable T1
inner join DoubleJoinedTable T2 using (Column1)
inner join AnotherTable A using (Column2)

Previous MySQL versions would assume that Column2 meant T2.Column2, but 5.0 doesn't let you do that. The final USING needs to be changed to ON:

DoubleJoinedTable T1
inner join DoubleJoinedTable T2 using (Column1)
inner join AnotherTable on A.Column2 = T2.Column2

I haven't been able to figure out why this is. I thought it was related to the new nested JOIN logic but the docs state that non-parenthetized INNER JOINs shouldn't be affected by those changes.

Posted by Liam Redmond on February 21 2006 2:42pm[Delete] [Edit]

When installing the 5.0 build from a 4.0 installation you cannot use "localhost" in your jdbc connection url anymore. beware, you my have to recompile/re-deploy all your java web apps rather than just changing the database server. "localhost" forces the use of unix sockets and you won't be able to connect without changing your jdbc url to contain the local IP address. I found no way of disabling unix sockets or making it so that localhost used tcp/ip.

Posted by Karl on May 15 2006 8:23pm[Delete] [Edit]

Another incompatible change:

As of 5.0.3 the structure of the XML produced from the MySQL client has changed, which may break any scripts that are parsing the output.

For details, see the Bug #7811:
http://bugs.mysql.com/bug.php?id=7811

Posted by Marky Goldstein on May 30 2006 12:57pm[Delete] [Edit]

Quite complicated update proceedure...

I have data in a sql file, and I would like to import it. Unfortunately I get non meaning full error messages about my Sql having errors... reading all these documented changes is a pain... why isn't there an 4.1 compatibility import wizard?

Posted by Michael Shigorin on August 4 2006 10:37am[Delete] [Edit]

A friend of mine (Igor Muratov) suggested mysqldump --allow-keywords to migrate MySQL 4.0 DB to 5.0 on another host, which worked. Otherwise, I'd get this:

ERROR 1064 (42000) at line 166: 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 'release varchar(100) NOT NULL default '',
serial varchar(10) default '0',
ar' at line 5

The difference in dumps is in quoted names.
on this:

CREATE TABLE rpm ( srpm varchar(100) binary NOT NULL default '',
name varchar(100) binary NOT NULL default '',
version varchar(100) NOT NULL default '',
release varchar(100) NOT NULL default '',
serial varchar(10) default NULL,
arch varchar(10) NOT NULL default 'i586',
rpmgroup mediumint(8) unsigned NOT NULL default '0',
size int(11) NOT NULL default '0',
summary varchar(255) NOT NULL default '',
description text NOT NULL,
optflags varchar(255) default NULL,
PRIMARY KEY (name),
KEY srpm (srpm)
) TYPE=MyISAM;

Posted by Patrick Feliciano on August 11 2006 9:26pm[Delete] [Edit]

I ran into a problem with an older configuration that had --err-log=<file> option set. This option was depricated in 4.0.2x in favor of --log-error=<file>, but continued to work through the 4.1 series. However it is completely disabled in 5.0. This causes the server to quit immediately after start up with no visible error and outputs the cause to the default error log location, which may not be where you are expecting to look if you have an existing logfile location that is not the default logfile location.

This was a bit of a headache to track, hopefully I'll save someone else the same headache.

NOTE: My config uses the mysqld_multi, however I don't believe that should have any bearing on things.

Posted by Chris Martin on November 28 2006 11:38pm[Delete] [Edit]

this is a clarification of Liam Redmond's comment above regarding:
'cannot use "localhost" in your connection url anymore'

This can be re-enabled in your my.cnf
See 'skip-networking' and/or 'bind-address'

Add your own comment.