MySQL upgrade considerations

Incompatible upgrade points

1. the character set or collation has changed in MySQL 5.1.21/5.1.23 and 5.1.24. Therefore, you may need to redo the index during the upgrade process.
Section 2.12.3 specifies the specific situation in which the index needs to be redone

2. For 5.0 compatibility, MySQL 5.1.29 changed the default BINLOG mode from mixed to statement.

3. MySQL 5.1.25 modified the server's method of processing prepared statement. This affects the SQL-level processing of prepared by the server.
The statement method (that is, the prepare statement) also affects processing at the client-server binary protocol level (that is, using
Mysql_stmt_perpare () c api function)

Before the upgrade, if you use prepared statement to modify the table or view metadata (that is, definitions ),
During statement, the server may crash; or it may only execute an error and then crash for a while. For example, if
Statement) delete a table and recreate the table with another definition.

After the upgrade, when you use the prepared statement to modify the table or view metadata, it will be detected. Then when it is
During execution, the server uses the atomic operation to re-prepare statement. In DDL statements, create/drop/alter/rename/
Truncate/analyze/optimize/repair table, metadata is modified. When the involved table or view
Definition cache is flush (it may be because the cache size limit is implicitly flush, or it may be caused by display call flush
), The server will also re-prepare statement.

Although re-prepare statment is an atomic operation, its use may lead to performance degradation.

Changes in table content (such as data changes caused by insert or update) will not result in re-prepare statement, nor will the SELECT statement
It will occur. However, unlike earlier versions of MySQL, for the same (prepared) select statement, the upgraded Server
Different data column sets may be returned, or a bunch of different types of columns will be returned. For example, when prepared statement is select * from
T1. Adding columns in Table T1 will cause the server to return a bunch of data with different structures during the next select operation.

The client library of the old version cannot handle the above changes. Therefore, for applications that use the new server and use the prepared statement
We recommend that you upgrade the client library at the same time.

During the above upgrade, the default value of the system variable table_definition_cache is changed from 128 to 256, which aims to reduce
The size limit causes implicit flush, thus reducing the probability of re-prepare statement. A new state variable is added.
Com_stmt_reprepare, used to record the number of times prepare statement

4. MySQL starts from 5.1.23 and cannot execute the show or describe statement in the stored routine and define a cursor for it. For example
If so, we recommend that you execute select on information_schema and define a cursor for it.

5. MySQL 5.1 supports a plug-in API that allows you to load and uninstall components during MySQL running. This API requires the mysql. plugin table.
After upgrading from a lower version of MySQL to a higher version, run the mysql_upgrade command to create this table (see section 4.4.8 ).
The plugin installation directory is specified by the system variable plugin_dir. Unlike earlier versions, this variable also defines where the server is loaded
UDF. This means that all UDF library files and plugin must be installed in the same directory.

6. The system variable table_cache is renamed table_open_cache.

7. stored programs (stored procedures, stored functions, triggers, and events) and views may contain non-ASCII characters
Question. The main problem is that when stored programs is converted to its physical storage
Failed to change.
To solve these problems, MySQL 5.1.21 changed the definition form of these objects. However, these changes will affect all stored programs.
And view. Use the Definition Format earlier than 5.1.21 to obtain the warning (for example, "no creation context ."). If you want to remove these
Warning, You need to dump stored programs and views from the old MySQL version (using mysqldump), and then in the new version
Use the new definition in MySQL to recreate them. When using mysqldump, set the -- default-character-set option to the original definition.
The non-ASCII character set used by these objects.

8. Starting from MySQL 5.1.20, mysqld_safe supports writing error logs to the syslog of the system (supporting log commands. In the control server
The new options -- Syslog and -- skip-Syslog should be used to replace -- log-error (see section 4.3.2 ).
After MySQL 5.1.21, the default option is -- skip-syslog. The purpose of this operation is to be consistent with that of MySQL versions earlier than 5.1.20.
MySQL 5.1.20 has the following behaviors:
1) the default option is -- syslog.
2) In some cases, syslog writing may fail.
Therefore, we recommend that you use -- skip-syslog or -- log-Error for 5.1.20.

9. Starting from MySQL 5.1.15, InnoDB rolls back only the last statement of the transaction when the transaction times out. Server also supports a new selection
Item: -- innodb_rollback_on_timeout indicates that InnoDB rolls back the entire transaction when the transaction times out (and MySQL 4.1 Behavior 1
To ).

10. Starting from MySQL 5.1.15, to open the system variable read_only, pay attention to the following situations:
1) when the display lock is held (such as lock tables or in a transaction holding the lock), if you try to open read_only, the server will return
Return Error
2) When other clients show that they hold the lock or are in a transaction holding the lock, if they attempt to open read_only, the client will be blocked
The lock is released or the transaction ends. Correspondingly, if this client attempts to open read_only
The lock request/start transaction will also be blocked until the read_only setting is successful.
3) when a global read lock (such as flush tables with read lock) is held, read_only can be successfully opened because
This operation does not involve table-level locks.

In earlier MySQL versions, requests that open read_only always return success immediately. Therefore, after read_only is enabled, the system data is still
However, it may be modified by other commands running at the same time.

11. From MySQL 5.1.13, the number of function names affected by ignore_space is reduced from 200 to about 30 (see section 8.2.4 ). This change
Improve parser consistency, but may cause incompatibility with some old SQL code in the following situations:
1) ignore_space is disabled
2) Add spaces (for example, Pi () and PI () after the same function name to distinguish between the storage function and the MySQL system function ())
For functions defined in MySQL 5.1.13 that are not affected by ignore_space, the preceding policy is invalid. To solve this problem, you can
Use the following method:
1) if the name of the stored function conflicts with that of the MySQL system function, add the schema name when using the function. For example (
Schema_name.pi () or schema_name.pi ())
2) modify the name of the storage function

12. For columns encoded with utf8, the full-text index parser mistakenly identifies some specific combinations of punctuation and space as words, leading to the return of search results.
Incorrect result. The solution to this problem depends on the modification of the full-text index used by MySQL 5.1.12. Therefore, for 5.1.12 (and its
Version extension ?), All columns with full-text index utf8 must be repaired using the repair table command:
Repair table tbl_name quick;

13. The storage engine can be plugged in or out at runtime. Therefore, the previously disabled and unavailable features are no longer applicable. From MySQL 5.1.22
This affects the no_engine_substitution SQL mode (see section 5.1.7 ).

14. MySQL 5.1.6 modified the full-text index structure. Therefore, if you upgrade from an earlier version to 5.1.6 or later, you need to use repair table ..
The quick statement repairs all tables that contain full-text indexes.

15. MySQL 5.1.6 writes general query and slow query to the system table by default. From MySQL 5.1.21, default write
Change the input location to a file. This modification aims to be compatible with MySQL 5.0. Of course, it is incompatible with MySQL 5.1.6. If from 5.1.6 or
To version 5.1.21, set -- log-output = table to keep the log writing behavior of the server unchanged.

16. For an Enum column, if its value range contains commas, the comma is mapped to 0xff during internal processing. Obviously, this policy will export
The comma and 0xff values cannot be distinguished. This problem has been fixed since MySQL 5.1.15. However
Level, you need to use mysqldump to export tables that contain such data, and then re-import.

17. If the delete statement for deleting multiple tables contains a fuzzy alias, uncertain side effects may occur, such
The data to be deleted. For example, delete from T1 as A2 using T1 as A1 inner join T2 as A2;
Since MySQL 5.1.23, aliases are restricted to defining tables only. In other cases, you can only use an alias instead of defining it. Therefore
You must check and override the SQL statements that use aliases.

18. If the/*... */structure contained in the SQL statement does not end with */, Parser considers it correct. For example, select 1/* + 2. Slave
MySQL 5.1.23 and later, Parser reports an error.
This modification may cause some compatibility issues. Bug #26302 (reference http://bugs.mysql.com/26302) is composed of views, stored
Routines, trigger, and end of comments in the event */is truncated. In the same case, these types of definitions will not be new
Parser accept. Therefore, you need to rebuild them during upgrade.

19. From MySQL 5.1.8, type = engine_name is equivalent to engine = engine_name. However, the former causes
Warning. Since MySQL 5.2.5, the former is discarded. Type is not recommended since MySQL 4.0.

20. The trigger namespace has been modified since MySQL 5.0.10. Previously, the names of triggers belonging to the same table must be different. Now belongs to the same
The hit of a schema (database) Trigger must be unique. One impact of this change is that drop trigger now uses a schema
Name instead of a table name (schema can be omitted. The default value is the current schema ). Therefore, if the version is upgraded
The sender must be re-created as follows:
1) upgrade to MySQL 5.0.10 or later. You can find the trigger information in the information_schema.triggers table.
(This should take effect even for triggers earlier than 5.0.10)
2) dump all trigger information 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_statment,' // ') into OUTFILE
'/Tmp/triggers. SQL' from information_schema.triggers as t;
This statement uses into OUTFILE, so the file permission is required for execution. The output file is generated on the server host. If you need to repair
Change the file name and directly modify the corresponding fields in the preceding statement. To be 100% secure, check the trigger definition in the output file,
You can also back up this file.
3) Stop MySQL server. Delete all. Trg files under the data folder (delete all triggers ). Execute this
Commands are as follows (for example, in Linux and in the data directory ):
Shell> RM */*. Trg
4) Start MySQL server and use the trigger. SQL file to recreate all triggers. The example here is:
Mysql> delimiter //;
Mysql> source/tmp/triggers. SQL //
5) use show triggers to check the status of all triggers.

21. MySQL 5.1.6 introduces the trigger permission. Previously, if you want to create or delete a trigger, you need the super permission. Now you only need the trigger
Permission. Therefore, you do not need to grant the super permission to the user to create a trigger. This is an improvement in security. However
The definer clause of the previous trigger must have the super permission, and now the trigger permission is required. When upgrading across this version, pay attention
Modify the grant table as described in section 4.4.8. The modification process grants the trigger permission to all users with super permissions. If you modify
If the grant table fails, the trigger will become invalid (after the grant table is modified successfully, the super permission can be managed more rationally ).

22. From MySQL 5.1.7, the c api function mysql_stmt_attr_get () returns a Boolean value. The previous return value is
Unsigned int, indicating stmt_attr_update_max_length (refer to bug #16144: http://bugs.mysql.com/16144 ).

Known issues

1. Before MySQL 5.1.30, the check table... for upgrade statement did not check an incompatible change to collaction. The
The changes are introduced by MySQL 5.1.24 (mysqlcheck and mysql_upgrade are affected because they call this statement ). In
5.1.30 before the problem is fixed, the binary version is upgraded (that is, the table is not dumped using mysqldump before the upgrade, and
After the upgrade, re-load the dump files) may damage the table. After the repair, check table... for upgrade can detect
Describes the problem and performs a warning operation on the table to be repaired. However, this fix is not backward compatible, so if you need to downgrade MySQL
Problems may occur:
1) execute a binary upgrade and finally get a version that contains the fix.
2) execute check table... for upgrade (or mysqlcheck, mysql_upgrade) to upgrade the table.
3) execute a binary downgrade and finally get a version that does not contain the fix.
The solution is to use mysqldump to dump the table before the downgrade, and then load the dump file after the downgrade. You can also choose to delete and recreate
Related indexes are used as an alternative.

2. for table names that contain non-ASCII characters, MySQL introduces the encoding concept (see section 8.2.3 ). Upgrade from MySQL 5.0 to MySQL 5.1
Then, the server detects the table names that contain non-ASCII characters and adds the # mysql50 # prefix before them. Then execute mysqlcheck
You can encode it according to the new format and remove the # mysql50 # prefix. However, these operations are only valid for tables and are not valid for views. To solve this problem
You can delete and recreate the view.
This problem was fixed in MySQL 5.1.23.

3. If you use mysqlcheck (or mysql_upgrade, it will call mysqlcheck) to upgrade from MySQL 5.0 to MySQL 5.1, if some
The table name must be enclosed in quotation marks. The solution is to rename the table first:
Rename table 'table' Le _ a' to table_a;
Rename table 'table B 'to table_ B;
After the name is changed, run the mysql_upgrade program. Then change the table name back:
Rename table table_a to 'table' Le _ ';
Rename table table_ B to 'table B ';
This problem was fixed in MySQL 5.1.23.

4. When creating a view, the server will create the arc directory under the database directory and maintain useless. frm file backup. MySQL
5.1.29 no longer creates or rename these backups, nor creates the arc directory. However, if the MySQL server is in the following situation:
Problems may occur during degradation:
1) create a view v_orig in MySQL 5.1.29 or later
2) Change v_orig to v_new, and then change it back to v_orig.
3) downgrade to 5.1.x, and then run mysql_upgrade.
4) Changing v_orig to v_new will fail.
The solution is:
1) Use mysqldump to dump data before downgrading and re-load the dump file after downgrading.
2) After the downgrade, the view is not renamed, but is deleted and rebuilt.