Before upgrading to MySQL 5.0, review the changes described in this section to identify upgrade issues that apply to your current MySQL installation and applications.
Changes marked as either Known
issue or Incompatible
change are incompatibilities with earlier versions of
MySQL, and 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. Sometimes
this involves dumping and reloading tables, or use of a
statement such as
CHECK TABLE or
For dump and reload instructions, see
Section 2.19.4, “Rebuilding or Repairing Tables or Indexes”. 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 188.8.131.52, “REPAIR TABLE Syntax”.
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.
After upgrading a 5.0 installation to 5.0.10 or higher, it is necessary to upgrade your grant tables. Otherwise, creating stored procedures and functions might not work. The procedure for doing this is described in Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.
MySQL 5.0.27 is the last version in MySQL 5.0 for which MySQL-Max binary distributions are provided, except for RPM distributions. For RPMs, MySQL 5.0.37 is the last release. After these versions, the features previously included in the mysqld-max server are included in mysqld.
If you previously installed a MySQL-Max distribution that includes a server named mysqld-max, and then upgrade later to a non-Max version of MySQL, mysqld_safe still attempts to run the old mysqld-max server. If you perform such an upgrade, you should remove the old mysqld-max server manually to ensure that mysqld_safe runs the new mysqld server.
Incompatible change: Character set or collation changes may require table indexes to be rebuilt. In MySQL 5.0, these occurred in version 5.0.48. For details, see Section 2.19.3, “Checking Whether Tables or Indexes Must Be Rebuilt”.
SHOW CREATE VIEWdisplays view definitions using an
ASclause for each column. If a column is created from an expression, the default alias is the expression text, which can be quite long. As of MySQL 5.0.52, aliases for column names in
CREATE VIEWstatements are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters). As a result, views created from the output of
SHOW CREATE VIEWfail if any column alias exceeds 64 characters. This can cause problems for replication or loading dump files. For additional information and workarounds, see Section C.4, “Restrictions on Views”.
Incompatible change: Beginning with MySQL 5.0.42, when a
DATEvalue is compared with a
DATEvalue is coerced to the
DATETIMEtype by adding the time portion as
00:00:00. Previously, the time portion of the
DATETIMEvalue was ignored, or the comparison could be performed as a string comparison. To mimic the old behavior, use the
CAST()function to cause the comparison operands to be treated as previously. For example:
date_col= CAST(NOW() AS DATE)
Incompatible change: For
ENUMcolumns that had enumeration values containing commas, the commas were mapped to
0xffinternally. However, this rendered the commas indistinguishable from true
0xffcharacters in the values. This no longer occurs. However, the fix requires that you dump and reload any tables that have
ENUMcolumns containing true
0xffin 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. For
BINARYcolumns, the pad value and how it is handled has changed as of MySQL 5.0.15. The pad value for inserts now is
0x00rather than space, and there is no stripping of the pad value for retrievals. For details, see Section 11.4.2, “The BINARY and VARBINARY Types”.
Incompatible change: As of MySQL 5.0.13,
InnoDBrolls back only the last statement on a transaction timeout. As of MySQL 5.0.32, a new option,
InnoDBto abort and roll back the entire transaction if a transaction timeout occurs (the same behavior as in MySQL 4.1).
Incompatible change: The namespace for triggers 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 TRIGGERsyntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used).
When upgrading from a version of MySQL 5 older than 5.0.10 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them or
DROP TRIGGERwill not work after the upgrade. Here is a suggested procedure for doing this:
Upgrade to MySQL 5.0.10 or later to be able to access trigger information in the
INFORMATION_SCHEMA.TRIGGERStable. (This should work even for pre-5.0.10 triggers.)
Dump all trigger definitions using the following
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
FILEprivilege. The file will be created on the server host. Use a different file name if you like. To be 100% safe, inspect the trigger definitions in the
triggers.sqlfile, and perhaps make a backup of the file.
Stop the server and drop all triggers by removing all
.TRGfiles in your database directories. Change location to your data directory and issue this command:
Start the server and re-create all triggers using the
delimiter // ;mysql>
source /tmp/triggers.sql //
SHOW TRIGGERSstatement to check that all triggers were created successfully.
Incompatible change: The indexing order for end-space in
MyISAMtables has changed. Starting from 5.0.3,
TEXTindexes are compared as space-padded at the end (just as MySQL sorts
TEXTfields). If you have an index on a
TEXTcolumn, you should run
CHECK TABLEon it. If the check reports errors, rebuild the indexes: Dump and reload the table if it is an
InnoDBtable, or run
REPAIR TABLEif it is a
Incompatible change. As of MySQL 5.0.3, trailing spaces no longer are removed from values stored in
VARBINARYcolumns. The maximum lengths for
VARBINARYcolumns in MySQL 5.0.3 and later are 65,535 characters and 65,535 bytes, respectively.
When a binary upgrade (file system-level copy of data files) to MySQL 5.0 is performed for a table with a
VARBINARYcolumn, the column is space-padded to the full permissible width of the column. This causes values in
VARBINARYcolumns 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:
For each table containing
VARBINARYcolumns, execute the following statement, where
tbl_nameis the name of the table and
engine_nameis the name of the storage engine currently used by
In other words, if the table named
MyISAMstorage engine, then you would use this statement:
ALTER TABLE mytable ENGINE=MYISAM;
This rebuilds the table so that it uses the 5.0
Then you must remove all trailing spaces from any
VARBINARYcolumn values. For each
varbinary_column, execute the following statement, where
tbl_nameis the name of the table containing the
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 afterward.
Incompatible change: The implementation of
DECIMALwas 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 12.17, “Precision Math”, in particular, Section 12.17.2, “DECIMAL Data Type Characteristics”.
DECIMALcolumns are stored in a more efficient format. To convert a table to use the new
DECIMALtype, you should do an
ALTER TABLEon it. (The
ALTER TABLEalso will change the table's
VARCHARcolumns to use the new
VARCHARdata type properties, described in a separate item.)
A consequence of the change in handling of the
NUMERICfixed-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 permitted 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 permissible 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:
The behavior used by the server for
DECIMALcolumns 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
DECIMALcolumns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer
DECIMALformat, dump them with mysqldump and reload them.
Incompatible change: MySQL 5.0.3 and up uses precision math when calculating with
DECIMALand 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
INTcolumn results in 1 as of MySQL 5.0.3, but might be 0 in older versions.) For more information about rounding behavior, see Section 12.17.4, “Rounding Behavior”, and Section 12.17.5, “Precision Math Examples”.
Incompatible change: In very old versions of MySQL (prior to 4.1), the
TIMESTAMPdata type supported a display width, which was silenty ignored beginning with MySQL 4.1. This is deprecated in MySQL 5.1, and removed altogether in MySQL 5.5. These changes in behavior can lead to two problem scenarios when trying to use
TIMESTAMP(columns with a MySQL 5.5 or later server:
When importing a dump file (for example, one created using mysqldump) created in a MySQL 5.0 or earlier server into a server from a newer release series, a
ALTER TABLEstatement containing
TIMESTAMP(causes the import to fail with a syntax error.
To fix this problem, edit the dump file in a text editor to replace any instances of
TIMESTAMPprior to importing the file. Be sure to use a plain text editor for this, and not a word processor; otherwise, the result is almost certain to be unusable for importing into the MySQL server.
When trying replicate any
ALTER TABLEstatement containing
TIMESTAMP(from a master MySQL server that supports the
TIMESTAMP(syntax to a MySQL 5.5 or newer slave, the statement causes replication to fail. Similarly, when you try to restore from a binary log written by a server that supports
TIMESTAMP(to a MySQL 5.5 or newer server, any
ALTER TABLEstatement containing
TIMESTAMP(causes the backup to fail. This holds true regardless of the logging format used by a MySQL 5.1 or newer server.
It may be possible to fix such issues using a hex editor, by replacing any width arguments used with
TIMESTAMP, and the parentheses containing them, with space characters (hexadecimal
20). This can be made to work as long as checksums were not enabled when creating the binary log. Be sure to use a programmer's binary hex editor and not a regular text editor or word processor for this; otherwise, the result is almost certain to be a corrupted binary log file. To guard against accidental corruption of the binary log, you should always work on a copy of the file rather than the original.
You should try to handle potential issues of these types proactively by updating with
TIMESTAMP(N)columns in your databases so that they use
TIMESTAMPinstead, before performing any upgrades.
InnoDBtables created with
DECIMALcolumns 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
DECIMALcolumn values were passed as
REAL_RESULTvalues 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 184.108.40.206, “UDF Argument Processing”.
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_deinitsymbol) defined in addition to the main function symbol. This behavior can be overridden with the
--allow-suspicious-udfsoption. See Section 220.127.116.11, “UDF Security Precautions”.
Incompatible change: The update log has been removed in MySQL 5.0. If you had enabled it previously, enable the binary log instead.
Incompatible change: Support for the
ISAMstorage engine has been removed in MySQL 5.0. If you have any
ISAMtables, you should convert them before upgrading. For example, to convert an
ISAMtable to use the
MyISAMstorage engine, use this statement:
tbl_nameENGINE = MyISAM;
Use a similar statement for every
ISAMtable in each of your databases.
Incompatible change: Support for
MyISAMtables 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
RAIDoptions in the
CREATE TABLEstatements, and reload the dump file. Another possibility is to use
CREATE TABLEto create a new table from the
RAIDtable. However, the
CREATE TABLEpart 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 13.1.10, “CREATE TABLE Syntax”.
RAIDtables in a given database are stored under the database directory in subdirectories that have names consisting of two hex digits in the range from
ff. After converting all tables that use
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, this indicates that there is some
RAIDtable that has not been converted.)
As of MySQL 5.0.25, the
lc_time_namessystem variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the
MONTHNAME()functions. See Section 10.7, “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 18.6, “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-versionoption 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.
Known issue: 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 permitted 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 procedures and functions, 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 18.104.22.168, “DECLARE ... HANDLER Syntax”.
Known issue: The fix for Bug #23491 introduced a problem with
SHOW CREATE VIEW, which is used by mysqldump. This causes an incompatibility when upgrading from versions affected by that bug fix (MySQL 5.0.40 through 5.0.43, MySQL 5.1.18 through 5.1.19): If you use mysqldump before upgrading from an affected version and reload the data after upgrading to a higher version, you must drop and recreate your views.
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 ... ENDblock) 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 ... ENDblock. 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: 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 FUNCTIONto drop the UDF, and then use
CREATE FUNCTIONto re-create the UDF with a different nonconflicting 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 nonconflicting name, or change calls to the function so that they use a database qualifier (that is, use
syntax). See Section 9.2.3, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.
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
USINGclause 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:
SELECT CHAR(ORD('A')) = 'a';+----------------------+ | CHAR(ORD('A')) = 'a' | +----------------------+ | 0 | +----------------------+
To perform a case-insensitive comparison, you can produce a result string in a nonbinary character set by adding a
USINGclause or converting the result:
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 | +--------------------------------------------+
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
NATURALjoins and joins specified with a
USINGclause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to
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 22.214.171.124, “JOIN Syntax”.
CREATE TABLE ... SELECT CHAR(...)produces a
VARBINARYcolumn, not a
VARCHARcolumn. To produce a
CONVERT()as just described to convert the
CHAR()result into a nonbinary character set.
Previously, the following statements inserted the value
ucs2string) 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
ucs2character with value
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.
TINYINT UNSIGNEDcolumn and has a value of 0. The server evaluates the following expression using 64-bit unsigned integer arithmetic with the following result:
SELECT i - 1 FROM t;+----------------------+ | i - 1 | +----------------------+ | 18446744073709551615 | +----------------------+
If the expression is used in an
UPDATE t SET i = i - 1statement, the expression is evaluated and the result assigned to
iaccording 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:
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:
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_SUBTRACTIONSQL mode. However, this will affect all integer subtractions involving unsigned values.
Incompatible change: Before MySQL 5.0.12,
SYSDATE()return the same value (the time at which the statement in which the function occurs begins executing). As of MySQL 5.0.12,
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 12.7, “Date and Time Functions” and for
SET TIMESTAMPin Section 13.7.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-nowoption (available as of MySQL 5.0.20).
Incompatible change: Before MySQL 5.0.13,
xis a non-
NULLvalue. As of 5.0.13, both functions return
NULLif 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 5.0.8, conversion of
DATETIMEvalues to numeric form by adding zero produced a result in
YYYYMMDDHHMMSSformat. The result of
DATETIME+0is now in
Incompatible change: In MySQL 5.0.6, the behavior of
LOAD DATA INFILEand
SELECT ... INTO OUTFILEhas changed when the
FIELDS TERMINATED BYand
FIELDS ENCLOSED BYvalues both are empty. Formerly, a column was read or written using 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 INFILEfor MySQL 5.0.6 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use
LOAD DATA INFILEand
SELECT ... INTO OUTFILE. For more information, see Section 13.2.6, “LOAD DATA INFILE Syntax”.
Incompatible change: Before MySQL 5.0.2,
SHOW STATUSreturned 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 STATUSstatement 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 may be reserved in MySQL 5.0 that were not reserved in MySQL 4.1. See Section 9.3, “Keywords and Reserved Words”.
LOAD DATA FROM MASTERand
LOAD TABLE FROM MASTERstatements are deprecated. See Section 126.96.36.199, “LOAD DATA FROM MASTER Syntax”, for recommended alternatives.
Comparisons made between
DOUBLEvalues 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, regardless of the MySQL version you are using. See Section B.5.4.8, “Problems with Floating-Point Values”.
MySQL 5.0.2 adds several SQL modes that enable stricter control over rejecting records that have invalid or missing values. See Section 5.1.7, “Server SQL Modes”, and Section 188.8.131.52, “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
As of MySQL 5.0.2, the
SCHEMASkeywords are accepted as synonyms for
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.)
Incompatible change: Because the MySQL 5.0 server has a new implementation of the
DECIMALdata 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_NEWDECIMALtype value added in 5.0. There is no way to disable the new
DECIMALdata 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_TRUNCATEDwarning symbol was renamed to
WARN_DATA_TRUNCATEDin MySQL 5.0.3.
reconnectflag in the
MYSQLstructure 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).