2.5.3 Upgrading From Version 3.22 to 3.23

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

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

The following list tells what you have to watch out for when upgrading to Version 3.23:

• All tables that use the tis620 character set must be fixed with myisamchk -r or REPAIR TABLE.
• 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 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 optimise the table. During OPTIMIZE TABLE, the table is now locked to prevent it from being used by other threads.
• The MySQL client mysql is now by default started with the option --no-named-commands (-g). 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.
• Date functions that work on parts of dates (like MONTH()) will now return 0 for 0000-00-00 dates. (In MySQL 3.22, these functions returned NULL.)
• 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.
• The default return type of IF() now depends on both arguments and not only the first argument.
• 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 are now reserved words.
• FLOAT(X) is now 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 behaviour, you can compile MySQL with the CXXFLAGS=-DLIKE_CMP_TOUPPER flag.
• REGEXP is now case-insensitive if neither of the strings are binary strings.
• 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.
• If you want your mysqldump files to be compatible between MySQL Version 3.22 and Version 3.23, you should not use the --opt or --all option to mysqldump.
• Check all your calls to DATE_FORMAT() to make sure there is a `%' before each format character. (MySQL Version 3.22 and later already allowed this syntax.)
• mysql_fetch_fields_direct() is now a function (it used to be a macro) and it returns a pointer to a MYSQL_FIELD instead of a MYSQL_FIELD.
• mysql_num_fields() can no longer 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 should now use mysql_field_count() instead.
• In MySQL Version 3.22, the output of SELECT DISTINCT ... was almost always sorted. In Version 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 SQL-99.
• An AND or OR with NULL values will now return NULL instead of 0. 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.

User Comments

A simple script to convert all your tables to
MyISAM format:
<code>
$link = mysql_connect
('localhost', 'user', 'password');
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list)) {
echo "Database: ".$row->Database;
$DB = $row->Database;
mysql_select_db($DB);
$tables=mysql_list_tables($DB);
while (list($bla)=mysql_fetch_array($tables))
{
$result = mysql_query("ALTER TABLE $bla
TYPE=MyISAM")or die("Invalid query");
echo "Table ".$bla." is converted to
MyISAM";
}
}
</code>

Add your own comment.