2.11. Upgrading MySQL

MySQL 5.0

previous page next page

2.11. Upgrading MySQL

As a general rule, we recommend that when upgrading from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0.

The following items form a checklist of things that you should do whenever you perform an upgrade:

  • Before upgrading from MySQL 4.1 to 5.0, read Section 2.11.2, “Upgrading from MySQL 4.1 to 5.0”) as well as Appendix D, MySQL Change History. These provide information about features that are new in MySQL 5.0 or differ from those found in MySQL 4.1. If you wish to upgrade from a release series previous to MySQL 4.1, you should upgrade to each successive release series in turn until you have reached MySQL 4.1, and then proceed with the upgrade to MySQL 5.0. For information on upgrading from MySQL 4.1 or earlier releases, see the MySQL 3.23, 4.0, 4.1 Reference Manual.

  • Before you perform an upgrade, back up your databases, including the database that contains the grant tables.

  • Some releases of MySQL introduce incompatible changes to tables. (Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.) Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.

    To avoid problems due to such changes, after you upgrade to a new version of MySQL, you should run mysql_upgrade to check your tables (and repair them if necessary), and to update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

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

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

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

You can always move the MySQL format files and data files between different versions on the same architecture as long as you stay within versions for the same release series of MySQL. If you change the character set when running MySQL, you must run myisamchk -r -q --set-collation= on all tables. Otherwise, your indexes may not be ordered correctly, because changing the character set may also change the sort order.

If you are cautious about using new versions, you can always rename your old mysqld before installing a newer one. For example, if you are using MySQL 4.1.13 and want to upgrade to 5.0.10, rename your current server from mysqld to mysqld-4.1.13. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld.

If, after an upgrade, you experience problems with recompiled client programs, such as or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, you should check the date for your file and library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries.

If problems occur, such as that the new mysqld server does not start or that you cannot connect without a password, verify that you do not have an old file from your previous installation. You can check this with the option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active file that affects server or client operation.

It is a good idea to rebuild and reinstall the Perl module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP extension and the Python module.

2.11.1. Upgrading from MySQL 5.0 to 5.1

When upgrading a 5.0 installation to 5.0.10 or above note that 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 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

2.11.2. Upgrading from MySQL 4.1 to 5.0

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

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

  • Check the items in the change lists found later in this section to see whether any of them might affect your applications. Note particularly any that are marked Incompatible change. These result in incompatibilities with earlier versions of MySQL, and may require your attention before you upgrade.

  • Some releases of MySQL introduce incompatible changes to tables. (Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.) Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.

    To avoid problems due to such changes, after you upgrade to a new version of MySQL, you should check your tables (and repair them if necessary), and update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • Read the MySQL 5.0 change history to see what significant new features you can use in 5.0. See Section D.1, “Changes in release 5.0.x (Production)”.

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

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

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

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

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

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

Server Changes:

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

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

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

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

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

    ALTER TABLE  ENGINE = MyISAM;

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

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

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

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

SQL Changes:

  • Incompatible change: Beginning with MySQL 5.0.12, natural joins and joins with , including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for joins and joins specified with a clause and proper ordering of output columns. The precedence of the comma operator also now is lower compared to , , 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 13.2.7.1, “ Syntax”.

  • Incompatible change: Previously, a lock wait timeout caused to roll back the entire current transaction. As of MySQL 5.0.13, it rolls back only the most recent SQL statement.

  • Incompatible change: The namespace for triggers has changed in MySQL 5.0.10. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that syntax 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 previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them or will not work after the upgrade. Here is a suggested procedure for doing this:

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

    2. Dump all trigger definitions using the following statement: 

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

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

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

      shell>

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

      mysql> 
mysql>

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

  • Incompatible change: As of MySQL 5.0.15, the function returns a binary string rather than a string in the connection character set. An optional clause may be used to produce a result in a specific character set instead. Also, arguments larger than 256 produce multiple characters. They are no longer interpreted modulo 256 to produce a single character each. These changes may cause some incompatibilities:

    • is no longer true: 

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

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

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

    • produces a column, not a column. To produce a column, use or as just described to convert the result into a non-binary character set.

    • Previously, the following statements inserted the value ( as a string) into the table: 

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

      As of MySQL 5.0.15, the statements insert a single character 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.

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

    mysql> 
+----------------------+
| i - 1                |
+----------------------+
| 18446744073709551615 |
+----------------------+

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

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

      mysql> 
+------+
| i    |
+------+
|    0 |
+------+

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

      mysql> 
+------+
| i    |
+------+
|  255 |
+------+

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

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

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

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

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

  • Incompatible change: Before MySQL 4.1.13/5.0.8, conversion of values to numeric form by adding zero produced a result in format. The result of is now in format.

  • Incompatible change: In MySQL 4.1.12/5.0.6, the behavior of and has changed when the and values both are empty. Formerly, a column was read or written the display width of the column. For example, 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 for MySQL 4.1.12/5.0.6 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use and . For more information, see Section 13.2.5, “ Syntax”.

  • Incompatible change: The implementation of has changed in MySQL 5.0.3. You should make your applications aware of this change. For information about this change, and about possible incompatibilities with old applications, see Chapter 21, Precision Math.

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

    A consequence of the change in handling of the and fixed-point data types is that the server is more strict to follow standard SQL. For example, a data type of stores a maximum value of 99.9. Before MySQL 5.0.3, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. As of MySQL 5.0.3, the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 5.0.3 and that contain floating-point data not strictly legal for the data type, you should alter the data types of those columns. For example: 

    ALTER TABLE  MODIFY  DECIMAL(4,1);

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

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

  • Incompatible change: and tables created with columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) If you have such tables, check and repair them with mysql_upgrade after upgrading. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

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

    SHOW /*!50002 GLOBAL */ STATUS;

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

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

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

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

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

    This issue can be resolved as follows:

    1. For each table containing columns, execute the statement 

      ALTER TABLE  ENGINE=;

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

      ALTER TABLE mytable ENGINE=MYISAM;

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

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

      UPDATE  SET  = RTRIM();

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

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

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

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

  • As of MySQL 5.0.3, is a separate data type, not a synonym for . See Section 11.1.1, “Overview of Numeric Types”.

  • MySQL 5.0.2 adds several SQL modes that allow stricter control over rejecting records that have invalid or missing values. See Section 5.2.5, “The Server SQL Mode”, and Section 1.9.6.2, “Constraints on Invalid Data”. If you want to enable this control but continue to use MySQL's capability for storing incorrect dates such as , you should start the server with .

  • As of MySQL 5.0.2, the and keywords are accepted as synonyms for and , 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.)

  • A new startup option named was added that causes to also acquire table locks. This option is enabled by default. This can cause deadlocks in applications that use and . If you application encounters deadlocks after upgrading, you may need to add to your file.

C API Changes:

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

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

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

  • The flag in the structure is set to 0 by . Only those client programs which did not explicitly set this flag to 0 or 1 after 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).

2.11.3. Copying MySQL Databases to Another Machine

You can copy the , , and files for tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 14.1, “The Storage Engine”.

In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.

Use mysqldump --help to see what options are available. If you are moving the data to a newer version of MySQL, you should use mysqldump --opt to take advantage of any optimizations that result in a dump file that is smaller and can be processed more quickly.

The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located: 

shell> ' create 
shell>  | mysql -h ''

If you want to copy a database from a remote machine over a slow network, you can use these commands: 

shell> 
shell> ' --opt --compress  | mysql

You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this: 

shell>  | gzip > .gz

Transfer the file containing the database contents to the target machine and run these commands there: 

shell> 
shell> .gz | mysql

You can also use mysqldump and mysqlimport to transfer the database. For large tables, this is much faster than simply using mysqldump. In the following commands, represents the full pathname of the directory you use to store the output from mysqldump.

First, create the directory for the output files and dump the database: 

shell> 
shell>

Then transfer the files in the directory to some corresponding directory on the target machine and load the files into MySQL there: 

shell>            # create database
shell> /*.sql | mysql    # create tables in database
shell>  /*.txt   # load data into tables

Do not forget to copy the database because that is where the grant tables are stored. You might have to run commands as the MySQL user on the new machine until you have the database in place.

After you import the database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.

previous page start next page