( Upgrading-from-3

Info Catalog ( Upgrading-from-4 ( Upgrade ( Upgrading-from-3
 2.10.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 to see what significant new features you
      can use in 4.0.   News-4.0.x.
    * If you are running MySQL Server on Windows, see  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  Upgrading-grant-tables.
    * If you are using replication, see  Replication Upgrade 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:
      If you are not sure of the table type for a given table, use this
           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
      `' 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 MySQL 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.
       Privileges provided.
      To get these new privileges to work, you must update the grant
      tables.  The procedure is described in 
      Upgrading-grant-tables.  Until you do this, all accounts have
      privileges. `SUPER' and `EXECUTE' privileges take their value from
      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).  Privileges
      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 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'
    * Values for the startup parameters
      `myisam_max_extra_sort_file_size' and
      `myisam_max_extra_sort_file_size' are given in bytes (they were
      given in megabytes before 4.0.3).
    * `mysqld' 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 system locking of `MyISAM'/`ISAM' files is 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_MAX_JOIN_SIZE'                `MAX_JOIN_SIZE'
      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' returns an empty set if binary logging is not
    * `SHOW SLAVE STATUS' returns an empty set if the slave is not
    * `SHOW INDEX' has two more columns than it had in 3.23 (`Null' and
    * 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
    * `CHECK', `LOCALTIME', and `LOCALTIMESTAMP' are reserved words.
    * `DOUBLE' and `FLOAT' columns honor the `UNSIGNED' flag on storage
      (before, `UNSIGNED' was ignored for these columns).
    * The result of all bitwise operators (`|', `&', `<<', `>>', and
      `~') is unsigned.  This may cause problems if you are using them
      in a context where you want a signed result.   Cast
      * 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'.   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 doesn't reset the `AUTO_INCREMENT' counter, 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
    * 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.   Fulltext Fine-tuning.
    * `LOCATE()' and `INSTR()' are case sensitive if one of the
      arguments is a binary string. Otherwise they are case insensitive.
    * `STRCMP()' 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(STR)' returns the characters in STR 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
    * The default type returned by `IFNULL(A,B)' is 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()'.  Threaded clients.
 *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.
Info Catalog ( Upgrading-from-4 ( Upgrade ( Upgrading-from-3
automatically generated byinfo2html