2.5 UpgradingDowngrading MySQL

2.5 Upgrading/Downgrading 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 you should do whenever you perform an upgrade:

• Read the change log in the online manual for the release series to which you are upgrading to see what new features you can use. For example, before upgrading from MySQL 4.1 to 5.0, read the 5.0 news items. See http://dev.mysql.com/doc/mysql/en/News.html.

• Before you do an upgrade, back up your databases.

• If you are running MySQL Server on Windows, see Section 2.5.7, "Upgrading MySQL Under Windows."

• An upgrade may involve changes to the grant tables that are stored in the mysql database. Occasionally new columns or tables are added to support new features. To take advantage of these features, be sure that your grant tables are up to date. The upgrade procedure is described in Section 2.5.8, "Upgrading the Grant Tables."

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

• If you install a MySQL-Max distribution that includes a server named mysqld-max , then upgrade later to a non-Max version of MySQL, mysqld_safe will still attempt to run the old mysqld-max server. If you perform such an upgrade, you should manually remove the old mysqld-max server 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. The current production release series is 4.0. If you change the character set when running MySQL, you must run myisamchk -r -q --set- character-set = charset on all MyISAM tables. Otherwise , your indexes may not be ordered correctly, because changing the character set may also change the sort order.

If you upgrade or downgrade from one release series to another, there may be incompatibilities in table storage formats. In this case, you can use mysqldump to dump your tables before upgrading. After upgrading, reload the dump file using mysql to re-create your tables.

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.0.18 and want to upgrade to 4.1.1, rename your current server from mysqld to mysqld-4.0.18 . 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 Commands out of sync 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 mysql.h file and libmysqlclient.a 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 doesn't want to start or that you can't connect without a password, verify that you don't have some old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults ). If this displays anything other than the program name , you have an active my.cnf file that affects server or client operation.

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

2.5.1 Upgrading from Version 4.1 to 5.0

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

• Read the 5.0 news items in the online manual to see what significant new features you can use in 5.0. See http://dev.mysql.com/doc/mysql/en/News.html.

• If you are running MySQL Server on Windows, see Section 2.5.7, "Upgrading MySQL Under Windows."

• MySQL 5.0 adds support for stored procedures. This support requires the proc table in the mysql database. To create this file, you should run the mysql_fix_privilege_tables script as described in Section 2.5.8, "Upgrading the Grant Tables."

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

2.5.2 Upgrading from Version 4.0 to 4.1

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

• Check the items in the change lists found later in this section to see whether any of them might affect your applications.

• Read the 4.1 news items in the online manual to see what significant new features you can use in 4.1. See http://dev.mysql.com/doc/mysql/en/News.html.

• If you are running MySQL Server on Windows, see Section 2.5.7, "Upgrading MySQL Under Windows."

  Important note: Early alpha Windows distributions for MySQL 4.1 do not contain an installer program. See Section 2.2.1.2, "Installing a Windows Binary Distribution," for instructions on how to install such a distribution.

• After upgrading, update the grant tables to have the new longer Password column that is needed for secure handling of passwords. The procedure uses mysql_fix_privilege_tables and is described in Section 2.5.8, "Upgrading the Grant Tables." Implications of the password-handling change for applications are given later in this section.

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

• The Berkeley DB table handler is updated to DB 4.1 (from 3.2) which has a new log format. If you have to downgrade back to 4.0 you must use mysqldump to dump your BDB tables in text format and delete all log. XXXXXXXXXX files before you start MySQL 4.0 and reload the data.

• Character set support has been improved. If you have table columns that store character data represented in a character set that the 4.1 server now supports directly, you can convert the columns to the proper character set using the instructions given later in this section.

• If you are using an old DBD-mysql module ( Msql-MySQL-modules ) you have to upgrade to use the newer DBD-mysql module. Anything above DBD-mysql 2. xx should be fine.

  If you don't upgrade, some methods (such as DBI->do() ) will not notice error conditions correctly.

Several visible behaviors have changed between MySQL 4.0 and MySQL 4.1 to fix some critical bugs and make MySQL more compatible with standard SQL. These changes may affect your applications.

Some of the 4.1 behaviors can be tested in 4.0 before performing a full upgrade to 4.1. We have added to later MySQL 4.0 releases (from 4.0.12 on) a --new startup option for mysqld . See Section 4.2.1, "mysqld Command-Line Options."

This option gives you the 4.1 behavior for the most critical changes. You can also enable these behaviors for a given client connection with the SET @@new=1 command, or turn them off if they are on with SET @@new=0 .

If you believe that some of the 4.1 changes will affect you, we recommend that before upgrading to 4.1, you download the latest MySQL 4.0 version and run it with the --new option by adding the following to your config file:

 [mysqld-4.0] new 

That way you can test the new behaviors in 4.0 to make sure that your applications work with them. This will help you have a smooth, painless transition when you perform a full upgrade to 4.1 later. Putting the --new option in the [mysqld-4.0] option group ensures that you don't accidentally later run the 4.1 version with the --new option.

The following lists describe changes that may affect applications and that you should watch out for when upgrading to version 4.1:

Server Changes:

• All tables and string columns now have a character set. Character set information is displayed by SHOW CREATE TABLE and mysqldump . (MySQL versions 4.0.6 and above can read the new dump files; older versions cannot.) This change should not affect applications that use only one character set.

• Normally, the server runs using the latin1 character set by default. If you have been storing column data that actually is in some other character set that the 4.1 server now supports directly, you can convert the column. However, you should avoid trying to convert directly from latin1 to the "real" character set. This may result in data loss. Instead, convert the column to a binary column type, and then from the binary type to a non-binary type with the desired character set. Conversion to and from binary involves no attempt at character value conversion and preserves your data intact. For example, suppose that you have a 4.0 table with three columns that are used to store values represented in latin1 , latin2 , and utf8 :

  
  

   CREATE TABLE t (     latin1_col CHAR(50),     latin2_col CHAR(100),     utf8_col CHAR(150) ); 

  
  

  After upgrading to MySQL 4.1, you want to convert this table to leave latin1_col alone but change the latin2_col and utf8_col columns to have character sets of latin2 and utf8 . First, back up your table, then convert the columns as follows :

  
  

   ALTER TABLE t MODIFY latin2_col BINARY(100); ALTER TABLE t MODIFY utf8_col BINARY(150); ALTER TABLE t MODIFY latin2_col CHAR(100) CHARACTER SET latin2; ALTER TABLE t MODIFY utf8_col CHAR(150) CHARACTER SET utf8; 

  
  

  The first two statements "remove" the character set information from the latin2_col and utf8_col columns. The second two statements assign the proper character sets to the two columns.

  If you like, you can combine the to-binary conversions and from-binary conversions into single statements:

  
  

   ALTER TABLE t     MODIFY latin2_col BINARY(100),     MODIFY utf8_col BINARY(150); ALTER TABLE t     MODIFY latin2_col CHAR(100) CHARACTER SET latin2,     MODIFY utf8_col CHAR(150) CHARACTER SET utf8; 

  
  

• The table definition format used in .frm files has changed slightly in 4.1. MySQL 4.0 versions from 4.0.11 on can read the new .frm format directly, but older versions cannot. If you need to move tables from 4.1 to a version earlier than 4.0.11, you should use mysqldump . See Section 7.8, "The mysqldump Database Backup Program."

• Important note: If you upgrade to MySQL 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1.0! That is because, for earlier versions, InnoDB is not aware of multiple tablespaces.

• If you are running multiple servers on the same Windows machine, you should use a different -- shared-memory -base-name option for each server.

• The interface to aggregated UDF functions has changed a bit. You must now declare a xxx _clear() function for each aggregate function XXX () .

Client Changes:

• mysqldump now has the --opt and --quote- names options enabled by default. You can turn them off with --skip-opt and --skip-quote-names .

SQL Changes:

• String comparison now works according to SQL standard: Instead of stripping end spaces before comparison, we now extend the shorter string with spaces. The problem with this is that now 'a' > 'a\t' , which it wasn't before. If you have any tables where you have a CHAR or VARCHAR column in which the last character in the column may be less than ASCII(32) , you should use REPAIR TABLE or myisamchk to ensure that the table is correct.

• When using multiple-table DELETE statements, you should use the alias of the tables from which you want to delete, not the actual table name. For example, instead of this:

  
  

   DELETE test FROM test AS t1, test2 WHERE ... 

  
  

  Do this:

  
  

   DELETE t1 FROM test AS t1, test2 WHERE ... 

  
  

• TIMESTAMP is now returned as a string in 'YYYY-MM-DD HH:MM:SS' format. (The --new option can be used from 4.0.12 on to make a 4.0 server behave as 4.1 in this respect.) If you want to have the value returned as a number (as MySQL 4.0 does) you should add +0 to TIMESTAMP columns when you retrieve them:

  
  

   mysql>  SELECT ts_col + 0 FROM   tbl_name   ;  

  
  

  Display widths for TIMESTAMP columns are no longer supported. For example, if you declare a column as TIMESTAMP(10) , the (10) is ignored.

  These changes were necessary for SQL standards compliance. In a future version, a further change will be made (backward compatible with this change), allowing the timestamp length to indicate the desired number of digits for fractions of a second.

• Binary values such as 0xFFDF now are assumed to be strings instead of numbers . This fixes some problems with character sets where it's convenient to input a string as a binary value. With this change, you should use CAST() if you want to compare binary values numerically as integers:

  
  

   mysql>  SELECT CAST(0xFEFF AS UNSIGNED INTEGER)  ->  < CAST(0xFF AS UNSIGNED INTEGER);  -> 0 

  
  

  If you don't use CAST() , a lexical string comparison will be done:

  
  

   mysql>  SELECT 0xFEFF < 0xFF;  -> 1 

  
  

  Using binary items in a numeric context or comparing them using the = operator should work as before. (The --new option can be used from 4.0.13 on to make a 4.0 server behave as 4.1 in this respect.)

• For functions that produce a DATE , DATETIME , or TIME value, the result returned to the client now is fixed up to have a temporal type. For example, in MySQL 4.1, you get this result:

  
  

   mysql>  SELECT CAST('2001-1-1' AS DATETIME);  -> '2001-01-01 00:00:00' 

  
  

  In MySQL 4.0, the result is different:

  
  

   mysql>  SELECT CAST('2001-1-1' AS DATETIME);  -> '2001-01-01' 

  
  

• DEFAULT values no longer can be specified for AUTO_INCREMENT columns. (In 4.0, a DEFAULT value is silently ignored; in 4.1, an error occurs.)

• LIMIT no longer accepts negative arguments. Use some large number (maximum 18446744073709551615) instead of “1.

• SERIALIZE is no longer a valid mode value for the sql_mode variable. You should use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE instead. SERIALIZE is no longer valid for the --sql-mode option for mysqld , either. Use --transaction-isolation=SERIALIZABLE instead.

C API Changes:

• Some C API calls such as mysql_real_query() now return 1 on error, not -1 . You may have to change some old applications if they use constructs like this:

  
  

   if (mysql_real_query(mysql_object, query, query_length) == -1) {   printf("Got error"); } 

  
  

  Change the call to test for a non-zero value instead:

  
  

   if (mysql_real_query(mysql_object, query, query_length) != 0) {   printf("Got error"); } 

  
  

Password-Handling Changes:

The password hashing mechanism has changed in 4.1 to provide better security, but this may cause compatibility problems if you still have clients that use the client library from 4.0 or earlier. (It is very likely that you will have 4.0 clients in situations where clients connect from remote hosts that have not yet upgraded to 4.1.) The following list indicates some possible upgrade strategies. They represent various tradeoffs between the goal of compatibility with old clients and the goal of security.

• Only upgrade the client to use 4.1 client libraries (not the server). No behavior will change (except the return value of some API calls), but you cannot use any of the new features provided by the 4.1 client/server protocol, either. (MySQL 4.1 has an extended client/server protocol that offers such features as prepared statements and multiple result sets.)

• Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table so that it can hold long password hashes. But run the server with the --old-passwords option to provide backward compatibility that allows pre-4.1 clients to continue to connect to their short-hash accounts. Eventually, when all your clients are upgraded to 4.1, you can stop using the --old-passwords server option. You can also change the passwords for your MySQL accounts to use the new more secure format.

• Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table. If you know that all clients also have been upgraded to 4.1, don't run the server with the --old-passwords option. Instead, change the passwords on all existing accounts so that they have the new format. A pure-4.1 installation is the most secure.

Further background on password hashing with respect to client authentication and password-changing operations may be found in Section 4.4.9, "Password Hashing in MySQL 4.1," and Section A.2.3, "Client does not support authentication protocol."

2.5.3 Upgrading from Version 3.23 to 4.0

In general, you should do the following when upgrading to MySQL 4.0 from 3.23:

• Check the items in the change lists found later in this section to see whether any of them might affect your applications.

• Read the 4.0 news items in the online manual to see what significant new features you can use in 4.0. See http://dev.mysql.com/doc/mysql/en/News.html.

• If you are running MySQL Server on Windows, see Section 2.5.7, "Upgrading MySQL Under Windows."

• After upgrading, update the grant tables to add new privileges and features. The procedure uses the mysql_fix_privilege_tables script and is described in Section 2.5.8, "Upgrading the Grant Tables."

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

• Edit any MySQL startup scripts or option files to not use any of the deprecated options described later in this section.

• Convert your old ISAM files to MyISAM files. One way to do this is with the mysql_convert_table_format script. (This is a Perl script; it requires that DBI be installed.) To convert the tables in a given database, use this command:

  
  

   shell>  mysql_convert_table_format database   db_name  

  
  

  Note that this should be used only if all tables in the given database are ISAM or MyISAM tables. To avoid converting tables of other types to MyISAM , you can explicitly list the names of your ISAM tables after the database name on the command line.

  Individual tables can be changed to MyISAM by using the following ALTER TABLE statement for each table to be converted:

  
  

   mysql>  ALTER TABLE   tbl_name   TYPE=MyISAM;  

  
  

  If you are not sure of the table type for a given table, use this statement:

  
  

   mysql>  SHOW TABLE STATUS LIKE '   tbl_name   ';  

  
  

• Ensure that you don't have any MySQL clients that use shared libraries (like the Perl DBD::mysql module). If you do, you should recompile them, because the data structures used in libmysqlclient.so have changed. The same applies to other MySQL interfaces as well, such as the Python MySQLdb module.

MySQL 4.0 will work even if you don't perform the preceding actions, but you will not be able to use the new security privileges in MySQL 4.0 and you may run into problems when upgrading later to MySQL 4.1 or newer. The ISAM file format still works in MySQL 4.0, but is deprecated and is not compiled in by default as of MySQL 4.1. MyISAM tables should be used instead.

Old clients should work with a Version 4.0 server without any problems.

Even if you perform the preceding actions, you can still downgrade to MySQL 3.23.52 or newer if you run into problems with the MySQL 4.0 series. In this case, you must use mysqldump to dump any tables that use full-text indexes and reload the dump file into the 3.23 server. This is necessary because 4.0 uses a new format for full-text indexing.

The following lists describe changes that may affect applications and that you should watch out for when upgrading to version 4.0:

Server Changes:

• MySQL 4.0 has a lot of new privileges in the mysql.user table. See Section 4.4.3, "Privileges Provided by MySQL."

  To get these new privileges to work, you must update the grant tables. The procedure is described in Section 2.5.8, "Upgrading the Grant Tables." Until you do this, all accounts have the SHOW DATABASES , CREATE TEMPORARY TABLES , and LOCK TABLES privileges. SUPER and EXECUTE privileges take their value from PROCESS . REPLICATION SLAVE and REPLICATION CLIENT take their values from FILE .

  If you have any scripts that create new MySQL user accounts, you may want to change them to use the new privileges. If you are not using GRANT commands in the scripts, this is a good time to change your scripts to use GRANT instead of modifying the grant tables directly.

  From version 4.0.2 on, the option --safe-show-database is deprecated (and no longer does anything). See Section 4.3.3, "Startup Options for mysqld Concerning Security."

  If you get Access denied errors for new users in version 4.0.2 and up, you should check whether you need some of the new grants that you didn't need before. In particular, you will need REPLICATION SLAVE (instead of FILE ) for new slave servers.

• safe_mysqld has been renamed to mysqld_safe . For backward compatibility, binary distributions will for some time include safe_mysqld as a symlink to mysqld_safe .

• InnoDB support is now included by default in binary distributions. If you build MySQL from source, InnoDB is configured in by default. If you do not use InnoDB and want to save memory when running a server that has InnoDB support enabled, use the --skip-innodb server startup option. To compile MySQL without InnoDB support, run configure with the --without-innodb option.

• Values for the startup parameters myisam_max_extra_sort_file_size and myisam_max_extra_sort_file_size now are given in bytes (they were given in megabytes before 4.0.3).

• mysqld now has the option --temp-pool enabled by default because this gives better performance with some operating systems (most notably Linux).

• The mysqld startup options --skip-locking and --enable-locking were renamed to --skip-external-locking and --external-locking .

• External system locking of MyISAM / ISAM files is now turned off by default. You can turn this on with --external-locking . (However, this is never needed for most users.)

• The following startup variables and options have been renamed:

  Old Name	New Name
  myisam_bulk_insert_tree_size	bulk_insert_buffer_size
  query_cache_startup_type	query_cache_type
  record_buffer	read_buffer_size
  record_rnd_buffer	read_rnd_buffer_size
  sort_buffer	sort_buffer_size
  warnings	log-warnings
  --err-log	--log-error (for mysqld_safe )

  
  

  The startup options record_buffer , sort_buffer , and warnings will still work in MySQL 4.0 but are deprecated.

SQL Changes:

• The following SQL variables have been renamed:

  Old Name	New Name
  SQL_BIG_TABLES	BIG_TABLES
  SQL_LOW_PRIORITY_UPDATES	LOW_PRIORITY_UPDATES
  SQL_MAX_JOIN_SIZE	MAX_JOIN_SIZE
  SQL_QUERY_CACHE_TYPE	QUERY_CACHE_TYPE

  
  

  The old names still work in MySQL 4.0 but are deprecated.

• You have to use SET GLOBAL SQL_SLAVE_SKIP_COUNTER= skip_count instead of SET SQL_SLAVE_SKIP_COUNTER= skip_count .

• SHOW MASTER STATUS now returns an empty set if binary logging is not enabled.

• SHOW SLAVE STATUS now returns an empty set if the slave is not initialized .

• SHOW INDEX has two more columns than it had in 3.23 ( Null and Index_type ).

• The format of SHOW OPEN TABLES has changed.

• ORDER BY col_name DESC sorts NULL values last, as of MySQL 4.0.11. In 3.23 and in earlier 4.0 versions, this was not always consistent.

• CHECK , LOCALTIME , and LOCALTIMESTAMP now are reserved words.

• DOUBLE and FLOAT columns now honor the UNSIGNED flag on storage (before, UNSIGNED was ignored for these columns).

• The result of all bitwise operators ( , & , << , >> , and ~ ) is now unsigned. This may cause problems if you are using them in a context where you want a signed result.

  Note: When you use subtraction between integer values where one is of type UNSIGNED , the result will be unsigned. In other words, before upgrading to MySQL 4.0, you should check your application for cases in which you are subtracting a value from an unsigned entity and want a negative answer or subtracting an unsigned value from an integer column. You can disable this behavior by using the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld . See Section 4.2.2, "The Server SQL Mode."

• You should use integers to store values in BIGINT columns (instead of using strings, as you did in MySQL 3.23). Using strings will still work, but using integers is more efficient.

• In MySQL 3.23, INSERT INTO ... SELECT always had IGNORE enabled. As of 4.0.1, MySQL will stop (and possibly roll back) by default in case of an error unless you specify IGNORE .

• You should use TRUNCATE TABLE when you want to delete all rows from a table and you don't need to obtain a count of the number of rows that were deleted. ( DELETE FROM tbl_name returns a row count in 4.0, and TRUNCATE TABLE is faster.)

• You will get an error if you have an active transaction or LOCK TABLES statement when trying to execute TRUNCATE TABLE or DROP DATABASE .

• To use MATCH ... AGAINST (... IN BOOLEAN MODE) full-text searches with your tables, you must rebuild their indexes with REPAIR TABLE tbl_name USE_FRM . If you attempt a boolean full-text search without rebuilding the indexes this way, the search will return incorrect results.

• LOCATE() and INSTR() are case sensitive if one of the arguments is a binary string. Otherwise they are case insensitive.

• STRCMP() now uses the current character set when performing comparisons. This makes the default comparison behavior not case sensitive unless one or both of the operands are binary strings.

• HEX( string ) now returns the characters in string converted to hexadecimal. If you want to convert a number to hexadecimal, you should ensure that you call HEX() with a numeric argument.

• RAND( seed ) returns a different random number series in 4.0 than in 3.23; this was done to further differentiate RAND( seed ) and RAND( seed +1) .

• The default type returned by IFNULL( A,B ) is now set to be the more "general" of the types of A and B . (The general-to-specific order is string, REAL , INTEGER ).

C API Changes:

• The old C API functions mysql_drop_db() , mysql_create_db() , and mysql_connect() are no longer supported unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS . However, it is preferable to change client programs to use the new 4.0 API instead.

• In the MYSQL_FIELD structure, length and max_length have changed from unsigned int to unsigned long . This should not cause any problems, except that they may generate warning messages when used as arguments in the printf() class of functions.

• Multi-threaded clients should use mysql_thread_init() and mysql_thread_end() .

Other Changes:

• If you want to recompile the Perl DBD::mysql module, use a recent version. Version 2.9003 is recommended. Versions older than 1.2218 should not be used because they use the deprecated mysql_drop_db() call.

2.5.4 Upgrading from Version 3.22 to 3.23

MySQL 3.22 and 3.21 clients will work without any problems with a MySQL 3.23 server.

When upgrading to MySQL 3.23 from an earlier version, note the following changes:

Table Changes:

• MySQL 3.23 supports tables of the new MyISAM type and the old ISAM type. By default, all new tables are created with type MyISAM unless you start mysqld with the --default-table-type=isam option. You don't have to convert your old ISAM tables to use them with MySQL 3.23. You can convert an ISAM table to MyISAM format with ALTER TABLE tbl_name TYPE=MyISAM or the Perl script mysql_convert_table_format .

• All tables that use the tis620 character set must be fixed with myisamchk -r or REPAIR TABLE .

• If you are using the german character sort order for ISAM tables, you must repair them with isamchk -r , because we have made some changes in the sort order.

Client Program Changes:

• The MySQL client mysql is now by default started with the --no-named-commands (-g) option. This option can be disabled with --enable-named-commands (-G) . This may cause incompatibility problems in some cases ”for example, in SQL scripts that use named commands without a semicolon. Long format commands still work from the first line.

• If you want your mysqldump files to be compatible between MySQL 3.22 and 3.23, you should not use the --opt or --all option to mysqldump .

SQL Changes:

• If you do a DROP DATABASE on a symbolically linked database, both the link and the original database are deleted. This didn't happen in MySQL 3.22 because configure didn't detect the availability of the readlink() system call.

• OPTIMIZE TABLE now works only for MyISAM tables. For other table types, you can use ALTER TABLE to optimize the table. During OPTIMIZE TABLE , the table is now locked to prevent it from being used by other threads.

• Date functions that work on parts of dates (such as MONTH() ) will now return 0 for 0000-00-00 dates. In MySQL 3.22, these functions returned NULL .

• The default return type of IF() now depends on both arguments, not just the first one.

• AUTO_INCREMENT columns should not be used to store negative numbers. The reason for this is that negative numbers caused problems when wrapping from “1 to 0. You should not store 0 in AUTO_INCREMENT columns, either; CHECK TABLE will complain about 0 values because they may change if you dump and restore the table. AUTO_INCREMENT for MyISAM tables is now handled at a lower level and is much faster than before. In addition, for MyISAM tables, old numbers are no longer reused, even if you delete rows from the table.

• CASE , DELAYED , ELSE , END , FULLTEXT , INNER , RIGHT , THEN , and WHEN now are reserved words.

• FLOAT( p ) now is a true floating-point type and not a value with a fixed number of decimals.

• When declaring columns using a DECIMAL( length,dec ) type, the length argument no longer includes a place for the sign or the decimal point.

• A TIME string must now be of one of the following formats: [[[DAYS] [H]H:]MM:]SS[.fraction] or [[[[[H]H]H]H]MM]SS[.fraction] .

• LIKE now compares strings using the same character comparison rules as for the = operator. If you require the old behavior, you can compile MySQL with the CXXFLAGS=-DLIKE_CMP_TOUPPER flag.

• REGEXP now is case insensitive if neither of the strings is a binary string.

• When you check or repair MyISAM ( .MYI ) tables, you should use the CHECK TABLE statement or the myisamchk command. For ISAM ( .ISM ) tables, use the isamchk command.

• Check all your calls to DATE_FORMAT() to make sure that there is a ' % ' before each format character. (MySQL 3.22 already allowed this syntax, but now ' % ' is required.)

• In MySQL 3.22, the output of SELECT DISTINCT ... was almost always sorted. In MySQL 3.23, you must use GROUP BY or ORDER BY to obtain sorted output.

• SUM() now returns NULL instead of 0 if there are no matching rows. This is required by standard SQL.

• An AND or OR with NULL values will now return NULL instead of . This mostly affects queries that use NOT on an AND/OR expression as NOT NULL = NULL .

• LPAD() and RPAD() now shorten the result string if it's longer than the length argument.

C API Changes:

• mysql_fetch_fields_direct() now is a function instead of a macro. It now returns a pointer to a MYSQL_FIELD instead of a MYSQL_FIELD .

• mysql_num_fields() no longer can be used on a MYSQL* object (it's now a function that takes a MYSQL_RES* value as an argument). With a MYSQL* object, you now should use mysql_field_count() instead.

2.5.5 Upgrading from Version 3.21 to 3.22

Nothing that affects compatibility has changed between versions 3.21 and 3.22. The only pitfall is that new tables that are created with DATE type columns will use the new way to store the date. You can't access these new columns from an old version of mysqld .

When upgrading to MySQL 3.23 from an earlier version, note the following changes:

• After installing MySQL Version 3.22, you should start the new server and then run the mysql_fix_privilege_tables script. This will add the new privileges that you need to use the GRANT command. If you forget this, you will get Access denied when you try to use ALTER TABLE , CREATE INDEX , or DROP INDEX . The procedure for updating the grant tables is described in Section 2.5.8, "Upgrading the Grant Tables."

• The C API interface to mysql_real_connect() has changed. If you have an old client program that calls this function, you must pass a for the new db argument (or recode the client to send the db element for faster connections). You must also call mysql_init() before calling mysql_real_connect() . This change was done to allow the new mysql_options() function to save options in the MYSQL handler structure.

• The mysqld variable key_buffer has been renamed to key_buffer_size , but you can still use the old name in your startup files.

2.5.6 Upgrading from Version 3.20 to 3.21

If you are running a version older than Version 3.20.28 and want to switch to Version 3.21, you need to do the following:

You can start the mysqld Version 3.21 server with the --old-protocol option to use it with clients from a Version 3.20 distribution. In this case, the server uses the old pre-3.21 password() checking rather than the new method. Also, the new client function mysql_errno() will not return any server error, only CR_UNKNOWN_ERROR . The function does work for client errors.

If you are not using the --old-protocol option to mysqld , you will need to make the following changes:

• All client code must be recompiled. If you are using ODBC, you must get the new MyODBC 2.x driver.

• The scripts/add_long_password script must be run to convert the Password field in the mysql.user table to CHAR(16) .

• All passwords must be reassigned in the mysql.user table to get 62-bit rather than 31-bit passwords.

• The table format hasn't changed, so you don't have to convert any tables.

MySQL 3.20.28 and above can handle the new user table format without affecting clients. If you have a MySQL version earlier than 3.20.28, passwords will no longer work with it if you convert the user table. So to be safe, you should first upgrade to at least Version 3.20.28 and then upgrade to Version 3.21.

The new client code works with a 3.20.x mysqld server, so if you experience problems with 3.21.x, you can use the old 3.20.x server without having to recompile the clients again.

If you are not using the --old-protocol option to mysqld , old clients will be unable to connect and will issue the following error message:

 ERROR: Protocol mismatch. Server Version = 10 Client Version = 9 

The Perl DBI interface also supports the old mysqlperl interface. The only change you have to make if you use mysqlperl is to change the arguments to the connect() function. The new arguments are: host , database , user , and password (note that the user and password arguments have changed places).

The following changes may affect queries in old applications:

• HAVING must now be specified before any ORDER BY clause.

• The parameters to LOCATE() have been swapped.

• There are some new reserved words. The most notable are DATE , TIME , and TIMESTAMP .

2.5.7 Upgrading MySQL Under Windows

When upgrading MySQL under Windows, please follow these steps:

1. Download the latest Windows distribution of MySQL.
   
   
2. Choose a time of day with low usage, where a maintenance break is acceptable.
   
   
3. Alert the users who still are active about the maintenance break.
   
   
4. Stop the running MySQL Server (for example, with NET STOP MySQL or with the Services utility if you are running MySQL as a service, or with mysqladmin shutdown otherwise).
   
   
5. Exit the WinMySQLAdmin program if it is running.
   
   
6. Run the installation script of the Windows distribution by clicking the Install button in WinZip and following the installation steps of the script.
   
   

   Important note: Early alpha Windows distributions for MySQL 4.1 do not contain an installer program. See Section 2.2.1.2, "Installing a Windows Binary Distribution," for instructions on how to install such a distribution.

7. You may either overwrite your old MySQL installation (usually located at C:\mysql ), or install it into a different directory, such as C:\mysql4 . Overwriting the old installation is recommended.
   
   
8. Restart the server. For example, use NET START MySQL if you run MySQL as a service, or invoke mysqld directly otherwise.
   
   
9. Update the grant tables. The procedure is described in Section 2.5.8, "Upgrading the Grant Tables."
   
   

Possible error situations:

 A system error has occurred. System error 1067 has occurred. The process terminated unexpectedly. 

These errors mean that your option file (by default C:\my.cnf ) contains an option that cannot be recognized by MySQL. You can verify that this is the case by trying to restart MySQL with the option file renamed to prevent the server from using it. (For example, rename C:\my.cnf to C:\my_cnf.old .) Once you have verified it, you need to identify which option is the culprit. Create a new my.cnf file and move parts of the old file to it (restarting the server after you move each part) until you determine which option causes server startup to fail.

2.5.8 Upgrading the Grant Tables

Some releases introduce changes to the structure of the grant tables (the tables in the mysql database) to add new privileges or features. To make sure that your grant tables are current when you update to a new version of MySQL, you should update your grant tables as well.

On Unix or Unix-like systems, update the grant tables by running the mysql_fix_privilege_tables script:

 shell>  mysql_fix_privilege_tables  

You must run this script while the server is running. It attempts to connect to the server running on the local host as root . If your root account requires a password, indicate the password on the command line. For MySQL 4.1 and up, specify the password like this:

 shell>  mysql_fix_privilege_tables --password=   root_password  

Prior to MySQL 4.1, specify the password like this:

 shell>  mysql_fix_privilege_tables   root_password  

The mysql_fix_privilege_tables script performs any actions necessary to convert your grant tables to the current format. You might see some Duplicate column name warnings as it runs; you can ignore them.

After running the script, stop the server and restart it.

On Windows systems, there isn't an easy way to update the grant tables until MySQL 4.0.15. From version 4.0.15 on, MySQL distributions include a mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. If your MySQL installation is located at C:\mysql , the commands look like this:

 C:\>  C:\mysql\bin\mysql -u root -p mysql  mysql>  SOURCE C:\mysql\scripts\mysql_fix_privilege_tables.sql  

The mysql command will prompt you for the root password; enter it when prompted. If your installation is located in some other directory, adjust the pathnames appropriately.

As with the Unix procedure, you might see some Duplicate column name warnings as mysql processes the statements in the mysql_fix_privilege_tables.sql script; you can ignore them.

After running the script, stop the server and restart it.

2.5.9 Copying MySQL Databases to Another Machine

If you are using MySQL 3.23 or later, you can copy the .frm , .MYI , and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 8.1, "The MyISAM Storage Engine."

The MySQL ISAM data and index files ( .ISD and *.ISM , respectively) are architecture dependent and in some cases operating system dependent. If you want to move your applications to another machine that has a different architecture or operating system than your current machine, you should not try to move a database by simply copying the files to the other machine. Use mysqldump instead.

By default, mysqldump will 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.

Try 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 faster.

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>  mysqladmin -h '   other hostname   ' create   db_name  shell>  mysqldump --opt   db_name   mysql -h '   other hostname   '   db_name  

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

 shell>  mysqladmin create   db_name  shell>  mysqldump -h '   other hostname   ' --opt --compress   db_name   mysql   db_name  

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

 shell>  mysqldump --quick   db_name   gzip >   db_name   .contents.gz  

(The file created in this example is compressed.) Transfer the file containing the database contents to the target machine and run these commands there:

 shell>  mysqladmin create   db_name  shell>  gunzip <   db_name   .contents.gz  mysql   db_name  

You can also use mysqldump and mysqlimport to transfer the database. For big tables, this is much faster than simply using mysqldump . In the following commands, DUMPDIR 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>  mkdir   DUMPDIR  shell>  mysqldump --tab=   DUMPDIR db_name  

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

 shell>  mysqladmin create   db_name  # create database shell>  cat   DUMPDIR   /*.sql  mysql   db_name  # create tables in database shell>  mysqlimport   db_name DUMPDIR   /*.txt  # load data into tables 

Also, don't forget to copy the mysql database because that is where the user , db , and host grant tables are stored. You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.

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