MySQL 5.1 Reference Manual  /  ...  /  Changes Affecting Upgrades to 5.1 Changes Affecting Upgrades to 5.1

Before upgrading to MySQL 5.1, review the changes described in this section to identify upgrade issues that apply to your current MySQL installation and applications.


In addition to the changes outlined in this section, review the Release Notes and other important information outlined in Before You Begin.

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 REPAIR TABLE.

For dump and reload instructions, see Section 2.13.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, “REPAIR TABLE Syntax”.

Configuration Changes
  • Before MySQL 5.1.11, to build MySQL from source with SSL support enabled, you would invoke configure with either the --with-openssl or --with-yassl option. In MySQL 5.1.11, those options both have been replaced by the --with-ssl option. By default, --with-ssl causes the bundled yaSSL library to be used. To select OpenSSL instead, give the option as --with-ssl=path, where path is the directory where the OpenSSL header files and libraries are located.

System Table Changes
  • After upgrading a 5.0 installation to 5.0.10 or above, it is necessary to upgrade your grant tables using mysql_upgrade. Otherwise, creating stored procedures and functions might not work. Running mysql_upgrade is part of the the in-place and logical upgrade procedures.

Server Changes
  • As of MySQL 5.1.9, the mysqld-max server is included in binary distributions. There is no separate MySQL-Max distribution. As of MySQL 5.1.12, there is no mysqld-max server at all in binary distributions. They contain a server that includes the features previously included in mysqld-max.

  • Known issue: As of MySQL 5.1.70, for new installations, the url columns in the mysql datatbase help tables are now created as type TEXT to accommodate longer URLs. For upgrades, mysql_upgrade does not update the columns. Modify them manually using these statements:

    ALTER TABLE mysql.help_category MODIFY url TEXT NOT NULL;
    ALTER TABLE mysql.help_topic MODIFY url TEXT NOT NULL;
  • Known issue: mysql_upgrade attempts to upgrade tables that are incompatible with the current version of MySQL. (It invokes mysqlcheck to check tables and, if necessary, repair them.) However this can fail for storage engines that do not support REPAIR TABLE, such as InnoDB, and leave tables in a nonupgradable state.

    To work around this problem, use ALTER TABLE tbl_name ENGINE=InnoDB to perform a null alter operation that rebuilds the table.

  • Known issue: After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables:

    In either case, the solution is to use mysqldump to dump all 5.0 ARCHIVE tables before upgrading, and reload them into MySQL 5.1 after upgrading. This problem is fixed in MySQL 5.6.4: The server can open ARCHIVE tables created in MySQL 5.0. However, it remains the recommended upgrade procedure to dump 5.0 ARCHIVE tables before upgrading and reload them after upgrading.

  • 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.

  • Known issue: Dumps performed by using mysqldump to generate a dump file before the upgrade and reloading the file after upgrading are subject to the following problem:

    Before MySQL 5.0.40, mysqldump displays SPATIAL index definitions using prefix lengths for the indexed columns. These prefix lengths are accepted in MySQL 5.0, but not as of MySQL 5.1. If you use mysqldump from versions of MySQL older than 5.0.40, any table containing SPATIAL indexes will cause an error when the dump file is reloaded into MySQL 5.1 or higher.

    For example, a table definition might look like this when dumped in MySQL 5.0:

    CREATE TABLE `t` (
     `g` geometry NOT NULL,
     SPATIAL KEY `g` (`g`(32))

    The SPATIAL index definition will not be accepted in MySQL 5.1. To work around this, edit the dump file to remove the prefix:

    CREATE TABLE `t` (
     `g` geometry NOT NULL,
     SPATIAL KEY `g` (`g`)

    Dump files can be large, so it may be preferable to dump table definitions and data separately to make it easier to edit the definitions:

    shell> mysqldump --no-data other_args > definitions.sql
    shell> mysqldump --no-create-info other_args > data.sql

    Then edit definitions.sql before reloading definitions.sql and data.sql, in that order.

    If you upgrade to a version of MySQL 5.0 higher than 5.0.40 before upgrading to MySQL 5.1, this problem does not occur.

  • Known issue: Before MySQL 5.1.30, the CHECK TABLE ... FOR UPGRADE statement did not check for incompatible collation changes made in MySQL 5.1.24. (This also affects mysqlcheck and mysql_upgrade, which cause that statement to be executed.)

    Prior to the fix made in 5.1.30, a binary upgrade (performed without dumping tables with mysqldump before the upgrade and reloading the dump file after the upgrade) would corrupt tables. After the fix, CHECK TABLE ... FOR UPGRADE properly detects the problem and warns about tables that need repair.

    However, the fix is not backward compatible and can result in a downgrading problem under these circumstances:

    1. Perform a binary upgrade to a version of MySQL that includes the fix.

    2. Run CHECK TABLE ... FOR UPGRADE (or mysqlcheck or mysql_upgrade) to upgrade tables.

    3. Perform a binary downgrade to a version of MySQL that does not include the fix.

    The solution is to dump tables with mysqldump before the downgrade and reload the dump file after the downgrade. Alternatively, drop and recreate affected indexes.

  • Known issue: MySQL introduces encoding for table names that have non-ASCII characters (see Section 9.2.3, “Mapping of Identifiers to File Names”). After a binary upgrade from MySQL 5.0 to 5.1 or higher, the server recognizes names that have non-ASCII characters and adds a #mysql50# prefix to them.

    As of MySQL 5.1.31, mysql_upgrade encodes these names by executing the following command:

    mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

    Prior to MySQL 5.1.31, mysql_upgrade does not execute this command, so you should execute it manually if you have database or table names that contain nonalphanumeric characters.

    Prior to MySQL 5.1.23, the mysqlcheck command does not perform the name encoding for views. To work around this problem, drop each affected view and recreate it.

    mysqlcheck cannot fix names that contain literal instances of the @ character that is used for encoding special characters. If you have databases or tables that contain this character, use mysqldump to dump them before upgrading to MySQL 5.1, and then reload the dump file after upgrading.

  • Known issue: When upgrading from MySQL 5.0 to versions of 5.1 prior to 5.1.23, running mysqlcheck (or mysql_upgrade, which runs mysqlcheck) to upgrade tables fails for names that must be written as quoted identifiers. To work around this problem, rename each affected table to a name that does not require quoting:

    RENAME TABLE `tab``le_a` TO table_a;
    RENAME TABLE `table b` TO table_b;

    After renaming the tables, run the mysql_upgrade program. Then rename the tables back to their original names:

    RENAME TABLE table_a TO `tab``le_a`;
    RENAME TABLE table_b TO `table b`;
  • Known issue: In connection with view creation, the server created arc directories inside database directories and maintained useless copies of .frm files there. Creation and renaming procedures of those copies as well as creation of arc directories has been discontinued in MySQL 5.1.29.

    This change does cause a problem when downgrading to older server versions which manifests itself under these circumstances:

    1. Create a view v_orig in MySQL 5.1.29 or higher.

    2. Rename the view to v_new and then back to v_orig.

    3. Downgrade to an older 5.1.x server and run mysql_upgrade.

    4. Try to rename v_orig to v_new again. This operation fails.

    As a workaround to avoid this problem, use either of these approaches:

    • Dump your data using mysqldump before downgrading and reload the dump file after downgrading.

    • Instead of renaming a view after the downgrade, drop it and recreate it.

  • Incompatible change: Character set or collation changes may require table indexes to be rebuilt. In MySQL 5.1, these occurred in versions 5.1.21, 5.1.23, and 5.1.24. For details, see Section 2.13.3, “Checking Whether Tables or Indexes Must Be Rebuilt”.

  • Incompatible change: Prior to MySQL 5.1.51, if you flushed the logs using FLUSH LOGS or mysqladmin flush-logs and mysqld was writing the error log to a file (for example, if it was started with the --log-error option), it renames the current log file with the suffix -old, then created a new empty log file. This had the problem that a second log-flushing operation thus caused the original error log file to be lost unless you saved it under a different name. For example, you could use the following commands to save the file:

    shell> mysqladmin flush-logs
    shell> mv host_name.err-old backup-directory

    To avoid the preceding file-loss problem, no renaming occurs as of MySQL 5.1.51; the server merely closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands:

    shell> mv host_name.err host_name.err-old
    shell> mysqladmin flush-logs
    shell> mv host_name.err-old backup-directory
  • Incompatible change: MySQL 5.1 implements support for a plugin API that enables the loading and unloading of components at runtime, without restarting the server. Section 22.2, “The MySQL Plugin API”. The plugin API requires the mysql.plugin table. After upgrading from an older version of MySQL, you should run the mysql_upgrade command to create this table. See Section 4.4.8, “mysql_upgrade — Check and Upgrade MySQL Tables”.

    Plugins are installed in the directory named by the plugin_dir system variable. This variable also controls the location from which the server loads user-defined functions (UDFs), which is a change from earlier versions of MySQL. That is, all UDF library files now must be installed in the plugin directory. When upgrading from an older version of MySQL, you must migrate your UDF files to the plugin directory.

  • Incompatible change: The table_cache system variable has been renamed to table_open_cache. Any scripts that refer to table_cache must be updated to use the new name.

  • Incompatible change: In MySQL 5.1.36, options for loading plugins such as pluggable storage engines were changed from boolean to tristate format. The implementations overlap, but if you previously used options of the form --plugin_name=0 or --plugin_name=1, you should instead use --plugin_name=OFF or --plugin_name=ON, respectively. For details, see Section, “Installing and Uninstalling Plugins”.

  • Incompatible change: From MySQL 5.1.24 to 5.1.31, the UPDATE statement was changed such that assigning NULL to a NOT NULL column caused an error even when strict SQL mode was not enabled. The original behavior before MySQL 5.1.24 was that such assignments caused an error only in strict SQL mode, and otherwise set the column to the implicit default value for the column data type and generated a warning. (For information about implicit default values, see Section 11.6, “Data Type Default Values”.)

    The change caused compatibility problems for applications that relied on the original behavior. It also caused replication problems between servers that had the original behavior and those that did not, for applications that assigned NULL to NOT NULL columns in UPDATE statements without strict SQL mode enabled. The change was reverted in MySQL 5.1.32 so that UPDATE again had the original behavior. Problems can still occur if you replicate between servers that have the modified UPDATE behavior and those that do not.

  • Incompatible change: As of MySQL 5.1.29, the default binary logging mode has been changed from MIXED to STATEMENT for compatibility with MySQL 5.0.

  • Incompatible change: In MySQL 5.1.25, a change was made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client/server protocol (using the mysql_stmt_prepare() C API function).

    Previously, changes to metadata of tables or views referred to in a prepared statement could cause a server crash when the statement was next executed, or perhaps an error at execute time with a crash occurring later. For example, this could happen after dropping a table and recreating it with a different definition.

    Now metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Repreparation also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES.

    Repreparation is automatic, but to the extent that it occurs, performance of prepared statements is diminished.

    Table content changes (for example, with INSERT or UPDATE) do not cause repreparation, nor do SELECT statements.

    An incompatibility with previous versions of MySQL is that a prepared statement may now return a different set of columns or different column types from one execution to the next. For example, if the prepared statement is SELECT * FROM t1, altering t1 to contain a different number of columns causes the next execution to return a number of columns different from the previous execution.

    Older versions of the client library cannot handle this change in behavior. For applications that use prepared statements with the new server, an upgrade to the new client library is strongly recommended.

    Along with this change to statement repreparation, the default value of the table_definition_cache system variable has been increased from 128 to 256. The purpose of this increase is to lessen the chance that prepared statements will need repreparation due to referred-to tables/views having been flushed from the cache to make room for new entries.

    A new status variable, Com_stmt_reprepare, has been introduced to track the number of repreparations.

  • Incompatible change: The -, *, and / operators and the functions POW() and EXP() could misbehave when used with floating-point numbers. Previously they might return +INF, -INF, or NaN in cases of numeric overflow (including that caused by division by zero) or when invalid arguments were used. As of MySQL 5.1.24, NULL is returned in all such cases.

  • Incompatible change: As of MySQL 5.1.23, within a stored routine, it is no longer permissible to declare a cursor for a SHOW or DESCRIBE statement. This happened to work in some instances, but is no longer supported. In many cases, a workaround for this change is to use the cursor with a SELECT query to read from an INFORMATION_SCHEMA table that produces the same information as the SHOW statement.

  • Incompatible change: SHOW CREATE VIEW displays view definitions using an AS alias_name clause 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.1.23, aliases for column names in CREATE VIEW statements 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 VIEW fail 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: Several issues were identified for stored programs (stored procedures and functions, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format.

    To address these problems, the representation for these objects was changed in MySQL 5.1.21. However, the fixes affect all stored programs and views. (For example, you will see warnings about no creation context.) To avoid warnings from the server about the use of old definitions from any release prior to 5.1.21, you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions. Invoke mysqldump with a --default-character-set option that names the non-ASCII character set that was used for the definitions when the objects were originally created, and the --routines, --events, and --triggers options to dump stored program definitions.

    Upgrading for triggers in particular must be handled carefully, for two reasons:

    • The output from mysqldump does not contain a DROP TRIGGER statement preceding each CREATE TRIGGER statement, so reloading the dump file will fail to re-create the triggers unless you manually drop them after generating the dump file and before reloading it.

    • If you are upgrading from a very old version of MySQL 5.0 (before 5.0.10), the trigger upgrade procedure is different because triggers for those versions were created using a different namespace (trigger names had to be unique per table, rather than per schema as is true now).

    Assuming that you are upgrading from MySQL 5.0.10 to 5.1.20 to MySQL 5.1.21 or later, use the following procedure to upgrade your triggers:

    • Use mysqldump to generate a dump file that contains the trigger definitions:

      mysqldump --triggers --no-create-db --no-data 
                --no-create-info --all-databases > triggers.sql

      You might need to add options to specify connection parameters, such as --user or --password. Also, if you are updating from a version of MySQL 5.1 older than 5.1.21, you may need to include a --default-character-set option that specifies the non-ASCII character set that was used for the definitions when the triggers were originally created.

      Otherwise, invoke mysqldump with exactly the preceding options to avoid generating a dump file that will not have the intended effect when reloaded. For example, if you omit the --no-create-db option, your databases will be removed and recreated with no contents when you reload the dump file.

    • Drop existing triggers. To see which triggers exist, use this statement:


      To generate DROP TRIGGERS statements for the triggers, use this statement:

      INTO OUTFILE '/tmp/drop_triggers.sql';

      The statement uses INTO OUTFILE, so you must have the FILE privilege. 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 drop_triggers.sql file, and perhaps make a backup of the file. Then execute the statements in the file:

      mysql --force < /tmp/drop_triggers.sql
    • Recreate the triggers by reloading the dump file created earlier:

      mysql --force < triggers.sql

    For upgrades from a version of MySQL older than 5.0.10 to MySQL 5.1.21 or later, use the following procedure to upgrade your triggers:

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

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

                    ' ', 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'

      The statement uses INTO OUTFILE, so you must have the FILE privilege. 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.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:

      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.1.20, mysqld_safe supports error logging to syslog on systems that support the logger command. The new --syslog and --skip-syslog options can be used instead of the --log-error option to control logging behavior, as described in Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.

    In 5.1.21 and up, the default is --skip-syslog, which is compatible with the default behavior of writing an error log file for releases prior to 5.1.20.

    In 5.1.20 only, the following conditions apply: 1) The default is to use syslog, which is not compatible with releases prior to 5.1.20. 2) Logging to syslog may fail to operate correctly in some cases. For these reasons, avoid using MySQL 5.1.20.

  • Incompatible change: As of MySQL 5.1.18, the plugin interface and its handling of system variables was changed. Command-line options such as --skip-innodb now cause an error if InnoDB is not built-in or plugin-loaded. You should use --loose-skip-innodb if you do not want any error even if InnoDB is not available. The --loose prefix modifier should be used for all command-line options where you are uncertain whether the plugin exists and when you want the operation to proceed even if the option is necessarily ignored due to the absence of the plugin. (For a description of how --loose works, see Section 4.2.4, “Using Options on the Command Line”.)

  • Incompatible change: As of MySQL 5.1.15, InnoDB rolls back only the last statement on a transaction timeout. 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: As of MySQL 5.1.15, the following conditions apply to enabling the read_only system variable:

    • If you attempt to enable read_only while you have any explicit locks (acquired with LOCK TABLES or have a pending transaction, an error will occur.

    • If other clients hold explicit table locks or have pending transactions, the attempt to enable read_only blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set.

    • read_only can be enabled while you hold a global read lock (acquired with FLUSH TABLES WITH READ LOCK) because that does not involve table locks.

    Previously, the attempt to enable read_only would return immediately even if explicit locks or transactions were pending, so some data changes could occur for statements executing in the server at the same time.

  • Incompatible change: The number of function names affected by IGNORE_SPACE was reduced significantly in MySQL 5.1.13, from about 200 to about 30. (For details about IGNORE_SPACE, see Section 9.2.4, “Function Name Parsing and Resolution”.) This change improves the consistency of parser operation. However, it also introduces the possibility of incompatibility for old SQL code that relies on the following conditions:

    • IGNORE_SPACE is disabled.

    • The presence or absence of whitespace following a function name is used to distinguish between a built-in function and stored function that have the same name (for example, PI() versus PI ()).

    For functions that are no longer affected by IGNORE_SPACE as of MySQL 5.1.13, that strategy no longer works. Either of the following approaches can be used if you have code that is subject to the preceding incompatibility:

    • If a stored function has a name that conflicts with a built-in function, refer to the stored function with a schema name qualifier, regardless of whether whitespace is present. For example, write schema_name.PI() or schema_name.PI ().

    • Alternatively, rename the stored function to use a nonconflicting name and change invocations of the function to use the new name.

  • Incompatible change: For utf8 columns, the full-text parser incorrectly considered several nonword punctuation and whitespace characters as word characters, causing some searches to return incorrect results. The fix involves a change to the full-text parser in MySQL 5.1.12, so as of 5.1.12, any tables that have FULLTEXT indexes on utf8 columns must be repaired with REPAIR TABLE:

    REPAIR TABLE tbl_name QUICK;
  • Incompatible change: Storage engines can be pluggable at runtime, so the distinction between disabled and invalid storage engines no longer applies. As of MySQL 5.1.12, this affects the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.7, “Server SQL Modes”.

  • Incompatible change: The structure of FULLTEXT indexes has been changed in MySQL 5.1.6. After upgrading to MySQL 5.1.6 or greater, any tables that have FULLTEXT indexes must be repaired with REPAIR TABLE:

    REPAIR TABLE tbl_name QUICK;
  • Incompatible change: In MySQL 5.1.6, when log tables were implemented, the default log destination for the general query and slow query log was TABLE. As of MySQL 5.1.21, this default has been changed to FILE, which is compatible with MySQL 5.0, but incompatible with earlier releases of MySQL 5.1. If you are upgrading from MySQL 5.0 to 5.1.21 or higher, no logging option changes should be necessary. However, if you are upgrading from 5.1.6 through 5.1.20 to 5.1.21 or higher and were using TABLE logging, use the --log-output=TABLE option explicitly to preserve your server's table-logging behavior.

  • Incompatible change: In very old versions of MySQL (prior to 4.1), the TIMESTAMP data type supported a display width, which was silently 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(N) 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 CREATE TABLE or ALTER TABLE statement containing TIMESTAMP(N) 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 TIMESTAMP(N) with TIMESTAMP prior 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.

    You should try to handle potential issues of these types proactively by updating with ALTER TABLE any TIMESTAMP(N) columns in your databases so that they use TIMESTAMP instead, before performing any upgrades.

  • 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.1 older than 5.1.15 to version 5.1.15 or newer.

  • As of MySQL 5.1.12, 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 10.7, “MySQL Server Locale Support”.

  • As of MySQL 5.1.9, 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. As of MySQL 5.1.12, there is no longer any separate mysqld-max server, so no change should be necessary.

SQL Changes
  • Known issue: Prior to MySQL 5.1.17, 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.1.17, the parser rejects this invalid construct, but if you perform a binary upgrade (without dumping and reloading your databases), existing handlers that contain the construct still are invalid and should be rewritten even if they appear to function as you expect.

    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, “DECLARE ... HANDLER Syntax”.

  • Incompatible change: The parser accepted statements that contained /* ... */ that were not properly closed with */, such as SELECT 1 /* + 2. As of MySQL 5.1.23, 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.

  • Incompatible change: Multiple-table DELETE statements containing ambiguous aliases could have unintended side effects such as deleting rows from the wrong table. Examples:

    DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
    DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

    To avoid ambiguity, declaration of aliases other than in the table_references part of the statement should be avoided:

    DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;

    As of MySQL 5.1.23, alias declarations outside the table_references part of the statement are disallowed for the USING variant of multiple-table DELETE syntax. (In MySQL 5.5, alias declarations outside table_references are disallowed for all multiple-table DELETE statements.) Statements containing aliases that are no longer permitted must be rewritten.

  • Incompatible change: As of MySQL 5.1.8, TYPE = engine_name is still accepted as a synonym for the ENGINE = engine_name table option but generates a warning. You should note that this option is not available in MySQL 5.1.7, and is removed altogether in MySQL 5.5 and produces a syntax error.

    TYPE has been deprecated since MySQL 4.0.

  • Incompatible change: MySQL 5.1.6 introduces the TRIGGER privilege. Previously, the SUPER privilege was needed to create or drop triggers. Now those operations require the TRIGGER privilege. This is a security improvement because you no longer need to grant users the SUPER privilege to enable them to create triggers. However, the requirement that the account named in a trigger's DEFINER clause must have the SUPER privilege has changed to a requirement for the TRIGGER privilege. When upgrading from a previous version of MySQL 5.0 or 5.1 to MySQL 5.1.6 or newer, be sure to update your grant tables by running mysql_upgrade. This will assign the TRIGGER privilege to all accounts that had the SUPER privilege. If you fail to update the grant tables, triggers may fail when activated. After updating the grant tables, you can revoke the SUPER privilege from those accounts that no longer otherwise require it.

  • Some keywords may be reserved in MySQL 5.1 that were not reserved in MySQL 5.0. See Section 9.3, “Keywords and Reserved Words”.

  • The BACKUP TABLE, and RESTORE TABLE statements are deprecated. mysqldump or mysqlhotcopy can be used as alternatives.

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

  • The INSTALL PLUGIN and UNINSTALL PLUGIN statements that are used for the plugin API are new. So is the WITH PARSER clause for FULLTEXT index creation that associates a parser plugin with a full-text index. Section 22.2, “The MySQL Plugin API”.

SC API Changes
  • Incompatible change: As of MySQL 5.1.7, the mysql_stmt_attr_get() C API function returns a boolean rather than an unsigned int for STMT_ATTR_UPDATE_MAX_LENGTH. (Bug #16144)

Download this Manual
User Comments
  Posted by Luke Matthews on August 13, 2015
Defaults for performance_schema=off changed to 'on' at 5.6.6.
As result, this leads to higher memory consumption by mysqld (+400Mb RSS used).

Also see
Sign Up Login You must be logged in to post a comment.