GNU Info

(mysql.info)Upgrading-from-3.22

---

Next: Upgrading-from-3.21 Prev: Upgrade Up: Upgrade

---

Enter node , (file) or (file)node

Upgrading From Version 3.22 to Version 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 change an `ISAM' table to
a `MyISAM' table 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 lists tell what you have to watch out for when upgrading
to Version 3.23:

   * All tables that uses the `tis620' character set must be fixed with
     `myisamchk -r' or `REPAIR TABLE'.

   * If you do a `DROP DATABASE' on a symbolic linked database, both the
     link and the original database is deleted.  (This didn't happen in
     3.22 because configure didn't detect the `readlink' system call).

   * `OPTIMIZE TABLE' now only works for *MyISAM* tables.  For other
     table types, you can use `ALTER TABLE' to optimize the table.
     During `OPTIMIZE TABLE' the table is now locked from 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.

   * If you are using the `german' character sort order, you must repair
     all your tables with `isamchk -r', as we have made some changes in
     the sort order!

   * The default return type of `IF' will now depend on both arguments
     and not only the first argument.

   * `AUTO_INCREMENT' will not work with negative numbers. The reason
     for this is that negative numbers caused problems when wrapping
     from -1 to 0.  `AUTO_INCREMENT' is now for MyISAM tables handled
     at a lower level and is much faster than before. For MyISAM tables
     old numbers are also not reused anymore, even if you delete some
     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 `DECIMAL(length,dec)' 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 `'=''.  If you require the old behavior, you can compile
     MySQL with the `CXXFLAGS=-DLIKE_CMP_TOUPPER' flag.

   * `REGEXP' is now case insensitive for normal (not binary) strings.

   * When you check/repair tables you should use `CHECK TABLE' or
     `myisamchk' for `MyISAM' tables (`.MYI') and `isamchk' for ISAM
     (`.ISM') tables.

   * 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
     `--full' option to `mysqldump'.

   * Check all your calls to `DATE_FORMAT()' to make sure there is a
     `%' before each format character.  (Later MySQL Version 3.22 did
     allow this syntax.)

   * `mysql_fetch_fields_direct' is now a function (it was 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 `MYSQL_RES*' as an argument. 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 is no matching
     rows. This is according to ANSI SQL.

   * 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()' will
     shorten the result string if it's longer than the length argument.