Author: paul
Date: 2009-11-06 21:39:38 +0100 (Fri, 06 Nov 2009)
New Revision: 17510
Log:
r46232@frost: paul | 2009-11-06 14:16:20 -0500
General revisions
Added:
trunk/refman-4.1/installing-updowngrade.xml
Modified:
trunk/refman-4.1/installing.xml
trunk/refman-4.1/storage-engines.xml
trunk/refman-5.0/installing-updowngrade.xml
trunk/refman-5.0/se-myisam-core.xml
trunk/refman-5.1-maria/sql-syntax-server-administration.xml
trunk/refman-5.1/installing-updowngrade.xml
trunk/refman-5.1/se-myisam-core.xml
trunk/refman-5.4/installing-updowngrade.xml
trunk/refman-5.4/se-myisam-core.xml
trunk/refman-5.5/installing-updowngrade.xml
trunk/refman-5.5/se-myisam-core.xml
trunk/refman-6.0.sav/installing-updowngrade.xml
trunk/refman-6.0.sav/se-myisam-core.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:27680
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:25547
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:46199
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
+ 07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/mysqldoc/trunk:27680
07c7e7b4-24e3-4b51-89d0-6dc09fec6bec:/mysqldoc-local/trunk:25547
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:43968
4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/trunk:44480
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:46232
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:39036
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/trunk:39546
Added: trunk/refman-4.1/installing-updowngrade.xml
===================================================================
--- trunk/refman-4.1/installing-updowngrade.xml (rev 0)
+++ trunk/refman-4.1/installing-updowngrade.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 2668, Lines Deleted: 0; 101898 bytes
@@ -0,0 +1,2668 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % all.entities SYSTEM "all-entities.ent">
+ %all.entities;
+]>
+<section id="upgrade-downgrade">
+
+ <title>Upgrading or Downgrading MySQL</title>
+
+ <indexterm>
+ <primary>upgrading</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>downgrading</primary>
+ </indexterm>
+
+ <section id="upgrade">
+
+ <title>Upgrading MySQL</title>
+
+ <indexterm>
+ <primary>upgrading</primary>
+ </indexterm>
+
+ <para>
+ As a general rule, to upgrade 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.
+ </para>
+
+ <para>
+ Whenever you perform an upgrade, use the items in the following
+ checklist as a guide:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Before any upgrade, back up your databases, including the
+ <literal>mysql</literal> database that contains the grant
+ tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Read <emphasis>all</emphasis> the notes the upgrading section
+ for the release series to which you are upgrading. Read the
+ change notes as well. These provide information about new
+ features you can use.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Some releases of MySQL introduce changes to the structure of
+ the grant tables to add new privileges or features. After you
+ update to a new version of MySQL, you should update your grant
+ tables to make sure that they have the current structure so
+ that you can take advantage of any new capabilities. See
+ <xref linkend="mysql-fix-privilege-tables"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are running MySQL Server on Windows, see
+ <xref linkend="windows-upgrading"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are using replication, see
+ <xref linkend="replication-upgrade"/>, for information on
+ upgrading your replication setup.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are upgrading an installation originally produced by
+ installing multiple RPM packages, it is best to upgrade all
+ the packages, not just some. For example, if you previously
+ installed the server and client RPMs, do not upgrade just the
+ server RPM.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you previously installed a MySQL-Max distribution that
+ includes a server named <command>mysqld-max</command>, and
+ then upgrade later to a non-Max version of MySQL,
+ <command>mysqld_safe</command> still attempts to run the old
+ <command>mysqld-max</command> server. If you perform such an
+ upgrade, you should remove the old
+ <command>mysqld-max</command> server manually to ensure that
+ <command>mysqld_safe</command> runs the new
+ <command>mysqld</command> server.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you have created a user-defined function (UDF) with a given
+ name and upgrade MySQL to a version that implements a new
+ built-in function with the same name, the UDF becomes
+ inaccessible. To correct this, use <literal role="stmt">DROP
+ FUNCTION</literal> to drop the UDF, and then use
+ <literal role="stmt">CREATE FUNCTION</literal> to re-create
+ the UDF with a different nonconflicting name. The same is true
+ if the new version of MySQL implements a built-in function
+ with the same name as an existing stored function. See
+ <xref linkend="function-resolution"/>, for the rules
+ describing how the server interprets references to different
+ kinds of functions.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ 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. Before MySQL
+ 4.1, if you change the character set when running MySQL, you must
+ run <command>myisamchk -r -q
+ --set-character-set=<replaceable>charset_name</replaceable></command>
+ on all <literal>MyISAM</literal> tables. Otherwise, your indexes
+ may not be ordered correctly, because changing the character set
+ may also change the sort order. As of MySQL 4.1, to convert tables
+ created before 4.1 to the format that includes character set and
+ collation information, use the instructions in
+ <xref linkend="charset-conversion"/>.
+ </para>
+
+ <para>
+ Normally, you can upgrade MySQL to a newer MySQL version without
+ having to do any changes to your tables. Please confirm whether
+ the upgrade notes to the particular version you are upgrading to
+ tell you anything about this. If there would be any
+ incompatibilities you can use <command>mysqldump</command> to dump
+ your tables before upgrading. After upgrading, reload the dump
+ file using <command>mysql</command> or
+ <command>mysqlimport</command> to re-create your tables.
+ </para>
+
+ <para>
+ If you are cautious about using new versions, you can always
+ rename your old <command>mysqld</command> 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
+ <command>mysqld</command> to <command>mysqld-4.0.18</command>. If
+ your new <command>mysqld</command> then does something unexpected,
+ you can simply shut it down and restart with your old
+ <command>mysqld</command>.
+ </para>
+
+ <para>
+ If, after an upgrade, you experience problems with recompiled
+ client programs, such as <literal>Commands out of sync</literal>
+ 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 <filename>mysql.h</filename> file
+ and <filename>libmysqlclient.a</filename> library to verify that
+ they are from the new MySQL distribution. If not, recompile your
+ programs with the new headers and libraries.
+ </para>
+
+ <para>
+ If problems occur, such as that the new <command>mysqld</command>
+ server does not want to start or that you cannot connect without a
+ password, verify that you do not have some old
+ <filename>my.cnf</filename> file from your previous installation.
+ You can check this with the
+ <option role="general">--print-defaults</option> option (for
+ example, <command>mysqld --print-defaults</command>). If this
+ command displays anything other than the program name, you have an
+ active <filename>my.cnf</filename> file that affects server or
+ client operation.
+ </para>
+
+ <para>
+ If your MySQL installation contains a large amount of data that
+ might take a long time to convert after an in-place upgrade, you
+ might find it useful to create a <quote>dummy</quote> database
+ instance for assessing what conversions might be needed and the
+ work involved to perform them. Make a copy of your MySQL instance
+ that contains a full copy of the <literal>mysql</literal>
+ database, plus all other databases without data. Run your upgrade
+ procedure on this dummy instance to see what actions might be
+ needed so that you can better evaluate the work involved when
+ performing actual data conversion on your original database
+ instance.
+ </para>
+
+ <para>
+ It is a good idea to rebuild and reinstall the Perl
+ <literal>DBD::mysql</literal> module whenever you install a new
+ release of MySQL. The same applies to other MySQL interfaces as
+ well, such as PHP <literal>mysql</literal> and extensions or the
+ Python <literal>MySQLdb</literal> module.
+ </para>
+
+ <section id="upgrading-from-previous-series">
+
+ <title>Upgrading from MySQL 4.0 to 4.1</title>
+
+ <indexterm>
+ <primary>compatibility</primary>
+ <secondary>between MySQL versions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>upgrading</primary>
+ <secondary>4.0 to 4.1</secondary>
+ </indexterm>
+
+ <note>
+ <para>
+ 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.
+ </para>
+
+ <para>
+ To upgrade to ¤t-series; from any previous version,
+ MySQL recommends that you dump your tables with
+ <command>mysqldump</command> before upgrading and reload the
+ dump file after upgrading.
+ </para>
+ </note>
+
+ <para>
+ In general, you should do the following when upgrading from
+ MySQL &previous-series; to ¤t-series;:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Read <emphasis>all</emphasis> the items in the following
+ sections to see whether any of them might affect your
+ applications:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <xref linkend="upgrade"/>, has general update
+ information.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The items in the change lists found later in this
+ section enable you to identify upgrade issues that apply
+ to your current MySQL installation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The MySQL ¤t-series; change history describes
+ significant new features you can use in ¤t-series;
+ or that differ from those found in MySQL
+ &previous-series;. Some of these changes may result in
+ incompatibilities. See <xref linkend="news-4-1-x"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Note particularly any changes that are marked
+ <emphasis role="bold">Known issue</emphasis> or
+ <emphasis role="bold">Incompatible change</emphasis>. These
+ incompatibilities with earlier versions of MySQL may require
+ your attention <emphasis>before you upgrade</emphasis>. Note
+ particularly the items under <quote>Server Changes</quote>
+ that related to changes in character set support.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ After upgrading, update the grant tables to obtain the new
+ longer <literal>Password</literal> column that is needed for
+ more secure handling of passwords. The procedure uses
+ <command>mysql_fix_privilege_tables</command> and is
+ described in <xref linkend="mysql-fix-privilege-tables"/>.
+ If you do not do this, MySQL does not use the new more
+ secure protocol to authenticate. Implications of the
+ password-handling change for applications are given later in
+ this section.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Check <xref linkend="checking-table-incompatibilities"/>, to
+ see whether changes to table formats or to character sets or
+ collations were made between your current version of MySQL
+ and the version to which you are upgrading. If so and these
+ changes result in an incompatibility between MySQL versions,
+ you will need to upgrade the affected tables using the
+ instructions in <xref linkend="rebuilding-tables"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are running MySQL Server on Windows, see
+ <xref linkend="windows-upgrading"/>. You should also be
+ aware that two of the Windows MySQL servers were renamed in
+ MySQL 4.1. See <xref linkend="windows-select-server"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are using replication, see
+ <xref linkend="replication-upgrade"/>, for information on
+ upgrading your replication setup.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 <command>mysqldump</command> to
+ dump your <literal>BDB</literal> tables in text format and
+ delete all <literal>log.XXXXXXXXXX</literal> files before
+ you start MySQL 4.0 and reload the data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 4.1.3 introduces support for per-connection time
+ zones. See <xref linkend="time-zone-support"/>. To enable
+ recognition of named time zones, you should create the time
+ zone tables in the <literal>mysql</literal> database. For
+ instructions, see <xref linkend="post-installation"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are using an old <literal>DBD-mysql</literal> module
+ (<literal>Msql-MySQL-modules</literal>) you must upgrade to
+ the newer <literal>DBD-mysql</literal> module. Anything
+ above <literal>DBD-mysql</literal> 2.xx should be
+ satisfactory.
+ </para>
+
+ <para>
+ If you do not upgrade, some methods (such as
+ <literal>DBI->do()</literal>) do not notice error
+ conditions correctly.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The
+ <option role="general">--defaults-file=<replaceable>option_file_name</replaceable></option>
+ option gives an error if the option file does not exist.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Some notes about upgrading from MySQL 4.0 to MySQL 4.1 on
+ Netware: Make sure to upgrade Perl and PHP versions.
+ Download Perl 5 for Netware from
+ <ulink url="http://forge.novell.com/modules/xfmod/project/?perl5"/>
+ and PHP from
+ <ulink url="http://forge.novell.com/modules/xfmod/project/?php"/>.
+ Download and install the Perl module for MySQL 4.1 from
+ <ulink url="http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1126"/>
+ and the PHP Extension for MySQL 4.1 from
+ <ulink url="http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1078"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ If your MySQL installation contains a large amount of data that
+ might take a long time to convert after an in-place upgrade, you
+ might find it useful to create a <quote>dummy</quote> database
+ instance for assessing what conversions might be needed and the
+ work involved to perform them. Make a copy of your MySQL
+ instance that contains a full copy of the
+ <literal>mysql</literal> database, plus all other databases
+ without data. Run your upgrade procedure on this dummy instance
+ to see what actions might be needed so that you can better
+ evaluate the work involved when performing actual data
+ conversion on your original database instance.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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 <option>--new</option> startup option for
+ <command>mysqld</command>. See <xref linkend="server-options"/>.
+ </para>
+
+ <para>
+ 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 <literal>SET @@new=1</literal> command, or
+ turn them off if they are on with <literal>SET
+ @@new=0</literal>.
+ </para>
+
+ <para>
+ If you believe that some of the 4.1 changes affect you, we
+ recommend that before upgrading to 4.1, you download the latest
+ MySQL 4.0 version and run it with the <option>--new</option>
+ option by adding the following to your config file:
+ </para>
+
+<programlisting>
+[mysqld-4.0]
+new
+</programlisting>
+
+ <para>
+ That way you can test the new behaviors in 4.0 to make sure that
+ your applications work with them. This helps you have a smooth,
+ painless transition when you perform a full upgrade to 4.1
+ later. Putting the <option>--new</option> option in the
+ <literal>[mysqld-4.0]</literal> option group ensures that you do
+ not accidentally later run the 4.1 version with the
+ <option>--new</option> option.
+ </para>
+
+ <para>
+ The following lists describe changes that may affect
+ applications and that you should watch out for when upgrading to
+ version 4.1.
+ </para>
+
+ <para>
+ <emphasis role="bold">Server Changes:</emphasis>
+ </para>
+
+ <para>
+ The most notable change is that character set support has been
+ improved. The server supports multiple character sets, and all
+ tables and nonbinary string columns
+ (<literal role="type">CHAR</literal>,
+ <literal role="type">VARCHAR</literal>, and
+ <literal role="type">TEXT</literal>) have a character set. See
+ <xref linkend="charset"/>. Binary string columns
+ (<literal role="type">BINARY</literal>,
+ <literal role="type">VARBINARY</literal>, and
+ <literal role="type">BLOB</literal>) contain strings of bytes
+ and do not have a character set.
+ </para>
+
+ <note>
+ <para>
+ This change in character set support results in the potential
+ for table damage if you do not upgrade properly, so consider
+ carefully the incompatibilities noted here.
+ </para>
+ </note>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: There
+ are conditions under which you should rebuild tables. In
+ general, to rebuild a table, dump it with
+ <command>mysqldump</command> and reload the dump file. Some
+ items in the following list indicate alternatives means for
+ rebuilding.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you have created or used <literal>InnoDB</literal>
+ tables with <literal role="type">TIMESTAMP</literal>
+ columns in MySQL versions 4.1.0 to 4.1.3, you must
+ rebuild those tables when you upgrade to MySQL 4.1.4 or
+ later. The storage format in those MySQL versions for
+ <literal role="type">TIMESTAMP</literal> columns was
+ incorrect. If you upgrade from MySQL 4.0 to 4.1.4 or
+ later, no rebuild of tables with
+ <literal role="type">TIMESTAMP</literal> columns is
+ needed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Starting from MySQL 4.1.3, <literal>InnoDB</literal>
+ uses the same character set comparison functions as
+ MySQL for non-<literal>latin1_swedish_ci</literal>
+ character strings that are not
+ <literal>BINARY</literal>. This changes the sorting
+ order of space and characters with a code < ASCII(32)
+ in those character sets. For
+ <literal>latin1_swedish_ci</literal> character strings
+ and <literal>BINARY</literal> strings,
+ <literal>InnoDB</literal> uses its own pad-spaces-at-end
+ comparison method, which stays unchanged. Note that
+ <literal>latin1_swedish_ci</literal> is the default
+ collation order for <literal>latin1</literal> in 4.0. If
+ you have an <literal>InnoDB</literal> table created with
+ MySQL 4.1.2 or earlier, with an index on a
+ non-<literal>latin1_swedish_ci</literal> character set
+ and collation order column that is not
+ <literal>BINARY</literal> (in the case of 4.1.0 and
+ 4.1.1, with any character set and collation), and that
+ column may contain characters with a code <
+ ASCII(32), you should do <literal role="stmt">ALTER
+ TABLE</literal> or <literal role="stmt">OPTIMIZE
+ TABLE</literal> on it to regenerate the index, after
+ upgrading to MySQL 4.1.3 or later. You can also rebuild
+ the table from a dump.
+ </para>
+
+ <para>
+ <literal>MyISAM</literal> tables also have to be rebuilt
+ or repaired in these cases. You can use
+ <command>mysqldump</command> to dump them in 4.0 and
+ then reload them in 4.1. An alternative is to use
+ <literal role="stmt">OPTIMIZE TABLE</literal> after
+ upgrading, but this <emphasis>must</emphasis> be done
+ before any updates are made in 4.1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ As of MySQL 4.1.2, string comparison works according to
+ the SQL standard: Instead of stripping end spaces before
+ comparison, we now extend the shorter string with
+ spaces. The problem with this is that now <literal>'a'
+ > 'a\t'</literal>, which it was not before. If you
+ have any tables where you have indexes on
+ <literal role="type">CHAR</literal>,
+ <literal role="type">VARCHAR</literal> or
+ <literal role="type">TEXT</literal> column in which the
+ last character in index values may be less than
+ <literal>ASCII(32)</literal>, you should rebuild those
+ indexes to ensure that the table is correct.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you have used column prefix indexes on UTF-8 columns
+ or other multi-byte character set columns in MySQL 4.1.0
+ to 4.1.5, you must rebuild the tables when you upgrade
+ to MySQL 4.1.6 or later.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you have used accent characters (characters with byte
+ values of 128 to 255) in database names, table names,
+ constraint names, or column names in versions of MySQL
+ earlier than 4.1, you cannot upgrade to MySQL 4.1
+ directly, because 4.1 uses UTF-8 to store metadata. Use
+ <literal role="stmt">RENAME TABLE</literal> to overcome
+ this if the accent character is in the table name or the
+ database name, or rebuild the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MyISAM</literal> tables now use an improved
+ checksum algorithm in MySQL 4.1. If you have
+ <literal>MyISAM</literal> tables with live checksum
+ enabled (you used <literal>CHECKSUM=1</literal> in
+ <literal role="stmt">CREATE TABLE</literal> or
+ <literal role="stmt">ALTER TABLE</literal>), these
+ tables appear to be corrupted following an upgrade. Use
+ <literal role="stmt">REPAIR TABLE</literal> to
+ recalculate the checksum for each such table.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: MySQL
+ interprets length specifications in character column
+ definitions in characters. (Earlier versions interpret them
+ in bytes.) For example,
+ <literal>CHAR(<replaceable>N</replaceable>)</literal> means
+ <replaceable>N</replaceable> characters, not
+ <replaceable>N</replaceable> bytes.
+ </para>
+
+ <para>
+ For single-byte character sets, this change makes no
+ difference. However, if you upgrade to MySQL 4.1 and
+ configure the server to use a multi-byte character set, the
+ apparent length of character columns changes. Suppose that a
+ 4.0 table contains a <literal>CHAR(8)</literal> column used
+ to store <literal>ujis</literal> characters. Eight bytes can
+ store from two to four <literal>ujis</literal> characters.
+ If you upgrade to 4.1 and configure the server to use
+ <literal>ujis</literal> as its default character set, the
+ server interprets character column lengths based on the
+ maximum size of a <literal>ujis</literal> character, which
+ is three bytes. The number of three-byte characters that fit
+ in eight bytes is two. Consequently, if you use
+ <literal role="stmt">SHOW CREATE TABLE</literal> to view the
+ table definition, MySQL displays <literal>CHAR(2)</literal>.
+ You can retrieve existing data from the table, but you can
+ only store new values containing up to two characters. To
+ correct this issue, use <literal role="stmt">ALTER
+ TABLE</literal> to change the column definition. For
+ example:
+ </para>
+
+<programlisting>
+ALTER TABLE <replaceable>tbl_name</replaceable> MODIFY <replaceable>col_name</replaceable> CHAR(8);
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change:</emphasis> As of
+ MySQL 4.1.2, handling of the
+ <literal role="type">FLOAT</literal> and
+ <literal role="type">DOUBLE</literal> floating-point data
+ types is more strict to follow standard SQL. For example, a
+ data type of <literal>FLOAT(3,1)</literal> stores a maximum
+ value of 99.9. Before 4.1.2, the server allowed larger
+ numbers to be stored. That is, it stored a value such as
+ 100.0 as 100.0. As of 4.1.2, the server clips 100.0 to the
+ maximum allowable value of 99.9. If you have tables that
+ were created before MySQL 4.1.2 and that contain
+ floating-point data not strictly legal for the data type,
+ you should alter the data types of those columns. For
+ example:
+ </para>
+
+<programlisting>
+ALTER TABLE <replaceable>tbl_name</replaceable> MODIFY <replaceable>col_name</replaceable> FLOAT(4,1);
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: In
+ connection with the support for per-connection time zones in
+ MySQL 4.1.3, the <literal role="sysvar">timezone</literal>
+ system variable was renamed to
+ <literal role="sysvar">system_time_zone</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: For
+ <literal role="type">ENUM</literal> columns that had
+ enumeration values containing commas, the commas were mapped
+ to 0xff internally. However, this rendered the commas
+ indistinguishable from true 0xff characters in the values.
+ This no longer occurs. However, the fix requires that you
+ dump and reload any tables that have
+ <literal role="type">ENUM</literal> columns containing true
+ 0xff in their values: Dump the tables using
+ <command>mysqldump</command> with the current server before
+ upgrading from a version of MySQL 4.1 older than 4.1.23 to
+ version 4.1.23 or newer.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: The
+ interface to aggregate user-defined functions changed as of
+ MySQL 4.1.1. You must declare a
+ <literal>xxx_clear()</literal> function for each aggregate
+ function <literal>XXX()</literal>.
+ <literal>xxx_clear()</literal> is used instead of
+ <literal>xxx_reset()</literal>. See
+ <xref linkend="udf-aggr-calling"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change:</emphasis> MySQL
+ 4.1 stores table names and column names in
+ <literal>utf8</literal>. If you have table names or column
+ names that use characters outside of the standard 7-bit
+ US-ASCII range, you may have to do a
+ <command>mysqldump</command> of your tables in MySQL 4.0 and
+ restore them after upgrading to MySQL 4.1. The symptom for
+ this problem is that you get a <literal>table not
+ found</literal> error when trying to access your tables. In
+ this case, you should be able to downgrade back to MySQL 4.0
+ and access your data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Important note:</emphasis> 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, <literal>InnoDB</literal> is not aware of multiple
+ tablespaces.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ All tables and nonbinary string columns
+ (<literal role="type">CHAR</literal>,
+ <literal role="type">VARCHAR</literal>, and
+ <literal role="type">TEXT</literal>) have a character set.
+ See <xref linkend="charset"/>. Binary string columns
+ (<literal role="type">BINARY</literal>,
+ <literal role="type">VARBINARY</literal>, and
+ <literal role="type">BLOB</literal>) contain strings of
+ bytes and do not have a character set.
+ </para>
+
+ <para>
+ Character set information is displayed by
+ <literal role="stmt">SHOW CREATE TABLE</literal> and
+ <command>mysqldump</command>. (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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you were using columns with the <literal>CHAR
+ BINARY</literal> or <literal>VARCHAR BINARY</literal> data
+ types in MySQL 4.0, these were treated as binary strings. To
+ have them treated as binary strings in MySQL 4.1, you should
+ convert them to the <literal role="type">BINARY</literal>
+ and <literal role="type">VARBINARY</literal> data types,
+ respectively.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you have table columns that store character data
+ represented in a character set that the 4.1 server supports
+ directly, you can convert the columns to the proper
+ character set using the instructions in
+ <xref linkend="charset-conversion"/>. Also, database, table,
+ and column identifiers are stored internally using Unicode
+ (UTF-8) regardless of the default character set. See
+ <xref linkend="identifiers"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The table definition format used in
+ <filename>.frm</filename> files has changed slightly in 4.1.
+ MySQL 4.0 versions from 4.0.11 on can read the new
+ <filename>.frm</filename> 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
+ <command>mysqldump</command>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Windows servers support connections from local clients using
+ shared memory if run with the
+ <option role="mysqld">--shared-memory</option> option. If
+ you are running multiple servers this way on the same
+ Windows machine, you should use a different
+ <option>--shared-memory-base-name</option> option for each
+ server.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ As of MySQL 4.1.21, the
+ <literal role="sysvar">lc_time_names</literal> system
+ variable specifies the locale that controls the language
+ used to display day and month names and abbreviations. This
+ variable affects the output from the
+ <literal role="func">DATE_FORMAT()</literal>,
+ <literal role="func">DAYNAME()</literal> and
+ <literal role="func">MONTHNAME()</literal> functions. See
+ <xref linkend="locale-support"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ As of MySQL 4.1.10a, the server by default no longer loads
+ user-defined functions (UDFs) unless they have at least one
+ auxiliary symbol defined in addition to the main function
+ symbol. This behavior can be overridden with the
+ <option role="mysqld">--allow-suspicious-udfs</option>
+ option. See <xref linkend="udf-security"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Client Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ As of MySQL 4.1, <command>mysqldump</command> has the
+ <option role="mysqldump">--opt</option> and
+ <option role="mysqldump">--quote-names</option> options
+ enabled by default. You can turn these off using
+ <option role="mysqldump">--skip-opt</option> and
+ <option>--skip-quote-names</option>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">SQL Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>:
+ <literal role="type">TIMESTAMP</literal> is returned in
+ MySQL 4.1 as a string in <literal>'YYYY-MM-DD
+ HH:MM:SS'</literal> format. (See
+ <xref linkend="timestamp"/>.) From 4.0.12 on, the
+ <option>--new</option> option can be used to make a 4.0
+ server behave as 4.1 in this respect. The effect of this
+ option is described in <xref linkend="timestamp-pre-4-1"/>.
+ </para>
+
+ <para>
+ When running the server with <option>--new</option>, if you
+ want to have a <literal role="type">TIMESTAMP</literal>
+ column returned as a number (as MySQL 4.0 does by default),
+ you should add <literal>+0</literal> when you retrieve it:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT ts_col + 0 FROM <replaceable>tbl_name</replaceable>;</userinput>
+</programlisting>
+
+ <para>
+ Display widths for <literal role="type">TIMESTAMP</literal>
+ columns are no longer supported in MySQL 4.1. For example,
+ if you declare a column as <literal>TIMESTAMP(10)</literal>,
+ the <literal>(10)</literal> is ignored.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: Binary
+ values such as <literal>0xFFDF</literal> are assumed to be
+ strings instead of numbers. This fixes some problems with
+ character sets where it is convenient to input a string as a
+ binary value. With this change, you should use
+ <literal role="func">CAST()</literal> if you want to compare
+ binary values numerically as integers:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT CAST(0xFEFF AS UNSIGNED INTEGER)</userinput>
+ -> <userinput>< CAST(0xFF AS UNSIGNED INTEGER);</userinput>
+ -> 0
+</programlisting>
+
+ <para>
+ If you do not use <literal role="func">CAST()</literal>, a
+ lexical string comparison is made instead:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 0xFEFF < 0xFF;</userinput>
+ -> 1
+</programlisting>
+
+ <para>
+ Using binary items in a numeric context or comparing them
+ using the <literal>=</literal> operator should work as
+ before. (The <option>--new</option> option can be used from
+ 4.0.13 on to make a 4.0 server behave as 4.1 in this
+ respect.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change:</emphasis> Before
+ MySQL 4.1.13, conversion of
+ <literal role="type">DATETIME</literal> values to numeric
+ form by adding zero produced a result in
+ <literal>YYYYMMDDHHMMSS</literal> format. The result of
+ <literal>DATETIME+0</literal> is now in
+ <literal>YYYYMMDDHHMMSS.000000</literal> format.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change:</emphasis> In
+ MySQL 4.1.12, the behavior of
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> and
+ <literal role="stmt" condition="select">SELECT ... INTO
+ OUTFILE</literal> has changed when the <literal>FIELDS
+ TERMINATED BY</literal> and <literal>FIELDS ENCLOSED
+ BY</literal> values both are empty. Formerly, a column was
+ read or written using the display width of the column. For
+ example, <literal>INT(4)</literal> was read or written using
+ a field with a width of 4. Now columns are read and written
+ using a field width wide enough to hold all values in the
+ field. However, data files written before this change was
+ made might not be reloaded correctly with
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> for MySQL 4.1.12 and up. This change also
+ affects data files read by <command>mysqlimport</command>
+ and written by <command>mysqldump --tab</command>, which use
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> and
+ <literal role="stmt" condition="select">SELECT ... INTO
+ OUTFILE</literal>. For more information, see
+ <xref linkend="load-data"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: Before
+ MySQL 4.1.1, the statement parser was less strict and its
+ string-to-date conversion would ignore everything up to the
+ first digit. As a result, invalid statements such as the
+ following were accepted:
+ </para>
+
+<programlisting>
+INSERT INTO t (datetime_col) VALUES ('stuff 2005-02-11 10:17:01');
+</programlisting>
+
+ <para>
+ As of MySQL 4.1.1, the parser is stricter and treats the
+ string as an invalid date, so the preceding statement
+ results in a warning.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: In
+ MySQL 4.1.2, the <literal>Type</literal> column in the
+ output from <literal role="stmt">SHOW TABLE STATUS</literal>
+ was renamed to <literal>Engine</literal>. This affects
+ applications that identify output columns by name rather
+ than by position.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: The
+ syntax for multiple-table
+ <literal role="stmt">DELETE</literal> statements that use
+ table aliases changed between MySQL 4.0 and 4.1. In MySQL
+ 4.0, you should use the true table name to refer to any
+ table from which rows should be deleted:
+ </para>
+
+<programlisting>
+DELETE test FROM test AS t1, test2 WHERE ...
+</programlisting>
+
+ <para>
+ In MySQL 4.1, you must use the alias:
+ </para>
+
+<programlisting>
+DELETE t1 FROM test AS t1, test2 WHERE ...
+</programlisting>
+
+ <para>
+ We did not make this change in 4.0 to avoid breaking any old
+ 4.0 applications that were using the old syntax. However, if
+ you use such <literal role="stmt">DELETE</literal>
+ statements and are using replication, the change in syntax
+ means that a 4.0 master cannot replicate to 4.1 (or higher)
+ slaves.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Some keywords are reserved in MySQL 4.1 that were not
+ reserved in MySQL 4.0. See <xref linkend="reserved-words"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>LOAD DATA FROM MASTER</literal> and
+ <literal>LOAD TABLE FROM MASTER</literal> statements are
+ deprecated. See <xref linkend="load-data-from-master"/>, for
+ recommended alternatives.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For functions that produce a
+ <literal role="type">DATE</literal>,
+ <literal role="type">DATETIME</literal>, or
+ <literal role="type">TIME</literal> value, the result
+ returned to the client is fixed up to have a temporal type.
+ For example, in MySQL 4.1, you obtain the following:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT CAST('2001-1-1' AS DATETIME);</userinput>
+ -> '2001-01-01 00:00:00'
+</programlisting>
+
+ <para>
+ In MySQL 4.0, the result of the stement is different:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT CAST('2001-1-1' AS DATETIME);</userinput>
+ -> '2001-01-01'
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>DEFAULT</literal> values no longer can be specified
+ for <literal>AUTO_INCREMENT</literal> columns. (In 4.0, a
+ <literal>DEFAULT</literal> value is silently ignored; in
+ 4.1, an error occurs.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LIMIT</literal> no longer accepts negative
+ arguments. Use some large number (maximum
+ 18446744073709551615) instead of -1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>SERIALIZE</literal> is no longer a valid mode value
+ for the <literal role="sysvar">sql_mode</literal> variable.
+ You should use <literal>SET TRANSACTION ISOLATION LEVEL
+ SERIALIZABLE</literal> instead. <literal>SERIALIZE</literal>
+ is no longer valid for the
+ <option role="mysqld">--sql-mode</option> option for
+ <command>mysqld</command>, either. Use
+ <option>--transaction-isolation=SERIALIZABLE</option>
+ instead.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A new startup option named
+ <option>innodb_table_locks</option> was added that causes
+ <literal role="stmt" condition="lock-tables">LOCK
+ TABLE</literal> to also acquire <literal>InnoDB</literal>
+ table locks. This option is enabled by default. This can
+ cause deadlocks in applications that use
+ <literal role="sysvar">autocommit = 1</literal> and
+ <literal role="stmt">LOCK TABLES</literal>. If you
+ application encounters deadlocks after upgrading, you may
+ need to add <literal>innodb_table_locks = 0</literal> to
+ your <filename>my.cnf</filename> file.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">C API Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Incompatible change</emphasis>: The
+ <literal role="cfunc">mysql_shutdown()</literal> C API
+ function has an extra parameter as of MySQL 4.1.3:
+ <literal>SHUTDOWN</literal>-level. You should convert any
+ <function>mysql_shutdown(<replaceable>X</replaceable>)</function>
+ call you have in your application to
+ <function>mysql_shutdown(<replaceable>X</replaceable>,SHUTDOWN_DEFAULT)</function>.
+ Any third-party API that links against the C API library
+ must be modified to account for this change or it will not
+ compile.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Some C API calls such as
+ <literal role="cfunc">mysql_real_query()</literal> return
+ <literal>1</literal> on error, not <option>-1</option>. You
+ may have to change some old applications if they use
+ constructs like this:
+ </para>
+
+<programlisting>
+if (mysql_real_query(mysql_object, query, query_length) == -1)
+{
+ printf("Got error");
+}
+</programlisting>
+
+ <para>
+ Change the call to test for a nonzero value instead:
+ </para>
+
+<programlisting>
+if (mysql_real_query(mysql_object, query, query_length) != 0)
+{
+ printf("Got error");
+}
+</programlisting>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Password-Handling Changes:</emphasis>
+ </para>
+
+ <para>
+ The password hashing mechanism changed in 4.1 to provide better
+ security; this may cause compatibility problems if you have
+ clients using the client library from 4.0 or earlier. (It is
+ very likely that you 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 goals
+ of compatibility with old clients and security.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Only upgrade the client to use 4.1 client libraries (not the
+ server). No behavior changes (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.)
+ See <xref linkend="c-api-prepared-statements"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Upgrade to 4.1 and run the
+ <command>mysql_fix_privilege_tables</command> script to
+ widen the <literal>Password</literal> column in the
+ <literal>user</literal> table so that it can hold long
+ password hashes. However — to provide backward
+ compatibility allowing pre-4.1 clients to continue
+ connecting to their short-hash accounts — run the
+ server with the
+ <option role="mysqld">--old-passwords</option> option.
+ Eventually, when all your clients are upgraded to 4.1, you
+ can stop using the
+ <option role="mysqld">--old-passwords</option> server
+ option. You can also change the passwords for your MySQL
+ accounts to use the new more secure format. A 4.1
+ installation using only the improved authentication protocol
+ is the most secure one.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Further background on password hashing with respect to client
+ authentication and password-changing operations may be found in
+ <xref linkend="password-hashing"/>, and
+ <xref linkend="old-client"/>.
+ </para>
+
+ </section>
+
+ <section id="upgrading-from-3-23">
+
+ <title>Upgrading from MySQL 3.23 to 4.0</title>
+
+ <indexterm>
+ <primary>compatibility</primary>
+ <secondary>between MySQL versions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>upgrading</primary>
+ <secondary>3.23 to 4.0</secondary>
+ </indexterm>
+
+ <para>
+ In general, you should do the following when upgrading from
+ MySQL 3.23 to 4.0:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Read all the items in <xref linkend="upgrade"/>, to see
+ whether any of them might affect your applications.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Read all the items in the change list found later in this
+ section to see whether any of them might affect your
+ applications. Note particularly any that are marked
+ <emphasis role="bold">Known issue</emphasis> or
+ <emphasis role="bold">Incompatible change</emphasis>; these
+ result in incompatibilities with earlier versions of MySQL.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Read the 4.0 changelog to see what significant new features
+ you can use in 4.0. See <xref linkend="news-4-0-x"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are running MySQL Server on Windows, see
+ <xref linkend="windows-upgrading"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ After upgrading, update the grant tables to add new
+ privileges and features. This procedure uses the
+ <command>mysql_fix_privilege_tables</command> script and is
+ described in <xref linkend="mysql-fix-privilege-tables"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are using replication, see
+ <xref linkend="replication-upgrade"/>, for information on
+ upgrading your replication setup.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Edit any MySQL startup scripts or option files so that they
+ do not use any of the options described as deprecated later
+ in this section.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Convert your old <literal>ISAM</literal> tables to
+ <literal>MyISAM</literal> format. One way to do this is with
+ the <command>mysql_convert_table_format</command> script.
+ (This is a Perl script; it requires that
+ <literal>DBI</literal> be installed.) To convert all of the
+ tables in a given database, use this command:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql_convert_table_format database <replaceable>db_name</replaceable></userinput>
+</programlisting>
+
+ <para>
+ Note that the above command should be used only if
+ <emphasis>all</emphasis> tables in the database are
+ <literal>ISAM</literal> or <literal>MyISAM</literal> tables.
+ To avoid converting tables of other types to
+ <literal>MyISAM</literal>, you can explicitly list the names
+ of the <literal>ISAM</literal> tables following the database
+ name on the command line.
+ </para>
+
+ <para>
+ Individual tables can be changed to
+ <literal>MyISAM</literal> by using the following
+ <literal role="stmt">ALTER TABLE</literal> statement for
+ each table to be converted:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE <replaceable>tbl_name</replaceable> TYPE=MyISAM;</userinput>
+</programlisting>
+
+ <para>
+ If you are not sure of the storage engine for a given table,
+ use this statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>SHOW TABLE STATUS LIKE '<replaceable>tbl_name</replaceable>';</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Ensure that you do not have any MySQL clients that use
+ shared libraries (like the Perl
+ <literal>DBD::mysql</literal> module). If you do, you should
+ recompile them, because the data structures used in
+ <filename>libmysqlclient.so</filename> have changed. The
+ same applies to other MySQL interfaces such as the Python
+ <literal>MySQLdb</literal> module.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ MySQL 4.0 works even if you do not perform the preceding
+ actions, but you cannot 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 <literal>ISAM</literal> file format still
+ works in MySQL 4.0, but is deprecated and is not compiled in by
+ default as of MySQL 4.1. <literal>MyISAM</literal> tables should
+ be used instead.
+ </para>
+
+ <para>
+ Old clients should work with a MySQL 4.0 server without any
+ problems.
+ </para>
+
+ <para>
+ 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
+ <command>mysqldump</command> 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 an improved format for
+ full-text indexing that is not backward-compatible.
+ </para>
+
+ <para>
+ The following lists describe changes that may affect
+ applications and that you should watch out for when upgrading to
+ version 4.0.
+ </para>
+
+ <para>
+ <emphasis role="bold">Server Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ As of MySQL 4.0.24, the server by default no longer loads
+ user-defined functions unless they have at least one
+ auxiliary symbol defined in addition to the main function
+ symbol. This behavior can be overridden with the
+ <option role="mysqld">--allow-suspicious-udfs</option>
+ option. See <xref linkend="udf-security"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 4.0 has many new privileges in the
+ <literal>mysql.user</literal> table. See
+ <xref linkend="privileges-provided"/>.
+ </para>
+
+ <para>
+ In order for these new privileges to work, you must update
+ the grant tables. The procedure for this is described in
+ <xref linkend="mysql-fix-privilege-tables"/>. Until you do
+ this, all accounts have the <literal role="priv">SHOW
+ DATABASES</literal>, <literal role="priv">CREATE TEMPORARY
+ TABLES</literal>, and <literal role="priv">LOCK
+ TABLES</literal> privileges.
+ <literal role="priv">SUPER</literal> and
+ <literal role="priv">EXECUTE</literal> privileges take their
+ value from <literal role="priv">PROCESS</literal>.
+ <literal role="priv">REPLICATION SLAVE</literal> and
+ <literal role="priv">REPLICATION CLIENT</literal> take their
+ values from <literal role="priv">FILE</literal>.
+ </para>
+
+ <para>
+ 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 <literal role="stmt">GRANT</literal>
+ commands in the scripts, this is a good time to change your
+ scripts to use <literal role="stmt">GRANT</literal> instead
+ of modifying the grant tables directly.
+ </para>
+
+ <para>
+ From version 4.0.2 on, the option
+ <option role="mysqld">--safe-show-database</option> is
+ deprecated (and no longer does anything). See
+ <xref linkend="privileges-options"/>.
+ </para>
+
+ <para>
+ If you get <literal>Access denied</literal> errors for new
+ users in version 4.0.2 and up, you should check whether you
+ need some of the new grants that you did not need before. In
+ particular, you need <literal role="priv">REPLICATION
+ SLAVE</literal> (instead of
+ <literal role="priv">FILE</literal>) for new slave servers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>safe_mysqld</command> has been renamed to
+ <command>mysqld_safe</command>. For backward compatibility,
+ binary distributions will for some time include
+ <command>safe_mysqld</command> as a symlink to
+ <command>mysqld_safe</command>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>InnoDB</literal> support is included by default in
+ binary distributions. If you build MySQL from source,
+ <literal>InnoDB</literal> is configured in by default. If
+ you do not use <literal>InnoDB</literal> and want to save
+ memory when running a server that has
+ <literal>InnoDB</literal> support enabled, use the
+ <option role="mysqld" condition="innodb">--skip-innodb</option>
+ server startup option. To compile MySQL without
+ <literal>InnoDB</literal> support, run
+ <command>configure</command> with the
+ <option>--without-innodb</option> option.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Values for the startup parameters
+ <literal role="sysvar">myisam_max_extra_sort_file_size</literal>
+ and
+ <literal role="sysvar">myisam_max_extra_sort_file_size</literal>
+ are given in bytes (prior to 4.0.3,they were given in
+ megabytes).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>mysqld</command> has the option
+ <option role="mysqld">--temp-pool</option> enabled by
+ default because this gives better performance with some
+ operating systems (most notably Linux).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <command>mysqld</command> startup options
+ <option>--skip-locking</option> and
+ <option>--enable-locking</option> were renamed to
+ <option role="mysqld">--skip-external-locking</option> and
+ <option role="mysqld">--external-locking</option>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ External system locking of
+ <literal>MyISAM</literal>/<literal>ISAM</literal> files is
+ turned off by default. You can turn this on with
+ <option role="mysqld">--external-locking</option>. (However,
+ this is never needed for most users.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The following startup variables and options were renamed:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Name in 3.23</emphasis></entry>
+ <entry><emphasis role="bold">Name in 4.0 (and above)</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>myisam_bulk_insert_tree_size</literal></entry>
+ <entry><literal role="sysvar">bulk_insert_buffer_size</literal></entry>
+ </row>
+ <row>
+ <entry><literal>query_cache_startup_type</literal></entry>
+ <entry><literal role="sysvar">query_cache_type</literal></entry>
+ </row>
+ <row>
+ <entry><literal>record_buffer</literal></entry>
+ <entry><literal role="sysvar">read_buffer_size</literal></entry>
+ </row>
+ <row>
+ <entry><literal>record_rnd_buffer</literal></entry>
+ <entry><literal role="sysvar">read_rnd_buffer_size</literal></entry>
+ </row>
+ <row>
+ <entry><literal>sort_buffer</literal></entry>
+ <entry><literal role="sysvar">sort_buffer_size</literal></entry>
+ </row>
+ <row>
+ <entry><option>--warnings</option></entry>
+ <entry><option role="mysqld">--log-warnings</option></entry>
+ </row>
+ <row>
+ <entry><option>--err-log</option></entry>
+ <entry><option role="mysqld">--log-error</option> (for
+ <command>mysqld_safe</command>)</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ The startup options <literal>record_buffer</literal>,
+ <literal>sort_buffer</literal>, and
+ <option>warnings</option> still work in MySQL 4.0 but are
+ deprecated.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">SQL Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Some keywords are reserved in MySQL 4.0 that were not
+ reserved in MySQL 3.23. See
+ <xref linkend="reserved-words"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The following SQL variables have been renamed:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Name in 3.23</emphasis></entry>
+ <entry><emphasis role="bold">Name in 4.0 and above</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>sql_big_tables</literal></entry>
+ <entry><literal role="sysvar">big_tables</literal></entry>
+ </row>
+ <row>
+ <entry><literal>sql_low_priority_updates</literal></entry>
+ <entry><literal role="sysvar">low_priority_updates</literal></entry>
+ </row>
+ <row>
+ <entry><literal>sql_max_join_size</literal></entry>
+ <entry><literal role="sysvar">max_join_size</literal></entry>
+ </row>
+ <row>
+ <entry><literal>sql_query_cache_type</literal></entry>
+ <entry><literal role="sysvar">query_cache_type</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ The older names still work in MySQL 4.0 but are deprecated.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You must use <literal>SET GLOBAL
+ SQL_SLAVE_SKIP_COUNTER=skip_count</literal> instead of
+ <literal>SET SQL_SLAVE_SKIP_COUNTER=skip_count</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="stmt">SHOW MASTER STATUS</literal> returns an
+ empty set if binary logging is not enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="stmt">SHOW SLAVE STATUS</literal> returns an
+ empty set if the slave is not initialized.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="stmt">SHOW INDEX</literal> has two more
+ columns in 4.0 than in 3.23 (<literal>Null</literal> and
+ <literal>Index_type</literal>).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The format of <literal role="stmt">SHOW OPEN
+ TABLES</literal> changed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ As of MySQL 4.0.11, <literal>ORDER BY col_name
+ DESC</literal> sorts <literal>NULL</literal> values last. In
+ 3.23 and in earlier 4.0 versions, this was not always
+ consistent.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>CHECK</literal>, <literal>LOCALTIME</literal>, and
+ <literal>LOCALTIMESTAMP</literal> are reserved words.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="type">DOUBLE</literal> and
+ <literal role="type">FLOAT</literal> columns honor the
+ <literal>UNSIGNED</literal> flag on storage (previously,
+ <literal>UNSIGNED</literal> was ignored for these columns).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The result of all bitwise operators (<literal>|</literal>,
+ <literal>&</literal>, <literal><<</literal>,
+ <literal>>></literal>, and <literal>~</literal>) is
+ unsigned. This may cause problems if you are using them in a
+ context where you want a signed result. See
+ <xref linkend="cast-functions"/>.
+ </para>
+
+ <note>
+ <para>
+ When you use subtraction between integer values where one
+ is of type <literal>UNSIGNED</literal>, the result is
+ 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
+ <option role="mysqld">--sql-mode=NO_UNSIGNED_SUBTRACTION</option>
+ option when starting <command>mysqld</command>. See
+ <xref linkend="server-sql-mode"/>.
+ </para>
+ </note>
+ </listitem>
+
+ <listitem>
+ <para>
+ You should use integers to store values in
+ <literal role="type">BIGINT</literal> columns (instead of
+ using strings as in MySQL 3.23). Using strings still works,
+ but using integers is more efficient.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ In MySQL 3.23,
+ <literal role="stmt" condition="insert-select">INSERT INTO
+ ... SELECT</literal> always had <literal>IGNORE</literal>
+ enabled. As of 4.0.1, MySQL stops (and possibly rolls back)
+ by default in case of an error unless you specify
+ <literal>IGNORE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You should use
+ <literal role="stmt" condition="truncate">TRUNCATE
+ TABLE</literal> when you want to delete all rows from a
+ table and you do not need to obtain a count of the number of
+ rows that were deleted. (<literal>DELETE FROM
+ <replaceable>tbl_name</replaceable></literal> returns a row
+ count in 4.0 and does not reset the
+ <literal>AUTO_INCREMENT</literal> counter, and
+ <literal role="stmt" condition="truncate">TRUNCATE
+ TABLE</literal> is faster.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You get an error if you have an active transaction or
+ <literal role="stmt">LOCK TABLES</literal> statement when
+ trying to execute
+ <literal role="stmt" condition="truncate">TRUNCATE
+ TABLE</literal> or <literal role="stmt">DROP
+ DATABASE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To use <literal>MATCH ... AGAINST (... IN BOOLEAN
+ MODE)</literal> full-text searches, you must rebuild
+ existing table indexes using <literal>REPAIR TABLE
+ <replaceable>tbl_name</replaceable> USE_FRM</literal>. If
+ you attempt a boolean full-text search without rebuilding
+ the indexes in this manner, the search returns incorrect
+ results. See <xref linkend="fulltext-fine-tuning"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="func">LOCATE()</literal> and
+ <literal role="func">INSTR()</literal> are case sensitive if
+ one of the arguments is a binary string. Otherwise they are
+ case insensitive.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="func">STRCMP()</literal> 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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="func">HEX(<replaceable>str</replaceable>)</literal>
+ returns the characters in <replaceable>str</replaceable>
+ converted to hexadecimal. If you want to convert a number to
+ hexadecimal, you should ensure that you call
+ <literal role="func">HEX()</literal> with a numeric
+ argument.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal role="func">RAND(seed)</literal> returns a
+ different random number series in 4.0 than in 3.23; this was
+ done to further differentiate
+ <literal role="func">RAND(seed)</literal> and
+ <literal role="func">RAND(seed+1)</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The default type returned by
+ <literal role="func">IFNULL(A,B)</literal> is set to be the
+ more <quote>general</quote> of the types of
+ <literal>A</literal> and <literal>B</literal>. (The
+ general-to-specific order is string,
+ <literal role="type">REAL</literal>,
+ <literal role="type">INTEGER</literal>).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">C API Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The old C API functions
+ <literal role="cfunc">mysql_drop_db()</literal>,
+ <literal role="cfunc">mysql_create_db()</literal>, and
+ <literal role="cfunc">mysql_connect()</literal> are no
+ longer supported in MySQL 4.0 unless MySQL is compiled with
+ <literal>CFLAGS=-DUSE_OLD_FUNCTIONS</literal>. It is
+ preferable to change client programs to use the new 4.0 API
+ instead.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ In the <literal>MYSQL_FIELD</literal> structure,
+ <literal>length</literal> and <literal>max_length</literal>
+ have changed from <literal>unsigned int</literal> to
+ <literal>unsigned long</literal>. This should not cause any
+ problems, except that they may generate warning messages
+ when used as arguments in the <literal>printf()</literal>
+ class of functions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Multi-threaded clients should use
+ <literal role="cfunc">mysql_thread_init()</literal> and
+ <literal role="cfunc">mysql_thread_end()</literal>. See
+ <xref linkend="threaded-clients"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Other Changes:</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you want to recompile the Perl
+ <literal>DBD::mysql</literal> module, use a recent version.
+ Version 2.9003 is recommended. Versions older than 1.2218
+ should not be used because they use the deprecated
+ <literal role="cfunc">mysql_drop_db()</literal> call.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
+
+ <section id="downgrading">
+
+ <title>Downgrading MySQL</title>
+
+ <indexterm>
+ <primary>downgrading</primary>
+ </indexterm>
+
+ <para>
+ This section describes what you should do to downgrade to an older
+ MySQL version in the unlikely case that the previous version
+ worked better than the new one.
+ </para>
+
+ <para>
+ If you are downgrading within the same release series (for
+ example, from 4.0.20 to 4.0.19) the general rule is that you
+ merely need to install the new binaries on top of the old ones.
+ There is no need to do anything with the databases. As always,
+ however, it is always a good idea to make a backup.
+ </para>
+
+ <para>
+ The following items form a checklist of things you should do
+ whenever you perform a downgrade:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Read the upgrading section for the release series from which
+ you are downgrading to be sure that it does not have any
+ features you really need. See <xref linkend="upgrade"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If there is a downgrading section for that version, please
+ read it, too!
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To see which new features were added between the version to
+ which you are downgrading and your current version, see the
+ change logs (<xref linkend="news"/>).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Check <xref linkend="checking-table-incompatibilities"/>, to
+ see whether changes to table formats or to character sets or
+ collations were made between your current version of MySQL and
+ the version to which you are downgrading. If so and these
+ changes result in an incompatibility between MySQL versions,
+ you will need to downgrade the affected tables using the
+ instructions in <xref linkend="rebuilding-tables"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ If you downgrade from one release series to another, there may be
+ incompatibilities in table storage formats. In this case, use
+ <command>mysqldump</command> to dump your tables before
+ downgrading. After downgrading, reload the dump file using
+ <command>mysql</command> or <command>mysqlimport</command> to
+ re-create your tables. For examples, ee
+ <xref linkend="copying-databases"/>.
+ </para>
+
+ <para>
+ A typical symptom of a downward-incompatible table format change
+ when you downgrade is that you cannot open tables. In that case,
+ use the following procedure:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Stop the older MySQL server that you are downgrading to.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Restart the newer MySQL server you are downgrading from.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Dump any tables that were inaccessible to the older server by
+ using <command>mysqldump</command> to create a dump file.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Stop the newer MySQL server and restart the older one.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Reload the dump file into the older server. Your tables should
+ be accessible.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ It might also be the case that the structure of the system tables
+ in the <literal>mysql</literal> database has changed and that
+ downgrading introduces some loss of functionality or requires some
+ adjustments. Here are some examples:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Trigger creation requires the <literal>TRIGGER</literal>
+ privilege as of MySQL 5.1. In MySQL 5.0, there is no
+ <literal>TRIGGER</literal> privilege and
+ <literal>SUPER</literal> is required instead. If you downgrade
+ from MySQL 5.1 to 5.0, you will need to give the
+ <literal>SUPER</literal> privilege to those accounts that had
+ the <literal>TRIGGER</literal> privilege in 5.1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Triggers were added in MySQL 5.0, so if you downgrade from 5.0
+ to 4.1, you cannot use triggers at all.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <section id="downgrading-to-previous-series">
+
+ <title>Downgrading to MySQL 4.0</title>
+
+ <para>
+ The table format in 4.1 changed to include more and new
+ character set information. Because of this, you must use
+ <command>mysqldump</command> to dump any tables you have created
+ with the newer MySQL server. For example, if all the tables in a
+ particular database need to be dumped to be reverted back to
+ MySQL 4.0 format, use this command:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump --create-options --compatible=mysql40 <replaceable>db_name</replaceable> > <replaceable>dump_file</replaceable></userinput>
+</programlisting>
+
+ <para>
+ Then stop the newer server, restart the older server, and read
+ in the dump file:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql <replaceable>db_name</replaceable> < <replaceable>dump_file</replaceable></userinput>
+</programlisting>
+
+ <para>
+ In the special case that you are downgrading
+ <literal>MyISAM</literal> tables, no special treatment is
+ necessary if all columns in the tables contain only numeric
+ columns or string columns (<literal role="type">CHAR</literal>,
+ <literal role="type">VARCHAR</literal>,
+ <literal role="type">TEXT</literal>, and so forth) that contain
+ only <literal>latin1</literal> data. Your 4.1 tables should be
+ directly usable with a 4.0 server.
+ </para>
+
+ <para>
+ If you used the <command>mysql_fix_privilege_tables</command>
+ script to upgrade the grant tables, you can either use the
+ preceding method to convert them to back to MySQL 4.0 or do the
+ following in MySQL 4.1 (or above):
+ </para>
+
+<programlisting>
+ALTER TABLE mysql.user
+ CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
+ALTER TABLE mysql.db
+ CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
+ALTER TABLE mysql.host
+ CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
+ALTER TABLE mysql.tables_priv
+ CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
+ALTER TABLE mysql.columns_priv
+ CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
+ALTER TABLE mysql.func
+ CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
+</programlisting>
+
+ </section>
+
+ </section>
+
+ <section id="checking-table-incompatibilities">
+
+ <title>Checking Whether Tables or Indexes Must Be Rebuilt</title>
+
+ <para>
+ A binary upgrade or downgrade is one that installs one version of
+ MySQL <quote>in place</quote> over an existing version, without
+ dumping and reloading tables:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Stop the server for the existing version if it is running.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Install a different version of MySQL. This is an upgrade if
+ the new version is higher than the original version, a
+ downgrade if the version is lower.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Start the server for the new version.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ In many cases, the tables from the previous version of MySQL can
+ be used without problem by the new version. However, sometimes
+ changes occur that require tables or table indexes to be rebuilt,
+ as described in this section. If you have tables that are affected
+ by any of the issues described here, rebuild the tables or indexes
+ as necessary using the instructions given in
+ <xref linkend="rebuilding-tables"/>.
+ </para>
+
+ <para>
+ <emphasis role="bold">Index Incompatibilities</emphasis>
+ </para>
+
+ <para>
+ If you perform a binary upgrade without dumping and reloading
+ tables, you cannot upgrade directly from MySQL 4.1 to 5.1 or
+ higher. This occurs due to an incompatible change in the
+ <literal>MyISAM</literal> table index format in MySQL 5.0. Upgrade
+ from MySQL 4.1 to 5.0 and repair all <literal>MyISAM</literal>
+ tables. Then upgrade from MySQL 5.0 to 5.1 and check and repair
+ your tables.
+ </para>
+
+ <para>
+ Modifications to the handling of character sets or collations
+ might change the character sort order, which causes the ordering
+ of entries in any index that uses an affected character set or
+ collation to be incorrect. Such changes result in several possible
+ problems:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Comparison results that differ from previous results
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inability to find some index values due to misordered index
+ entries
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Misordered <literal>ORDER BY</literal> results
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Tables that <literal role="stmt">CHECK TABLE</literal> reports
+ as being in need of repair
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The solution to these problems is to rebuild any indexes that use
+ an affected character set or collation, either by dropping and
+ re-creating the indexes, or by dumping and reloading the entire
+ table. For information about rebuilding indexes, see
+ <xref linkend="rebuilding-tables"/>.
+ </para>
+
+ <para>
+ To check whether a table has indexes that must be rebuilt, consult
+ the following list. It indicates which versions of MySQL
+ introduced character set or collation changes that require indexes
+ to be rebuilt. Each entry indicates the version in which the
+ change occurred and the character sets or collations that the
+ change affects. If the change is associated with a particular bug
+ report, the bug number is given.
+ </para>
+
+ <para>
+ The list applies both for binary upgrades and downgrades. For
+ example, Bug#27877 was fixed in MySQL 5.1.24 and 5.4.0, so it
+ applies to upgrades from versions older than 5.1.24 to 5.1.24 or
+ newer, and to downgrades from 5.1.24 or newer to versions older
+ than 5.1.24.
+ </para>
+
+ <para>
+ Changes that cause index rebuilding to be necessary:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ MySQL 4.1.2 (Bug#3152)
+ </para>
+
+ <para>
+ String comparison works according to the SQL standard: Instead
+ of stripping end spaces before comparison, we now extend the
+ shorter string with spaces. The problem with this is that now
+ <literal>'a' > 'a\t'</literal>, which it was not before. If
+ you have any tables where you have indexes on
+ <literal role="type">CHAR</literal>,
+ <literal role="type">VARCHAR</literal> or
+ <literal role="type">TEXT</literal> column in which the last
+ character in index values may be less than
+ <literal>ASCII(32)</literal>, you should rebuild those
+ indexes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 4.1.3
+ </para>
+
+ <para>
+ <literal>InnoDB</literal> uses the same character set
+ comparison functions as MySQL for
+ non-<literal>latin1_swedish_ci</literal> character strings
+ that are not <literal>BINARY</literal>. This changes the
+ sorting order of space and characters with a code <
+ ASCII(32) in those character sets. This affects
+ <literal>InnoDB</literal> tables with an index on a
+ non-<literal>latin1_swedish_ci</literal> character set and
+ collation order column that is not <literal>BINARY</literal>
+ if that column contains characters with a code < ASCII(32).
+ (For MySQL 4.1.0 and 4.1.1, it affects indexes with any
+ character set and collation).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 5.0.48, 5.1.21 (Bug#29461)
+ </para>
+
+ <para>
+ Affects indexes for columns that use any of these character
+ sets: <literal>eucjpms</literal>, <literal>euc_kr</literal>,
+ <literal>gb2312</literal>, <literal>latin7</literal>,
+ <literal>macce</literal>, <literal>ujis</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 5.0.48, 5.1.23 (Bug#27562)
+ </para>
+
+ <para>
+ Affects indexes that use the
+ <literal>ascii_general_ci</literal> collation for columns that
+ contain any of these characters: <literal>'`'</literal> GRAVE
+ ACCENT, <literal>'['</literal> LEFT SQUARE BRACKET,
+ <literal>'\'</literal> REVERSE SOLIDUS, <literal>']'</literal>
+ RIGHT SQUARE BRACKET, <literal>'~'</literal> TILDE
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 5.1.24, 5.4.0 (Bug#27877)
+ </para>
+
+ <para>
+ Affects indexes that use the
+ <literal>utf8_general_ci</literal> or
+ <literal>ucs2_general_ci</literal> collation for columns that
+ contain <literal>'ß'</literal> LATIN SMALL LETTER SHARP S
+ (German).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="rebuilding-tables">
+
+ <title>Rebuilding or Repairing Tables or Indexes</title>
+
+ <indexterm>
+ <primary>table</primary>
+ <secondary>rebuilding</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>index</primary>
+ <secondary>rebuilding</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>table</primary>
+ <secondary>repair</secondary>
+ </indexterm>
+
+ <para>
+ This section describes how to rebuild a table. This can be
+ necessitated by changes to MySQL such as how data types are
+ handled or changes to character set handling. For example, an
+ error in a collation might have been corrected, necessitating a
+ table rebuild to update the indexes for character columns that
+ use the collation.
+(For examples, see
+ <xref linkend="checking-table-incompatibilities"/>.)
+It might also be that a table repair or upgrade
+ should be done as indicated by a table check operation such as
+ that performed by <literal>CHECK TABLE</literal> or
+ <command>mysqlcheck</command>.
+ </para>
+
+ <para>
+ Methods for rebuilding a table include dumping and reloading it,
+ or using <literal role="stmt">ALTER TABLE</literal> or
+ <literal role="stmt">REPAIR TABLE</literal>.
+ </para>
+
+ <note>
+ <para>
+ If you are rebuilding tables because a different version of
+ MySQL will not handle them after a binary (in-place) upgrade or
+ downgrade, you must use the dump-and-reload method. Dump the
+ tables <emphasis>before</emphasis> upgrading or downgrading
+ using your original version of MySQL. Then reload the tables
+ <emphasis>after</emphasis> upgrading or downgrading.
+ </para>
+
+ <para>
+ If you use the dump-and-reload method of rebuilding tables only
+ for the purpose of rebuilding indexes, you can perform the dump
+ either before or after upgrading or downgrading. Reloading still
+ must be done afterward.
+ </para>
+ </note>
+
+ <para>
+ To rebuild a table by dumping and reloading it, use
+ <command>mysqldump</command> to create a dump file and
+ <command>mysql</command> to reload the file:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump <replaceable>db_name</replaceable> t1 > dump.sql</userinput>
+shell> <userinput>mysql <replaceable>db_name</replaceable> < dump.sql</userinput>
+</programlisting>
+
+ <para>
+ To rebuild all the tables in a single database, specify the
+ database name without any following table name:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump <replaceable>db_name</replaceable> > dump.sql</userinput>
+shell> <userinput>mysql <replaceable>db_name</replaceable> < dump.sql</userinput>
+</programlisting>
+
+ <para>
+ To rebuild all tables in all databases, use the
+ <option role="mysqldump">--all-databases</option> option:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump --all-databases > dump.sql</userinput>
+shell> <userinput>mysql < dump.sql</userinput>
+</programlisting>
+
+ <para>
+ Before MySQL 4.1, use the <option role="mysqldump">--opt</option>
+ and <option role="mysqldump">--quote-names</option> options. As of
+ 4.1, those options are enabled by default.
+ </para>
+
+ <para>
+ To rebuild a table with <literal role="stmt">ALTER
+ TABLE</literal>, use a <quote>null</quote> alteration; that is, an
+ <literal role="stmt">ALTER TABLE</literal> statement that
+ <quote>changes</quote> the table to use the storage engine that it
+ already has. For example, if <literal>t1</literal> is a
+ <literal>MyISAM</literal> table, use this statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>ALTER TABLE t1 ENGINE = MyISAM;</userinput>
+</programlisting>
+
+ <para>
+ If you are not sure which storage engine to specify in the
+ <literal role="stmt">ALTER TABLE</literal> statement, use
+ <literal role="stmt">SHOW CREATE TABLE</literal> to display the
+ table definition.
+ </para>
+
+ <para>
+ If you must rebuild a table because a table checking operation
+ indicates that the table is corrupt or needs an upgrade, you can
+ use <literal role="stmt">REPAIR TABLE</literal> if that statement
+ supports the table's storage engine. For example, to repair a
+ <literal>MyISAM</literal> table, use this statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>REPAIR TABLE t1;</userinput>
+</programlisting>
+
+ <para>
+ For storage engines such as <literal>InnoDB</literal> that
+ <literal role="stmt">REPAIR TABLE</literal> does not support, use
+ <command>mysqldump</command> to create a dump file and
+ <command>mysql</command> to reload the file, as described earlier.
+ </para>
+
+ <para>
+ For specifics about which storage engines
+ <literal role="stmt">REPAIR TABLE</literal> supports, see
+ <xref linkend="repair-table"/>.
+ </para>
+
+ <para>
+ <command>mysqlcheck --repair</command> provides command-line
+ access to the <literal role="stmt">REPAIR TABLE</literal>
+ statement. This can be a more convenient means of repairing tables
+ because you can use the
+ <option role="mysqlcheck">--databases</option> or
+ <option role="mysqlcheck">--all-databases</option> option to
+ repair all tables in specific databases or all databases,
+ respectively:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqlcheck --repair --databases <replaceable>db_name</replaceable> ...</userinput>
+shell> <userinput>mysqlcheck --repair --all-databases</userinput>
+</programlisting>
+
+ </section>
+
+ <section id="copying-databases">
+
+ <title>Copying MySQL Databases to Another Machine</title>
+
+ <indexterm>
+ <primary>upgrading</primary>
+ <secondary>different architecture</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>copying databases</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>databases</primary>
+ <secondary>copying</secondary>
+ </indexterm>
+
+ <para>
+ If you are using MySQL 3.23 or later, you can copy the
+ <filename>.frm</filename>, <filename>.MYI</filename>, and
+ <filename>.MYD</filename> files for <literal>MyISAM</literal>
+ tables between different architectures that support the same
+ floating-point format. (MySQL takes care of any byte-swapping
+ issues.) See <xref linkend="myisam-storage-engine"/>.
+ </para>
+
+ <para>
+ The MySQL <literal>ISAM</literal> data and index files
+ (<filename>.ISD</filename> and <filename>*.ISM</filename>,
+ respectively) are dependent upon the architecture and, in some
+ cases, the operating system. If you want to move applications to
+ another machine having a different architecture or operating
+ system than that of the current machine, you should not try to
+ move a database by simply copying the files to the other machine.
+ Use <command>mysqldump</command> instead.
+ </para>
+
+ <para>
+ By default, <command>mysqldump</command> creates a file containing
+ SQL statements. You can then transfer the file to the other
+ machine and use it as input to the <command>mysql</command>
+ client.
+ </para>
+
+ <para>
+ Try <command>mysqldump --help</command> to see what options are
+ available. Before MySQL 4.1, if you are moving the data to a newer
+ version of MySQL, you should add the
+ <option role="mysqldump">--opt</option> option to the
+ <command>mysqldump</command> commands shown here, to take
+ advantage of any optimizations that result in a dump file that is
+ smaller and can be processed faster.
+ (<option role="mysqldump">--opt</option> is enabled by default as
+ of MySQL 4.1.)
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqladmin -h '<replaceable>other_hostname</replaceable>' create <replaceable>db_name</replaceable></userinput>
+shell> <userinput>mysqldump <replaceable>db_name</replaceable> | mysql -h '<replaceable>other_hostname</replaceable>' <replaceable>db_name</replaceable></userinput>
+</programlisting>
+
+ <remark role="todo">
+ The next example not only changes copy direction (from
+ local->remote to remote->local), it adds - -compress. But -
+ -compress is unrelated and can be used in either case. Split into
+ two examples.
+ </remark>
+
+ <para>
+ If you want to copy a database from a remote machine over a slow
+ network, you can use these commands:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqladmin create <replaceable>db_name</replaceable></userinput>
+shell> <userinput>mysqldump -h '<replaceable>other_hostname</replaceable>' --compress <replaceable>db_name</replaceable> | mysql <replaceable>db_name</replaceable></userinput>
+</programlisting>
+
+ <para>
+ You can also store the dump in a file, transfer the file to the
+ target machine, and then load the file into the database there.
+ For example, you can dump a database to a compressed file on the
+ source machine like this:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqldump --quick <replaceable>db_name</replaceable> | gzip > <replaceable>db_name</replaceable>.gz</userinput>
+</programlisting>
+
+ <para>
+ Transfer the file containing the database contents to the target
+ machine and run these commands there:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqladmin create <replaceable>db_name</replaceable></userinput>
+shell> <userinput>gunzip < <replaceable>db_name</replaceable>.gz | mysql <replaceable>db_name</replaceable></userinput>
+</programlisting>
+
+ <indexterm>
+ <primary>mysqldump</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>mysqlimport</primary>
+ </indexterm>
+
+ <para>
+ You can also use <command>mysqldump</command> and
+ <command>mysqlimport</command> to transfer the database. For very
+ large tables, this is much faster than simply using
+ <command>mysqldump</command>. In the following commands,
+ <replaceable>DUMPDIR</replaceable> represents the full path name
+ of the directory you use to store the output from
+ <command>mysqldump</command>.
+ </para>
+
+ <para>
+ First, create the directory for the output files and dump the
+ database:
+ </para>
+
+<programlisting>
+shell> <userinput>mkdir <replaceable>DUMPDIR</replaceable></userinput>
+shell> <userinput>mysqldump --tab=<replaceable>DUMPDIR</replaceable> <replaceable>db_name</replaceable></userinput>
+</programlisting>
+
+ <para>
+ Then transfer the files in the <replaceable>DUMPDIR</replaceable>
+ directory to a directory on the target machine and load the files
+ into MySQL there:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqladmin create <replaceable>db_name</replaceable> # create database</userinput>
+shell> <userinput>cat <replaceable>DUMPDIR</replaceable>/*.sql | mysql <replaceable>db_name</replaceable> # create tables in database</userinput>
+shell> <userinput>mysqlimport <replaceable>db_name</replaceable> <replaceable>DUMPDIR</replaceable>/*.txt # load data into tables</userinput>
+</programlisting>
+
+ <para>
+ Do not forget to copy the <literal>mysql</literal> database
+ because that is where the <literal>user</literal>,
+ <literal>db</literal>, and <literal>host</literal> grant tables
+ are stored. You might have to run commands as the MySQL
+ <literal>root</literal> user on the new machine until you have the
+ <literal>mysql</literal> database in place.
+ </para>
+
+ <para>
+ After you import the <literal>mysql</literal> database on the new
+ machine, execute <command>mysqladmin flush-privileges</command> so
+ that the server reloads the grant table information.
+ </para>
+
+ </section>
+
+</section>
Modified: trunk/refman-4.1/installing.xml
===================================================================
--- trunk/refman-4.1/installing.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-4.1/installing.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 2691; 106609 bytes
@@ -11764,2698 +11764,8 @@
</section>
- <section id="upgrade-downgrade">
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="installing-updowngrade.xml"/>
- <title>Upgrading or Downgrading MySQL</title>
-
- <indexterm>
- <primary>upgrading</primary>
- </indexterm>
-
- <indexterm>
- <primary>downgrading</primary>
- </indexterm>
-
- <section id="upgrade">
-
- <title>Upgrading MySQL</title>
-
- <indexterm>
- <primary>upgrading</primary>
- </indexterm>
-
- <para>
- As a general rule, to upgrade 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.
- </para>
-
- <para>
- Whenever you perform an upgrade, use the items in the following
- checklist as a guide:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Before any upgrade, back up your databases, including the
- <literal>mysql</literal> database that contains the grant
- tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Read <emphasis>all</emphasis> the notes the upgrading
- section for the release series to which you are upgrading.
- Read the change notes as well. These provide information
- about new features you can use.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Some releases of MySQL introduce changes to the structure of
- the grant tables to add new privileges or features. After
- you update to a new version of MySQL, you should update your
- grant tables to make sure that they have the current
- structure so that you can take advantage of any new
- capabilities. See
- <xref linkend="mysql-fix-privilege-tables"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are running MySQL Server on Windows, see
- <xref linkend="windows-upgrading"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are using replication, see
- <xref linkend="replication-upgrade"/>, for information on
- upgrading your replication setup.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are upgrading an installation originally produced by
- installing multiple RPM packages, it is best to upgrade all
- the packages, not just some. For example, if you previously
- installed the server and client RPMs, do not upgrade just
- the server RPM.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you previously installed a MySQL-Max distribution that
- includes a server named <command>mysqld-max</command>, and
- then upgrade later to a non-Max version of MySQL,
- <command>mysqld_safe</command> still attempts to run the old
- <command>mysqld-max</command> server. If you perform such an
- upgrade, you should remove the old
- <command>mysqld-max</command> server manually to ensure that
- <command>mysqld_safe</command> runs the new
- <command>mysqld</command> server.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you have created a user-defined function (UDF) with a
- given name and upgrade MySQL to a version that implements a
- new built-in function with the same name, the UDF becomes
- inaccessible. To correct this, use <literal role="stmt">DROP
- FUNCTION</literal> to drop the UDF, and then use
- <literal role="stmt">CREATE FUNCTION</literal> to re-create
- the UDF with a different nonconflicting name. The same is
- true if the new version of MySQL implements a built-in
- function with the same name as an existing stored function.
- See <xref linkend="function-resolution"/>, for the rules
- describing how the server interprets references to different
- kinds of functions.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- 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.
- Before MySQL 4.1, if you change the character set when running
- MySQL, you must run <command>myisamchk -r -q
- --set-character-set=<replaceable>charset_name</replaceable></command>
- on all <literal>MyISAM</literal> tables. Otherwise, your indexes
- may not be ordered correctly, because changing the character set
- may also change the sort order. As of MySQL 4.1, to convert
- tables created before 4.1 to the format that includes character
- set and collation information, use the instructions in
- <xref linkend="charset-conversion"/>.
- </para>
-
- <para>
- Normally, you can upgrade MySQL to a newer MySQL version without
- having to do any changes to your tables. Please confirm whether
- the upgrade notes to the particular version you are upgrading to
- tell you anything about this. If there would be any
- incompatibilities you can use <command>mysqldump</command> to
- dump your tables before upgrading. After upgrading, reload the
- dump file using <command>mysql</command> or
- <command>mysqlimport</command> to re-create your tables.
- </para>
-
- <para>
- If you are cautious about using new versions, you can always
- rename your old <command>mysqld</command> 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
- <command>mysqld</command> to <command>mysqld-4.0.18</command>.
- If your new <command>mysqld</command> then does something
- unexpected, you can simply shut it down and restart with your
- old <command>mysqld</command>.
- </para>
-
- <para>
- If, after an upgrade, you experience problems with recompiled
- client programs, such as <literal>Commands out of sync</literal>
- 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 <filename>mysql.h</filename> file
- and <filename>libmysqlclient.a</filename> library to verify that
- they are from the new MySQL distribution. If not, recompile your
- programs with the new headers and libraries.
- </para>
-
- <para>
- If problems occur, such as that the new
- <command>mysqld</command> server does not want to start or that
- you cannot connect without a password, verify that you do not
- have some old <filename>my.cnf</filename> file from your
- previous installation. You can check this with the
- <option role="general">--print-defaults</option> option (for
- example, <command>mysqld --print-defaults</command>). If this
- command displays anything other than the program name, you have
- an active <filename>my.cnf</filename> file that affects server
- or client operation.
- </para>
-
- <para>
- If your MySQL installation contains a large amount of data that
- might take a long time to convert after an in-place upgrade, you
- might find it useful to create a <quote>dummy</quote> database
- instance for assessing what conversions might be needed and the
- work involved to perform them. Make a copy of your MySQL
- instance that contains a full copy of the
- <literal>mysql</literal> database, plus all other databases
- without data. Run your upgrade procedure on this dummy instance
- to see what actions might be needed so that you can better
- evaluate the work involved when performing actual data
- conversion on your original database instance.
- </para>
-
- <para>
- It is a good idea to rebuild and reinstall the Perl
- <literal>DBD::mysql</literal> module whenever you install a new
- release of MySQL. The same applies to other MySQL interfaces as
- well, such as PHP <literal>mysql</literal> and extensions or the
- Python <literal>MySQLdb</literal> module.
- </para>
-
- <section id="upgrading-from-previous-series">
-
- <title>Upgrading from MySQL 4.0 to 4.1</title>
-
- <indexterm>
- <primary>compatibility</primary>
- <secondary>between MySQL versions</secondary>
- </indexterm>
-
- <indexterm>
- <primary>upgrading</primary>
- <secondary>4.0 to 4.1</secondary>
- </indexterm>
-
- <note>
- <para>
- 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.
- </para>
-
- <para>
- To upgrade to ¤t-series; from any previous version,
- MySQL recommends that you dump your tables with
- <command>mysqldump</command> before upgrading and reload the
- dump file after upgrading.
- </para>
- </note>
-
- <para>
- In general, you should do the following when upgrading from
- MySQL &previous-series; to ¤t-series;:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Read <emphasis>all</emphasis> the items in the following
- sections to see whether any of them might affect your
- applications:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <xref linkend="upgrade"/>, has general update
- information.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The items in the change lists found later in this
- section enable you to identify upgrade issues that
- apply to your current MySQL installation.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The MySQL ¤t-series; change history describes
- significant new features you can use in
- ¤t-series; or that differ from those found in
- MySQL &previous-series;. Some of these changes may
- result in incompatibilities. See
- <xref linkend="news-4-1-x"/>.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- Note particularly any changes that are marked
- <emphasis role="bold">Known issue</emphasis> or
- <emphasis role="bold">Incompatible change</emphasis>.
- These incompatibilities with earlier versions of MySQL may
- require your attention <emphasis>before you
- upgrade</emphasis>. Note particularly the items under
- <quote>Server Changes</quote> that related to changes in
- character set support.
- </para>
- </listitem>
-
- <listitem>
- <para>
- After upgrading, update the grant tables to obtain the new
- longer <literal>Password</literal> column that is needed
- for more secure handling of passwords. The procedure uses
- <command>mysql_fix_privilege_tables</command> and is
- described in <xref linkend="mysql-fix-privilege-tables"/>.
- If you do not do this, MySQL does not use the new more
- secure protocol to authenticate. Implications of the
- password-handling change for applications are given later
- in this section.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Check <xref linkend="checking-table-incompatibilities"/>,
- to see whether changes to table formats or to character
- sets or collations were made between your current version
- of MySQL and the version to which you are upgrading. If so
- and these changes result in an incompatibility between
- MySQL versions, you will need to upgrade the affected
- tables using the instructions in
- <xref linkend="rebuilding-tables"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are running MySQL Server on Windows, see
- <xref linkend="windows-upgrading"/>. You should also be
- aware that two of the Windows MySQL servers were renamed
- in MySQL 4.1. See <xref linkend="windows-select-server"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are using replication, see
- <xref linkend="replication-upgrade"/>, for information on
- upgrading your replication setup.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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 <command>mysqldump</command> to
- dump your <literal>BDB</literal> tables in text format and
- delete all <literal>log.XXXXXXXXXX</literal> files before
- you start MySQL 4.0 and reload the data.
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL 4.1.3 introduces support for per-connection time
- zones. See <xref linkend="time-zone-support"/>. To enable
- recognition of named time zones, you should create the
- time zone tables in the <literal>mysql</literal> database.
- For instructions, see <xref linkend="post-installation"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are using an old <literal>DBD-mysql</literal>
- module (<literal>Msql-MySQL-modules</literal>) you must
- upgrade to the newer <literal>DBD-mysql</literal> module.
- Anything above <literal>DBD-mysql</literal> 2.xx should be
- satisfactory.
- </para>
-
- <para>
- If you do not upgrade, some methods (such as
- <literal>DBI->do()</literal>) do not notice error
- conditions correctly.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The
- <option role="general">--defaults-file=<replaceable>option_file_name</replaceable></option>
- option gives an error if the option file does not exist.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Some notes about upgrading from MySQL 4.0 to MySQL 4.1 on
- Netware: Make sure to upgrade Perl and PHP versions.
- Download Perl 5 for Netware from
- <ulink url="http://forge.novell.com/modules/xfmod/project/?perl5"/>
- and PHP from
- <ulink url="http://forge.novell.com/modules/xfmod/project/?php"/>.
- Download and install the Perl module for MySQL 4.1 from
- <ulink url="http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1126"/>
- and the PHP Extension for MySQL 4.1 from
- <ulink url="http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1078"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- If your MySQL installation contains a large amount of data
- that might take a long time to convert after an in-place
- upgrade, you might find it useful to create a
- <quote>dummy</quote> database instance for assessing what
- conversions might be needed and the work involved to perform
- them. Make a copy of your MySQL instance that contains a full
- copy of the <literal>mysql</literal> database, plus all other
- databases without data. Run your upgrade procedure on this
- dummy instance to see what actions might be needed so that you
- can better evaluate the work involved when performing actual
- data conversion on your original database instance.
- </para>
-
- <para>
- 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.
- </para>
-
- <para>
- 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 <option>--new</option> startup
- option for <command>mysqld</command>. See
- <xref linkend="server-options"/>.
- </para>
-
- <para>
- 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 <literal>SET @@new=1</literal>
- command, or turn them off if they are on with <literal>SET
- @@new=0</literal>.
- </para>
-
- <para>
- If you believe that some of the 4.1 changes affect you, we
- recommend that before upgrading to 4.1, you download the
- latest MySQL 4.0 version and run it with the
- <option>--new</option> option by adding the following to your
- config file:
- </para>
-
-<programlisting>
-[mysqld-4.0]
-new
-</programlisting>
-
- <para>
- That way you can test the new behaviors in 4.0 to make sure
- that your applications work with them. This helps you have a
- smooth, painless transition when you perform a full upgrade to
- 4.1 later. Putting the <option>--new</option> option in the
- <literal>[mysqld-4.0]</literal> option group ensures that you
- do not accidentally later run the 4.1 version with the
- <option>--new</option> option.
- </para>
-
- <para>
- The following lists describe changes that may affect
- applications and that you should watch out for when upgrading
- to version 4.1.
- </para>
-
- <para>
- <emphasis role="bold">Server Changes:</emphasis>
- </para>
-
- <para>
- The most notable change is that character set support has been
- improved. The server supports multiple character sets, and all
- tables and nonbinary string columns
- (<literal role="type">CHAR</literal>,
- <literal role="type">VARCHAR</literal>, and
- <literal role="type">TEXT</literal>) have a character set. See
- <xref linkend="charset"/>. Binary string columns
- (<literal role="type">BINARY</literal>,
- <literal role="type">VARBINARY</literal>, and
- <literal role="type">BLOB</literal>) contain strings of bytes
- and do not have a character set.
- </para>
-
- <note>
- <para>
- This change in character set support results in the
- potential for table damage if you do not upgrade properly,
- so consider carefully the incompatibilities noted here.
- </para>
- </note>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>:
- There are conditions under which you should rebuild
- tables. In general, to rebuild a table, dump it with
- <command>mysqldump</command> and reload the dump file.
- Some items in the following list indicate alternatives
- means for rebuilding.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If you have created or used <literal>InnoDB</literal>
- tables with <literal role="type">TIMESTAMP</literal>
- columns in MySQL versions 4.1.0 to 4.1.3, you must
- rebuild those tables when you upgrade to MySQL 4.1.4
- or later. The storage format in those MySQL versions
- for <literal role="type">TIMESTAMP</literal> columns
- was incorrect. If you upgrade from MySQL 4.0 to 4.1.4
- or later, no rebuild of tables with
- <literal role="type">TIMESTAMP</literal> columns is
- needed.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Starting from MySQL 4.1.3, <literal>InnoDB</literal>
- uses the same character set comparison functions as
- MySQL for non-<literal>latin1_swedish_ci</literal>
- character strings that are not
- <literal>BINARY</literal>. This changes the sorting
- order of space and characters with a code <
- ASCII(32) in those character sets. For
- <literal>latin1_swedish_ci</literal> character strings
- and <literal>BINARY</literal> strings,
- <literal>InnoDB</literal> uses its own
- pad-spaces-at-end comparison method, which stays
- unchanged. Note that
- <literal>latin1_swedish_ci</literal> is the default
- collation order for <literal>latin1</literal> in 4.0.
- If you have an <literal>InnoDB</literal> table created
- with MySQL 4.1.2 or earlier, with an index on a
- non-<literal>latin1_swedish_ci</literal> character set
- and collation order column that is not
- <literal>BINARY</literal> (in the case of 4.1.0 and
- 4.1.1, with any character set and collation), and that
- column may contain characters with a code <
- ASCII(32), you should do <literal role="stmt">ALTER
- TABLE</literal> or <literal role="stmt">OPTIMIZE
- TABLE</literal> on it to regenerate the index, after
- upgrading to MySQL 4.1.3 or later. You can also
- rebuild the table from a dump.
- </para>
-
- <para>
- <literal>MyISAM</literal> tables also have to be
- rebuilt or repaired in these cases. You can use
- <command>mysqldump</command> to dump them in 4.0 and
- then reload them in 4.1. An alternative is to use
- <literal role="stmt">OPTIMIZE TABLE</literal> after
- upgrading, but this <emphasis>must</emphasis> be done
- before any updates are made in 4.1.
- </para>
- </listitem>
-
- <listitem>
- <para>
- As of MySQL 4.1.2, string comparison works according
- to the SQL standard: Instead of stripping end spaces
- before comparison, we now extend the shorter string
- with spaces. The problem with this is that now
- <literal>'a' > 'a\t'</literal>, which it was not
- before. If you have any tables where you have indexes
- on <literal role="type">CHAR</literal>,
- <literal role="type">VARCHAR</literal> or
- <literal role="type">TEXT</literal> column in which
- the last character in index values may be less than
- <literal>ASCII(32)</literal>, you should rebuild those
- indexes to ensure that the table is correct.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you have used column prefix indexes on UTF-8
- columns or other multi-byte character set columns in
- MySQL 4.1.0 to 4.1.5, you must rebuild the tables when
- you upgrade to MySQL 4.1.6 or later.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you have used accent characters (characters with
- byte values of 128 to 255) in database names, table
- names, constraint names, or column names in versions
- of MySQL earlier than 4.1, you cannot upgrade to MySQL
- 4.1 directly, because 4.1 uses UTF-8 to store
- metadata. Use <literal role="stmt">RENAME
- TABLE</literal> to overcome this if the accent
- character is in the table name or the database name,
- or rebuild the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MyISAM</literal> tables now use an improved
- checksum algorithm in MySQL 4.1. If you have
- <literal>MyISAM</literal> tables with live checksum
- enabled (you used <literal>CHECKSUM=1</literal> in
- <literal role="stmt">CREATE TABLE</literal> or
- <literal role="stmt">ALTER TABLE</literal>), these
- tables appear to be corrupted following an upgrade.
- Use <literal role="stmt">REPAIR TABLE</literal> to
- recalculate the checksum for each such table.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>:
- MySQL interprets length specifications in character column
- definitions in characters. (Earlier versions interpret
- them in bytes.) For example,
- <literal>CHAR(<replaceable>N</replaceable>)</literal>
- means <replaceable>N</replaceable> characters, not
- <replaceable>N</replaceable> bytes.
- </para>
-
- <para>
- For single-byte character sets, this change makes no
- difference. However, if you upgrade to MySQL 4.1 and
- configure the server to use a multi-byte character set,
- the apparent length of character columns changes. Suppose
- that a 4.0 table contains a <literal>CHAR(8)</literal>
- column used to store <literal>ujis</literal> characters.
- Eight bytes can store from two to four
- <literal>ujis</literal> characters. If you upgrade to 4.1
- and configure the server to use <literal>ujis</literal> as
- its default character set, the server interprets character
- column lengths based on the maximum size of a
- <literal>ujis</literal> character, which is three bytes.
- The number of three-byte characters that fit in eight
- bytes is two. Consequently, if you use
- <literal role="stmt">SHOW CREATE TABLE</literal> to view
- the table definition, MySQL displays
- <literal>CHAR(2)</literal>. You can retrieve existing data
- from the table, but you can only store new values
- containing up to two characters. To correct this issue,
- use <literal role="stmt">ALTER TABLE</literal> to change
- the column definition. For example:
- </para>
-
-<programlisting>
-ALTER TABLE <replaceable>tbl_name</replaceable> MODIFY <replaceable>col_name</replaceable> CHAR(8);
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change:</emphasis> As
- of MySQL 4.1.2, handling of the
- <literal role="type">FLOAT</literal> and
- <literal role="type">DOUBLE</literal> floating-point data
- types is more strict to follow standard SQL. For example,
- a data type of <literal>FLOAT(3,1)</literal> stores a
- maximum value of 99.9. Before 4.1.2, the server allowed
- larger numbers to be stored. That is, it stored a value
- such as 100.0 as 100.0. As of 4.1.2, the server clips
- 100.0 to the maximum allowable value of 99.9. If you have
- tables that were created before MySQL 4.1.2 and that
- contain floating-point data not strictly legal for the
- data type, you should alter the data types of those
- columns. For example:
- </para>
-
-<programlisting>
-ALTER TABLE <replaceable>tbl_name</replaceable> MODIFY <replaceable>col_name</replaceable> FLOAT(4,1);
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>: In
- connection with the support for per-connection time zones
- in MySQL 4.1.3, the
- <literal role="sysvar">timezone</literal> system variable
- was renamed to
- <literal role="sysvar">system_time_zone</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>: For
- <literal role="type">ENUM</literal> columns that had
- enumeration values containing commas, the commas were
- mapped to 0xff internally. However, this rendered the
- commas indistinguishable from true 0xff characters in the
- values. This no longer occurs. However, the fix requires
- that you dump and reload any tables that have
- <literal role="type">ENUM</literal> columns containing
- true 0xff in their values: Dump the tables using
- <command>mysqldump</command> with the current server
- before upgrading from a version of MySQL 4.1 older than
- 4.1.23 to version 4.1.23 or newer.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>: The
- interface to aggregate user-defined functions changed as
- of MySQL 4.1.1. You must declare a
- <literal>xxx_clear()</literal> function for each aggregate
- function <literal>XXX()</literal>.
- <literal>xxx_clear()</literal> is used instead of
- <literal>xxx_reset()</literal>. See
- <xref linkend="udf-aggr-calling"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change:</emphasis>
- MySQL 4.1 stores table names and column names in
- <literal>utf8</literal>. If you have table names or column
- names that use characters outside of the standard 7-bit
- US-ASCII range, you may have to do a
- <command>mysqldump</command> of your tables in MySQL 4.0
- and restore them after upgrading to MySQL 4.1. The symptom
- for this problem is that you get a <literal>table not
- found</literal> error when trying to access your tables.
- In this case, you should be able to downgrade back to
- MySQL 4.0 and access your data.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Important note:</emphasis> 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, <literal>InnoDB</literal> is not aware
- of multiple tablespaces.
- </para>
- </listitem>
-
- <listitem>
- <para>
- All tables and nonbinary string columns
- (<literal role="type">CHAR</literal>,
- <literal role="type">VARCHAR</literal>, and
- <literal role="type">TEXT</literal>) have a character set.
- See <xref linkend="charset"/>. Binary string columns
- (<literal role="type">BINARY</literal>,
- <literal role="type">VARBINARY</literal>, and
- <literal role="type">BLOB</literal>) contain strings of
- bytes and do not have a character set.
- </para>
-
- <para>
- Character set information is displayed by
- <literal role="stmt">SHOW CREATE TABLE</literal> and
- <command>mysqldump</command>. (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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you were using columns with the <literal>CHAR
- BINARY</literal> or <literal>VARCHAR BINARY</literal> data
- types in MySQL 4.0, these were treated as binary strings.
- To have them treated as binary strings in MySQL 4.1, you
- should convert them to the
- <literal role="type">BINARY</literal> and
- <literal role="type">VARBINARY</literal> data types,
- respectively.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you have table columns that store character data
- represented in a character set that the 4.1 server
- supports directly, you can convert the columns to the
- proper character set using the instructions in
- <xref linkend="charset-conversion"/>. Also, database,
- table, and column identifiers are stored internally using
- Unicode (UTF-8) regardless of the default character set.
- See <xref linkend="identifiers"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The table definition format used in
- <filename>.frm</filename> files has changed slightly in
- 4.1. MySQL 4.0 versions from 4.0.11 on can read the new
- <filename>.frm</filename> 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
- <command>mysqldump</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Windows servers support connections from local clients
- using shared memory if run with the
- <option role="mysqld">--shared-memory</option> option. If
- you are running multiple servers this way on the same
- Windows machine, you should use a different
- <option>--shared-memory-base-name</option> option for each
- server.
- </para>
- </listitem>
-
- <listitem>
- <para>
- As of MySQL 4.1.21, the
- <literal role="sysvar">lc_time_names</literal> system
- variable specifies the locale that controls the language
- used to display day and month names and abbreviations.
- This variable affects the output from the
- <literal role="func">DATE_FORMAT()</literal>,
- <literal role="func">DAYNAME()</literal> and
- <literal role="func">MONTHNAME()</literal> functions. See
- <xref linkend="locale-support"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- As of MySQL 4.1.10a, the server by default no longer loads
- user-defined functions (UDFs) unless they have at least
- one auxiliary symbol defined in addition to the main
- function symbol. This behavior can be overridden with the
- <option role="mysqld">--allow-suspicious-udfs</option>
- option. See <xref linkend="udf-security"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Client Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- As of MySQL 4.1, <command>mysqldump</command> has the
- <option role="mysqldump">--opt</option> and
- <option role="mysqldump">--quote-names</option> options
- enabled by default. You can turn these off using
- <option role="mysqldump">--skip-opt</option> and
- <option>--skip-quote-names</option>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">SQL Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>:
- <literal role="type">TIMESTAMP</literal> is returned in
- MySQL 4.1 as a string in <literal>'YYYY-MM-DD
- HH:MM:SS'</literal> format. (See
- <xref linkend="timestamp"/>.) From 4.0.12 on, the
- <option>--new</option> option can be used to make a 4.0
- server behave as 4.1 in this respect. The effect of this
- option is described in
- <xref linkend="timestamp-pre-4-1"/>.
- </para>
-
- <para>
- When running the server with <option>--new</option>, if
- you want to have a
- <literal role="type">TIMESTAMP</literal> column returned
- as a number (as MySQL 4.0 does by default), you should add
- <literal>+0</literal> when you retrieve it:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT ts_col + 0 FROM <replaceable>tbl_name</replaceable>;</userinput>
-</programlisting>
-
- <para>
- Display widths for
- <literal role="type">TIMESTAMP</literal> columns are no
- longer supported in MySQL 4.1. For example, if you declare
- a column as <literal>TIMESTAMP(10)</literal>, the
- <literal>(10)</literal> is ignored.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>:
- Binary values such as <literal>0xFFDF</literal> are
- assumed to be strings instead of numbers. This fixes some
- problems with character sets where it is convenient to
- input a string as a binary value. With this change, you
- should use <literal role="func">CAST()</literal> if you
- want to compare binary values numerically as integers:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT CAST(0xFEFF AS UNSIGNED INTEGER)</userinput>
- -> <userinput>< CAST(0xFF AS UNSIGNED INTEGER);</userinput>
- -> 0
-</programlisting>
-
- <para>
- If you do not use <literal role="func">CAST()</literal>, a
- lexical string comparison is made instead:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT 0xFEFF < 0xFF;</userinput>
- -> 1
-</programlisting>
-
- <para>
- Using binary items in a numeric context or comparing them
- using the <literal>=</literal> operator should work as
- before. (The <option>--new</option> option can be used
- from 4.0.13 on to make a 4.0 server behave as 4.1 in this
- respect.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change:</emphasis>
- Before MySQL 4.1.13, conversion of
- <literal role="type">DATETIME</literal> values to numeric
- form by adding zero produced a result in
- <literal>YYYYMMDDHHMMSS</literal> format. The result of
- <literal>DATETIME+0</literal> is now in
- <literal>YYYYMMDDHHMMSS.000000</literal> format.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change:</emphasis> In
- MySQL 4.1.12, the behavior of
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> and
- <literal role="stmt" condition="select">SELECT ... INTO
- OUTFILE</literal> has changed when the <literal>FIELDS
- TERMINATED BY</literal> and <literal>FIELDS ENCLOSED
- BY</literal> values both are empty. Formerly, a column was
- read or written using the display width of the column. For
- example, <literal>INT(4)</literal> was read or written
- using a field with a width of 4. Now columns are read and
- written using a field width wide enough to hold all values
- in the field. However, data files written before this
- change was made might not be reloaded correctly with
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> for MySQL 4.1.12 and up. This change also
- affects data files read by <command>mysqlimport</command>
- and written by <command>mysqldump --tab</command>, which
- use <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> and
- <literal role="stmt" condition="select">SELECT ... INTO
- OUTFILE</literal>. For more information, see
- <xref linkend="load-data"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>:
- Before MySQL 4.1.1, the statement parser was less strict
- and its string-to-date conversion would ignore everything
- up to the first digit. As a result, invalid statements
- such as the following were accepted:
- </para>
-
-<programlisting>
-INSERT INTO t (datetime_col) VALUES ('stuff 2005-02-11 10:17:01');
-</programlisting>
-
- <para>
- As of MySQL 4.1.1, the parser is stricter and treats the
- string as an invalid date, so the preceding statement
- results in a warning.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>: In
- MySQL 4.1.2, the <literal>Type</literal> column in the
- output from <literal role="stmt">SHOW TABLE
- STATUS</literal> was renamed to <literal>Engine</literal>.
- This affects applications that identify output columns by
- name rather than by position.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>: The
- syntax for multiple-table
- <literal role="stmt">DELETE</literal> statements that use
- table aliases changed between MySQL 4.0 and 4.1. In MySQL
- 4.0, you should use the true table name to refer to any
- table from which rows should be deleted:
- </para>
-
-<programlisting>
-DELETE test FROM test AS t1, test2 WHERE ...
-</programlisting>
-
- <para>
- In MySQL 4.1, you must use the alias:
- </para>
-
-<programlisting>
-DELETE t1 FROM test AS t1, test2 WHERE ...
-</programlisting>
-
- <para>
- We did not make this change in 4.0 to avoid breaking any
- old 4.0 applications that were using the old syntax.
- However, if you use such
- <literal role="stmt">DELETE</literal> statements and are
- using replication, the change in syntax means that a 4.0
- master cannot replicate to 4.1 (or higher) slaves.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Some keywords are reserved in MySQL 4.1 that were not
- reserved in MySQL 4.0. See
- <xref linkend="reserved-words"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>LOAD DATA FROM MASTER</literal> and
- <literal>LOAD TABLE FROM MASTER</literal> statements are
- deprecated. See <xref linkend="load-data-from-master"/>,
- for recommended alternatives.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For functions that produce a
- <literal role="type">DATE</literal>,
- <literal role="type">DATETIME</literal>, or
- <literal role="type">TIME</literal> value, the result
- returned to the client is fixed up to have a temporal
- type. For example, in MySQL 4.1, you obtain the following:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT CAST('2001-1-1' AS DATETIME);</userinput>
- -> '2001-01-01 00:00:00'
-</programlisting>
-
- <para>
- In MySQL 4.0, the result of the stement is different:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT CAST('2001-1-1' AS DATETIME);</userinput>
- -> '2001-01-01'
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- <literal>DEFAULT</literal> values no longer can be
- specified for <literal>AUTO_INCREMENT</literal> columns.
- (In 4.0, a <literal>DEFAULT</literal> value is silently
- ignored; in 4.1, an error occurs.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LIMIT</literal> no longer accepts negative
- arguments. Use some large number (maximum
- 18446744073709551615) instead of -1.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>SERIALIZE</literal> is no longer a valid mode
- value for the <literal role="sysvar">sql_mode</literal>
- variable. You should use <literal>SET TRANSACTION
- ISOLATION LEVEL SERIALIZABLE</literal> instead.
- <literal>SERIALIZE</literal> is no longer valid for the
- <option role="mysqld">--sql-mode</option> option for
- <command>mysqld</command>, either. Use
- <option>--transaction-isolation=SERIALIZABLE</option>
- instead.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A new startup option named
- <option>innodb_table_locks</option> was added that causes
- <literal role="stmt" condition="lock-tables">LOCK
- TABLE</literal> to also acquire <literal>InnoDB</literal>
- table locks. This option is enabled by default. This can
- cause deadlocks in applications that use
- <literal role="sysvar">autocommit = 1</literal> and
- <literal role="stmt">LOCK TABLES</literal>. If you
- application encounters deadlocks after upgrading, you may
- need to add <literal>innodb_table_locks = 0</literal> to
- your <filename>my.cnf</filename> file.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">C API Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <emphasis role="bold">Incompatible change</emphasis>: The
- <literal role="cfunc">mysql_shutdown()</literal> C API
- function has an extra parameter as of MySQL 4.1.3:
- <literal>SHUTDOWN</literal>-level. You should convert any
- <function>mysql_shutdown(<replaceable>X</replaceable>)</function>
- call you have in your application to
- <function>mysql_shutdown(<replaceable>X</replaceable>,SHUTDOWN_DEFAULT)</function>.
- Any third-party API that links against the C API library
- must be modified to account for this change or it will not
- compile.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Some C API calls such as
- <literal role="cfunc">mysql_real_query()</literal> return
- <literal>1</literal> on error, not <option>-1</option>.
- You may have to change some old applications if they use
- constructs like this:
- </para>
-
-<programlisting>
-if (mysql_real_query(mysql_object, query, query_length) == -1)
-{
- printf("Got error");
-}
-</programlisting>
-
- <para>
- Change the call to test for a nonzero value instead:
- </para>
-
-<programlisting>
-if (mysql_real_query(mysql_object, query, query_length) != 0)
-{
- printf("Got error");
-}
-</programlisting>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Password-Handling Changes:</emphasis>
- </para>
-
- <para>
- The password hashing mechanism changed in 4.1 to provide
- better security; this may cause compatibility problems if you
- have clients using the client library from 4.0 or earlier. (It
- is very likely that you 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 goals
- of compatibility with old clients and security.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Only upgrade the client to use 4.1 client libraries (not
- the server). No behavior changes (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.) See
- <xref linkend="c-api-prepared-statements"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Upgrade to 4.1 and run the
- <command>mysql_fix_privilege_tables</command> script to
- widen the <literal>Password</literal> column in the
- <literal>user</literal> table so that it can hold long
- password hashes. However — to provide backward
- compatibility allowing pre-4.1 clients to continue
- connecting to their short-hash accounts — run the
- server with the
- <option role="mysqld">--old-passwords</option> option.
- Eventually, when all your clients are upgraded to 4.1, you
- can stop using the
- <option role="mysqld">--old-passwords</option> server
- option. You can also change the passwords for your MySQL
- accounts to use the new more secure format. A 4.1
- installation using only the improved authentication
- protocol is the most secure one.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Further background on password hashing with respect to client
- authentication and password-changing operations may be found
- in <xref linkend="password-hashing"/>, and
- <xref linkend="old-client"/>.
- </para>
-
- </section>
-
- <section id="upgrading-from-3-23">
-
- <title>Upgrading from MySQL 3.23 to 4.0</title>
-
- <indexterm>
- <primary>compatibility</primary>
- <secondary>between MySQL versions</secondary>
- </indexterm>
-
- <indexterm>
- <primary>upgrading</primary>
- <secondary>3.23 to 4.0</secondary>
- </indexterm>
-
- <para>
- In general, you should do the following when upgrading from
- MySQL 3.23 to 4.0:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Read all the items in <xref linkend="upgrade"/>, to see
- whether any of them might affect your applications.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Read all the items in the change list found later in this
- section to see whether any of them might affect your
- applications. Note particularly any that are marked
- <emphasis role="bold">Known issue</emphasis> or
- <emphasis role="bold">Incompatible change</emphasis>;
- these result in incompatibilities with earlier versions of
- MySQL.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Read the 4.0 changelog to see what significant new
- features you can use in 4.0. See
- <xref linkend="news-4-0-x"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are running MySQL Server on Windows, see
- <xref linkend="windows-upgrading"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- After upgrading, update the grant tables to add new
- privileges and features. This procedure uses the
- <command>mysql_fix_privilege_tables</command> script and
- is described in
- <xref linkend="mysql-fix-privilege-tables"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are using replication, see
- <xref linkend="replication-upgrade"/>, for information on
- upgrading your replication setup.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Edit any MySQL startup scripts or option files so that
- they do not use any of the options described as deprecated
- later in this section.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Convert your old <literal>ISAM</literal> tables to
- <literal>MyISAM</literal> format. One way to do this is
- with the <command>mysql_convert_table_format</command>
- script. (This is a Perl script; it requires that
- <literal>DBI</literal> be installed.) To convert all of
- the tables in a given database, use this command:
- </para>
-
-<programlisting>
-shell> <userinput>mysql_convert_table_format database <replaceable>db_name</replaceable></userinput>
-</programlisting>
-
- <para>
- Note that the above command should be used only if
- <emphasis>all</emphasis> tables in the database are
- <literal>ISAM</literal> or <literal>MyISAM</literal>
- tables. To avoid converting tables of other types to
- <literal>MyISAM</literal>, you can explicitly list the
- names of the <literal>ISAM</literal> tables following the
- database name on the command line.
- </para>
-
- <para>
- Individual tables can be changed to
- <literal>MyISAM</literal> by using the following
- <literal role="stmt">ALTER TABLE</literal> statement for
- each table to be converted:
- </para>
-
-<programlisting>
-mysql> <userinput>ALTER TABLE <replaceable>tbl_name</replaceable> TYPE=MyISAM;</userinput>
-</programlisting>
-
- <para>
- If you are not sure of the storage engine for a given
- table, use this statement:
- </para>
-
-<programlisting>
-mysql> <userinput>SHOW TABLE STATUS LIKE '<replaceable>tbl_name</replaceable>';</userinput>
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- Ensure that you do not have any MySQL clients that use
- shared libraries (like the Perl
- <literal>DBD::mysql</literal> module). If you do, you
- should recompile them, because the data structures used in
- <filename>libmysqlclient.so</filename> have changed. The
- same applies to other MySQL interfaces such as the Python
- <literal>MySQLdb</literal> module.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- MySQL 4.0 works even if you do not perform the preceding
- actions, but you cannot 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 <literal>ISAM</literal> file format
- still works in MySQL 4.0, but is deprecated and is not
- compiled in by default as of MySQL 4.1.
- <literal>MyISAM</literal> tables should be used instead.
- </para>
-
- <para>
- Old clients should work with a MySQL 4.0 server without any
- problems.
- </para>
-
- <para>
- 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
- <command>mysqldump</command> 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 an improved format
- for full-text indexing that is not backward-compatible.
- </para>
-
- <para>
- The following lists describe changes that may affect
- applications and that you should watch out for when upgrading
- to version 4.0.
- </para>
-
- <para>
- <emphasis role="bold">Server Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- As of MySQL 4.0.24, the server by default no longer loads
- user-defined functions unless they have at least one
- auxiliary symbol defined in addition to the main function
- symbol. This behavior can be overridden with the
- <option role="mysqld">--allow-suspicious-udfs</option>
- option. See <xref linkend="udf-security"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL 4.0 has many new privileges in the
- <literal>mysql.user</literal> table. See
- <xref linkend="privileges-provided"/>.
- </para>
-
- <para>
- In order for these new privileges to work, you must update
- the grant tables. The procedure for this is described in
- <xref linkend="mysql-fix-privilege-tables"/>. Until you do
- this, all accounts have the <literal role="priv">SHOW
- DATABASES</literal>, <literal role="priv">CREATE TEMPORARY
- TABLES</literal>, and <literal role="priv">LOCK
- TABLES</literal> privileges.
- <literal role="priv">SUPER</literal> and
- <literal role="priv">EXECUTE</literal> privileges take
- their value from <literal role="priv">PROCESS</literal>.
- <literal role="priv">REPLICATION SLAVE</literal> and
- <literal role="priv">REPLICATION CLIENT</literal> take
- their values from <literal role="priv">FILE</literal>.
- </para>
-
- <para>
- 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
- <literal role="stmt">GRANT</literal> commands in the
- scripts, this is a good time to change your scripts to use
- <literal role="stmt">GRANT</literal> instead of modifying
- the grant tables directly.
- </para>
-
- <para>
- From version 4.0.2 on, the option
- <option role="mysqld">--safe-show-database</option> is
- deprecated (and no longer does anything). See
- <xref linkend="privileges-options"/>.
- </para>
-
- <para>
- If you get <literal>Access denied</literal> errors for new
- users in version 4.0.2 and up, you should check whether
- you need some of the new grants that you did not need
- before. In particular, you need
- <literal role="priv">REPLICATION SLAVE</literal> (instead
- of <literal role="priv">FILE</literal>) for new slave
- servers.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <command>safe_mysqld</command> has been renamed to
- <command>mysqld_safe</command>. For backward
- compatibility, binary distributions will for some time
- include <command>safe_mysqld</command> as a symlink to
- <command>mysqld_safe</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>InnoDB</literal> support is included by default
- in binary distributions. If you build MySQL from source,
- <literal>InnoDB</literal> is configured in by default. If
- you do not use <literal>InnoDB</literal> and want to save
- memory when running a server that has
- <literal>InnoDB</literal> support enabled, use the
- <option role="mysqld" condition="innodb">--skip-innodb</option>
- server startup option. To compile MySQL without
- <literal>InnoDB</literal> support, run
- <command>configure</command> with the
- <option>--without-innodb</option> option.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Values for the startup parameters
- <literal role="sysvar">myisam_max_extra_sort_file_size</literal>
- and
- <literal role="sysvar">myisam_max_extra_sort_file_size</literal>
- are given in bytes (prior to 4.0.3,they were given in
- megabytes).
- </para>
- </listitem>
-
- <listitem>
- <para>
- <command>mysqld</command> has the option
- <option role="mysqld">--temp-pool</option> enabled by
- default because this gives better performance with some
- operating systems (most notably Linux).
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <command>mysqld</command> startup options
- <option>--skip-locking</option> and
- <option>--enable-locking</option> were renamed to
- <option role="mysqld">--skip-external-locking</option> and
- <option role="mysqld">--external-locking</option>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- External system locking of
- <literal>MyISAM</literal>/<literal>ISAM</literal> files is
- turned off by default. You can turn this on with
- <option role="mysqld">--external-locking</option>.
- (However, this is never needed for most users.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- The following startup variables and options were renamed:
- </para>
-
- <informaltable>
- <tgroup cols="2">
- <colspec colwidth="50*"/>
- <colspec colwidth="50*"/>
- <tbody>
- <row>
- <entry><emphasis role="bold">Name in 3.23</emphasis></entry>
- <entry><emphasis role="bold">Name in 4.0 (and above)</emphasis></entry>
- </row>
- <row>
- <entry><literal>myisam_bulk_insert_tree_size</literal></entry>
- <entry><literal role="sysvar">bulk_insert_buffer_size</literal></entry>
- </row>
- <row>
- <entry><literal>query_cache_startup_type</literal></entry>
- <entry><literal role="sysvar">query_cache_type</literal></entry>
- </row>
- <row>
- <entry><literal>record_buffer</literal></entry>
- <entry><literal role="sysvar">read_buffer_size</literal></entry>
- </row>
- <row>
- <entry><literal>record_rnd_buffer</literal></entry>
- <entry><literal role="sysvar">read_rnd_buffer_size</literal></entry>
- </row>
- <row>
- <entry><literal>sort_buffer</literal></entry>
- <entry><literal role="sysvar">sort_buffer_size</literal></entry>
- </row>
- <row>
- <entry><option>--warnings</option></entry>
- <entry><option role="mysqld">--log-warnings</option></entry>
- </row>
- <row>
- <entry><option>--err-log</option></entry>
- <entry><option role="mysqld">--log-error</option> (for
- <command>mysqld_safe</command>)</entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
-
- <para>
- The startup options <literal>record_buffer</literal>,
- <literal>sort_buffer</literal>, and
- <option>warnings</option> still work in MySQL 4.0 but are
- deprecated.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">SQL Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Some keywords are reserved in MySQL 4.0 that were not
- reserved in MySQL 3.23. See
- <xref linkend="reserved-words"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The following SQL variables have been renamed:
- </para>
-
- <informaltable>
- <tgroup cols="2">
- <colspec colwidth="50*"/>
- <colspec colwidth="50*"/>
- <tbody>
- <row>
- <entry><emphasis role="bold">Name in 3.23</emphasis></entry>
- <entry><emphasis role="bold">Name in 4.0 and above</emphasis></entry>
- </row>
- <row>
- <entry><literal>sql_big_tables</literal></entry>
- <entry><literal role="sysvar">big_tables</literal></entry>
- </row>
- <row>
- <entry><literal>sql_low_priority_updates</literal></entry>
- <entry><literal role="sysvar">low_priority_updates</literal></entry>
- </row>
- <row>
- <entry><literal>sql_max_join_size</literal></entry>
- <entry><literal role="sysvar">max_join_size</literal></entry>
- </row>
- <row>
- <entry><literal>sql_query_cache_type</literal></entry>
- <entry><literal role="sysvar">query_cache_type</literal></entry>
- </row>
- </tbody>
- </tgroup>
- </informaltable>
-
- <para>
- The older names still work in MySQL 4.0 but are
- deprecated.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You must use <literal>SET GLOBAL
- SQL_SLAVE_SKIP_COUNTER=skip_count</literal> instead of
- <literal>SET SQL_SLAVE_SKIP_COUNTER=skip_count</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="stmt">SHOW MASTER STATUS</literal> returns
- an empty set if binary logging is not enabled.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="stmt">SHOW SLAVE STATUS</literal> returns
- an empty set if the slave is not initialized.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="stmt">SHOW INDEX</literal> has two more
- columns in 4.0 than in 3.23 (<literal>Null</literal> and
- <literal>Index_type</literal>).
- </para>
- </listitem>
-
- <listitem>
- <para>
- The format of <literal role="stmt">SHOW OPEN
- TABLES</literal> changed.
- </para>
- </listitem>
-
- <listitem>
- <para>
- As of MySQL 4.0.11, <literal>ORDER BY col_name
- DESC</literal> sorts <literal>NULL</literal> values last.
- In 3.23 and in earlier 4.0 versions, this was not always
- consistent.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CHECK</literal>, <literal>LOCALTIME</literal>,
- and <literal>LOCALTIMESTAMP</literal> are reserved words.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="type">DOUBLE</literal> and
- <literal role="type">FLOAT</literal> columns honor the
- <literal>UNSIGNED</literal> flag on storage (previously,
- <literal>UNSIGNED</literal> was ignored for these
- columns).
- </para>
- </listitem>
-
- <listitem>
- <para>
- The result of all bitwise operators (<literal>|</literal>,
- <literal>&</literal>, <literal><<</literal>,
- <literal>>></literal>, and <literal>~</literal>) is
- unsigned. This may cause problems if you are using them in
- a context where you want a signed result. See
- <xref linkend="cast-functions"/>.
- </para>
-
- <note>
- <para>
- When you use subtraction between integer values where
- one is of type <literal>UNSIGNED</literal>, the result
- is 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
- <option role="mysqld">--sql-mode=NO_UNSIGNED_SUBTRACTION</option>
- option when starting <command>mysqld</command>. See
- <xref linkend="server-sql-mode"/>.
- </para>
- </note>
- </listitem>
-
- <listitem>
- <para>
- You should use integers to store values in
- <literal role="type">BIGINT</literal> columns (instead of
- using strings as in MySQL 3.23). Using strings still
- works, but using integers is more efficient.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In MySQL 3.23,
- <literal role="stmt" condition="insert-select">INSERT INTO
- ... SELECT</literal> always had <literal>IGNORE</literal>
- enabled. As of 4.0.1, MySQL stops (and possibly rolls
- back) by default in case of an error unless you specify
- <literal>IGNORE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You should use
- <literal role="stmt" condition="truncate">TRUNCATE
- TABLE</literal> when you want to delete all rows from a
- table and you do not need to obtain a count of the number
- of rows that were deleted. (<literal>DELETE FROM
- <replaceable>tbl_name</replaceable></literal> returns a
- row count in 4.0 and does not reset the
- <literal>AUTO_INCREMENT</literal> counter, and
- <literal role="stmt" condition="truncate">TRUNCATE
- TABLE</literal> is faster.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- You get an error if you have an active transaction or
- <literal role="stmt">LOCK TABLES</literal> statement when
- trying to execute
- <literal role="stmt" condition="truncate">TRUNCATE
- TABLE</literal> or <literal role="stmt">DROP
- DATABASE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- To use <literal>MATCH ... AGAINST (... IN BOOLEAN
- MODE)</literal> full-text searches, you must rebuild
- existing table indexes using <literal>REPAIR TABLE
- <replaceable>tbl_name</replaceable> USE_FRM</literal>. If
- you attempt a boolean full-text search without rebuilding
- the indexes in this manner, the search returns incorrect
- results. See <xref linkend="fulltext-fine-tuning"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="func">LOCATE()</literal> and
- <literal role="func">INSTR()</literal> are case sensitive
- if one of the arguments is a binary string. Otherwise they
- are case insensitive.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="func">STRCMP()</literal> 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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="func">HEX(<replaceable>str</replaceable>)</literal>
- returns the characters in <replaceable>str</replaceable>
- converted to hexadecimal. If you want to convert a number
- to hexadecimal, you should ensure that you call
- <literal role="func">HEX()</literal> with a numeric
- argument.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal role="func">RAND(seed)</literal> returns a
- different random number series in 4.0 than in 3.23; this
- was done to further differentiate
- <literal role="func">RAND(seed)</literal> and
- <literal role="func">RAND(seed+1)</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The default type returned by
- <literal role="func">IFNULL(A,B)</literal> is set to be
- the more <quote>general</quote> of the types of
- <literal>A</literal> and <literal>B</literal>. (The
- general-to-specific order is string,
- <literal role="type">REAL</literal>,
- <literal role="type">INTEGER</literal>).
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">C API Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The old C API functions
- <literal role="cfunc">mysql_drop_db()</literal>,
- <literal role="cfunc">mysql_create_db()</literal>, and
- <literal role="cfunc">mysql_connect()</literal> are no
- longer supported in MySQL 4.0 unless MySQL is compiled
- with <literal>CFLAGS=-DUSE_OLD_FUNCTIONS</literal>. It is
- preferable to change client programs to use the new 4.0
- API instead.
- </para>
- </listitem>
-
- <listitem>
- <para>
- In the <literal>MYSQL_FIELD</literal> structure,
- <literal>length</literal> and
- <literal>max_length</literal> have changed from
- <literal>unsigned int</literal> to <literal>unsigned
- long</literal>. This should not cause any problems, except
- that they may generate warning messages when used as
- arguments in the <literal>printf()</literal> class of
- functions.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Multi-threaded clients should use
- <literal role="cfunc">mysql_thread_init()</literal> and
- <literal role="cfunc">mysql_thread_end()</literal>. See
- <xref linkend="threaded-clients"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Other Changes:</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If you want to recompile the Perl
- <literal>DBD::mysql</literal> module, use a recent
- version. Version 2.9003 is recommended. Versions older
- than 1.2218 should not be used because they use the
- deprecated <literal role="cfunc">mysql_drop_db()</literal>
- call.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- </section>
-
- <section id="downgrading">
-
- <title>Downgrading MySQL</title>
-
- <indexterm>
- <primary>downgrading</primary>
- </indexterm>
-
- <para>
- This section describes what you should do to downgrade to an
- older MySQL version in the unlikely case that the previous
- version worked better than the new one.
- </para>
-
- <para>
- If you are downgrading within the same release series (for
- example, from 4.0.20 to 4.0.19) the general rule is that you
- merely need to install the new binaries on top of the old ones.
- There is no need to do anything with the databases. As always,
- however, it is always a good idea to make a backup.
- </para>
-
- <para>
- The following items form a checklist of things you should do
- whenever you perform a downgrade:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Read the upgrading section for the release series from which
- you are downgrading to be sure that it does not have any
- features you really need. See <xref linkend="upgrade"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If there is a downgrading section for that version, please
- read it, too!
- </para>
- </listitem>
-
- <listitem>
- <para>
- To see which new features were added between the version to
- which you are downgrading and your current version, see the
- change logs (<xref linkend="news"/>).
- </para>
- </listitem>
-
- <listitem>
- <para>
- Check <xref linkend="checking-table-incompatibilities"/>, to
- see whether changes to table formats or to character sets or
- collations were made between your current version of MySQL
- and the version to which you are downgrading. If so and
- these changes result in an incompatibility between MySQL
- versions, you will need to downgrade the affected tables
- using the instructions in
- <xref linkend="rebuilding-tables"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- 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.
- </para>
-
- <para>
- If you downgrade from one release series to another, there may
- be incompatibilities in table storage formats. In this case, use
- <command>mysqldump</command> to dump your tables before
- downgrading. After downgrading, reload the dump file using
- <command>mysql</command> or <command>mysqlimport</command> to
- re-create your tables. For examples, ee
- <xref linkend="copying-databases"/>.
- </para>
-
- <para>
- A typical symptom of a downward-incompatible table format change
- when you downgrade is that you cannot open tables. In that case,
- use the following procedure:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- Stop the older MySQL server that you are downgrading to.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Restart the newer MySQL server you are downgrading from.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Dump any tables that were inaccessible to the older server
- by using <command>mysqldump</command> to create a dump file.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Stop the newer MySQL server and restart the older one.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Reload the dump file into the older server. Your tables
- should be accessible.
- </para>
- </listitem>
-
- </orderedlist>
-
- <para>
- It might also be the case that the structure of the system
- tables in the <literal>mysql</literal> database has changed and
- that downgrading introduces some loss of functionality or
- requires some adjustments. Here are some examples:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Trigger creation requires the <literal>TRIGGER</literal>
- privilege as of MySQL 5.1. In MySQL 5.0, there is no
- <literal>TRIGGER</literal> privilege and
- <literal>SUPER</literal> is required instead. If you
- downgrade from MySQL 5.1 to 5.0, you will need to give the
- <literal>SUPER</literal> privilege to those accounts that
- had the <literal>TRIGGER</literal> privilege in 5.1.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Triggers were added in MySQL 5.0, so if you downgrade from
- 5.0 to 4.1, you cannot use triggers at all.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <section id="downgrading-to-previous-series">
-
- <title>Downgrading to MySQL 4.0</title>
-
- <para>
- The table format in 4.1 changed to include more and new
- character set information. Because of this, you must use
- <command>mysqldump</command> to dump any tables you have
- created with the newer MySQL server. For example, if all the
- tables in a particular database need to be dumped to be
- reverted back to MySQL 4.0 format, use this command:
- </para>
-
-<programlisting>
-shell> <userinput>mysqldump --create-options --compatible=mysql40 <replaceable>db_name</replaceable> > <replaceable>dump_file</replaceable></userinput>
-</programlisting>
-
- <para>
- Then stop the newer server, restart the older server, and read
- in the dump file:
- </para>
-
-<programlisting>
-shell> <userinput>mysql <replaceable>db_name</replaceable> < <replaceable>dump_file</replaceable></userinput>
-</programlisting>
-
- <para>
- In the special case that you are downgrading
- <literal>MyISAM</literal> tables, no special treatment is
- necessary if all columns in the tables contain only numeric
- columns or string columns
- (<literal role="type">CHAR</literal>,
- <literal role="type">VARCHAR</literal>,
- <literal role="type">TEXT</literal>, and so forth) that
- contain only <literal>latin1</literal> data. Your 4.1 tables
- should be directly usable with a 4.0 server.
- </para>
-
- <para>
- If you used the <command>mysql_fix_privilege_tables</command>
- script to upgrade the grant tables, you can either use the
- preceding method to convert them to back to MySQL 4.0 or do
- the following in MySQL 4.1 (or above):
- </para>
-
-<programlisting>
-ALTER TABLE mysql.user
- CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-ALTER TABLE mysql.db
- CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-ALTER TABLE mysql.host
- CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-ALTER TABLE mysql.tables_priv
- CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-ALTER TABLE mysql.columns_priv
- CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-ALTER TABLE mysql.func
- CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-</programlisting>
-
- </section>
-
- </section>
-
- <section id="checking-table-incompatibilities">
-
- <title>Checking Whether Tables or Indexes Must Be Rebuilt</title>
-
- <para>
- A binary upgrade or downgrade is one that installs one version
- of MySQL <quote>in place</quote> over an existing version,
- without dumping and reloading tables:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- Stop the server for the existing version if it is running.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Install a different version of MySQL. This is an upgrade if
- the new version is higher than the original version, a
- downgrade if the version is lower.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Start the server for the new version.
- </para>
- </listitem>
-
- </orderedlist>
-
- <para>
- In many cases, the tables from the previous version of MySQL can
- be used without problem by the new version. However, sometimes
- changes occur that require tables or table indexes to be
- rebuilt, as described in this section. If you have tables that
- are affected by any of the issues described here, rebuild the
- tables or indexes as necessary using the instructions given in
- <xref linkend="rebuilding-tables"/>.
- </para>
-
- <para>
- <emphasis role="bold">Index Incompatibilities</emphasis>
- </para>
-
- <para>
- If you perform a binary upgrade without dumping and reloading
- tables, you cannot upgrade directly from MySQL 4.1 to 5.1 or
- higher. This occurs due to an incompatible change in the
- <literal>MyISAM</literal> table index format in MySQL 5.0.
- Upgrade from MySQL 4.1 to 5.0 and repair all
- <literal>MyISAM</literal> tables. Then upgrade from MySQL 5.0 to
- 5.1 and check and repair your tables.
- </para>
-
- <para>
- Modifications to the handling of character sets or collations
- might change the character sort order, which causes the ordering
- of entries in any index that uses an affected character set or
- collation to be incorrect. Such changes result in several
- possible problems:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Comparison results that differ from previous results
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inability to find some index values due to misordered index
- entries
- </para>
- </listitem>
-
- <listitem>
- <para>
- Misordered <literal>ORDER BY</literal> results
- </para>
- </listitem>
-
- <listitem>
- <para>
- Tables that <literal role="stmt">CHECK TABLE</literal>
- reports as being in need of repair
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The solution to these problems is to rebuild any indexes that
- use an affected character set or collation, either by dropping
- and re-creating the indexes, or by dumping and reloading the
- entire table. For information about rebuilding indexes, see
- <xref linkend="rebuilding-tables"/>.
- </para>
-
- <para>
- To check whether a table has indexes that must be rebuilt,
- consult the following list. It indicates which versions of MySQL
- introduced character set or collation changes that require
- indexes to be rebuilt. Each entry indicates the version in which
- the change occurred and the character sets or collations that
- the change affects. If the change is associated with a
- particular bug report, the bug number is given.
- </para>
-
- <para>
- The list applies both for binary upgrades and downgrades. For
- example, Bug#27877 was fixed in MySQL 5.1.24 and 5.4.0, so it
- applies to upgrades from versions older than 5.1.24 to 5.1.24 or
- newer, and to downgrades from 5.1.24 or newer to versions older
- than 5.1.24.
- </para>
-
- <para>
- Changes that cause index rebuilding to be necessary:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- MySQL 4.1.2 (Bug#3152)
- </para>
-
- <para>
- String comparison works according to the SQL standard:
- Instead of stripping end spaces before comparison, we now
- extend the shorter string with spaces. The problem with this
- is that now <literal>'a' > 'a\t'</literal>, which it was
- not before. If you have any tables where you have indexes on
- <literal role="type">CHAR</literal>,
- <literal role="type">VARCHAR</literal> or
- <literal role="type">TEXT</literal> column in which the last
- character in index values may be less than
- <literal>ASCII(32)</literal>, you should rebuild those
- indexes.
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL 4.1.3
- </para>
-
- <para>
- <literal>InnoDB</literal> uses the same character set
- comparison functions as MySQL for
- non-<literal>latin1_swedish_ci</literal> character strings
- that are not <literal>BINARY</literal>. This changes the
- sorting order of space and characters with a code <
- ASCII(32) in those character sets. This affects
- <literal>InnoDB</literal> tables with an index on a
- non-<literal>latin1_swedish_ci</literal> character set and
- collation order column that is not <literal>BINARY</literal>
- if that column contains characters with a code <
- ASCII(32). (For MySQL 4.1.0 and 4.1.1, it affects indexes
- with any character set and collation).
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL 5.0.48, 5.1.21 (Bug#29461)
- </para>
-
- <para>
- Affects indexes for columns that use any of these character
- sets: <literal>eucjpms</literal>, <literal>euc_kr</literal>,
- <literal>gb2312</literal>, <literal>latin7</literal>,
- <literal>macce</literal>, <literal>ujis</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL 5.0.48, 5.1.23 (Bug#27562)
- </para>
-
- <para>
- Affects indexes that use the
- <literal>ascii_general_ci</literal> collation for columns
- that contain any of these characters: <literal>'`'</literal>
- GRAVE ACCENT, <literal>'['</literal> LEFT SQUARE BRACKET,
- <literal>'\'</literal> REVERSE SOLIDUS,
- <literal>']'</literal> RIGHT SQUARE BRACKET,
- <literal>'~'</literal> TILDE
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL 5.1.24, 5.4.0 (Bug#27877)
- </para>
-
- <para>
- Affects indexes that use the
- <literal>utf8_general_ci</literal> or
- <literal>ucs2_general_ci</literal> collation for columns
- that contain <literal>'ß'</literal> LATIN SMALL LETTER
- SHARP S (German).
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="rebuilding-tables">
-
- <title>Rebuilding or Repairing Tables or Indexes</title>
-
- <indexterm>
- <primary>table</primary>
- <secondary>rebuilding</secondary>
- </indexterm>
-
- <indexterm>
- <primary>index</primary>
- <secondary>rebuilding</secondary>
- </indexterm>
-
- <indexterm>
- <primary>table</primary>
- <secondary>repair</secondary>
- </indexterm>
-
- <para>
- This section describes how to rebuild a table. This can be
- necessitated by changes to MySQL such as how data types are
- handled or changes to character set handling. For example, an
- error in a collation might have been corrected, necessitating a
- table rebuild to rebuild the indexes for character columns that
- use the collation. It might also be that a table repair or
- upgrade should be done as indicated by a table check operation
- such as that performed by <literal>CHECK TABLE</literal> or
- <command>mysqlcheck</command>.
- </para>
-
- <para>
- Methods for rebuilding a table include dumping and reloading it,
- or using <literal role="stmt">ALTER TABLE</literal> or
- <literal role="stmt">REPAIR TABLE</literal>.
- </para>
-
- <note>
- <para>
- If you are rebuilding tables because a different version of
- MySQL will not handle them after a binary (in-place) upgrade
- or downgrade, you must use the dump-and-reload method. Dump
- the tables <emphasis>before</emphasis> upgrading or
- downgrading (using your original version of MySQL), and reload
- the tables <emphasis>after</emphasis> upgrading or downgrading
- (after installing the new version).
- </para>
-
- <para>
- If you use the dump-and-reload method of rebuilding tables
- only for the purpose of rebuilding indexes, you can perform
- the dump either before or after upgrading or downgrading.
- Reloading still must be done afterward.
- </para>
- </note>
-
- <para>
- To re-create a table by dumping and reloading it, use
- <command>mysqldump</command> to create a dump file and
- <command>mysql</command> to reload the file:
- </para>
-
-<programlisting>
-shell> <userinput>mysqldump <replaceable>db_name</replaceable> t1 > dump.sql</userinput>
-shell> <userinput>mysql <replaceable>db_name</replaceable> < dump.sql</userinput>
-</programlisting>
-
- <para>
- To recreate all the tables in a single database, specify the
- database name without any following table name:
- </para>
-
-<programlisting>
-shell> <userinput>mysqldump <replaceable>db_name</replaceable> > dump.sql</userinput>
-shell> <userinput>mysql <replaceable>db_name</replaceable> < dump.sql</userinput>
-</programlisting>
-
- <para>
- To recreate all tables in all databases, use the
- <option role="mysqldump">--all-databases</option> option:
- </para>
-
-<programlisting>
-shell> <userinput>mysqldump --all-databases > dump.sql</userinput>
-shell> <userinput>mysql < dump.sql</userinput>
-</programlisting>
-
- <para>
- Before MySQL 4.1, use the
- <option role="mysqldump">--opt</option> and
- <option role="mysqldump">--quote-names</option> options. As of
- 4.1, those options are enabled by default.
- </para>
-
- <para>
- To rebuild a table with <literal role="stmt">ALTER
- TABLE</literal>, use a <quote>null</quote> alteration; that is,
- an <literal role="stmt">ALTER TABLE</literal> statement that
- <quote>changes</quote> the table to use the storage engine that
- it already has. For example, if <literal>t1</literal> is a
- <literal>MyISAM</literal> table, use this statement:
- </para>
-
-<programlisting>
-mysql> <userinput>ALTER TABLE t1 ENGINE = MyISAM;</userinput>
-</programlisting>
-
- <para>
- If you are not sure which storage engine to specify in the
- <literal role="stmt">ALTER TABLE</literal> statement, use
- <literal role="stmt">SHOW CREATE TABLE</literal> to display the
- table definition.
- </para>
-
- <para>
- If you must rebuild a table because a table checking operation
- indicates that the table is corrupt or needs an upgrade, you can
- use <literal role="stmt">REPAIR TABLE</literal> if that
- statement supports the table's storage engine. For example, to
- repair a <literal>MyISAM</literal> table, use this statement:
- </para>
-
-<programlisting>
-mysql> <userinput>REPAIR TABLE t1;</userinput>
-</programlisting>
-
- <para>
- For storage engines such as <literal>InnoDB</literal> that
- <literal role="stmt">REPAIR TABLE</literal> does not support,
- use <command>mysqldump</command> to create a dump file and
- <command>mysql</command> to reload the file, as described
- earlier.
- </para>
-
- <para>
- For specifics about which storage engines
- <literal role="stmt">REPAIR TABLE</literal> supports, see
- <xref linkend="repair-table"/>.
- </para>
-
- <para>
- <command>mysqlcheck --repair</command> provides command-line
- access to the <literal role="stmt">REPAIR TABLE</literal>
- statement. This can be a more convenient means of repairing
- tables because you can use the
- <option role="mysqlcheck">--databases</option> or
- <option role="mysqlcheck">--all-databases</option> option to
- repair all tables in specific databases or all databases,
- respectively:
- </para>
-
-<programlisting>
-shell> <userinput>mysqlcheck --repair --databases <replaceable>db_name</replaceable> ...</userinput>
-shell> <userinput>mysqlcheck --repair --all-databases</userinput>
-</programlisting>
-
- </section>
-
- <section id="copying-databases">
-
- <title>Copying MySQL Databases to Another Machine</title>
-
- <indexterm>
- <primary>upgrading</primary>
- <secondary>different architecture</secondary>
- </indexterm>
-
- <indexterm>
- <primary>copying databases</primary>
- </indexterm>
-
- <indexterm>
- <primary>databases</primary>
- <secondary>copying</secondary>
- </indexterm>
-
- <para>
- If you are using MySQL 3.23 or later, you can copy the
- <filename>.frm</filename>, <filename>.MYI</filename>, and
- <filename>.MYD</filename> files for <literal>MyISAM</literal>
- tables between different architectures that support the same
- floating-point format. (MySQL takes care of any byte-swapping
- issues.) See <xref linkend="myisam-storage-engine"/>.
- </para>
-
- <para>
- The MySQL <literal>ISAM</literal> data and index files
- (<filename>.ISD</filename> and <filename>*.ISM</filename>,
- respectively) are dependent upon the architecture and, in some
- cases, the operating system. If you want to move applications to
- another machine having a different architecture or operating
- system than that of the current machine, you should not try to
- move a database by simply copying the files to the other
- machine. Use <command>mysqldump</command> instead.
- </para>
-
- <para>
- By default, <command>mysqldump</command> creates a file
- containing SQL statements. You can then transfer the file to the
- other machine and use it as input to the
- <command>mysql</command> client.
- </para>
-
- <para>
- Try <command>mysqldump --help</command> to see what options are
- available. Before MySQL 4.1, if you are moving the data to a
- newer version of MySQL, you should add the
- <option role="mysqldump">--opt</option> option to the
- <command>mysqldump</command> commands shown here, to take
- advantage of any optimizations that result in a dump file that
- is smaller and can be processed faster.
- (<option role="mysqldump">--opt</option> is enabled by default
- as of MySQL 4.1.)
- </para>
-
- <para>
- 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:
- </para>
-
-<programlisting>
-shell> <userinput>mysqladmin -h '<replaceable>other_hostname</replaceable>' create <replaceable>db_name</replaceable></userinput>
-shell> <userinput>mysqldump <replaceable>db_name</replaceable> | mysql -h '<replaceable>other_hostname</replaceable>' <replaceable>db_name</replaceable></userinput>
-</programlisting>
-
- <remark role="todo">
- The next example not only changes copy direction (from
- local->remote to remote->local), it adds - -compress. But
- - -compress is unrelated and can be used in either case. Split
- into two examples.
- </remark>
-
- <para>
- If you want to copy a database from a remote machine over a slow
- network, you can use these commands:
- </para>
-
-<programlisting>
-shell> <userinput>mysqladmin create <replaceable>db_name</replaceable></userinput>
-shell> <userinput>mysqldump -h '<replaceable>other_hostname</replaceable>' --compress <replaceable>db_name</replaceable> | mysql <replaceable>db_name</replaceable></userinput>
-</programlisting>
-
- <para>
- You can also store the dump in a file, transfer the file to the
- target machine, and then load the file into the database there.
- For example, you can dump a database to a compressed file on the
- source machine like this:
- </para>
-
-<programlisting>
-shell> <userinput>mysqldump --quick <replaceable>db_name</replaceable> | gzip > <replaceable>db_name</replaceable>.gz</userinput>
-</programlisting>
-
- <para>
- Transfer the file containing the database contents to the target
- machine and run these commands there:
- </para>
-
-<programlisting>
-shell> <userinput>mysqladmin create <replaceable>db_name</replaceable></userinput>
-shell> <userinput>gunzip < <replaceable>db_name</replaceable>.gz | mysql <replaceable>db_name</replaceable></userinput>
-</programlisting>
-
- <indexterm>
- <primary>mysqldump</primary>
- </indexterm>
-
- <indexterm>
- <primary>mysqlimport</primary>
- </indexterm>
-
- <para>
- You can also use <command>mysqldump</command> and
- <command>mysqlimport</command> to transfer the database. For
- very large tables, this is much faster than simply using
- <command>mysqldump</command>. In the following commands,
- <replaceable>DUMPDIR</replaceable> represents the full path name
- of the directory you use to store the output from
- <command>mysqldump</command>.
- </para>
-
- <para>
- First, create the directory for the output files and dump the
- database:
- </para>
-
-<programlisting>
-shell> <userinput>mkdir <replaceable>DUMPDIR</replaceable></userinput>
-shell> <userinput>mysqldump --tab=<replaceable>DUMPDIR</replaceable> <replaceable>db_name</replaceable></userinput>
-</programlisting>
-
- <para>
- Then transfer the files in the
- <replaceable>DUMPDIR</replaceable> directory to a directory on
- the target machine and load the files into MySQL there:
- </para>
-
-<programlisting>
-shell> <userinput>mysqladmin create <replaceable>db_name</replaceable> # create database</userinput>
-shell> <userinput>cat <replaceable>DUMPDIR</replaceable>/*.sql | mysql <replaceable>db_name</replaceable> # create tables in database</userinput>
-shell> <userinput>mysqlimport <replaceable>db_name</replaceable> <replaceable>DUMPDIR</replaceable>/*.txt # load data into tables</userinput>
-</programlisting>
-
- <para>
- Do not forget to copy the <literal>mysql</literal> database
- because that is where the <literal>user</literal>,
- <literal>db</literal>, and <literal>host</literal> grant tables
- are stored. You might have to run commands as the MySQL
- <literal>root</literal> user on the new machine until you have
- the <literal>mysql</literal> database in place.
- </para>
-
- <para>
- After you import the <literal>mysql</literal> database on the
- new machine, execute <command>mysqladmin
- flush-privileges</command> so that the server reloads the grant
- table information.
- </para>
-
- </section>
-
- </section>
-
<section id="operating-system-specific-notes">
<title>Operating System-Specific Notes</title>
Modified: trunk/refman-4.1/storage-engines.xml
===================================================================
--- trunk/refman-4.1/storage-engines.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-4.1/storage-engines.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 571 bytes
@@ -500,7 +500,7 @@
<literal>MyISAM</literal> tables with
<command>myisampack</command> to take up much less space. See
<xref linkend="mysqlcheck"/>,
- <xref linkend="myisam-crash-recovery"/>, and
+ <xref linkend="myisamchk"/>, and
<xref linkend="myisampack"/>.
</para>
Modified: trunk/refman-5.0/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.0/installing-updowngrade.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.0/installing-updowngrade.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 5, Lines Added: 10, Lines Deleted: 8; 2373 bytes
@@ -2126,8 +2126,11 @@
necessitated by changes to MySQL such as how data types are
handled or changes to character set handling. For example, an
error in a collation might have been corrected, necessitating a
- table rebuild to rebuild the indexes for character columns that
- use the collation. It might also be that a table repair or upgrade
+ table rebuild to update the indexes for character columns that
+ use the collation.
+(For examples, see
+ <xref linkend="checking-table-incompatibilities"/>.)
+It might also be that a table repair or upgrade
should be done as indicated by a table check operation such as
that performed by <literal>CHECK TABLE</literal>,
<command>mysqlcheck</command>, or
@@ -2146,9 +2149,8 @@
MySQL will not handle them after a binary (in-place) upgrade or
downgrade, you must use the dump-and-reload method. Dump the
tables <emphasis>before</emphasis> upgrading or downgrading
- (using your original version of MySQL), and reload the tables
- <emphasis>after</emphasis> upgrading or downgrading (after
- installing the new version).
+ using your original version of MySQL. Then reload the tables
+ <emphasis>after</emphasis> upgrading or downgrading.
</para>
<para>
@@ -2160,7 +2162,7 @@
</note>
<para>
- To re-create a table by dumping and reloading it, use
+ To rebuild a table by dumping and reloading it, use
<command>mysqldump</command> to create a dump file and
<command>mysql</command> to reload the file:
</para>
@@ -2171,7 +2173,7 @@
</programlisting>
<para>
- To recreate all the tables in a single database, specify the
+ To rebuild all the tables in a single database, specify the
database name without any following table name:
</para>
@@ -2181,7 +2183,7 @@
</programlisting>
<para>
- To recreate all tables in all databases, use the
+ To rebuild all tables in all databases, use the
<option role="mysqldump">--all-databases</option> option:
</para>
Modified: trunk/refman-5.0/se-myisam-core.xml
===================================================================
--- trunk/refman-5.0/se-myisam-core.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.0/se-myisam-core.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 583 bytes
@@ -66,7 +66,7 @@
utility. You can also compress <literal>MyISAM</literal> tables with
<command>myisampack</command> to take up much less space. See
<xref linkend="mysqlcheck"/>,
- <xref linkend="myisam-crash-recovery"/>, and
+ <xref linkend="myisamchk"/>, and
<xref linkend="myisampack"/>.
</para>
Modified: trunk/refman-5.1/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.1/installing-updowngrade.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.1/installing-updowngrade.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 5, Lines Added: 10, Lines Deleted: 8; 2373 bytes
@@ -2166,8 +2166,11 @@
necessitated by changes to MySQL such as how data types are
handled or changes to character set handling. For example, an
error in a collation might have been corrected, necessitating a
- table rebuild to rebuild the indexes for character columns that
- use the collation. It might also be that a table repair or upgrade
+ table rebuild to update the indexes for character columns that
+ use the collation.
+(For examples, see
+ <xref linkend="checking-table-incompatibilities"/>.)
+It might also be that a table repair or upgrade
should be done as indicated by a table check operation such as
that performed by <literal>CHECK TABLE</literal>,
<command>mysqlcheck</command>, or
@@ -2186,9 +2189,8 @@
MySQL will not handle them after a binary (in-place) upgrade or
downgrade, you must use the dump-and-reload method. Dump the
tables <emphasis>before</emphasis> upgrading or downgrading
- (using your original version of MySQL), and reload the tables
- <emphasis>after</emphasis> upgrading or downgrading (after
- installing the new version).
+ using your original version of MySQL. Then reload the tables
+ <emphasis>after</emphasis> upgrading or downgrading.
</para>
<para>
@@ -2200,7 +2202,7 @@
</note>
<para>
- To re-create a table by dumping and reloading it, use
+ To rebuild a table by dumping and reloading it, use
<command>mysqldump</command> to create a dump file and
<command>mysql</command> to reload the file:
</para>
@@ -2211,7 +2213,7 @@
</programlisting>
<para>
- To recreate all the tables in a single database, specify the
+ To rebuild all the tables in a single database, specify the
database name without any following table name:
</para>
@@ -2221,7 +2223,7 @@
</programlisting>
<para>
- To recreate all tables in all databases, use the
+ To rebuild all tables in all databases, use the
<option role="mysqldump">--all-databases</option> option:
</para>
Modified: trunk/refman-5.1/se-myisam-core.xml
===================================================================
--- trunk/refman-5.1/se-myisam-core.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.1/se-myisam-core.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 583 bytes
@@ -64,7 +64,7 @@
utility. You can also compress <literal>MyISAM</literal> tables with
<command>myisampack</command> to take up much less space. See
<xref linkend="mysqlcheck"/>,
- <xref linkend="myisam-crash-recovery"/>, and
+ <xref linkend="myisamchk"/>, and
<xref linkend="myisampack"/>.
</para>
Modified: trunk/refman-5.1-maria/sql-syntax-server-administration.xml
===================================================================
--- trunk/refman-5.1-maria/sql-syntax-server-administration.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.1-maria/sql-syntax-server-administration.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 840 bytes
@@ -2066,7 +2066,7 @@
<literal>OK</literal>. If you don't get <literal>OK</literal>,
or <literal>Table is already up to date</literal> you should
normally run a repair of the table. See
- <xref linkend="table-maintenance"/>. <literal>Table is already
+ <xref linkend="myisam-table-maintenance"/>. <literal>Table is already
up to date</literal> means that the storage engine for the table
indicated that there was no need to check the table.
</para>
Modified: trunk/refman-5.4/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.4/installing-updowngrade.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.4/installing-updowngrade.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 5, Lines Added: 10, Lines Deleted: 8; 2373 bytes
@@ -1065,8 +1065,11 @@
necessitated by changes to MySQL such as how data types are
handled or changes to character set handling. For example, an
error in a collation might have been corrected, necessitating a
- table rebuild to rebuild the indexes for character columns that
- use the collation. It might also be that a table repair or upgrade
+ table rebuild to update the indexes for character columns that
+ use the collation.
+(For examples, see
+ <xref linkend="checking-table-incompatibilities"/>.)
+It might also be that a table repair or upgrade
should be done as indicated by a table check operation such as
that performed by <literal>CHECK TABLE</literal>,
<command>mysqlcheck</command>, or
@@ -1085,9 +1088,8 @@
MySQL will not handle them after a binary (in-place) upgrade or
downgrade, you must use the dump-and-reload method. Dump the
tables <emphasis>before</emphasis> upgrading or downgrading
- (using your original version of MySQL), and reload the tables
- <emphasis>after</emphasis> upgrading or downgrading (after
- installing the new version).
+ using your original version of MySQL. Then reload the tables
+ <emphasis>after</emphasis> upgrading or downgrading.
</para>
<para>
@@ -1099,7 +1101,7 @@
</note>
<para>
- To re-create a table by dumping and reloading it, use
+ To rebuild a table by dumping and reloading it, use
<command>mysqldump</command> to create a dump file and
<command>mysql</command> to reload the file:
</para>
@@ -1110,7 +1112,7 @@
</programlisting>
<para>
- To recreate all the tables in a single database, specify the
+ To rebuild all the tables in a single database, specify the
database name without any following table name:
</para>
@@ -1120,7 +1122,7 @@
</programlisting>
<para>
- To recreate all tables in all databases, use the
+ To rebuild all tables in all databases, use the
<option role="mysqldump">--all-databases</option> option:
</para>
Modified: trunk/refman-5.4/se-myisam-core.xml
===================================================================
--- trunk/refman-5.4/se-myisam-core.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.4/se-myisam-core.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 583 bytes
@@ -64,7 +64,7 @@
utility. You can also compress <literal>MyISAM</literal> tables with
<command>myisampack</command> to take up much less space. See
<xref linkend="mysqlcheck"/>,
- <xref linkend="myisam-crash-recovery"/>, and
+ <xref linkend="myisamchk"/>, and
<xref linkend="myisampack"/>.
</para>
Modified: trunk/refman-5.5/installing-updowngrade.xml
===================================================================
--- trunk/refman-5.5/installing-updowngrade.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.5/installing-updowngrade.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 5, Lines Added: 10, Lines Deleted: 8; 2373 bytes
@@ -1009,8 +1009,11 @@
necessitated by changes to MySQL such as how data types are
handled or changes to character set handling. For example, an
error in a collation might have been corrected, necessitating a
- table rebuild to rebuild the indexes for character columns that
- use the collation. It might also be that a table repair or upgrade
+ table rebuild to update the indexes for character columns that
+ use the collation.
+(For examples, see
+ <xref linkend="checking-table-incompatibilities"/>.)
+It might also be that a table repair or upgrade
should be done as indicated by a table check operation such as
that performed by <literal>CHECK TABLE</literal>,
<command>mysqlcheck</command>, or
@@ -1029,9 +1032,8 @@
MySQL will not handle them after a binary (in-place) upgrade or
downgrade, you must use the dump-and-reload method. Dump the
tables <emphasis>before</emphasis> upgrading or downgrading
- (using your original version of MySQL), and reload the tables
- <emphasis>after</emphasis> upgrading or downgrading (after
- installing the new version).
+ using your original version of MySQL. Then reload the tables
+ <emphasis>after</emphasis> upgrading or downgrading.
</para>
<para>
@@ -1043,7 +1045,7 @@
</note>
<para>
- To re-create a table by dumping and reloading it, use
+ To rebuild a table by dumping and reloading it, use
<command>mysqldump</command> to create a dump file and
<command>mysql</command> to reload the file:
</para>
@@ -1054,7 +1056,7 @@
</programlisting>
<para>
- To recreate all the tables in a single database, specify the
+ To rebuild all the tables in a single database, specify the
database name without any following table name:
</para>
@@ -1064,7 +1066,7 @@
</programlisting>
<para>
- To recreate all tables in all databases, use the
+ To rebuild all tables in all databases, use the
<option role="mysqldump">--all-databases</option> option:
</para>
Modified: trunk/refman-5.5/se-myisam-core.xml
===================================================================
--- trunk/refman-5.5/se-myisam-core.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-5.5/se-myisam-core.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 583 bytes
@@ -64,7 +64,7 @@
utility. You can also compress <literal>MyISAM</literal> tables with
<command>myisampack</command> to take up much less space. See
<xref linkend="mysqlcheck"/>,
- <xref linkend="myisam-crash-recovery"/>, and
+ <xref linkend="myisamchk"/>, and
<xref linkend="myisampack"/>.
</para>
Modified: trunk/refman-6.0.sav/installing-updowngrade.xml
===================================================================
--- trunk/refman-6.0.sav/installing-updowngrade.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-6.0.sav/installing-updowngrade.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 5, Lines Added: 10, Lines Deleted: 8; 2385 bytes
@@ -1494,8 +1494,11 @@
necessitated by changes to MySQL such as how data types are
handled or changes to character set handling. For example, an
error in a collation might have been corrected, necessitating a
- table rebuild to rebuild the indexes for character columns that
- use the collation. It might also be that a table repair or upgrade
+ table rebuild to update the indexes for character columns that
+ use the collation.
+(For examples, see
+ <xref linkend="checking-table-incompatibilities"/>.)
+It might also be that a table repair or upgrade
should be done as indicated by a table check operation such as
that performed by <literal>CHECK TABLE</literal>,
<command>mysqlcheck</command>, or
@@ -1514,9 +1517,8 @@
MySQL will not handle them after a binary (in-place) upgrade or
downgrade, you must use the dump-and-reload method. Dump the
tables <emphasis>before</emphasis> upgrading or downgrading
- (using your original version of MySQL), and reload the tables
- <emphasis>after</emphasis> upgrading or downgrading (after
- installing the new version).
+ using your original version of MySQL. Then reload the tables
+ <emphasis>after</emphasis> upgrading or downgrading.
</para>
<para>
@@ -1528,7 +1530,7 @@
</note>
<para>
- To re-create a table by dumping and reloading it, use
+ To rebuild a table by dumping and reloading it, use
<command>mysqldump</command> to create a dump file and
<command>mysql</command> to reload the file:
</para>
@@ -1539,7 +1541,7 @@
</programlisting>
<para>
- To recreate all the tables in a single database, specify the
+ To rebuild all the tables in a single database, specify the
database name without any following table name:
</para>
@@ -1549,7 +1551,7 @@
</programlisting>
<para>
- To recreate all tables in all databases, use the
+ To rebuild all tables in all databases, use the
<option role="mysqldump">--all-databases</option> option:
</para>
Modified: trunk/refman-6.0.sav/se-myisam-core.xml
===================================================================
--- trunk/refman-6.0.sav/se-myisam-core.xml 2009-11-06 20:24:57 UTC (rev 17509)
+++ trunk/refman-6.0.sav/se-myisam-core.xml 2009-11-06 20:39:38 UTC (rev 17510)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 595 bytes
@@ -61,7 +61,7 @@
utility. You can also compress <literal>MyISAM</literal> tables with
<command>myisampack</command> to take up much less space. See
<xref linkend="mysqlcheck"/>,
- <xref linkend="myisam-crash-recovery"/>, and
+ <xref linkend="myisamchk"/>, and
<xref linkend="myisampack"/>.
</para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r17510 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-5.1-maria refman-5.4 refman-5.5 refman-6.0.sav | paul.dubois | 6 Nov |
