Tip: You 'd better back up your data before installing any new version of software. Although mysql has provided the best quality services, you should back up your data when you use the software test release version. In general, when upgrading from mysql 4.1 to mysql 5.0, You need to perform the following steps:
Tip: You 'd better back up your data before installing any new version of software. Although mysql has provided the best quality services, you should back up your data when you use the software test release version. In general, when upgrading from mysql 4.1 to mysql 5.0, You need to perform the following steps:
Tip: You 'd better back up your data before installing any new version of software. Although mysql has provided the best quality services, you should back up your data when you use the software test release version. In general, when upgrading from mysql 4.1 to mysql 5.0, You need to perform the following steps:
• This part mainly checks the table items in the modified list created later to find whether there are table items that will affect your application. Pay attention to the Incompatible change options, which may not match earlier Mysql versions and should be noticed before the update.
• Read the history of MySQL 5.0 and understand the important new features you can use in MySQL 5.0. See Section D.1, "Changes in release 5.0.x (Production )"
• If you are running a Mysql server in Windows, see Section 2.3.15, "Upgrading MySQL on Windows. At the same time, you should also note that two Mysql servers in Windows are renamed. For details, see Section 2.3.9, "Selecting a MySQL Server type"
• MySQL 5.0 adds support for programs that store information. This option is required for the proc data table in your mysql database. To generate this file, you should run the mysql_fix_privilege_tables script. For details about this script, see Section 2.10.3, "Upgrading the Grant Tables ".
• The browser function is added to MySQL5.0. This option requires you to add additional special permission columns to the user and db tables in the mysql database. To generate these columns, you should run the mysql_fix_privilege_tables script. For details, see Section 2.10.3, "Upgrading the Grant Tables ".
• If you use the Replication function, see Section 6.6, "Upgrading a Replication Setup", which provides information about how to upgrade your Replication settings. Several visual behaviors have been changed between MySQL 4.1 and MySQL to ensure compatibility between MySQL and standard SQL. These changes may affect your application. The following column items mainly describe the changes that may affect the application. Therefore, you should pay special attention to these column items before updating MySQL to version 5.0.
Several changes:
• Incompatible changes: the index order of the end space in the TEXT column of the InnoDB and MyISAM tables has changed. Starting from version 5.0.3, the TEXT index is regarded as the space filled at the end (like the char, VARCHAR, and TEXT fields in the MySQL DATA type ). If you have an index in a text column, you should run the check table command on it. If this check returns an error, you need to re-create the index: if it is an InnoDB TABLE, clear and re-load the TABLE. If it is a MyISAM TABLE, run the optimize table or repair table command.
• Incompatible changes: MyISAM and InnoDB tables containing DECIMAL columns will be corrupted after being upgraded to MySQL 5.0.6. Before updating, clear the tables using mysqldump and reload them after the update. (These tables generated in MySQL5.0.6 are used in earlier versions, such as MySQL 5.0.3 to 5.0.5. The same mismatch may occur)
• Incompatible changes: For MySQL 5.0.3, the server no longer loads user-defined functions by default, unless they have at least one auxiliary tag (for example, xxx_init or xxx_deinit) in addition to the main function symbol ). This behavior can be ignored by the -- allow-suspicious-udfs option. See Section 25.2.3.6, "User-Defined Function Security Precautions ".
• Incompatible changes: logs updated in MySQL 5.0 will be deleted. If you activate it in advance, you actually activate binary logs.
• Incompatible changes: Support for ISAM storage has been deleted. If you have any ISAM tables, you should convert them before update. For example, to convert an ISAM table into a MyISAM storage mechanism, you can use the following declaration:
Alter table tbl_name ENGINE = MyISAM; the processing of other ISAM tables in your database is the same.
• Incompatible changes: in MySQL 5.0, RAID options for MyISAM tables have been deleted. If you have tables that use these options, you should convert them before updating. One way is to run the mysqldump command to clear these tables, edit the dump file to delete the RAID option in the create table declaration, and reload the dump file. Another method is to use the create table new_tbl... SELECT raid_tbl Declaration to CREATE a new raid table. However, the create table part in the declaration must contain enough information to regenerate the attributes of the column and index. Otherwise, the attributes of the column and index may be lost and will not appear in the new TABLE. See Section 13.1.5, "create table Syntax ".
• In a specific database, for RAID tables. the MYD file is stored in the database Directory, which is in the subdirectory with two hexadecimal values (00 to ff. After converting all the tables using the RAID option, these RAID-related subdirectories may still exist but can be deleted. After proving that they are empty, you can manually delete them. (If they are not empty, some RAID tables have not been converted ).
• In MySQL 5.0.6, the binary logs for storing routines and triggers have changed. This change mainly involves security, replication, and data recovery. For details about this, see Section 18.4, "Binary Logging of Stored Routines and Triggers "..
SQL changes:
• Incompatible changes: the namespace of the trigger has changed in MySQL 5.0.10. In previous versions, the trigger name is unique in each table. Currently, the schema (database) must be unique. The potential cause of this change is that the drop trigger syntax uses the schema name instead of the table name (the schema is selected when it can be ignored, that is, the current schema will be used ). When updating from MySQL 5 to MySQL 5.0.10 or later, you must delete all triggers and regenerate them. Otherwise, the drop trigger will not work after the update. For this purpose, we provide the following reference steps:
1. Upgrade MySQL to 5.0.10 to access the trigger information in the INFORMATION_SCHEMA.TRIGGERS table (it should be equally valid for triggers earlier than 5.0.10 .)
2. Use the following SELECT statement to delete all trigger definitions.
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;
This statement uses into outfile, so you must have the FILE permission. This file will be generated on the server host. You can select a different file name based on your preferences. For absolute security, check the trigger definitions in the triggers. SQL file. If necessary, back up the file.
1. Stop the server and delete all triggers by deleting all. TRG files in your database directory. Change the current path to your database directory and enter the following command: shell> rm */*. TRG
2. Start the server and use the triggers. SQL file to regenerate all triggers. In this example, the command is as follows:
1. mysql> delimiter //; 2. mysql> source/tmp/triggers. SQL // 3 use the SHOW TRIGGERS declaration to check whether all triggers are successfully generated.
• Incompatible changes: For MySQL 5.0.15, CHAR () functions return a binary string instead of a string in a set of connected character sets. An optional USING charset statement can be used to generate a specific character set. At the same time, variables longer than 256 characters may be used to generate a single character set. These changes may cause mismatch.
o CHAR(ORD('A')) = 'a' is no longer true: omysql> SELECT CHAR(ORD('A')) = 'a';o+----------------------+o| CHAR(ORD('A')) = 'a' |o+----------------------+o| 0 |o+----------------------+For comparison, you can add a USING statement or convert the result into a result string in a non-binary character set.
mysql> SELEC 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 |+--------------------------------------------+OCREATE TABLE... Select char (...) The VARBINARY column instead of the VARCHAR column is generated. To generate a VARCHAR column, use USING or CONVERT () to convert char () to a non-binary character set as described earlier.
O in earlier versions, the following statement inserts 0x00410041 ('A' as a ucs2 string) into the table. For MySQL 5.0.15, the statement uses the 0x414 value to insert a ucs2 character.
OCREATE TABLE t (ucs2_column CHAR (2) character set ucs2 );
OINSERT INTO t VALUES (CHAR (0x41,0x41 ));
• Incompatible changes: From MySQL 5.0.12, connections that naturally connect to and use the USING command include external connection variables, which are processed USING the SQL: 2003 standard. This change includes removing redundant output columns for the NATURAL and USING statements, and standardizing the order of output lists. The priority of the comma operator is still lower than that of the JOIN operator.
• The decimal series are stored in a more effective format. To convert a TABLE to a new DECIMAL (floating point number) type, you should execute an alter table command on it. The alter table command also changes the VARCHAR column of the TABLE to use the new VARCHAR column type. For information that may not match the original application, see Chapter 22 and Precision Math.
• MySQL 5.0.3 and later versions use a digital precision set (64-bit floating point number) to calculate point values while bypassing precise numbers. See Chapter 22, Precision Math
• For MySQL 5.0.3, the ending space will no longer be removed from the VARCHAR and VARBINARY columns. in MySQL 5.0.3 and later versions, the maximum length of VARCHAR and VARBINARY columns is 65,535 characters and 65,535 bytes.
Note: If you generate a table that contains the new VARCHAR or r VARBINARY columns in MySQL 5.0.3 or later versions, these tables will not work in versions earlier than MySQL 5.0.3. Delete these tables before downgrade and reload them after downgrade.
• For MySQL 5.0.3, BIT is a separate data type, rather than TINYINT (1. See Section 11.1.1, "Overview of Numeric Types"
• MySQL 5.0.2 adds several records for more strict control over discarded records containing invalid or missing values. See Section 5.3.2, "The Server SQL Mode". And Section 1.8.6.2, "Constraints on Invalid Data ". If you want to activate this control but continue to use the incorrect storage date, such as '2017-02-31 ', you should take -- SQL _mode = TRADITIONAL, ALLOW_INVALID_DATES to start the server.
• For MySQL 5.0.2, the SCHEMA and SCHEMAS keywords are accepted as the DATABASE and DATABASES meanings respectively. (However, "schemata" is a correct syntax and may appear in the system database and data table names of MySQL 5.0, so it can be used as an output keyword)
• In MySQL 5.0, user variables are case-insensitive. In MySQL 4.1, SET @ x = 0; SET @ X = 1; SELECT @ x; both variables are generated and 0 is returned. in MySQL 5.0, this command generates a variable and returns 1 at the same time.
• A startup selection item named innodb_table_locks is added. This option may cause table lock in InnoDB as well. This option is activated by default. This may cause application deadlocks using AUTOCOMMIT = 1 and lock tables. If your application encounters a deadlock after upgrade, you may need to add innodb_table_locks = 0 to your my. cnf file.
C api changes:
• Because the MySQL 5.0 server has a new execution function for Fractional data, if the server is used by client programs of earlier versions that are still linked to the client library of MySQL, A problem occurs. If a client uses the binary Client/Server protocol to execute the prepared statement that can generate numerical results, an error occurs: the error occurs because the client library of Version 4.1 does not support the new MYSQL_TYPE_NEWDECIMAL type added in version 5.0. On the server side, there is no way to block this new decimal data type. You can avoid this problem by reconnecting to the application from the MySQL 5.0 client library.
• The reconnect flag in the MYSQL structure can be set to 0 through mysql_real_connect. Only client programs that do not explicitly set the flag to 0 or 1 after mysql_real_connect () are changed. Automatic reconnection is considered dangerous by default (mainly due to table locks, temporary tables, user variables, and session variables may be lost after the reconnection ).
