DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) upgrading-from-4-1

Info Catalog (mysql.info) upgrading-from-5-0 (mysql.info) upgrade (mysql.info) upgrading-to-arch
 
 2.10.2 Upgrading from MySQL 4.1 to 5.0
 --------------------------------------
 
 * 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 to MySQL 5.0
 from 4.1:
 
    * 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  mysql-upgrade.
 
    * Read the MySQL 5.0 change history to see what significant new
      features you can use in 5.0.  See  news-5-0-x.
 
    * If you are running MySQL Server on Windows, see 
      windows-upgrading.
 
    * MySQL 5.0 adds support for stored procedures.  This support
      requires the `mysql.proc' table. To create this table, you should
      run the `mysql_upgrade' script as described in 
      mysql-upgrade.
 
    * MySQL 5.0 adds support for views. This support requires extra
      privilege columns in the `mysql.user' and `mysql.db' tables. To
      create these columns, you should run the `mysql_upgrade' script as
      described in  mysql-upgrade.
 
    * If you are using replication, see  replication-upgrade, 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 `TEXT'
      columns for `InnoDB' and `MyISAM' tables has changed. Starting from
      5.0.3, `TEXT' indexes are compared as space-padded at the end
      (just as MySQL sorts `CHAR', `VARCHAR' and `TEXT' fields). If you
      have a index on a `TEXT' column, you should run `CHECK TABLE' on
      it. If the check reports errors, rebuild the indexes: Dump and
      reload the table if it is an `InnoDB' table, or run `OPTIMIZE
      TABLE' or `REPAIR TABLE' if it is a `MyISAM' table.
 
    * *Warning: Incompatible change*. For `BINARY' columns, the pad
      value and how it is handled has changed as of MySQL 5.0.15. The
      pad value for inserts now is `0x00' rather than space, and there
      is no stripping of the pad value for selects. For details, see
       binary-varbinary.
 
    * *Incompatible change*: The implementation of `DECIMAL' has changed
      in MySQL 5.0.3. You should make your applications aware of that
      change, which is described in 
      precision-math-decimal-changes.
 
      A consequence of the change in handling of the `DECIMAL' and
      `NUMERIC' fixed-point data types is that the server is more strict
      to follow standard SQL. For example, a data type of `DECIMAL(3,1)'
      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 TBL_NAME MODIFY COL_NAME DECIMAL(4,1);
 
    * *Incompatible change*: `MyISAM' and `InnoDB' tables created with
      `DECIMAL' 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 
      mysql-upgrade.
 
    * *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 `xxx_init' or
      `xxx_deinit' symbol) defined in addition to the main function
      symbol.  This behavior can be overridden with the
      -allow-suspicious-udfs option. See  udf-security.
 
    * *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 `ISAM' storage engine has
      been removed in MySQL 5.0. If you have any `ISAM' tables, you
      should convert them _before_ upgrading. For example, to convert an
      `ISAM' table to use the `MyISAM' storage engine, use this
      statement:
 
           ALTER TABLE TBL_NAME ENGINE = MyISAM;
 
      Use a similar statement for every `ISAM' table in each of your
      databases.
 
    * *Incompatible change*: Support for `RAID' options in `MyISAM'
      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 `RAID' options in the `CREATE TABLE' statements, and
      reload the dump file. Another possibility is to use `CREATE TABLE
      NEW_TBL ... SELECT RAID_TBL' to create a new table from the `RAID'
      table. However, the `CREATE TABLE' 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  create-table.
 
      The `.MYD' files for `RAID' 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 `00' to `ff'. After
      converting all tables that use `RAID' options, these `RAID'-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 `RAID' 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 
      stored-procedure-logging.
 
 *SQL Changes:*
 
    * *Incompatible change:* Previously, a lock wait timeout caused
      `InnoDB' 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 `DROP TRIGGER' 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 `DROP
      TRIGGER' 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 `INFORMATION_SCHEMA.TRIGGERS' table.  (It
           should work even for pre-5.0.10 triggers.)
 
        2. Dump all trigger definitions using the following `SELECT'
           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 `INTO OUTFILE', so you must have the
           `FILE' 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 `triggers.sql' file,
           and perhaps make a backup of the file.
 
        3. Stop the server and drop all triggers by removing all `.TRG'
           files in your database directories. Change location to your
           data directory and issue this command:
 
                shell> rm */*.TRG
 
        4. Start the server and re-create all triggers using the
           `triggers.sql' file. For the file created earlier, use these
           commands in the `mysql' program:
 
                mysql> delimiter // ;
                mysql> source /tmp/triggers.sql //
 
        5. Use the `SHOW TRIGGERS' statement to check that all triggers
           were created successfully.
 
    * *Incompatible change:* As of MySQL 5.0.15, the `CHAR()' function
      returns a binary string rather than a string in the connection
      character set. An optional `USING CHARSET_NAME' 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:
 
         * `CHAR(ORD('A')) = 'a'' is no longer true:
 
                mysql> SELECT CHAR(ORD('A')) = 'a';
                +----------------------+
                | 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
           `USING' clause or converting the result:
 
                mysql> SELECT CHAR(ORD('A') USING latin1) = 'a';
                +-----------------------------------+
                | CHAR(ORD('A') USING latin1) = 'a' |
                +-----------------------------------+
                |                                 1 |
                +-----------------------------------+
                mysql> SELECT CONVERT(CHAR(ORD('A')) USING latin1) = 'a';
                +--------------------------------------------+
                | CONVERT(CHAR(ORD('A')) USING latin1) = 'a' |
                +--------------------------------------------+
                |                                          1 |
                +--------------------------------------------+
 
         * `CREATE TABLE ... SELECT CHAR(...)' produces a `VARBINARY'
           column, not a `VARCHAR' column. To produce a `VARCHAR'
           column, use `USING' or `CONVERT()' as just described to
           convert the `CHAR()' result into a non-binary character set.
 
         * Previously, the following statements inserted the value
           `0x00410041' (`'AA'' as a `ucs2' 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 `ucs2'
           character with value `0x4141'.
 
    * *Incompatible change:* Beginning with MySQL 5.0.12, natural joins
      and joins with `USING', including outer join variants, are
      processed according to the SQL:2003 standard. The changes include
      elimination of redundant output columns for `NATURAL' joins and
      joins specified with a `USING' clause and proper ordering of
      output columns. The precedence of the comma operator also now is
      lower compared to `JOIN', `LEFT JOIN', 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  join.
 
    * *Incompatible change:* Before MySQL 5.0.13, `GREATEST(X,NULL)' and
      `LEAST(X,NULL)' return X when X is a non-`NULL' value. As of
      5.0.3, both functions return `NULL' if any argument is `NULL', 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
      `DATETIME' values to numeric form by adding zero produced a result
      in `YYYYMMDDHHMMSS' format. The result of `DATETIME+0' is now in
      `YYYYMMDDHHMMSS.000000' format.
 
    * Some keywords are reserved in MySQL 5.0 that were not reserved in
      MySQL 4.1. See  reserved-words.
 
    * As of MySQL 5.0.3, `DECIMAL' columns are stored in a more
      efficient format. To convert a table to use the new `DECIMAL'
      type, you should do an `ALTER TABLE' on it. The `ALTER TABLE' also
      will change the table's `VARCHAR' columns to use the new `VARCHAR'
      data type. For information about possible incompatibilities with
      old applications, see  precision-math.
 
    * MySQL 5.0.3 and up uses precision math when calculating with
      `DECIMAL' values (64 decimal digits) and for rounding exact-value
      numbers. See  precision-math.
 
    * Comparisons made between `FLOAT' or `DOUBLE' 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 `WHERE COL_NAME=SOME_DOUBLE',
      _regardless of the MySQL version you are using_. See 
      problems-with-float.
 
    * As of MySQL 5.0.3, trailing spaces no longer are removed from
      values stored in `VARCHAR' and `VARBINARY' columns. The maximum
      lengths for `VARCHAR' and `VARBINARY' columns in MySQL 5.0.3 and
      later are 65,535 characters and 65,535 bytes, respectively.
 
      * If you create a table with new `VARCHAR' or `VARBINARY'
      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 before
      downgrading and reload it after downgrading.
 
    * As of MySQL 5.0.3, `BIT' is a separate data type, not a synonym
      for `TINYINT(1)'. See  numeric-type-overview.
 
    * MySQL 5.0.2 adds several SQL modes that allow stricter control
      over rejecting records that have invalid or missing values. See
       server-sql-mode, and  constraint-invalid-data. If
      you want to enable this control but continue to use MySQL's
      capability for storing incorrect dates such as `'2004-02-31'', you
      should start the server with
      -sql_mode=TRADITIONAL,ALLOW_INVALID_DATES.
 
    * As of MySQL 5.0.2, the `SCHEMA' and `SCHEMAS' keywords are
      accepted as synonyms for `DATABASE' and `DATABASES', 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 for input.)
 
    * User variables are not case sensitive in MySQL 5.0. In MySQL 4.1,
      `SET @x = 0; SET @X = 1; SELECT @x;' created two variables and
      returned `0'. In MySQL 5.0, it creates one variable and returns
      `1'.
 
    * A new startup option named innodb_table_locks was added that causes
      `LOCK TABLE' to also acquire `InnoDB' table locks. This option is
      enabled by default. This can cause deadlocks in applications that
      use `AUTOCOMMIT=1' and `LOCK TABLES'. If you application
      encounters deadlocks after upgrading, you may need to add
      `innodb_table_locks=0' to your `my.cnf' file.
 
 *C API Changes:*
 
    * *Incompatible change*: Because the MySQL 5.0 server has a new
      implementation of the `DECIMAL' 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: `'Using unsupported buffer type: 246''
 
      This error occurs because the 4.1 client libraries do not support
      the new `MYSQL_TYPE_NEWDECIMAL' type value added in 5.0. There is
      no way to disable the new `DECIMAL' 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 `ER_WARN_DATA_TRUNCATED' warning symbol
      was renamed to `WARN_DATA_TRUNCATED' in MySQL 5.0.3.
 
    * The `reconnect' flag in the `MYSQL' structure is set to 0 by
      `mysql_real_connect()'. Only those client programs which did not
      explicitly set this flag to 0 or 1 after `mysql_real_connect()'
      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).
 
Info Catalog (mysql.info) upgrading-from-5-0 (mysql.info) upgrade (mysql.info) upgrading-to-arch
automatically generated byinfo2html