MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mcbrown Date:March 1 2007 2:37pm
Subject:svn commit - mysqldoc@docsrva: r5135 - trunk/refman-4.1
View as plain text  
Author: mcbrown
Date: 2007-03-01 15:37:49 +0100 (Thu, 01 Mar 2007)
New Revision: 5135

Log:
Updating appendix structure for 4.1



Added:
   trunk/refman-4.1/errors-problems.xml
Removed:
   trunk/refman-4.1/error-handling.xml
   trunk/refman-4.1/porting.xml
Modified:
   trunk/refman-4.1/extending-mysql.xml
   trunk/refman-4.1/functions.xml
   trunk/refman-4.1/installing.xml
   trunk/refman-4.1/introduction.xml
   trunk/refman-4.1/language-structure.xml
   trunk/refman-4.1/manual.xml
   trunk/refman-4.1/news-4.0.xml
   trunk/refman-4.1/problems.xml
   trunk/refman-4.1/restrictions.xml
   trunk/refman-4.1/storage-engines.xml


Added: trunk/refman-4.1/errors-problems.xml
===================================================================
--- trunk/refman-4.1/errors-problems.xml	                        (rev 0)
+++ trunk/refman-4.1/errors-problems.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 5733, Lines Deleted: 0; 206913 bytes

@@ -0,0 +1,5733 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE appendix PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+  <!ENTITY % fixedchars.entities  SYSTEM "../common/fixedchars.ent">
+  %fixedchars.entities;
+  <!ENTITY % urls.entities       SYSTEM "../refman-common/urls.ent">
+  %urls.entities;
+  <!ENTITY % versions.entities    SYSTEM "versions.ent">
+  %versions.entities;
+]>
+<appendix id="error-handling">
+
+  <title>Errors, Error Codes, and Common Problems</title>
+
+  <remark role="todo">
+    say something about SHOW WARNINGS, SHOW ERRORS, @warning_count,
+    @error_count.
+  </remark>
+
+  <para>
+    This appendix lists common problems and errors that may occur and
+    potential resolutions, in addition to listing the errors that may
+    appear when you call MySQL from any host language. The first section
+    covers problems and resolutions. Detailed information on errors is
+    provided; The first list displays server error messages. The second
+    list displays client program messages.
+  </para>
+
+  <section id="problems">
+
+    <title>Problems and Common Errors</title>
+
+    <indexterm>
+      <primary>problems</primary>
+      <secondary>common errors</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>errors</primary>
+      <secondary>common</secondary>
+    </indexterm>
+
+    <para>
+      This section lists some common problems and error messages that
+      you may encounter. It describes how to determine the causes of the
+      problems and what to do to solve them.
+    </para>
+
+    <section id="what-is-crashing">
+
+      <title>How to Determine What Is Causing a Problem</title>
+
+      <para>
+        When you run into a problem, the first thing you should do is to
+        find out which program or piece of equipment is causing it:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            If you have one of the following symptoms, then it is
+            probably a hardware problems (such as memory, motherboard,
+            CPU, or hard disk) or kernel problem:
+          </para>
+
+          <itemizedlist>
+
+            <listitem>
+              <para>
+                The keyboard doesn't work. This can normally be checked
+                by pressing the Caps Lock key. If the Caps Lock light
+                doesn't change, you have to replace your keyboard.
+                (Before doing this, you should try to restart your
+                computer and check all cables to the keyboard.)
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                The mouse pointer doesn't move.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                The machine doesn't answer to a remote machine's pings.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Other programs that are not related to MySQL don't
+                behave correctly.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Your system restarted unexpectedly. (A faulty user-level
+                program should never be able to take down your system.)
+              </para>
+            </listitem>
+
+          </itemizedlist>
+
+          <para>
+            In this case, you should start by checking all your cables
+            and run some diagnostic tool to check your hardware! You
+            should also check whether there are any patches, updates, or
+            service packs for your operating system that could likely
+            solve your problem. Check also that all your libraries (such
+            as <literal>glibc</literal>) are up to date.
+          </para>
+
+          <para>
+            It's always good to use a machine with ECC memory to
+            discover memory problems early.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If your keyboard is locked up, you may be able to recover by
+            logging in to your machine from another machine and
+            executing <literal>kbd_mode -a</literal>.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Please examine your system log file
+            (<filename>/var/log/messages</filename> or similar) for
+            reasons for your problem. If you think the problem is in
+            MySQL, you should also examine MySQL's log files. See
+            <xref linkend="log-files"/>.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If you don't think you have hardware problems, you should
+            try to find out which program is causing problems. Try using
+            <command>top</command>, <command>ps</command>, Task Manager,
+            or some similar program, to check which program is taking
+            all CPU or is locking the machine.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Use <command>top</command>, <command>df</command>, or a
+            similar program to check whether you are out of memory, disk
+            space, file descriptors, or some other critical resource.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If the problem is some runaway process, you can always try
+            to kill it. If it doesn't want to die, there is probably a
+            bug in the operating system.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        If after you have examined all other possibilities and you have
+        concluded that the MySQL server or a MySQL client is causing the
+        problem, it's time to create a bug report for our mailing list
+        or our support team. In the bug report, try to give a very
+        detailed description of how the system is behaving and what you
+        think is happening. You should also state why you think that
+        MySQL is causing the problem. Take into consideration all the
+        situations in this chapter. State any problems exactly how they
+        appear when you examine your system. Use the <quote>copy and
+        paste</quote> method for any output and error messages from
+        programs and log files.
+      </para>
+
+      <para>
+        Try to describe in detail which program is not working and all
+        symptoms you see. We have in the past received many bug reports
+        that state only <quote>the system doesn't work.</quote> This
+        doesn't provide us with any information about what could be the
+        problem.
+      </para>
+
+      <para>
+        If a program fails, it's always useful to know the following
+        information:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            Has the program in question made a segmentation fault (did
+            it dump core)?
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Is the program taking up all available CPU time? Check with
+            <command>top</command>. Let the program run for a while, it
+            may simply be evaluating something computationally
+            intensive.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            If the <command>mysqld</command> server is causing problems,
+            can you get any response from it with <command>mysqladmin -u
+            root ping</command> or <command>mysqladmin -u root
+            processlist</command>?
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            What does a client program say when you try to connect to
+            the MySQL server? (Try with <command>mysql</command>, for
+            example.) Does the client jam? Do you get any output from
+            the program?
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        When sending a bug report, you should follow the outline
+        described in <xref linkend="bug-reports"/>.
+      </para>
+
+    </section>
+
+    <section id="common-errors">
+
+      <title>Common Errors When Using MySQL Programs</title>
+
+      <indexterm>
+        <primary>errors</primary>
+        <secondary>list of</secondary>
+      </indexterm>
+
+      <para>
+        This section lists some errors that users frequently encounter
+        when running MySQL programs. Although the problems show up when
+        you try to run client programs, the solutions to many of the
+        problems involves changing the configuration of the MySQL
+        server.
+      </para>
+
+      <section id="error-access-denied">
+
+        <title><literal>Access denied</literal></title>
+
+        <indexterm>
+          <primary>errors</primary>
+          <secondary>access denied</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>problems</primary>
+          <secondary>access denied errors</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>access denied errors</primary>
+        </indexterm>
+
+        <para>
+          An <literal>Access denied</literal> error can have many
+          causes. Often the problem is related to the MySQL accounts
+          that the server allows client programs to use when connecting.
+          See <xref linkend="access-denied"/>, and
+          <xref linkend="privileges"/>.
+        </para>
+
+      </section>
+
+      <section id="can-not-connect-to-server">
+
+        <title><literal>Can't connect to [local] MySQL server</literal></title>
+
+        <para>
+          A MySQL client on Unix can connect to the
+          <command>mysqld</command> server in two different ways: By
+          using a Unix socket file to connect through a file in the
+          filesystem (default <filename>/tmp/mysql.sock</filename>), or
+          by using TCP/IP, which connects through a port number. A Unix
+          socket file connection is faster than TCP/IP, but can be used
+          only when connecting to a server on the same computer. A Unix
+          socket file is used if you don't specify a hostname or if you
+          specify the special hostname <literal>localhost</literal>.
+        </para>
+
+        <para>
+          If the MySQL server is running on Windows 9x or Me, you can
+          connect only via TCP/IP. If the server is running on Windows
+          NT, 2000, XP, or 2003 and is started with the
+          <option>--enable-named-pipe</option> option, you can also
+          connect with named pipes if you run the client on the host
+          where the server is running. The name of the named pipe is
+          <literal>MySQL</literal> by default. If you don't give a
+          hostname when connecting to <command>mysqld</command>, a MySQL
+          client first tries to connect to the named pipe. If that
+          doesn't work, it connects to the TCP/IP port. You can force
+          the use of named pipes on Windows by using
+          <literal>.</literal> as the hostname.
+        </para>
+
+        <para>
+          The error (2002) <literal>Can't connect to ...</literal>
+          normally means that there is no MySQL server running on the
+          system or that you are using an incorrect Unix socket filename
+          or TCP/IP port number when trying to connect to the server.
+        </para>
+
+        <para>
+          The error (2003) <literal>Can't connect to MySQL server on
+          '<replaceable>server</replaceable>' (10061)</literal>
+          indicates that the network connection has been refused. You
+          should check that there is a MySQL server running, that it has
+          network connections enabled, the network port you specified is
+          the one configured on the server, and that the TCP/IP port you
+          are using has not been blocked by a firewall or port blocking
+          service.
+        </para>
+
+        <para>
+          Start by checking whether there is a process named
+          <command>mysqld</command> running on your server host. (Use
+          <command>ps xa | grep mysqld</command> on Unix or the Task
+          Manager on Windows.) If there is no such process, you should
+          start the server. See <xref linkend="starting-server"/>.
+        </para>
+
+        <para>
+          If a <command>mysqld</command> process is running, you can
+          check it by trying the following commands. The port number or
+          Unix socket filename might be different in your setup.
+          <literal>host_ip</literal> represents the IP number of the
+          machine where the server is running.
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysqladmin version</userinput>
+shell&gt; <userinput>mysqladmin variables</userinput>
+shell&gt; <userinput>mysqladmin -h `hostname` version variables</userinput>
+shell&gt; <userinput>mysqladmin -h `hostname` --port=3306 version</userinput>
+shell&gt; <userinput>mysqladmin -h host_ip version</userinput>
+shell&gt; <userinput>mysqladmin --protocol=socket --socket=/tmp/mysql.sock version</userinput>
+</programlisting>
+
+        <para>
+          Note the use of backticks rather than forward quotes with the
+          <literal>hostname</literal> command; these cause the output of
+          <literal>hostname</literal> (that is, the current hostname) to
+          be substituted into the <command>mysqladmin</command> command.
+          If you have no <literal>hostname</literal> command or are
+          running on Windows, you can manually type the hostname of your
+          machine (without backticks) following the
+          <literal>-h</literal> option. You can also try <literal>-h
+          127.0.0.1</literal> to connect with TCP/IP to the local host.
+        </para>
+
+        <para>
+          Here are some reasons the <literal>Can't connect to local
+          MySQL server</literal> error might occur:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <command>mysqld</command> is not running. Check your
+              operating system's process list to ensure the
+              <command>mysqld</command> process is present.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You're running a MySQL server on Windows with many TCP/IP
+              connections to it. If you're experiencing that quite often
+              your clients get that error, you can find a workaround
+              here:
+              <xref linkend="can-not-connect-to-server-on-windows"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are running on a system that uses MIT-pthreads. If you
+              are running on a system that doesn't have native threads,
+              <command>mysqld</command> uses the MIT-pthreads package.
+              See <xref linkend="which-os"/>. However, not all
+              MIT-pthreads versions support Unix socket files. On a
+              system without socket file support, you must always
+              specify the hostname explicitly when connecting to the
+              server. Try using this command to check the connection to
+              the server:
+            </para>
+
+<programlisting>
+shell&gt; <userinput>mysqladmin -h `hostname` version</userinput>
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              Someone has removed the Unix socket file that
+              <command>mysqld</command> uses
+              (<filename>/tmp/mysql.sock</filename> by default). For
+              example, you might have a <command>cron</command> job that
+              removes old files from the <filename>/tmp</filename>
+              directory. You can always run <command>mysqladmin
+              version</command> to check whether the Unix socket file
+              that <command>mysqladmin</command> is trying to use really
+              exists. The fix in this case is to change the
+              <command>cron</command> job to not remove
+              <filename>mysql.sock</filename> or to place the socket
+              file somewhere else. See
+              <xref linkend="problems-with-mysql-sock"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You have started the <command>mysqld</command> server with
+              the <option>--socket=/path/to/socket</option> option, but
+              forgotten to tell client programs the new name of the
+              socket file. If you change the socket pathname for the
+              server, you must also notify the MySQL clients. You can do
+              this by providing the same <option>--socket</option>
+              option when you run client programs. You also need to
+              ensure that clients have permission to access the
+              <filename>mysql.sock</filename> file. To find out where
+              the socket file is, you can do:
+            </para>
+
+<programlisting>
+shell&gt; <userinput>netstat -ln | grep mysql</userinput>
+</programlisting>
+
+            <para>
+              See <xref linkend="problems-with-mysql-sock"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are using Linux and one server thread has died (dumped
+              core). In this case, you must kill the other
+              <command>mysqld</command> threads (for example, with
+              <literal>kill</literal> or with the
+              <literal>mysql_zap</literal> script) before you can
+              restart the MySQL server. See <xref linkend="crashing"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The server or client program might not have the proper
+              access privileges for the directory that holds the Unix
+              socket file or the socket file itself. In this case, you
+              must either change the access privileges for the directory
+              or socket file so that the server and clients can access
+              them, or restart <command>mysqld</command> with a
+              <option>--socket</option> option that specifies a socket
+              filename in a directory where the server can create it and
+              where client programs can access it.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          If you get the error message <literal>Can't connect to MySQL
+          server on some_host</literal>, you can try the following
+          things to find out what the problem is:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Check whether the server is running on that host by
+              executing <literal>telnet some_host 3306</literal> and
+              pressing the Enter key a couple of times. (3306 is the
+              default MySQL port number. Change the value if your server
+              is listening to a different port.) If there is a MySQL
+              server running and listening to the port, you should get a
+              response that includes the server's version number. If you
+              get an error such as <literal>telnet: Unable to connect to
+              remote host: Connection refused</literal>, then there is
+              no server running on the given port.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If the server is running on the local host, try using
+              <command>mysqladmin -h localhost variables</command> to
+              connect using the Unix socket file. Verify the TCP/IP port
+              number that the server is configured to listen to (it is
+              the value of the <literal>port</literal> variable.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Make sure that your <command>mysqld</command> server was
+              not started with the <option>--skip-networking</option>
+              option. If it was, you cannot connect to it using TCP/IP.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Check to make sure that there is no firewall blocking
+              access to MySQL. Your firewall may be configured on the
+              basis of the application being executed, or the post
+              number used by MySQL for communication (3306 by default).
+            </para>
+
+            <para>
+              Under Linux or Unix, check your IP tables (or similar)
+              configuration to ensure that the port has not been
+              blocked.
+            </para>
+
+            <para>
+              Under Windows, applications such as ZoneAlarm and the
+              Windows XP personal firewall may need to be configured to
+              allow external access to a MySQL server.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you are running under Linux and Security-Enhanced Linux
+              (SELinux) is enabled, make sure you have disabled SELinux
+              protection for the <literal>mysqld</literal> process.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <section id="can-not-connect-to-server-on-windows">
+
+          <title><literal>Connection to MySQL Server Failing on Windows</literal></title>
+
+          <para>
+            When you're running a MySQL server on Windows with many
+            TCP/IP connections to it, and you're experiencing that quite
+            often your clients get a <literal>Can't connect to MySQL
+            server</literal> error, the reason might be that Windows
+            doesn't allow for enough ephemeral (short-lived) ports to
+            serve those connections.
+          </para>
+
+          <para>
+            By default, Windows allows 5000 ephemeral (short-lived) TCP
+            ports to the user. After any port is closed it will remain
+            in a <literal>TIME_WAIT</literal> status for 120 seconds.
+            This status allows the connection to be reused at a much
+            lower cost than reinitializing a brand new connection.
+            However, the port will not be available again until this
+            time expires.
+          </para>
+
+          <para>
+            With a small stack of available TCP ports (5000) and a high
+            number of TCP ports being open and closed over a short
+            period of time along with the <literal>TIME_WAIT</literal>
+            status you have a good chance for running out of ports.
+            There are two ways to address this problem:
+          </para>
+
+          <itemizedlist>
+
+            <listitem>
+              <para>
+                Reduce the number of TCP ports consumed quickly by
+                investigating connection pooling or persistent
+                connections where possible
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Tune some settings in the Windows registry (see below)
+              </para>
+            </listitem>
+
+          </itemizedlist>
+
+          <para>
+            <emphasis role="bold"> IMPORTANT: The following procedure
+            involves modifying the Windows registry. Before you modify
+            the registry, make sure to back it up and make sure that you
+            understand how to restore the registry if a problem occurs.
+            For information about how to back up, restore, and edit the
+            registry, view the following article in the Microsoft
+            Knowledge Base:
+            <ulink url="http://support.microsoft.com/kb/256986/EN-US/"/>.
+            </emphasis>
+          </para>
+
+          <orderedlist>
+
+            <listitem>
+              <para>
+                Start Registry Editor
+                (<filename>Regedt32.exe</filename>).
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Locate the following key in the registry:
+              </para>
+
+<programlisting>
+HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
+</programlisting>
+            </listitem>
+
+            <listitem>
+              <para>
+                On the <literal>Edit</literal> menu, click <literal>Add
+                Value</literal>, and then add the following registry
+                value:
+              </para>
+
+<programlisting>
+Value Name: MaxUserPort
+Data Type: REG_DWORD
+Value: 65534
+</programlisting>
+
+              <para>
+                This sets the number of ephemeral ports available to any
+                user. The valid range is between 5000 and 65534
+                (decimal). The default value is 0x1388 (5000 decimal).
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                On the <literal>Edit</literal> menu, click <literal>Add
+                Value</literal>, and then add the following registry
+                value:
+              </para>
+
+<programlisting>
+Value Name: TcpTimedWaitDelay
+Data Type: REG_DWORD
+Value: 30
+</programlisting>
+
+              <para>
+                This sets the number of seconds to hold a TCP port
+                connection in <literal>TIME_WAIT</literal> state before
+                closing. The valid range is between 0 (zero) and 300
+                (decimal). The default value is 0x78 (120 decimal).
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Quit Registry Editor.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Reboot the machine.
+              </para>
+            </listitem>
+
+          </orderedlist>
+
+          <para>
+            Note: Undoing the above should be as simple as deleting the
+            registry entries you've created.
+          </para>
+
+        </section>
+
+      </section>
+
+      <section id="old-client">
+
+        <title><literal>Client does not support authentication protocol</literal></title>
+
+        <para>
+          MySQL 4.1 and up uses an authentication protocol based on a
+          password hashing algorithm that is incompatible with that used
+          by older clients. If you upgrade the server from 4.0, attempts
+          to connect to it with an older client may fail with the
+          following message:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysql</userinput>
+Client does not support authentication protocol requested
+by server; consider upgrading MySQL client
+</programlisting>
+
+        <para>
+          To solve this problem, you should use one of the following
+          approaches:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Upgrade all client programs to use a 4.1.1 or newer client
+              library.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              When connecting to the server with a pre-4.1 client
+              program, use an account that still has a pre-4.1-style
+              password.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Reset the password to pre-4.1 style for each user that
+              needs to use a pre-4.1 client program. This can be done
+              using the <literal>SET PASSWORD</literal> statement and
+              the <literal>OLD_PASSWORD()</literal> function:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>SET PASSWORD FOR</userinput>
+    -&gt; <userinput>'<replaceable>some_user</replaceable>'@'<replaceable>some_host</replaceable>' = OLD_PASSWORD('<replaceable>newpwd</replaceable>');</userinput>
+</programlisting>
+
+            <para>
+              Alternatively, use <literal>UPDATE</literal> and
+              <literal>FLUSH PRIVILEGES</literal>:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>UPDATE mysql.user SET Password = OLD_PASSWORD('<replaceable>newpwd</replaceable>')</userinput>
+    -&gt; <userinput>WHERE Host = '<replaceable>some_host</replaceable>' AND User = '<replaceable>some_user</replaceable>';</userinput>
+mysql&gt; <userinput>FLUSH PRIVILEGES;</userinput>
+</programlisting>
+
+            <para>
+              Substitute the password you want to use for
+              <quote><replaceable>newpwd</replaceable></quote> in the
+              preceding examples. MySQL cannot tell you what the
+              original password was, so you'll need to pick a new one.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Tell the server to use the older password hashing
+              algorithm:
+            </para>
+
+            <orderedlist>
+
+              <listitem>
+                <para>
+                  Start <command>mysqld</command> with the
+                  <option>--old-passwords</option> option.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  Assign an old-format password to each account that has
+                  had its password updated to the longer 4.1 format. You
+                  can identify these accounts with the following query:
+                </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT Host, User, Password FROM mysql.user</userinput>
+    -&gt; <userinput>WHERE LENGTH(Password) &gt; 16;</userinput>
+</programlisting>
+
+                <para>
+                  For each account record displayed by the query, use
+                  the <literal>Host</literal> and
+                  <literal>User</literal> values and assign a password
+                  using the <literal>OLD_PASSWORD()</literal> function
+                  and either <literal>SET PASSWORD</literal> or
+                  <literal>UPDATE</literal>, as described earlier.
+                </para>
+              </listitem>
+
+            </orderedlist>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          <emphasis role="bold">Note</emphasis>: In PHP, the standard
+          <literal>mysql</literal> extension does not support the new
+          authentication protocol in MySQL 4.1.1 and higher. This is
+          true regardless of the PHP version being used. If you wish to
+          use the <literal>mysql</literal> extension with MySQL 4.1 or
+          newer, you will need to follow one of the options discussed
+          above for configuring MySQL to work with old clients. The
+          <literal>mysqli</literal> extension (stands for "MySQL,
+          Improved"; new in PHP 5) <emphasis role="bold">is</emphasis>
+          compatible with the improved password hashing employed in
+          MySQL 4.1 and higher, and no special configuration of MySQL
+          need be done to use this newer MySQL client library for PHP.
+          For more information about the <literal>mysqli</literal>
+          extension, see <ulink url="http://php.net/mysqli"/>.
+        </para>
+
+        <para>
+          It may also be possible to compile the older
+          <literal>mysql</literal> extension against the new MySQL
+          client library. This is beyond the scope of this Manual;
+          consult the PHP documentation for more information. You also
+          be able to obtain assistance with these issues in our
+          <ulink url="&base-url-forum-list;?52">MySQL with PHP
+          forum</ulink>.
+        </para>
+
+        <para>
+          For additional background on password hashing and
+          authentication, see <xref linkend="password-hashing"/>.
+        </para>
+
+      </section>
+
+      <section id="password-too-long">
+
+        <title>Password Fails When Entered Interactively</title>
+
+        <para>
+          MySQL client programs prompt for a password when invoked with
+          a <option>--password</option> or <option>-p</option> option
+          that has no following password value:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysql -u <replaceable>user_name</replaceable> -p</userinput>
+Enter password:
+</programlisting>
+
+        <para>
+          On some systems, you may find that your password works when
+          specified in an option file or on the command line, but not
+          when you enter it interactively at the <literal>Enter
+          password:</literal> prompt. This occurs when the library
+          provided by the system to read passwords limits password
+          values to a small number of characters (typically eight). That
+          is a problem with the system library, not with MySQL. To work
+          around it, change your MySQL password to a value that is eight
+          or fewer characters long, or put your password in an option
+          file.
+        </para>
+
+      </section>
+
+      <section id="blocked-host">
+
+        <title><literal>Host '<replaceable>host_name</replaceable>' is
+          blocked</literal></title>
+
+        <para>
+          If you get the following error, it means that
+          <command>mysqld</command> has received many connect requests
+          from the host
+          <literal>'<replaceable>host_name</replaceable>'</literal> that
+          have been interrupted in the middle:
+        </para>
+
+<programlisting>
+Host '<replaceable>host_name</replaceable>' is blocked because of many connection errors.
+Unblock with 'mysqladmin flush-hosts'
+</programlisting>
+
+        <para>
+          The number of interrupted connect requests allowed is
+          determined by the value of the
+          <literal>max_connect_errors</literal> system variable. After
+          <literal>max_connect_errors</literal> failed requests,
+          <command>mysqld</command> assumes that something is wrong (for
+          example, that someone is trying to break in), and blocks the
+          host from further connections until you execute a
+          <command>mysqladmin flush-hosts</command> command or issue a
+          <literal>FLUSH HOSTS</literal> statement. See
+          <xref linkend="server-system-variables"/>.
+        </para>
+
+        <para>
+          By default, <command>mysqld</command> blocks a host after 10
+          connection errors. You can adjust the value by starting the
+          server like this:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysqld_safe --max_connect_errors=10000 &amp;</userinput>
+</programlisting>
+
+        <para>
+          If you get this error message for a given host, you should
+          first verify that there isn't anything wrong with TCP/IP
+          connections from that host. If you are having network
+          problems, it does you no good to increase the value of the
+          <literal>max_connect_errors</literal> variable.
+        </para>
+
+      </section>
+
+      <section id="too-many-connections">
+
+        <title><literal>Too many connections</literal></title>
+
+        <para>
+          If you get a <literal>Too many connections</literal> error
+          when you try to connect to the <command>mysqld</command>
+          server, this means that all available connections are in use
+          by other clients.
+        </para>
+
+        <para>
+          The number of connections allowed is controlled by the
+          <literal>max_connections</literal> system variable. Its
+          default value is 100. If you need to support more connections,
+          you should restart <command>mysqld</command> with a larger
+          value for this variable.
+        </para>
+
+        <para>
+          <command>mysqld</command> actually allows
+          <literal>max_connections+1</literal> clients to connect. The
+          extra connection is reserved for use by accounts that have the
+          <literal>SUPER</literal> privilege. By granting the
+          <literal>SUPER</literal> privilege to administrators and not
+          to normal users (who should not need it), an administrator can
+          connect to the server and use <literal>SHOW
+          PROCESSLIST</literal> to diagnose problems even if the maximum
+          number of unprivileged clients are connected. See
+          <xref linkend="show-processlist"/>.
+        </para>
+
+        <para>
+          The maximum number of connections MySQL can support depends on
+          the quality of the thread library on a given platform. Linux
+          or Solaris should be able to support 500-1000 simultaneous
+          connections, depending on how much RAM you have and what your
+          clients are doing. Static Linux binaries provided by MySQL AB
+          can support up to 4000 connections.
+        </para>
+
+      </section>
+
+      <section id="out-of-memory">
+
+        <title><literal>Out of memory</literal></title>
+
+        <para>
+          If you issue a query using the <command>mysql</command> client
+          program and receive an error like the following one, it means
+          that <command>mysql</command> does not have enough memory to
+          store the entire query result:
+        </para>
+
+<programlisting>
+mysql: Out of memory at line 42, 'malloc.c'
+mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
+ERROR 2008: MySQL client ran out of memory
+</programlisting>
+
+        <para>
+          To remedy the problem, first check whether your query is
+          correct. Is it reasonable that it should return so many rows?
+          If not, correct the query and try again. Otherwise, you can
+          invoke <command>mysql</command> with the
+          <option>--quick</option> option. This causes it to use the
+          <literal>mysql_use_result()</literal> C API function to
+          retrieve the result set, which places less of a load on the
+          client (but more on the server).
+        </para>
+
+      </section>
+
+      <section id="gone-away">
+
+        <title><literal>MySQL server has gone away</literal></title>
+
+        <indexterm>
+          <primary>CR_SERVER_GONE_ERROR</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>CR_SERVER_LOST_ERROR</primary>
+        </indexterm>
+
+        <para>
+          This section also covers the related <literal>Lost connection
+          to server during query</literal> error.
+        </para>
+
+        <para>
+          The most common reason for the <literal>MySQL server has gone
+          away</literal> error is that the server timed out and closed
+          the connection. In this case, you normally get one of the
+          following error codes (which one you get is operating
+          system-dependent):
+        </para>
+
+        <informaltable>
+          <tgroup cols="2">
+            <colspec colwidth="35*"/>
+            <colspec colwidth="65*"/>
+            <tbody>
+              <row>
+                <entry><emphasis role="bold">Error Code</emphasis></entry>
+                <entry><emphasis role="bold">Description</emphasis></entry>
+              </row>
+              <row>
+                <entry><literal>CR_SERVER_GONE_ERROR</literal></entry>
+                <entry>The client couldn't send a question to the server.</entry>
+              </row>
+              <row>
+                <entry><literal>CR_SERVER_LOST</literal></entry>
+                <entry>The client didn't get an error when writing to the server, but it didn't
+                  get a full answer (or any answer) to the question.</entry>
+              </row>
+            </tbody>
+          </tgroup>
+        </informaltable>
+
+        <para>
+          By default, the server closes the connection after eight hours
+          if nothing has happened. You can change the time limit by
+          setting the <literal>wait_timeout</literal> variable when you
+          start <command>mysqld</command>. See
+          <xref linkend="server-system-variables"/>.
+        </para>
+
+        <para>
+          If you have a script, you just have to issue the query again
+          for the client to do an automatic reconnection. This assumes
+          that you have automatic reconnection in the client enabled
+          (which is the default for the <literal>mysql</literal>
+          command-line client).
+        </para>
+
+        <para>
+          Some other common reasons for the <literal>MySQL server has
+          gone away</literal> error are:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              You (or the db administrator) has killed the running
+              thread with a <literal>KILL</literal> statement or a
+              <command>mysqladmin kill</command> command.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You tried to run a query after closing the connection to
+              the server. This indicates a logic error in the
+              application that should be corrected.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              A client application running on a different host does not
+              have the necessary privileges to connect to the MySQL
+              server from that host.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You got a timeout from the TCP/IP connection on the client
+              side. This may happen if you have been using the commands:
+              <literal>mysql_options(...,
+              MYSQL_OPT_READ_TIMEOUT,...)</literal> or
+              <literal>mysql_options(...,
+              MYSQL_OPT_WRITE_TIMEOUT,...)</literal>. In this case
+              increasing the timeout may help solve the problem.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You have encountered a timeout on the server side and the
+              automatic reconnection in the client is disabled (the
+              <literal>reconnect</literal> flag in the
+              <literal>MYSQL</literal> structure is equal to 0).
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are using a Windows client and the server had dropped
+              the connection (probably because
+              <literal>wait_timeout</literal> expired) before the
+              command was issued.
+            </para>
+
+            <para>
+              The problem on Windows is that in some cases MySQL doesn't
+              get an error from the OS when writing to the TCP/IP
+              connection to the server, but instead gets the error when
+              trying to read the answer from the connection.
+            </para>
+
+            <para>
+              In this case, even if the <literal>reconnect</literal>
+              flag in the <literal>MYSQL</literal> structure is equal to
+              1, MySQL does not automatically reconnect and re-issue the
+              query as it doesn't know if the server did get the
+              original query or not.
+            </para>
+
+            <para>
+              The solution to this is to either do a
+              <literal>mysql_ping</literal> on the connection if there
+              has been a long time since the last query (this is what
+              <literal>MyODBC</literal> does) or set
+              <literal>wait_timeout</literal> on the
+              <command>mysqld</command> server so high that it in
+              practice never times out.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can also get these errors if you send a query to the
+              server that is incorrect or too large. If
+              <command>mysqld</command> receives a packet that is too
+              large or out of order, it assumes that something has gone
+              wrong with the client and closes the connection. If you
+              need big queries (for example, if you are working with big
+              <literal>BLOB</literal> columns), you can increase the
+              query limit by setting the server's
+              <literal>max_allowed_packet</literal> variable, which has
+              a default value of 1MB. You may also need to increase the
+              maximum packet size on the client end. More information on
+              setting the packet size is given in
+              <xref linkend="packet-too-large"/>.
+            </para>
+
+            <para>
+              An <literal>INSERT</literal> or <literal>REPLACE</literal>
+              statement that inserts a great many rows can also cause
+              these sorts of errors. Either one of these statements
+              sends a single request to the server irrespective of the
+              number of rows to be inserted; thus, you can often avoid
+              the error by reducing the number of rows sent per
+              <literal>INSERT</literal> or <literal>REPLACE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You also get a lost connection if you are sending a packet
+              16MB or larger if your client is older than 4.0.8 and your
+              server is 4.0.8 and above, or the other way around.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              It is also possible to see this error if hostname lookups
+              fail (for example, if the DNS server on which your server
+              or network relies goes down). This is because MySQL is
+              dependent on the host system for name resolution, but has
+              no way of knowing whether it is working &mdash; from
+              MySQL's point of view the problem is indistinguishable
+              from any other network timeout.
+            </para>
+
+            <para>
+              You may also see the <literal>MySQL server has gone
+              away</literal> error if MySQL is started with the
+              <option>--skip-networking</option> option.
+            </para>
+
+            <para>
+              Another networking issue that can cause this error occurs
+              if if the MySQL port (default 3306) is blocked by your
+              firewall, thus preventing any connections at all to the
+              MySQL server.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can also encounter this error with applications that
+              fork child processes, all of which try to use the same
+              connection to the MySQL server. This can be avoided by
+              using a separate connection for each child process.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You have encountered a bug where the server died while
+              executing the query.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          You can check whether the MySQL server died and restarted by
+          executing <command>mysqladmin version</command> and examining
+          the server's uptime. If the client connection was broken
+          because <command>mysqld</command> crashed and restarted, you
+          should concentrate on finding the reason for the crash. Start
+          by checking whether issuing the query again kills the server
+          again. See <xref linkend="crashing"/>.
+        </para>
+
+        <para>
+          You can get more information about the lost connections by
+          starting mysqld with the <option>--log-warnings=2</option>
+          option. This logs some of the disconnected errors in the
+          <literal>hostname.err</literal> file. See
+          <xref linkend="error-log"/>.
+        </para>
+
+        <para>
+          If you want to create a bug report regarding this problem, be
+          sure that you include the following information:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Indicate whether the MySQL server died. You can find
+              information about this in the server error log. See
+              <xref linkend="crashing"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If a specific query kills <command>mysqld</command> and
+              the tables involved were checked with <literal>CHECK
+              TABLE</literal> before you ran the query, can you provide
+              a reproducible test case? See
+              <xref linkend="reproducible-test-case"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              What is the value of the <literal>wait_timeout</literal>
+              system variable in the MySQL server? (<command>mysqladmin
+              variables</command> gives you the value of this variable.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Have you tried to run <command>mysqld</command> with the
+              <option>--log</option> option to determine whether the
+              problem query appears in the log?
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          See also <xref linkend="communication-errors"/>, and
+          <xref linkend="bug-reports"/>.
+        </para>
+
+      </section>
+
+      <section id="packet-too-large">
+
+        <title><literal>Packet too large</literal></title>
+
+        <para>
+          A communication packet is a single SQL statement sent to the
+          MySQL server, a single row that is sent to the client, or a
+          binary log event sent from a master replication server to a
+          slave.
+        </para>
+
+        <para>
+          In MySQL 3.23, the largest possible packet is 16MB, due to
+          limits in the client/server protocol. In MySQL 4.0.1 and up,
+          the limit is 1GB.
+        </para>
+
+        <remark role="note">
+          Commented out following: too vague.
+        </remark>
+
+<!--  
+      <para>
+        In MySQL 4.0.1 and up, this is only limited by the amount of 
+        memory you have on your server (up to a theoretical maximum of 
+        2GB). 
+      </para>
+-->
+
+        <para>
+          When a MySQL client or the <command>mysqld</command> server
+          receives a packet bigger than
+          <literal>max_allowed_packet</literal> bytes, it issues a
+          <literal>Packet too large</literal> error and closes the
+          connection. With some clients, you may also get a
+          <literal>Lost connection to MySQL server during
+          query</literal> error if the communication packet is too
+          large.
+        </para>
+
+        <para>
+          Both the client and the server have their own
+          <literal>max_allowed_packet</literal> variable, so if you want
+          to handle big packets, you must increase this variable both in
+          the client and in the server.
+        </para>
+
+        <para>
+          If you are using the <command>mysql</command> client program,
+          its default <literal>max_allowed_packet</literal> variable is
+          16MB. That is also the maximum value before MySQL 4.0. To set
+          a larger value from 4.0 on, start <command>mysql</command>
+          like this:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysql --max_allowed_packet=32M</userinput>
+</programlisting>
+
+        <para>
+          That sets the packet size to 32MB.
+        </para>
+
+        <para>
+          The server's default <literal>max_allowed_packet</literal>
+          value is 1MB. You can increase this if the server needs to
+          handle big queries (for example, if you are working with big
+          <literal>BLOB</literal> columns). For example, to set the
+          variable to 16MB, start the server like this:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysqld --max_allowed_packet=16M</userinput>
+</programlisting>
+
+        <para>
+          Before MySQL 4.0, use this syntax instead:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysqld --set-variable=max_allowed_packet=16M</userinput>
+</programlisting>
+
+        <para>
+          You can also use an option file to set
+          <literal>max_allowed_packet</literal>. For example, to set the
+          size for the server to 16MB, add the following lines in an
+          option file:
+        </para>
+
+<programlisting>
+[mysqld]
+max_allowed_packet=16M
+</programlisting>
+
+        <para>
+          Before MySQL 4.0, use this syntax instead:
+        </para>
+
+<programlisting>
+[mysqld]
+set-variable = max_allowed_packet=16M
+</programlisting>
+
+        <para>
+          It's safe to increase the value of this variable because the
+          extra memory is allocated only when needed. For example,
+          <command>mysqld</command> allocates more memory only when you
+          issue a long query or when <command>mysqld</command> must
+          return a large result row. The small default value of the
+          variable is a precaution to catch incorrect packets between
+          the client and server and also to ensure that you don't run
+          out of memory by using large packets accidentally.
+        </para>
+
+        <para>
+          You can also get strange problems with large packets if you
+          are using large <literal>BLOB</literal> values but have not
+          given <command>mysqld</command> access to enough memory to
+          handle the query. If you suspect this is the case, try adding
+          <command>ulimit -d 256000</command> to the beginning of the
+          <command>mysqld_safe</command> script and restarting
+          <command>mysqld</command>.
+        </para>
+
+      </section>
+
+      <section id="communication-errors">
+
+        <title>Communication Errors and Aborted Connections</title>
+
+        <indexterm>
+          <primary>aborted clients</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>aborted connection</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>connection</primary>
+          <secondary>aborted</secondary>
+        </indexterm>
+
+        <para>
+          The server error log can be a useful source of information
+          about connection problems. See <xref linkend="error-log"/>.
+          Starting with MySQL 3.23.40, if you start the server with the
+          <option>--warnings</option> option (or
+          <option>--log-warnings</option> from MySQL 4.0.3 on), you
+          might find messages like this in your error log:
+        </para>
+
+<programlisting>
+010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'
+</programlisting>
+
+        <para>
+          If <literal>Aborted connections</literal> messages appear in
+          the error log, the cause can be any of the following:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              The client program did not call
+              <literal>mysql_close()</literal> before exiting.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The client had been sleeping more than
+              <literal>wait_timeout</literal> or
+              <literal>interactive_timeout</literal> seconds without
+              issuing any requests to the server. See
+              <xref linkend="server-system-variables"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The client program ended abruptly in the middle of a data
+              transfer.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          When any of these things happen, the server increments the
+          <literal>Aborted_clients</literal> status variable.
+        </para>
+
+        <para>
+          The server increments the <literal>Aborted_connects</literal>
+          status variable when the following things happen:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              A client doesn't have privileges to connect to a database.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              A client uses an incorrect password.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              A connection packet doesn't contain the right information.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              It takes more than <literal>connect_timeout</literal>
+              seconds to get a connect packet. See
+              <xref linkend="server-system-variables"/>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          If these kinds of things happen, it might indicate that
+          someone is trying to break into your server!
+        </para>
+
+        <formalpara role="mnmas">
+
+          <title>MySQL Enterprise</title>
+
+          <para>
+            For reasons of security and performance the advisors
+            provided by the MySQL Network Monitoring and Advisory
+            Service pay special attention to the
+            <literal>Aborted_connections</literal> status variable. For
+            more information see,
+            <ulink url="&base-url-enterprise;advisors.html"/>.
+          </para>
+
+        </formalpara>
+
+        <para>
+          Other reasons for problems with aborted clients or aborted
+          connections:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Use of Ethernet protocol with Linux, both half and full
+              duplex. Many Linux Ethernet drivers have this bug. You
+              should test for this bug by transferring a huge file via
+              FTP between the client and server machines. If a transfer
+              goes in burst-pause-burst-pause mode, you are experiencing
+              a Linux duplex syndrome. The only solution is switching
+              the duplex mode for both your network card and hub/switch
+              to either full duplex or to half duplex and testing the
+              results to determine the best setting.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Some problem with the thread library that causes
+              interrupts on reads.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Badly configured TCP/IP.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Faulty Ethernets, hubs, switches, cables, and so forth.
+              This can be diagnosed properly only by replacing hardware.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The <literal>max_allowed_packet</literal> variable value
+              is too small or queries require more memory than you have
+              allocated for <command>mysqld</command>. See
+              <xref linkend="packet-too-large"/>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          See also <xref linkend="gone-away"/>.
+        </para>
+
+      </section>
+
+      <section id="full-table">
+
+        <title><literal>The table is full</literal></title>
+
+        <indexterm>
+          <primary>table is full</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>tables</primary>
+          <secondary>maximum size</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>size of tables</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>operating systems</primary>
+          <secondary>file-size limits</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>limits</primary>
+          <secondary>file-size</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>files</primary>
+          <secondary>size limits</secondary>
+        </indexterm>
+
+        <para>
+          The maximum effective table size for MySQL databases is
+          usually determined by operating system constraints on file
+          sizes, not by MySQL internal limits. The following table lists
+          some examples of operating system file-size limits. This is
+          only a rough guide and is not intended to be definitive. For
+          the most up-to-date information, be sure to check the
+          documentation specific to your operating system.
+        </para>
+
+        <informaltable>
+          <tgroup cols="2">
+            <colspec colwidth="30*"/>
+            <colspec colwidth="50*"/>
+            <tbody>
+              <row>
+                <entry><emphasis role="bold">Operating System</emphasis></entry>
+                <entry><emphasis role="bold">File-size Limit</emphasis></entry>
+              </row>
+              <row>
+                <entry>Win32 w/ FAT/FAT32</entry>
+                <entry>2GB/4GB</entry>
+              </row>
+              <row>
+                <entry>Win32 w/ NTFS</entry>
+                <entry>2TB (possibly larger)</entry>
+              </row>
+              <row>
+                <entry>Linux 2.2-Intel 32-bit</entry>
+                <entry>2GB (LFS: 4GB)</entry>
+              </row>
+              <row>
+                <entry>Linux 2.4+</entry>
+                <entry>(using ext3 filesystem) 4TB</entry>
+              </row>
+              <row>
+                <entry>Solaris 9/10</entry>
+                <entry>16TB</entry>
+              </row>
+              <row>
+                <entry>MacOS X w/ HFS+</entry>
+                <entry>2TB</entry>
+              </row>
+              <row>
+                <entry>NetWare w/NSS filesystem</entry>
+                <entry>8TB</entry>
+              </row>
+            </tbody>
+          </tgroup>
+        </informaltable>
+
+        <para>
+          Windows users, please note that FAT and VFAT (FAT32) are
+          <emphasis>not</emphasis> considered suitable for production
+          use with MySQL. Use NTFS instead.
+        </para>
+
+        <para>
+          On Linux 2.2, you can get <literal>MyISAM</literal> tables
+          larger than 2GB in size by using the Large File Support (LFS)
+          patch for the ext2 filesystem. Most current Linux
+          distributions are based on kernel 2.4 or higher and include
+          all the required LFS patches. On Linux 2.4, patches also exist
+          for ReiserFS to get support for big files (up to 2TB). With
+          JFS and XFS, petabyte and larger files are possible on Linux.
+        </para>
+
+        <para>
+          For a detailed overview about LFS in Linux, have a look at
+          Andreas Jaeger's <citetitle>Large File Support in
+          Linux</citetitle> page at
+          <ulink url="http://www.suse.de/~aj/linux_lfs.html"/>.
+        </para>
+
+        <para>
+          If you do encounter a full-table error, there are several
+          reasons why it might have occurred:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              You are using a MySQL server older than 3.23 and an
+              in-memory temporary table becomes larger than
+              <literal>tmp_table_size</literal> bytes. To avoid this
+              problem, you can use the
+              <option>--tmp_table_size=<replaceable>val</replaceable></option>
+              option to make <command>mysqld</command> increase the
+              temporary table size or use the SQL option
+              <literal>SQL_BIG_TABLES</literal> before you issue the
+              problematic query. See <xref linkend="set-option"/>.
+            </para>
+
+            <para>
+              You can also start <command>mysqld</command> with the
+              <option>--big-tables</option> option. This is exactly the
+              same as using <literal>SQL_BIG_TABLES</literal> for all
+              queries.
+            </para>
+
+            <para>
+              As of MySQL 3.23, this problem should not occur. If an
+              in-memory temporary table becomes larger than
+              <literal>tmp_table_size</literal>, the server
+              automatically converts it to a disk-based
+              <literal>MyISAM</literal> table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The <literal>InnoDB</literal> storage engine maintains
+              <literal>InnoDB</literal> tables within a tablespace that
+              can be created from several files. This allows a table to
+              exceed the maximum individual file size. The tablespace
+              can include raw disk partitions, which allows extremely
+              large tables. The maximum tablespace size is 64TB.
+            </para>
+
+            <para>
+              If you are using <literal>InnoDB</literal> tables and run
+              out of room in the <literal>InnoDB</literal> tablespace.
+              In this case, the solution is to extend the
+              <literal>InnoDB</literal> tablespace. See
+              <xref linkend="adding-and-removing"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are using <literal>ISAM</literal> or
+              <literal>MyISAM</literal> tables on an operating system
+              that supports files only up to 2GB in size and you have
+              hit this limit for the data file or index file.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are using a <literal>MyISAM</literal> table and the
+              space required for the table exceeds what is allowed by
+              the internal pointer size. <literal>MyISAM</literal>
+              creates tables to allow up to 4GB by default, but this
+              limit can be changed up to the maximum allowable size of
+              65,536TB (256<superscript>7</superscript> &minus; 1
+              bytes).
+            </para>
+
+            <para>
+              If you need a <literal>MyISAM</literal> table that is
+              larger than the default limit and your operating system
+              supports large files, the <literal>CREATE TABLE</literal>
+              statement supports <literal>AVG_ROW_LENGTH</literal> and
+              <literal>MAX_ROWS</literal> options. See
+              <xref linkend="create-table"/>. The server uses these
+              options to determine how large a table to allow.
+            </para>
+
+            <para>
+              If the pointer size is too small for an existing table,
+              you can change the options with <literal>ALTER
+              TABLE</literal> to increase a table's maximum allowable
+              size. See <xref linkend="alter-table"/>.
+            </para>
+
+<programlisting>
+ALTER TABLE <replaceable>tbl_name</replaceable> MAX_ROWS=1000000000 AVG_ROW_LENGTH=<replaceable>nnn</replaceable>;
+</programlisting>
+
+            <para>
+              You have to specify <literal>AVG_ROW_LENGTH</literal> only
+              for tables with <literal>BLOB</literal> or
+              <literal>TEXT</literal> columns; in this case, MySQL can't
+              optimize the space required based only on the number of
+              rows.
+            </para>
+
+            <para>
+              To change the default size limit for
+              <literal>MyISAM</literal> tables, set the
+              <literal>myisam_data_pointer_size</literal>, which sets
+              the number of bytes used for internal row pointers. The
+              value is used to set the pointer size for new tables if
+              you do not specify the <literal>MAX_ROWS</literal> option.
+              The value of <literal>myisam_data_pointer_size</literal>
+              can be from 2 to 7. A value of 4 allows tables up to 4GB;
+              a value of 6 allows tables up to 256TB.
+            </para>
+
+            <para>
+              You can check the maximum data and index sizes by using
+              this statement:
+            </para>
+
+<programlisting>
+SHOW TABLE STATUS FROM <replaceable>db_name</replaceable> LIKE '<replaceable>tbl_name</replaceable>';
+</programlisting>
+
+            <para>
+              You also can use <command>myisamchk -dv
+              /path/to/table-index-file</command>. See
+              <xref linkend="show"/>, or <xref linkend="myisamchk"/>.
+            </para>
+
+            <para>
+              Other ways to work around file-size limits for
+              <literal>MyISAM</literal> tables are as follows:
+            </para>
+
+            <itemizedlist>
+
+              <listitem>
+                <para>
+                  If your large table is read-only, you can use
+                  <command>myisampack</command> to compress it.
+                  <command>myisampack</command> usually compresses a
+                  table by at least 50%, so you can have, in effect,
+                  much bigger tables. <command>myisampack</command> also
+                  can merge multiple tables into a single table. See
+                  <xref linkend="myisampack"/>.
+                </para>
+
+                <para>
+                  You can also get around the operating system file
+                  limit for <literal>MyISAM</literal> data files by
+                  using the <literal>RAID</literal> options for
+                  <literal>CREATE TABLE</literal>. See
+                  <xref linkend="create-table"/>.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  MySQL includes a <literal>MERGE</literal> library that
+                  allows you to handle a collection of
+                  <literal>MyISAM</literal> tables that have identical
+                  structure as a single <literal>MERGE</literal> table.
+                  See <xref linkend="merge-storage-engine"/>.
+                </para>
+              </listitem>
+
+            </itemizedlist>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are using the <literal>NDB</literal> storage engine,
+              in which case you need to increase the values for the
+              <literal>DataMemory</literal> and
+              <literal>IndexMemory</literal> configuration parameters in
+              your <filename>config.ini</filename> file. See
+              <xref linkend="mysql-cluster-config-params-ndbd"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are using the <literal>MEMORY</literal>
+              (<literal>HEAP</literal>) storage engine; in this case you
+              need to increase the value of the
+              <literal>max_heap_table_size</literal> system variable.
+              See <xref linkend="server-system-variables"/>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="cannot-create">
+
+        <title><literal>Can't create/write to file</literal></title>
+
+        <indexterm>
+          <primary>can't create/write to file</primary>
+        </indexterm>
+
+        <para>
+          If you get an error of the following type for some queries, it
+          means that MySQL cannot create a temporary file for the result
+          set in the temporary directory:
+        </para>
+
+<programlisting>
+Can't create/write to file '\\sqla3fe_0.ism'.
+</programlisting>
+
+        <para>
+          The preceding error is a typical message for Windows; the Unix
+          message is similar.
+        </para>
+
+        <para>
+          One fix is to start <command>mysqld</command> with the
+          <option>--tmpdir</option> option or to add the option to the
+          <literal>[mysqld]</literal> section of your option file. For
+          example, to specify a directory of
+          <filename>C:\temp</filename>, use these lines:
+        </para>
+
+<programlisting>
+[mysqld]
+tmpdir=C:/temp
+</programlisting>
+
+        <para>
+          The <filename>C:\temp</filename> directory must exist and have
+          sufficient space for the MySQL server to write to. See
+          <xref linkend="option-files"/>.
+        </para>
+
+        <para>
+          Another cause of this error can be permissions issues. Make
+          sure that the MySQL server can write to the
+          <literal>tmpdir</literal> directory.
+        </para>
+
+        <para>
+          Check also the error code that you get with
+          <command>perror</command>. One reason the server cannot write
+          to a table is that the filesystem is full:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>perror 28</userinput>
+Error code  28:  No space left on device
+</programlisting>
+
+      </section>
+
+      <section id="commands-out-of-sync">
+
+        <title><literal>Commands out of sync</literal></title>
+
+        <indexterm>
+          <primary>commands out of sync</primary>
+        </indexterm>
+
+        <para>
+          If you get <literal>Commands out of sync; you can't run this
+          command now</literal> in your client code, you are calling
+          client functions in the wrong order.
+        </para>
+
+        <para>
+          This can happen, for example, if you are using
+          <literal>mysql_use_result()</literal> and try to execute a new
+          query before you have called
+          <literal>mysql_free_result()</literal>. It can also happen if
+          you try to execute two queries that return data without
+          calling <literal>mysql_use_result()</literal> or
+          <literal>mysql_store_result()</literal> in between.
+        </para>
+
+      </section>
+
+      <section id="ignoring-user">
+
+        <title><literal>Ignoring user</literal></title>
+
+        <para>
+          If you get the following error, it means that when
+          <command>mysqld</command> was started or when it reloaded the
+          grant tables, it found an account in the
+          <literal>user</literal> table that had an invalid password.
+        </para>
+
+        <para>
+          <literal>Found wrong password for user
+          '<replaceable>some_user</replaceable>'@'<replaceable>some_host</replaceable>';
+          ignoring user</literal>
+        </para>
+
+        <para>
+          As a result, the account is simply ignored by the permission
+          system.
+        </para>
+
+        <para>
+          The following list indicates possible causes of and fixes for
+          this problem:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              You may be running a new version of
+              <command>mysqld</command> with an old
+              <literal>user</literal> table. You can check this by
+              executing <command>mysqlshow mysql user</command> to see
+              whether the <literal>Password</literal> column is shorter
+              than 16 characters. If so, you can correct this condition
+              by running the
+              <literal>scripts/add_long_password</literal> script.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The account has an old password (eight characters long)
+              and you didn't start <command>mysqld</command> with the
+              <option>--old-protocol</option> option. Update the account
+              in the <literal>user</literal> table to have a new
+              password or restart <command>mysqld</command> with the
+              <option>--old-protocol</option> option.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>PASSWORD()</primary>
+              </indexterm>
+
+              You have specified a password in the
+              <literal>user</literal> table without using the
+              <literal>PASSWORD()</literal> function. Use
+              <command>mysql</command> to update the account in the
+              <literal>user</literal> table with a new password, making
+              sure to use the <literal>PASSWORD()</literal> function:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>UPDATE user SET Password=PASSWORD('<replaceable>newpwd</replaceable>')</userinput>
+    -&gt; <userinput>WHERE User='<replaceable>some_user</replaceable>' AND Host='<replaceable>some_host</replaceable>';</userinput>
+</programlisting>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="cannot-find-table">
+
+        <title><literal>Table '<replaceable>tbl_name</replaceable>' doesn't
+          exist</literal></title>
+
+        <para>
+          If you get either of the following errors, it usually means
+          that no table exists in the default database with the given
+          name:
+        </para>
+
+<programlisting>
+Table '<replaceable>tbl_name</replaceable>' doesn't exist
+Can't find file: '<replaceable>tbl_name</replaceable>' (errno: 2)
+</programlisting>
+
+        <para>
+          In some cases, it may be that the table does exist but that
+          you are referring to it incorrectly:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Because MySQL uses directories and files to store
+              databases and tables, database and table names are case
+              sensitive if they are located on a filesystem that has
+              case-sensitive filenames.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Even for filesystems that are not case sensitive, such as
+              on Windows, all references to a given table within a query
+              must use the same lettercase.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          You can check which tables are in the default database with
+          <literal>SHOW TABLES</literal>. See <xref linkend="show"/>.
+        </para>
+
+      </section>
+
+      <section id="cannot-initialize-character-set">
+
+        <title><literal>Can't initialize character set</literal></title>
+
+        <indexterm>
+          <primary>multi-byte character sets</primary>
+        </indexterm>
+
+        <para>
+          You might see an error like this if you have character set
+          problems:
+        </para>
+
+<programlisting>
+MySQL Connection Failed: Can't initialize character set <replaceable>charset_name</replaceable>
+</programlisting>
+
+        <para>
+          This error can have any of the following causes:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              The character set is a multi-byte character set and you
+              have no support for the character set in the client. In
+              this case, you need to recompile the client by running
+              <command>configure</command> with the
+              <option>--with-charset=<replaceable>charset_name</replaceable></option>
+              or
+              <option>--with-extra-charsets=<replaceable>charset_name</replaceable></option>
+              option. See <xref linkend="configure-options"/>.
+            </para>
+
+            <para>
+              All standard MySQL binaries are compiled with
+              <option>--with-extra-character-sets=complex</option>,
+              which enables support for all multi-byte character sets.
+              See <xref linkend="character-sets"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The character set is a simple character set that is not
+              compiled into <command>mysqld</command>, and the character
+              set definition files are not in the place where the client
+              expects to find them.
+            </para>
+
+            <para>
+              In this case, you need to use one of the following methods
+              to solve the problem:
+            </para>
+
+            <itemizedlist>
+
+              <listitem>
+                <para>
+                  Recompile the client with support for the character
+                  set. See <xref linkend="configure-options"/>.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  Specify to the client the directory where the
+                  character set definition files are located. For many
+                  clients, you can do this with the
+                  <option>--character-sets-dir</option> option.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  Copy the character definition files to the path where
+                  the client expects them to be.
+                </para>
+              </listitem>
+
+            </itemizedlist>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="not-enough-file-handles">
+
+        <title><errortext>'<replaceable>File</replaceable>' Not Found</errortext> and
+          Similar Errors</title>
+
+        <para>
+          If you get <literal>ERROR '...' not found (errno:
+          23)</literal>, <literal>Can't open file: ... (errno:
+          24)</literal>, or any other error with <literal>errno
+          23</literal> or <literal>errno 24</literal> from MySQL, it
+          means that you haven't allocated enough file descriptors for
+          the MySQL server. You can use the <command>perror</command>
+          utility to get a description of what the error number means:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>perror 23</userinput>
+Error code  23:  File table overflow
+shell&gt; <userinput>perror 24</userinput>
+Error code  24:  Too many open files
+shell&gt; <userinput>perror 11</userinput>
+Error code  11:  Resource temporarily unavailable
+</programlisting>
+
+        <para>
+          The problem here is that <command>mysqld</command> is trying
+          to keep open too many files simultaneously. You can either
+          tell <command>mysqld</command> not to open so many files at
+          once or increase the number of file descriptors available to
+          <command>mysqld</command>.
+        </para>
+
+        <para>
+          To tell <command>mysqld</command> to keep open fewer files at
+          a time, you can make the table cache smaller by reducing the
+          value of the <literal>table_cache</literal> system variable
+          (the default value is 64). Reducing the value of
+          <literal>max_connections</literal> also reduces the number of
+          open files (the default value is 100).
+        </para>
+
+        <indexterm>
+          <primary>ulimit</primary>
+        </indexterm>
+
+        <para>
+          To change the number of file descriptors available to
+          <command>mysqld</command>, you can use the
+          <option>--open-files-limit</option> option to
+          <command>mysqld_safe</command> or (as of MySQL 3.23.30) set
+          the <literal>open_files_limit</literal> system variable. See
+          <xref linkend="server-system-variables"/>. The easiest way to
+          set these values is to add an option to your option file. See
+          <xref linkend="option-files"/>. If you have an old version of
+          <command>mysqld</command> that doesn't support setting the
+          open files limit, you can edit the
+          <command>mysqld_safe</command> script. There is a
+          commented-out line <command>ulimit -n 256</command> in the
+          script. You can remove the &lsquo;<literal>#</literal>&rsquo;
+          character to uncomment this line, and change the number
+          <literal>256</literal> to set the number of file descriptors
+          to be made available to <command>mysqld</command>.
+        </para>
+
+        <para>
+          <option>--open-files-limit</option> and
+          <command>ulimit</command> can increase the number of file
+          descriptors, but only up to the limit imposed by the operating
+          system. There is also a <quote>hard</quote> limit that can be
+          overridden only if you start <command>mysqld_safe</command> or
+          <command>mysqld</command> as <literal>root</literal> (just
+          remember that you also need to start the server with the
+          <option>--user</option> option in this case so that it does
+          not continue to run as <literal>root</literal> after it starts
+          up). If you need to increase the operating system limit on the
+          number of file descriptors available to each process, consult
+          the documentation for your system.
+        </para>
+
+        <para>
+          <emphasis role="bold">Note</emphasis>: If you run the
+          <command>tcsh</command> shell, <command>ulimit</command> does
+          not work! <command>tcsh</command> also reports incorrect
+          values when you ask for the current limits. In this case, you
+          should start <command>mysqld_safe</command> using
+          <command>sh</command>.
+        </para>
+
+      </section>
+
+    </section>
+
+    <section id="installation-issues">
+
+      <title>Installation-Related Issues</title>
+
+      <section id="link-errors">
+
+        <title>Problems Linking to the MySQL Client Library</title>
+
+        <indexterm>
+          <primary>linking</primary>
+          <secondary>errors</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>errors</primary>
+          <secondary>linking</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>problems</primary>
+          <secondary>linking</secondary>
+        </indexterm>
+
+        <para>
+          When you are linking an application program to use the MySQL
+          client library, you might get undefined reference errors for
+          symbols that start with <literal>mysql_</literal>, such as
+          those shown here:
+        </para>
+
+<programlisting>
+/tmp/ccFKsdPa.o: In function `main':
+/tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init'
+/tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect'
+/tmp/ccFKsdPa.o(.text+0x57): undefined reference to `mysql_real_connect'
+/tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error'
+/tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'
+</programlisting>
+
+        <para>
+          You should be able to solve this problem by adding
+          <literal>-Ldir_path -lmysqlclient</literal> at the end of your
+          link command, where <literal>dir_path</literal> represents the
+          pathname of the directory where the client library is located.
+          To determine the correct directory, try this command:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysql_config --libs</userinput>
+</programlisting>
+
+        <para>
+          The output from <command>mysql_config</command> might indicate
+          other libraries that should be specified on the link command
+          as well.
+        </para>
+
+        <para>
+          If you get <literal>undefined reference</literal> errors for
+          the <literal>uncompress</literal> or
+          <literal>compress</literal> function, add
+          <literal>-lz</literal> to the end of your link command and try
+          again.
+        </para>
+
+        <para>
+          If you get <literal>undefined reference</literal> errors for a
+          function that should exist on your system, such as
+          <literal>connect</literal>, check the manual page for the
+          function in question to determine which libraries you should
+          add to the link command.
+        </para>
+
+        <para>
+          You might get <literal>undefined reference</literal> errors
+          such as the following for functions that don't exist on your
+          system:
+        </para>
+
+<programlisting>
+mf_format.o(.text+0x201): undefined reference to `__lxstat'
+</programlisting>
+
+        <para>
+          This usually means that your MySQL client library was compiled
+          on a system that is not 100% compatible with yours. In this
+          case, you should download the latest MySQL source distribution
+          and compile MySQL yourself. See
+          <xref linkend="installing-source"/>.
+        </para>
+
+        <para>
+          You might get undefined reference errors at runtime when you
+          try to execute a MySQL program. If these errors specify
+          symbols that start with <literal>mysql_</literal> or indicate
+          that the <literal>mysqlclient</literal> library can't be
+          found, it means that your system can't find the shared
+          <filename>libmysqlclient.so</filename> library. The fix for
+          this is to tell your system to search for shared libraries
+          where the library is located. Use whichever of the following
+          methods is appropriate for your system:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Add the path to the directory where
+              <filename>libmysqlclient.so</filename> is located to the
+              <literal>LD_LIBRARY_PATH</literal> environment variable.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Add the path to the directory where
+              <filename>libmysqlclient.so</filename> is located to the
+              <literal>LD_LIBRARY</literal> environment variable.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Copy <filename>libmysqlclient.so</filename> to some
+              directory that is searched by your system, such as
+              <filename>/lib</filename>, and update the shared library
+              information by executing <literal>ldconfig</literal>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Another way to solve this problem is by linking your program
+          statically with the <literal>-static</literal> option, or by
+          removing the dynamic MySQL libraries before linking your code.
+          Before trying the second method, you should be sure that no
+          other programs are using the dynamic libraries.
+        </para>
+
+      </section>
+
+      <section id="file-permissions">
+
+        <title>Problems with File Permissions</title>
+
+        <indexterm>
+          <primary>files</primary>
+          <secondary>permissions</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>error messages</primary>
+          <secondary>can't find file</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>files</primary>
+          <secondary>not found message</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>UMASK environment variable</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>Environment variable</primary>
+          <secondary>UMASK</secondary>
+        </indexterm>
+
+        <para>
+          If you have problems with file permissions, the
+          <literal>UMASK</literal> environment variable might be set
+          incorrectly when <command>mysqld</command> starts. For
+          example, MySQL might issue the following error message when
+          you create a table:
+        </para>
+
+<programlisting>
+ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
+</programlisting>
+
+        <para>
+          The default <literal>UMASK</literal> value is
+          <literal>0660</literal>. You can change this behavior by
+          starting <command>mysqld_safe</command> as follows:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>UMASK=384  # = 600 in octal</userinput>
+shell&gt; <userinput>export UMASK</userinput>
+shell&gt; <userinput>mysqld_safe &amp;</userinput>
+</programlisting>
+
+        <indexterm>
+          <primary>UMASK_DIR environment variable</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>Environment variable</primary>
+          <secondary>UMASK_DIR</secondary>
+        </indexterm>
+
+        <para>
+          By default, MySQL creates database and <literal>RAID</literal>
+          directories with an access permission value of
+          <literal>0700</literal>. You can modify this behavior by
+          setting the <literal>UMASK_DIR</literal> variable. If you set
+          its value, new directories are created with the combined
+          <literal>UMASK</literal> and <literal>UMASK_DIR</literal>
+          values. For example, if you want to give group access to all
+          new directories, you can do this:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>UMASK_DIR=504  # = 770 in octal</userinput>
+shell&gt; <userinput>export UMASK_DIR</userinput>
+shell&gt; <userinput>mysqld_safe &amp;</userinput>
+</programlisting>
+
+        <para>
+          In MySQL 3.23.25 and above, MySQL assumes that the value for
+          <literal>UMASK</literal> and <literal>UMASK_DIR</literal> is
+          in octal if it starts with a zero.
+        </para>
+
+        <para>
+          See <xref linkend="environment-variables"/>.
+        </para>
+
+      </section>
+
+    </section>
+
+    <section id="administration-issues">
+
+      <title>Administration-Related Issues</title>
+
+      <section id="resetting-permissions">
+
+        <title>How to Reset the Root Password</title>
+
+        <indexterm>
+          <primary>passwords</primary>
+          <secondary>forgotten</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>passwords</primary>
+          <secondary>lost</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>passwords</primary>
+          <secondary>resetting</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>root user</primary>
+          <secondary>password resetting</secondary>
+        </indexterm>
+
+        <para>
+          If you have never set a <literal>root</literal> password for
+          MySQL, the server does not require a password at all for
+          connecting as <literal>root</literal>. However, it is
+          recommended to set a password for each account. See
+          <xref linkend="security-guidelines"/>.
+        </para>
+
+        <para>
+          If you set a <literal>root</literal> password previously, but
+          have forgotten what it was, you can set a new password. The
+          following procedure is for Windows systems. The procedure for
+          Unix systems is given later in this section.
+        </para>
+
+        <para>
+          The procedure under Windows:
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              Log on to your system as Administrator.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Stop the MySQL server if it is running. For a server that
+              is running as a Windows service, go to the Services
+              manager:
+            </para>
+
+<programlisting>
+Start Menu -&gt; Control Panel -&gt; Administrative Tools -&gt; Services
+</programlisting>
+
+            <para>
+              Then find the MySQL service in the list, and stop it.
+            </para>
+
+            <para>
+              If your server is not running as a service, you may need
+              to use the Task Manager to force it to stop.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Create a text file and place the following command within
+              it on a single line:
+            </para>
+
+<programlisting>
+SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
+</programlisting>
+
+            <para>
+              Save the file with any name. For this example the file
+              will be <filename>C:\mysql-init.txt</filename>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Open a console window to get to the DOS command prompt:
+            </para>
+
+<programlisting>
+Start Menu -&gt; Run -&gt; cmd
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              We are assuming that you installed MySQL to
+              <filename>C:\mysql</filename>. If you installed MySQL to
+              another location, adjust the following commands
+              accordingly.
+            </para>
+
+            <para>
+              At the DOS command prompt, execute this command:
+            </para>
+
+<programlisting>
+C:\&gt; <userinput>C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt</userinput>
+</programlisting>
+
+            <para>
+              The contents of the file named by the
+              <option>--init-file</option> option are executed at server
+              startup, changing the <literal>root</literal> password.
+              After the server has started successfully, you should
+              delete <filename>C:\mysql-init.txt</filename>.
+            </para>
+
+            <para>
+              Users of MySQL 4.1 and higher who install MySQL using the
+              MySQL Installation Wizard may need to specify a
+              <option>--defaults-file</option> option:
+            </para>
+
+<programlisting>
+C:\&gt; <userinput>"C:\Program Files\MySQL\MySQL Server &current-series;\bin\mysqld-nt.exe"</userinput>
+         <userinput>--defaults-file="C:\Program Files\MySQL\MySQL Server &current-series;\my.ini"</userinput>
+         <userinput>--init-file=C:\mysql-init.txt</userinput>
+</programlisting>
+
+            <para>
+              The appropriate <option>--defaults-file</option> setting
+              can be found using the Services Manager:
+            </para>
+
+<programlisting>
+Start Menu -&gt; Control Panel -&gt; Administrative Tools -&gt; Services
+</programlisting>
+
+            <para>
+              Find the MySQL service in the list, right-click on it, and
+              choose the <literal>Properties</literal> option. The
+              <literal>Path to executable</literal> field contains the
+              <option>--defaults-file</option> setting.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Stop the MySQL server, then restart it in normal mode
+              again. If you run the server as a service, start it from
+              the Windows Services window. If you start the server
+              manually, use whatever command you normally use.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You should be able to connect using the new password.
+            </para>
+          </listitem>
+
+        </orderedlist>
+
+        <para>
+          In a Unix environment, the procedure for resetting the
+          <literal>root</literal> password is as follows:
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              Log on to your system as either the Unix
+              <literal>root</literal> user or as the same user that the
+              <command>mysqld</command> server runs as.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Locate the <filename>.pid</filename> file that contains
+              the server's process ID. The exact location and name of
+              this file depend on your distribution, hostname, and
+              configuration. Common locations are
+              <filename>/var/lib/mysql/</filename>,
+              <filename>/var/run/mysqld/</filename>, and
+              <filename>/usr/local/mysql/data/</filename>. Generally,
+              the filename has the extension of
+              <filename>.pid</filename> and begins with either
+              <filename>mysqld</filename> or your system's hostname.
+            </para>
+
+            <para>
+              You can stop the MySQL server by sending a normal
+              <literal>kill</literal> (not <literal>kill -9</literal>)
+              to the <command>mysqld</command> process, using the
+              pathname of the <filename>.pid</filename> file in the
+              following command:
+            </para>
+
+<programlisting>
+shell&gt; <userinput>kill `cat /mysql-data-directory/host_name.pid`</userinput>
+</programlisting>
+
+            <para>
+              Note the use of backticks rather than forward quotes with
+              the <literal>cat</literal> command; these cause the output
+              of <literal>cat</literal> to be substituted into the
+              <literal>kill</literal> command.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Create a text file and place the following command within
+              it on a single line:
+            </para>
+
+<programlisting>
+SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');
+</programlisting>
+
+            <para>
+              Save the file with any name. For this example the file
+              will be <filename>~/mysql-init</filename>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Restart the MySQL server with the special
+              <option>--init-file=~/mysql-init</option> option:
+            </para>
+
+<programlisting>
+shell&gt; <userinput>mysqld_safe --init-file=~/mysql-init &amp;</userinput>
+</programlisting>
+
+            <para>
+              The contents of the init-file are executed at server
+              startup, changing the root password. After the server has
+              started successfully you should delete
+              <filename>~/mysql-init</filename>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You should be able to connect using the new password.
+            </para>
+          </listitem>
+
+        </orderedlist>
+
+        <para>
+          Alternatively, on any platform, you can set the new password
+          using the <command>mysql</command> client(but this approach is
+          less secure):
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              Stop <command>mysqld</command> and restart it with the
+              <option>--skip-grant-tables --user=root</option> options
+              (Windows users omit the <option>--user=root</option>
+              portion).
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Connect to the <command>mysqld</command> server with this
+              command:
+            </para>
+
+<programlisting>
+shell&gt; <userinput>mysql -u root</userinput>
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              Issue the following statements in the
+              <command>mysql</command> client:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>UPDATE mysql.user SET Password=PASSWORD('<replaceable>newpwd</replaceable>')</userinput>
+    -&gt;                   <userinput>WHERE User='root';</userinput>
+mysql&gt; <userinput>FLUSH PRIVILEGES;</userinput>
+</programlisting>
+
+            <para>
+              Replace <quote><replaceable>newpwd</replaceable></quote>
+              with the actual <literal>root</literal> password that you
+              want to use.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You should be able to connect using the new password.
+            </para>
+          </listitem>
+
+        </orderedlist>
+
+      </section>
+
+      <section id="crashing">
+
+        <title>What to Do If MySQL Keeps Crashing</title>
+
+        <indexterm>
+          <primary>crash</primary>
+          <secondary>repeated</secondary>
+        </indexterm>
+
+        <para>
+          Each MySQL version is tested on many platforms before it is
+          released. This doesn't mean that there are no bugs in MySQL,
+          but if there are bugs, they should be very few and can be hard
+          to find. If you have a problem, it always helps if you try to
+          find out exactly what crashes your system, because you have a
+          much better chance of getting the problem fixed quickly.
+        </para>
+
+        <para>
+          First, you should try to find out whether the problem is that
+          the <command>mysqld</command> server dies or whether your
+          problem has to do with your client. You can check how long
+          your <command>mysqld</command> server has been up by executing
+          <command>mysqladmin version</command>. If
+          <command>mysqld</command> has died and restarted, you may find
+          the reason by looking in the server's error log. See
+          <xref linkend="error-log"/>.
+        </para>
+
+        <para>
+          On some systems, you can find in the error log a stack trace
+          of where <command>mysqld</command> died that you can resolve
+          with the <literal>resolve_stack_dump</literal> program. See
+          <xref linkend="using-stack-trace"/>. Note that the variable
+          values written in the error log may not always be 100%
+          correct.
+        </para>
+
+        <para>
+          Many server crashes are caused by corrupted data files or
+          index files. MySQL updates the files on disk with the
+          <literal>write()</literal> system call after every SQL
+          statement and before the client is notified about the result.
+          (This is not true if you are running with
+          <option>--delay-key-write</option>, in which case data files
+          are written but not index files.) This means that data file
+          contents are safe even if <command>mysqld</command> crashes,
+          because the operating system ensures that the unflushed data
+          is written to disk. You can force MySQL to flush everything to
+          disk after every SQL statement by starting
+          <command>mysqld</command> with the <option>--flush</option>
+          option.
+        </para>
+
+        <para>
+          The preceding means that normally you should not get corrupted
+          tables unless one of the following happens:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              The MySQL server or the server host was killed in the
+              middle of an update.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You have found a bug in <command>mysqld</command> that
+              caused it to die in the middle of an update.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Some external program is manipulating data files or index
+              files at the same time as <command>mysqld</command>
+              without locking the table properly.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You are running many <command>mysqld</command> servers
+              using the same data directory on a system that doesn't
+              support good filesystem locks (normally handled by the
+              <literal>lockd</literal> lock manager), or you are running
+              multiple servers with external locking disabled.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You have a crashed data file or index file that contains
+              very corrupt data that confused <command>mysqld</command>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You have found a bug in the data storage code. This isn't
+              likely, but it's at least possible. In this case, you can
+              try to change the storage engine to another engine by
+              using <literal>ALTER TABLE</literal> on a repaired copy of
+              the table.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Because it is very difficult to know why something is
+          crashing, first try to check whether things that work for
+          others crash for you. Please try the following things:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Stop the <command>mysqld</command> server with
+              <command>mysqladmin shutdown</command>, run
+              <command>myisamchk --silent --force */*.MYI</command> from
+              the data directory to check all <literal>MyISAM</literal>
+              tables, and restart <command>mysqld</command>. This
+              ensures that you are running from a clean state. See
+              <xref linkend="database-administration"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Start <command>mysqld</command> with the
+              <option>--log</option> option and try to determine from
+              the information written to the log whether some specific
+              query kills the server. About 95% of all bugs are related
+              to a particular query. Normally, this is one of the last
+              queries in the log file just before the server restarts.
+              See <xref linkend="query-log"/>. If you can repeatedly
+              kill MySQL with a specific query, even when you have
+              checked all tables just before issuing it, then you have
+              been able to locate the bug and should submit a bug report
+              for it. See <xref linkend="bug-reports"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Try to make a test case that we can use to repeat the
+              problem. See <xref linkend="reproducible-test-case"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Try running the tests in the
+              <filename>mysql-test</filename> directory and the MySQL
+              benchmarks. See <xref linkend="mysql-test-suite"/>. They
+              should test MySQL rather well. You can also add code to
+              the benchmarks that simulates your application. The
+              benchmarks can be found in the
+              <filename>sql-bench</filename> directory in a source
+              distribution or, for a binary distribution, in the
+              <filename>sql-bench</filename> directory under your MySQL
+              installation directory.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Try the <literal>fork_big.pl</literal> script. (It is
+              located in the <filename>tests</filename> directory of
+              source distributions.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you configure MySQL for debugging, it is much easier to
+              gather information about possible errors if something goes
+              wrong. Configuring MySQL for debugging causes a safe
+              memory allocator to be included that can find some errors.
+              It also provides a lot of output about what is happening.
+              Reconfigure MySQL with the <option>--with-debug</option>
+              or <option>--with-debug=full</option> option to
+              <command>configure</command> and then recompile. See
+              <xref linkend="debugging-server"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Make sure that you have applied the latest patches for
+              your operating system.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Use the <option>--skip-external-locking</option> option to
+              <command>mysqld</command>. On some systems, the
+              <literal>lockd</literal> lock manager does not work
+              properly; the <option>--skip-external-locking</option>
+              option tells <command>mysqld</command> not to use external
+              locking. (This means that you cannot run two
+              <command>mysqld</command> servers on the same data
+              directory and that you must be careful if you use
+              <command>myisamchk</command>. Nevertheless, it may be
+              instructive to try the option as a test.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Have you tried <command>mysqladmin -u root
+              processlist</command> when <command>mysqld</command>
+              appears to be running but not responding? Sometimes
+              <command>mysqld</command> is not comatose even though you
+              might think so. The problem may be that all connections
+              are in use, or there may be some internal lock problem.
+              <command>mysqladmin -u root processlist</command> usually
+              is able to make a connection even in these cases, and can
+              provide useful information about the current number of
+              connections and their status.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Run the command <command>mysqladmin -i 5 status</command>
+              or <command>mysqladmin -i 5 -r status</command> in a
+              separate window to produce statistics while you run your
+              other queries.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Try the following:
+            </para>
+
+            <orderedlist>
+
+              <listitem>
+                <para>
+                  Start <command>mysqld</command> from
+                  <command>gdb</command> (or another debugger). See
+                  <xref linkend="using-gdb-on-mysqld"/>.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  Run your test scripts.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  Print the backtrace and the local variables at the
+                  three lowest levels. In <command>gdb</command>, you
+                  can do this with the following commands when
+                  <command>mysqld</command> has crashed inside
+                  <command>gdb</command>:
+                </para>
+
+<programlisting>
+backtrace
+info local
+up
+info local
+up
+info local
+</programlisting>
+
+                <para>
+                  With <command>gdb</command>, you can also examine
+                  which threads exist with <literal>info
+                  threads</literal> and switch to a specific thread with
+                  <literal>thread
+                  <replaceable>N</replaceable></literal>, where
+                  <replaceable>N</replaceable> is the thread ID.
+                </para>
+              </listitem>
+
+            </orderedlist>
+          </listitem>
+
+          <listitem>
+            <para>
+              Try to simulate your application with a Perl script to
+              force MySQL to crash or misbehave.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Send a normal bug report. See
+              <xref linkend="bug-reports"/>. Be even more detailed than
+              usual. Because MySQL works for many people, it may be that
+              the crash results from something that exists only on your
+              computer (for example, an error that is related to your
+              particular system libraries).
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you have a problem with tables containing
+              dynamic-length rows and you are using only
+              <literal>VARCHAR</literal> columns (not
+              <literal>BLOB</literal> or <literal>TEXT</literal>
+              columns), you can try to change all
+              <literal>VARCHAR</literal> to <literal>CHAR</literal> with
+              <literal>ALTER TABLE</literal>. This forces MySQL to use
+              fixed-size rows. Fixed-size rows take a little extra
+              space, but are much more tolerant to corruption.
+            </para>
+
+            <para>
+              The current dynamic row code has been in use at MySQL AB
+              for several years with very few problems, but
+              dynamic-length rows are by nature more prone to errors, so
+              it may be a good idea to try this strategy to see whether
+              it helps.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Do not rule out your server hardware when diagnosing
+              problems. Defective hardware can be the cause of data
+              corruption. Particular attention should be paid to both
+              RAMS and hard-drives when troubleshooting hardware.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="full-disk">
+
+        <title>How MySQL Handles a Full Disk</title>
+
+        <indexterm>
+          <primary>full disk</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>disk full</primary>
+        </indexterm>
+
+        <para>
+          This section describes how MySQL responds to disk-full errors
+          (such as <quote>no space left on device</quote>), and, as of
+          MySQL 4.0.22, to quota-exceeded errors (such as <quote>write
+          failed</quote> or <quote>user block limit reached</quote>).
+        </para>
+
+        <para>
+          This section is relevant for writes to
+          <literal>MyISAM</literal> tables. As of MySQL 4.1.9, it also
+          applies for writes to binary log files and binary log index
+          file, except that references to <quote>row</quote> and
+          <quote>record</quote> should be understood to mean
+          <quote>event.</quote>
+        </para>
+
+        <para>
+          When a disk-full condition occurs, MySQL does the following:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              It checks once every minute to see whether there is enough
+              space to write the current row. If there is enough space,
+              it continues as if nothing had happened.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Every 10 minutes it writes an entry to the log file,
+              warning about the disk-full condition.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          To alleviate the problem, you can take the following actions:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              To continue, you only have to free enough disk space to
+              insert all records.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              To abort the thread, you must use <command>mysqladmin
+              kill</command>. The thread is aborted the next time it
+              checks the disk (in one minute).
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Other threads might be waiting for the table that caused
+              the disk-full condition. If you have several
+              <quote>locked</quote> threads, killing the one thread that
+              is waiting on the disk-full condition allows the other
+              threads to continue.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Exceptions to the preceding behavior are when you use
+          <literal>REPAIR TABLE</literal> or <literal>OPTIMIZE
+          TABLE</literal> or when the indexes are created in a batch
+          after <literal>LOAD DATA INFILE</literal> or after an
+          <literal>ALTER TABLE</literal> statement. All of these
+          statements may create large temporary files that, if left to
+          themselves, would cause big problems for the rest of the
+          system. If the disk becomes full while MySQL is doing any of
+          these operations, it removes the big temporary files and mark
+          the table as crashed. The exception is that for <literal>ALTER
+          TABLE</literal>, the old table is left unchanged.
+        </para>
+
+      </section>
+
+      <section id="temporary-files">
+
+        <title>Where MySQL Stores Temporary Files</title>
+
+        <para>
+          MySQL uses the value of the <literal>TMPDIR</literal>
+          environment variable as the pathname of the directory in which
+          to store temporary files. If you don't have
+          <literal>TMPDIR</literal> set, MySQL uses the system default,
+          which is normally <filename>/tmp</filename>,
+          <filename>/var/tmp</filename>, or
+          <filename>/usr/tmp</filename>. If the filesystem containing
+          your temporary file directory is too small, you can use the
+          <option>--tmpdir</option> option to <command>mysqld</command>
+          to specify a directory in a filesystem where you have enough
+          space.
+        </para>
+
+        <para>
+          Starting from MySQL 4.1, the <option>--tmpdir</option> option
+          can be set to a list of several paths that are used in
+          round-robin fashion. Paths should be separated by colon
+          characters (&lsquo;<literal>:</literal>&rsquo;) on Unix and
+          semicolon characters (&lsquo;<literal>;</literal>&rsquo;) on
+          Windows, NetWare, and OS/2.
+          <emphasis role="bold">Note</emphasis>: To spread the load
+          effectively, these paths should be located on different
+          <emphasis>physical</emphasis> disks, not different partitions
+          of the same disk.
+        </para>
+
+        <para>
+          If the MySQL server is acting as a replication slave, you
+          should not set <option>--tmpdir</option> to point to a
+          directory on a memory-based filesystem or to a directory that
+          is cleared when the server host restarts. A replication slave
+          needs some of its temporary files to survive a machine restart
+          so that it can replicate temporary tables or <literal>LOAD
+          DATA INFILE</literal> operations. If files in the temporary
+          file directory are lost when the server restarts, replication
+          fails.
+        </para>
+
+        <para>
+          MySQL creates all temporary files as hidden files. This
+          ensures that the temporary files are removed if
+          <command>mysqld</command> is terminated. The disadvantage of
+          using hidden files is that you do not see a big temporary file
+          that fills up the filesystem in which the temporary file
+          directory is located.
+        </para>
+
+        <para>
+          When sorting (<literal>ORDER BY</literal> or <literal>GROUP
+          BY</literal>), MySQL normally uses one or two temporary files.
+          The maximum disk space required is determined by the following
+          expression:
+        </para>
+
+<programlisting>
+(length of what is sorted + sizeof(row pointer))
+* number of matched rows
+* 2
+</programlisting>
+
+        <para>
+          The row pointer size is usually four bytes, but may grow in
+          the future for really big tables.
+        </para>
+
+        <para>
+          For some <literal>SELECT</literal> queries, MySQL also creates
+          temporary SQL tables. These are not hidden and have names of
+          the form <filename>SQL_*</filename>.
+        </para>
+
+        <para>
+          <literal>ALTER TABLE</literal> creates a temporary table in
+          the same directory as the original table.
+        </para>
+
+      </section>
+
+      <section id="problems-with-mysql-sock">
+
+        <title>How to Protect or Change the MySQL Unix Socket File</title>
+
+        <indexterm>
+          <primary><literal>mysql.sock</literal></primary>
+          <secondary>protection</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>deletion</primary>
+          <secondary><literal>mysql.sock</literal></secondary>
+        </indexterm>
+
+        <para>
+          The default location for the Unix socket file that the server
+          uses for communication with local clients is
+          <filename>/tmp/mysql.sock</filename>. (For some distribution
+          formats, the directory might be different, such as
+          <filename>/var/lib/mysql</filename> for RPMs.)
+        </para>
+
+        <para>
+          On some versions of Unix, anyone can delete files in the
+          <filename>/tmp</filename> directory or other similar
+          directories used for temporary files. If the socket file is
+          located in such a directory on your system, this might cause
+          problems.
+        </para>
+
+        <para>
+          On most versions of Unix, you can protect your
+          <filename>/tmp</filename> directory so that files can be
+          deleted only by their owners or the superuser
+          (<literal>root</literal>). To do this, set the
+          <literal>sticky</literal> bit on the <filename>/tmp</filename>
+          directory by logging in as <literal>root</literal> and using
+          the following command:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>chmod +t /tmp</userinput>
+</programlisting>
+
+        <para>
+          You can check whether the <literal>sticky</literal> bit is set
+          by executing <literal>ls -ld /tmp</literal>. If the last
+          permission character is <literal>t</literal>, the bit is set.
+        </para>
+
+        <indexterm>
+          <primary>changing socket location</primary>
+        </indexterm>
+
+        <para>
+          Another approach is to change the place where the server
+          creates the Unix socket file. If you do this, you should also
+          let client programs know the new location of the file. You can
+          specify the file location in several ways:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Specify the path in a global or local option file. For
+              example, put the following lines in
+              <literal>/etc/my.cnf</literal>:
+            </para>
+
+<programlisting>
+[mysqld]
+socket=/path/to/socket
+
+[client]
+socket=/path/to/socket
+</programlisting>
+
+            <para>
+              See <xref linkend="option-files"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Specify a <option>--socket</option> option on the command
+              line to <command>mysqld_safe</command> and when you run
+              client programs.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Set the <literal>MYSQL_UNIX_PORT</literal> environment
+              variable to the path of the Unix socket file.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Recompile MySQL from source to use a different default
+              Unix socket file location. Define the path to the file
+              with the <option>--with-unix-socket-path</option> option
+              when you run <command>configure</command>. See
+              <xref linkend="configure-options"/>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          You can test whether the new socket location works by
+          attempting to connect to the server with this command:
+        </para>
+
+<programlisting>
+shell&gt; <userinput>mysqladmin --socket=/path/to/socket version</userinput>
+</programlisting>
+
+      </section>
+
+      <section id="timezone-problems">
+
+        <title>Time Zone Problems</title>
+
+        <indexterm>
+          <primary>time zone problems</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>problems</primary>
+          <secondary>time zone</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>TZ environment variable</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>Environment variable</primary>
+          <secondary>TZ</secondary>
+        </indexterm>
+
+        <para>
+          If you have a problem with <literal>SELECT NOW()</literal>
+          returning values in UTC and not your local time, you have to
+          tell the server your current time zone. The same applies if
+          <literal>UNIX_TIMESTAMP()</literal> returns the wrong value.
+          This should be done for the environment in which the server
+          runs; for example, in <command>mysqld_safe</command> or
+          <command>mysql.server</command>. See
+          <xref linkend="environment-variables"/>.
+        </para>
+
+        <para>
+          You can set the time zone for the server with the
+          <option>--timezone=<replaceable>timezone_name</replaceable></option>
+          option to <command>mysqld_safe</command>. You can also set it
+          by setting the <literal>TZ</literal> environment variable
+          before you start <command>mysqld</command>.
+        </para>
+
+        <para>
+          The allowable values for <option>--timezone</option> or
+          <literal>TZ</literal> are system-dependent. Consult your
+          operating system documentation to see what values are
+          acceptable.
+        </para>
+
+      </section>
+
+    </section>
+
+    <section id="query-issues">
+
+      <title>Query-Related Issues</title>
+
+      <section id="case-sensitivity">
+
+        <title>Case Sensitivity in Searches</title>
+
+        <indexterm>
+          <primary>case sensitivity</primary>
+          <secondary>in searches</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>searching</primary>
+          <secondary>and case sensitivity</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>Chinese</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>Big5 Chinese character encoding</primary>
+        </indexterm>
+
+        <para>
+          By default, MySQL searches are not case sensitive (although
+          there are some character sets that are never case insensitive,
+          such as <literal>czech</literal>). This means that if you
+          search with <literal><replaceable>col_name</replaceable> LIKE
+          'a%'</literal>, you get all column values that start with
+          <literal>A</literal> or <literal>a</literal>. If you want to
+          make this search case sensitive, make sure that one of the
+          operands has a case sensitive or binary collation. For
+          example, if you are comparing a column and a string that both
+          have the <literal>latin1</literal> character set, you can use
+          the <literal>COLLATE</literal> operator to cause either
+          operand to have the <literal>latin1_general_cs</literal> or
+          <literal>latin1_bin</literal> collation. For example:
+        </para>
+
+<programlisting>
+<replaceable>col_name</replaceable> COLLATE latin1_general_cs LIKE 'a%'
+<replaceable>col_name</replaceable> LIKE 'a%' COLLATE latin1_general_cs
+<replaceable>col_name</replaceable> COLLATE latin1_bin LIKE 'a%'
+<replaceable>col_name</replaceable> LIKE 'a%' COLLATE latin1_bin
+</programlisting>
+
+        <para>
+          If you want a column always to be treated in case-sensitive
+          fashion, declare it with a case sensitive or binary collation.
+          See <xref linkend="create-table"/>.
+        </para>
+
+        <para>
+          Before MySQL 4.1, <literal>COLLATE</literal> is unavailable.
+          Use the <literal>BINARY</literal> operator in expressions to
+          treat a string as a binary string: <literal>BINARY
+          <replaceable>col_name</replaceable> LIKE 'a%'</literal> or
+          <literal><replaceable>col_name</replaceable> LIKE BINARY
+          'a%'</literal>. In column declarations, use the
+          <literal>BINARY</literal> attribute.
+        </para>
+
+        <para>
+          Simple comparison operations (<literal>&gt;=, &gt;, =, &lt;,
+          &lt;=</literal>, sorting, and grouping) are based on each
+          character's <quote>sort value.</quote> Characters with the
+          same sort value (such as &lsquo;<literal>E</literal>&rsquo;,
+          &lsquo;<literal>e</literal>&rsquo;, and
+          &lsquo;<literal>é</literal>&rsquo;) are treated as the
+          same character.
+        </para>
+
+      </section>
+
+      <section id="using-date">
+
+        <title>Problems Using <literal>DATE</literal> Columns</title>
+
+        <indexterm>
+          <primary>DATE</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>DATE columns</primary>
+          <secondary>problems</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>problems</primary>
+          <secondary><literal>DATE</literal> columns</secondary>
+        </indexterm>
+
+        <para>
+          The format of a <literal>DATE</literal> value is
+          <literal>'YYYY-MM-DD'</literal>. According to standard SQL, no
+          other format is allowed. You should use this format in
+          <literal>UPDATE</literal> expressions and in the
+          <literal>WHERE</literal> clause of <literal>SELECT</literal>
+          statements. For example:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM <replaceable>tbl_name</replaceable> WHERE date &gt;= '2003-05-05';</userinput>
+</programlisting>
+
+        <para>
+          As a convenience, MySQL automatically converts a date to a
+          number if the date is used in a numeric context (and vice
+          versa). It is also smart enough to allow a
+          <quote>relaxed</quote> string form when updating and in a
+          <literal>WHERE</literal> clause that compares a date to a
+          <literal>TIMESTAMP</literal>, <literal>DATE</literal>, or
+          <literal>DATETIME</literal> column. (<quote>Relaxed
+          form</quote> means that any punctuation character may be used
+          as the separator between parts. For example,
+          <literal>'2004-08-15'</literal> and
+          <literal>'2004#08#15'</literal> are equivalent.) MySQL can
+          also convert a string containing no separators (such as
+          <literal>'20040815'</literal>), provided it makes sense as a
+          date.
+        </para>
+
+        <para>
+          When you compare a <literal>DATE</literal>,
+          <literal>TIME</literal>, <literal>DATETIME</literal>, or
+          <literal>TIMESTAMP</literal> to a constant string with the
+          <literal>&lt;</literal>, <literal>&lt;=</literal>,
+          <literal>=</literal>, <literal>&gt;=</literal>,
+          <literal>&gt;</literal>, or <literal>BETWEEN</literal>
+          operators, MySQL normally converts the string to an internal
+          long integer for faster comparison (and also for a bit more
+          <quote>relaxed</quote> string checking). However, this
+          conversion is subject to the following exceptions:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              When you compare two columns
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              When you compare a <literal>DATE</literal>,
+              <literal>TIME</literal>, <literal>DATETIME</literal>, or
+              <literal>TIMESTAMP</literal> column to an expression
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              When you use any other comparison method than those just
+              listed, such as <literal>IN</literal> or
+              <literal>STRCMP()</literal>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          For these exceptional cases, the comparison is done by
+          converting the objects to strings and performing a string
+          comparison.
+        </para>
+
+        <para>
+          To keep things safe, assume that strings are compared as
+          strings and use the appropriate string functions if you want
+          to compare a temporal value to a string.
+        </para>
+
+        <para>
+          The special date <literal>'0000-00-00'</literal> can be stored
+          and retrieved as <literal>'0000-00-00'.</literal> When using a
+          <literal>'0000-00-00'</literal> date through MyODBC, it is
+          automatically converted to <literal>NULL</literal> in MyODBC
+          2.50.12 and above, because ODBC can't handle this kind of
+          date.
+        </para>
+
+        <para>
+          Because MySQL performs the conversions described above, the
+          following statements work:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES (19970505);</userinput>
+mysql&gt; <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('19970505');</userinput>
+mysql&gt; <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('97-05-05');</userinput>
+mysql&gt; <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('1997.05.05');</userinput>
+mysql&gt; <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('1997 05 05');</userinput>
+mysql&gt; <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('0000-00-00');</userinput>
+
+mysql&gt; <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE idate &gt;= '1997-05-05';</userinput>
+mysql&gt; <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE idate &gt;= 19970505;</userinput>
+mysql&gt; <userinput>SELECT MOD(idate,100) FROM <replaceable>tbl_name</replaceable> WHERE idate &gt;= 19970505;</userinput>
+mysql&gt; <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE idate &gt;= '19970505';</userinput>
+</programlisting>
+
+        <para>
+          However, the following does not work:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE STRCMP(idate,'20030505')=0;</userinput>
+</programlisting>
+
+        <para>
+          <literal>STRCMP()</literal> is a string function, so it
+          converts <literal>idate</literal> to a string in
+          <literal>'YYYY-MM-DD'</literal> format and performs a string
+          comparison. It does not convert <literal>'20030505'</literal>
+          to the date <literal>'2003-05-05'</literal> and perform a date
+          comparison.
+        </para>
+
+        <para>
+          If the date cannot be converted to any reasonable value, a
+          <literal>0</literal> is stored in the <literal>DATE</literal>
+          column, which is retrieved as <literal>'0000-00-00'</literal>.
+          This is both a speed and a convenience issue. We believe that
+          the database server's responsibility is to retrieve the same
+          date you stored (even if the data was not logically correct in
+          all cases). We think it is up to the application and not the
+          server to check the dates.
+        </para>
+
+      </section>
+
+      <section id="problems-with-null">
+
+        <title>Problems with <literal>NULL</literal> Values</title>
+
+        <indexterm>
+          <primary><literal>NULL</literal> values</primary>
+          <secondary>vs. empty values</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>NULL</primary>
+        </indexterm>
+
+        <para>
+          The concept of the <literal>NULL</literal> value is a common
+          source of confusion for newcomers to SQL, who often think that
+          <literal>NULL</literal> is the same thing as an empty string
+          <literal>''</literal>. This is not the case. For example, the
+          following statements are completely different:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO my_table (phone) VALUES (NULL);</userinput>
+mysql&gt; <userinput>INSERT INTO my_table (phone) VALUES ('');</userinput>
+</programlisting>
+
+        <para>
+          Both statements insert a value into the
+          <literal>phone</literal> column, but the first inserts a
+          <literal>NULL</literal> value and the second inserts an empty
+          string. The meaning of the first can be regarded as
+          <quote>phone number is not known</quote> and the meaning of
+          the second can be regarded as <quote>the person is known to
+          have no phone, and thus no phone number.</quote>
+        </para>
+
+        <para>
+          To help with <literal>NULL</literal> handling, you can use the
+          <literal>IS NULL</literal> and <literal>IS NOT NULL</literal>
+          operators and the <literal>IFNULL()</literal> function.
+        </para>
+
+        <para>
+          In SQL, the <literal>NULL</literal> value is never true in
+          comparison to any other value, even <literal>NULL</literal>.
+          An expression that contains <literal>NULL</literal> always
+          produces a <literal>NULL</literal> value unless otherwise
+          indicated in the documentation for the operators and functions
+          involved in the expression. All columns in the following
+          example return <literal>NULL</literal>:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);</userinput>
+</programlisting>
+
+        <para>
+          If you want to search for column values that are
+          <literal>NULL</literal>, you cannot use an <literal>expr =
+          NULL</literal> test. The following statement returns no rows,
+          because <literal>expr = NULL</literal> is never true for any
+          expression:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM my_table WHERE phone = NULL;</userinput>
+</programlisting>
+
+        <para>
+          To look for <literal>NULL</literal> values, you must use the
+          <literal>IS NULL</literal> test. The following statements show
+          how to find the <literal>NULL</literal> phone number and the
+          empty phone number:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM my_table WHERE phone IS NULL;</userinput>
+mysql&gt; <userinput>SELECT * FROM my_table WHERE phone = '';</userinput>
+</programlisting>
+
+        <para>
+          See <xref linkend="working-with-null"/>, for additional
+          information and examples.
+        </para>
+
+        <para>
+          You can add an index on a column that can have
+          <literal>NULL</literal> values if you are using MySQL 3.23.2
+          or newer and are using the <literal>MyISAM</literal>,
+          <literal>InnoDB</literal>, or <literal>BDB</literal> storage
+          engine. As of MySQL 4.0.2, the <literal>MEMORY</literal>
+          storage engine also supports <literal>NULL</literal> values in
+          indexes. Otherwise, you must declare an indexed column
+          <literal>NOT NULL</literal> and you cannot insert
+          <literal>NULL</literal> into the column.
+        </para>
+
+        <indexterm>
+          <primary>LOAD DATA INFILE</primary>
+        </indexterm>
+
+        <para>
+          When reading data with <literal>LOAD DATA INFILE</literal>,
+          empty or missing columns are updated with
+          <literal>''</literal>. If you want a <literal>NULL</literal>
+          value in a column, you should use <literal>\N</literal> in the
+          data file. The literal word
+          <quote><literal>NULL</literal></quote> may also be used under
+          some circumstances. See <xref linkend="load-data"/>.
+        </para>
+
+        <para>
+          When using <literal>DISTINCT</literal>, <literal>GROUP
+          BY</literal>, or <literal>ORDER BY</literal>, all
+          <literal>NULL</literal> values are regarded as equal.
+        </para>
+
+        <para>
+          When using <literal>ORDER BY</literal>,
+          <literal>NULL</literal> values are presented first, or last if
+          you specify <literal>DESC</literal> to sort in descending
+          order. Exception: In MySQL 4.0.2 through 4.0.10,
+          <literal>NULL</literal> values sort first regardless of sort
+          order.
+        </para>
+
+        <para>
+          Aggregate (summary) functions such as
+          <literal>COUNT()</literal>, <literal>MIN()</literal>, and
+          <literal>SUM()</literal> ignore <literal>NULL</literal>
+          values. The exception to this is <literal>COUNT(*)</literal>,
+          which counts rows and not individual column values. For
+          example, the following statement produces two counts. The
+          first is a count of the number of rows in the table, and the
+          second is a count of the number of non-<literal>NULL</literal>
+          values in the <literal>age</literal> column:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT COUNT(*), COUNT(age) FROM person;</userinput>
+</programlisting>
+
+        <indexterm>
+          <primary><literal>TIMESTAMP</literal></primary>
+          <secondary>and <literal>NULL</literal> values</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary><literal>AUTO_INCREMENT</literal></primary>
+          <secondary>and <literal>NULL</literal> values</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary><literal>NULL</literal> values</primary>
+          <secondary>and <literal>TIMESTAMP</literal> columns</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary><literal>NULL</literal> values</primary>
+          <secondary>and <literal>AUTO_INCREMENT</literal> columns</secondary>
+        </indexterm>
+
+        <para>
+          For some data types, MySQL handles <literal>NULL</literal>
+          values specially. If you insert <literal>NULL</literal> into a
+          <literal>TIMESTAMP</literal> column, the current date and time
+          is inserted. If you insert <literal>NULL</literal> into an
+          integer column that has the <literal>AUTO_INCREMENT</literal>
+          attribute, the next number in the sequence is inserted.
+        </para>
+
+      </section>
+
+      <section id="problems-with-alias">
+
+        <title>Problems with Column Aliases</title>
+
+        <indexterm>
+          <primary>alias</primary>
+        </indexterm>
+
+        <para>
+          You can use an alias to refer to a column in <literal>GROUP
+          BY</literal>, <literal>ORDER BY</literal>, or
+          <literal>HAVING</literal> clauses. Aliases can also be used to
+          give columns better names:
+        </para>
+
+<programlisting>
+SELECT SQRT(a*b) AS root FROM <replaceable>tbl_name</replaceable> GROUP BY root HAVING root &gt; 0;
+SELECT id, COUNT(*) AS cnt FROM <replaceable>tbl_name</replaceable> GROUP BY id HAVING cnt &gt; 0;
+SELECT id AS 'Customer identity' FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+
+        <para>
+          Standard SQL doesn't allow you to refer to a column alias in a
+          <literal>WHERE</literal> clause. This restriction is imposed
+          because when the <literal>WHERE</literal> code is executed,
+          the column value may not yet be determined. For example, the
+          following query is illegal:
+        </para>
+
+<programlisting>
+SELECT id, COUNT(*) AS cnt FROM <replaceable>tbl_name</replaceable> WHERE cnt &gt; 0 GROUP BY id;
+</programlisting>
+
+        <para>
+          The <literal>WHERE</literal> statement is executed to
+          determine which rows should be included in the <literal>GROUP
+          BY</literal> part, whereas <literal>HAVING</literal> is used
+          to decide which rows from the result set should be used.
+        </para>
+
+      </section>
+
+      <section id="non-transactional-tables">
+
+        <title>Rollback Failure for Non-Transactional Tables</title>
+
+        <indexterm>
+          <primary>Non-transactional tables</primary>
+        </indexterm>
+
+        <para>
+          If you receive the following message when trying to perform a
+          <literal>ROLLBACK</literal>, it means that one or more of the
+          tables you used in the transaction do not support
+          transactions:
+        </para>
+
+<programlisting>
+Warning: Some non-transactional changed tables couldn't be rolled back
+</programlisting>
+
+        <para>
+          These non-transactional tables are not affected by the
+          <literal>ROLLBACK</literal> statement.
+        </para>
+
+        <para>
+          If you were not deliberately mixing transactional and
+          non-transactional tables within the transaction, the most
+          likely cause for this message is that a table you thought was
+          transactional actually is not. This can happen if you try to
+          create a table using a transactional storage engine that is
+          not supported by your <command>mysqld</command> server (or
+          that was disabled with a startup option). If
+          <command>mysqld</command> doesn't support a storage engine, it
+          instead creates the table as a <literal>MyISAM</literal>
+          table, which is non-transactional.
+        </para>
+
+        <para>
+          You can check the storage engine for a table by using either
+          of these statements:
+        </para>
+
+<programlisting>
+SHOW TABLE STATUS LIKE '<replaceable>tbl_name</replaceable>';
+SHOW CREATE TABLE <replaceable>tbl_name</replaceable>;
+</programlisting>
+
+        <para>
+          See <xref linkend="show-table-status"/>, and
+          <xref linkend="show-create-table"/>.
+        </para>
+
+        <para>
+          You can check which storage engines your
+          <command>mysqld</command> server supports by using this
+          statement:
+        </para>
+
+<programlisting>
+SHOW ENGINES;
+</programlisting>
+
+        <para>
+          Before MySQL 4.1.2, <literal>SHOW ENGINES</literal> is
+          unavailable. Use the following statement instead and check the
+          value of the variable that is associated with the storage
+          engine in which you are interested:
+        </para>
+
+<programlisting>
+SHOW VARIABLES LIKE 'have_%';
+</programlisting>
+
+        <para>
+          For example, to determine whether the
+          <literal>InnoDB</literal> storage engine is available, check
+          the value of the <literal>have_innodb</literal> variable.
+        </para>
+
+        <para>
+          See <xref linkend="show-engines"/>, and
+          <xref linkend="show-variables"/>.
+        </para>
+
+      </section>
+
+      <section id="deleting-from-related-tables">
+
+        <title>Deleting Rows from Related Tables</title>
+
+        <indexterm>
+          <primary>deleting</primary>
+          <secondary>rows</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>rows</primary>
+          <secondary>deleting</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>tables</primary>
+          <secondary>deleting rows</secondary>
+        </indexterm>
+
+        <para>
+          MySQL does not support subqueries prior to version 4.1, or the
+          use of more than one table in the <literal>DELETE</literal>
+          statement prior to version 4.0. If your version of MySQL does
+          not support subqueries or multiple-table
+          <literal>DELETE</literal> statements, you can use the
+          following approach to delete rows from two related tables:
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              <literal>SELECT</literal> the rows based on some
+              <literal>WHERE</literal> condition in the main table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>DELETE</literal> the rows in the main table based
+              on the same condition.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>DELETE FROM related_table WHERE related_column IN
+              (selected_rows)</literal>.
+            </para>
+          </listitem>
+
+        </orderedlist>
+
+        <para>
+          If the total length of the <literal>DELETE</literal> statement
+          for <literal>related_table</literal> is more than 1MB (the
+          default value of the <literal>max_allowed_packet</literal>
+          system variable), you should split it into smaller parts and
+          execute multiple <literal>DELETE</literal> statements. You
+          probably get the fastest <literal>DELETE</literal> by
+          specifying only 100 to 1,000 <literal>related_column</literal>
+          values per statement if the <literal>related_column</literal>
+          is indexed. If the <literal>related_column</literal> isn't
+          indexed, the speed is independent of the number of arguments
+          in the <literal>IN</literal> clause.
+        </para>
+
+      </section>
+
+      <section id="no-matching-rows">
+
+        <title>Solving Problems with No Matching Rows</title>
+
+        <indexterm>
+          <primary>no matching rows</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>rows</primary>
+          <secondary>matching problems</secondary>
+        </indexterm>
+
+        <para>
+          If you have a complicated query that uses many tables but that
+          doesn't return any rows, you should use the following
+          procedure to find out what is wrong:
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              Test the query with <literal>EXPLAIN</literal> to check
+              whether you can find something that is obviously wrong.
+              See <xref linkend="explain"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Select only those columns that are used in the
+              <literal>WHERE</literal> clause.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Remove one table at a time from the query until it returns
+              some rows. If the tables are large, it's a good idea to
+              use <literal>LIMIT 10</literal> with the query.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Issue a <literal>SELECT</literal> for the column that
+              should have matched a row against the table that was last
+              removed from the query.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you are comparing <literal>FLOAT</literal> or
+              <literal>DOUBLE</literal> columns with numbers that have
+              decimals, you can't use equality (<literal>=</literal>)
+              comparisons. This problem is common in most computer
+              languages because not all floating-point values can be
+              stored with exact precision. In some cases, changing the
+              <literal>FLOAT</literal> to a <literal>DOUBLE</literal>
+              fixes this. See <xref linkend="problems-with-float"/>.
+            </para>
+
+            <para>
+              Similar problems may be encountered when comparing
+              <literal>DECIMAL</literal> values.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you still can't figure out what's wrong, create a
+              minimal test that can be run with <literal>mysql test &lt;
+              query.sql</literal> that shows your problems. You can
+              create a test file by dumping the tables with
+              <command>mysqldump --quick db_name
+              <replaceable>tbl_name_1</replaceable> ...
+              <replaceable>tbl_name_n</replaceable> &gt;
+              query.sql</command>. Open the file in an editor, remove
+              some insert lines (if there are more than needed to
+              demonstrate the problem), and add your
+              <literal>SELECT</literal> statement at the end of the
+              file.
+            </para>
+
+            <para>
+              Verify that the test file demonstrates the problem by
+              executing these commands:
+            </para>
+
+<programlisting>
+shell&gt; <userinput>mysqladmin create test2</userinput>
+shell&gt; <userinput>mysql test2 &lt; query.sql</userinput>
+</programlisting>
+
+            <para>
+              Attach the test file to a bug report, which you can file
+              using the instructions in <xref linkend="bug-reports"/>.
+            </para>
+          </listitem>
+
+        </orderedlist>
+
+      </section>
+
+      <section id="problems-with-float">
+
+        <title>Problems with Floating-Point Comparisons</title>
+
+        <para>
+          Floating-point numbers sometimes cause confusion because they
+          are approximate. That is, they are not stored as exact values
+          inside computer architecture. What you can see on the screen
+          usually is not the exact value of the number. The
+          <literal>FLOAT</literal> and <literal>DOUBLE</literal> data
+          types are such, and <literal>DECIMAL</literal> operations
+          before MySQL 5.0.3 are approximate as well.
+        </para>
+
+        <para>
+          Prior to MySQL 5.0.3, <literal>DECIMAL</literal> columns store
+          values with exact precision because they are represented as
+          strings, but calculations on <literal>DECIMAL</literal> values
+          are done using floating-point operations. As of 5.0.3, MySQL
+          performs <literal>DECIMAL</literal> operations with a
+          precision of 64 decimal digits, which should solve most common
+          inaccuracy problems when it comes to
+          <literal>DECIMAL</literal> columns. (If your server is from
+          MySQL 5.0.3 or higher, but you have <literal>DECIMAL</literal>
+          columns in tables that were created before 5.0.3, the old
+          behavior still applies to those columns. To convert the tables
+          to the newer <literal>DECIMAL</literal> format, dump them with
+          <command>mysqldump</command> and reload them.)
+        </para>
+
+        <para>
+          The following example (for versions of MySQL older than 5.0.3)
+          demonstrates the problem. It shows that even for older
+          <literal>DECIMAL</literal> columns, calculations that are done
+          using floating-point operations are subject to floating-point
+          error. (Were you to replace the <literal>DECIMAL</literal>
+          columns with <literal>FLOAT</literal>, similar problems would
+          occur for all versions of MySQL.)
+        </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+-->
+mysql&gt; <userinput>CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));</userinput>
+mysql&gt; <userinput>INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),</userinput>
+    -&gt; <userinput>(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),</userinput>
+    -&gt; <userinput>(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),</userinput>
+    -&gt; <userinput>(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),</userinput>
+    -&gt; <userinput>(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),</userinput>
+    -&gt; <userinput>(6, 0.00, 0.00), (6, -51.40, 0.00);</userinput>
+
+mysql&gt; <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b</userinput>
+    -&gt; <userinput>FROM t1 GROUP BY i HAVING a &lt;&gt; b;</userinput>
++------+--------+-------+
+| i    | a      | b     |
++------+--------+-------+
+|    1 |  21.40 | 21.40 |
+|    2 |  76.80 | 76.80 |
+|    3 |   7.40 |  7.40 |
+|    4 |  15.40 | 15.40 |
+|    5 |   7.20 |  7.20 |
+|    6 | -51.40 |  0.00 |
++------+--------+-------+
+</programlisting>
+
+        <para>
+          The result is correct. Although the first five records look
+          like they should not satisfy the comparison (the values of
+          <literal>a</literal> and <literal>b</literal> do not appear to
+          be different), they may do so because the difference between
+          the numbers shows up around the tenth decimal or so, depending
+          on factors such as computer architecture or the compiler
+          version or optimization level. For example, different CPUs may
+          evaluate floating-point numbers differently.
+        </para>
+
+        <para>
+          As of MySQL 5.0.3, you will get only the last row in the above
+          result.
+        </para>
+
+        <para>
+          The problem cannot be solved by using
+          <literal>ROUND()</literal> or similar functions, because the
+          result is still a floating-point number:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b</userinput>
+    -&gt; <userinput>FROM t1 GROUP BY i HAVING a &lt;&gt; b;</userinput>
++------+--------+-------+
+| i    | a      | b     |
++------+--------+-------+
+|    1 |  21.40 | 21.40 |
+|    2 |  76.80 | 76.80 |
+|    3 |   7.40 |  7.40 |
+|    4 |  15.40 | 15.40 |
+|    5 |   7.20 |  7.20 |
+|    6 | -51.40 |  0.00 |
++------+--------+-------+
+</programlisting>
+
+        <para>
+          This is what the numbers in column <literal>a</literal> look
+          like when displayed with more decimal places:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,</userinput>
+    -&gt; <userinput>ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a &lt;&gt; b;</userinput>
++------+----------------------+-------+
+| i    | a                    | b     |
++------+----------------------+-------+
+|    1 |  21.3999999999999986 | 21.40 |
+|    2 |  76.7999999999999972 | 76.80 |
+|    3 |   7.4000000000000004 |  7.40 |
+|    4 |  15.4000000000000004 | 15.40 |
+|    5 |   7.2000000000000002 |  7.20 |
+|    6 | -51.3999999999999986 |  0.00 |
++------+----------------------+-------+
+</programlisting>
+
+        <para>
+          Depending on your computer architecture, you may or may not
+          see similar results. For example, on some machines you may get
+          the <quote>correct</quote> results by multiplying both
+          arguments by 1, as the following example shows.
+        </para>
+
+        <para>
+          <emphasis role="bold">Warning:</emphasis> Never use this
+          method in your applications. It is not an example of a
+          trustworthy method!
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b</userinput>
+    -&gt; <userinput>FROM t1 GROUP BY i HAVING a &lt;&gt; b;</userinput>
++------+--------+------+
+| i    | a      | b    |
++------+--------+------+
+|    6 | -51.40 | 0.00 |
++------+--------+------+
+</programlisting>
+
+        <para>
+          The reason that the preceding example seems to work is that on
+          the particular machine where the test was done, CPU
+          floating-point arithmetic happens to round the numbers to the
+          same value. However, there is no rule that any CPU should do
+          so, so this method cannot be trusted.
+        </para>
+
+        <para>
+          The correct way to do floating-point number comparison is to
+          first decide on an acceptable tolerance for differences
+          between the numbers and then do the comparison against the
+          tolerance value. For example, if we agree that floating-point
+          numbers should be regarded the same if they are same within a
+          precision of one in ten thousand (0.0001), the comparison
+          should be written to find differences larger than the
+          tolerance value:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1</userinput>
+    -&gt; <userinput>GROUP BY i HAVING ABS(a - b) &gt; 0.0001;</userinput>
++------+--------+------+
+| i    | a      | b    |
++------+--------+------+
+|    6 | -51.40 | 0.00 |
++------+--------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+        <para>
+          Conversely, to get rows where the numbers are the same, the
+          test should find differences within the tolerance value:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1</userinput>
+    -&gt; <userinput>GROUP BY i HAVING ABS(a - b) &lt;= 0.0001;</userinput>
++------+-------+-------+
+| i    | a     | b     |
++------+-------+-------+
+|    1 | 21.40 | 21.40 |
+|    2 | 76.80 | 76.80 |
+|    3 |  7.40 |  7.40 |
+|    4 | 15.40 | 15.40 |
+|    5 |  7.20 |  7.20 |
++------+-------+-------+
+</programlisting>
+
+      </section>
+
+    </section>
+
+    <section id="optimizer-issues">
+
+      <title>Optimizer-Related Issues</title>
+
+      <para>
+        MySQL uses a cost-based optimizer to determine the best way to
+        resolve a query. In many cases, MySQL can calculate the best
+        possible query plan, but sometimes MySQL doesn't have enough
+        information about the data at hand and has to make
+        <quote>educated</quote> guesses about the data.
+      </para>
+
+      <para>
+        For the cases when MySQL does not do the "right" thing, tools
+        that you have available to help MySQL are:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            Use the <literal>EXPLAIN</literal> statement to get
+            information about how MySQL processes a query. To use it,
+            just add the keyword <literal>EXPLAIN</literal> to the front
+            of your <literal>SELECT</literal> statement:
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i;</userinput>
+</programlisting>
+
+          <para>
+            <literal>EXPLAIN</literal> is discussed in more detail in
+            <xref linkend="explain"/>.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Use <literal>ANALYZE TABLE
+            <replaceable>tbl_name</replaceable></literal> to update the
+            key distributions for the scanned table. See
+            <xref linkend="analyze-table"/>.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            <indexterm>
+              <primary>FORCE INDEX</primary>
+            </indexterm>
+
+            Use <literal>FORCE INDEX</literal> for the scanned table to
+            tell MySQL that table scans are very expensive compared to
+            using the given index. See <xref linkend="select"/>.
+          </para>
+
+<programlisting>
+SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
+WHERE t1.col_name=t2.col_name;
+</programlisting>
+
+          <para>
+            <literal>USE INDEX</literal> and <literal>IGNORE
+            INDEX</literal> may also be useful.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Global and table-level <literal>STRAIGHT_JOIN</literal>. See
+            <xref linkend="select"/>.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            You can tune global or thread-specific system variables. For
+            example, Start <command>mysqld</command> with the
+            <option>--max-seeks-for-key=1000</option> option or use
+            <literal>SET max_seeks_for_key=1000</literal> to tell the
+            optimizer to assume that no key scan causes more than 1,000
+            key seeks. See <xref linkend="server-system-variables"/>.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
+    </section>
+
+    <section id="table-definition-issues">
+
+      <title>Table Definition-Related Issues</title>
+
+      <section id="alter-table-problems">
+
+        <title>Problems with <literal>ALTER TABLE</literal></title>
+
+        <indexterm>
+          <primary>ALTER TABLE</primary>
+        </indexterm>
+
+        <indexterm>
+          <primary>changing</primary>
+          <secondary>table</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>table</primary>
+          <secondary>changing</secondary>
+        </indexterm>
+
+        <para>
+          <literal>ALTER TABLE</literal> changes a table to the current
+          character set. If you get a duplicate-key error during
+          <literal>ALTER TABLE</literal>, the cause is either that the
+          new character sets maps two keys to the same value or that the
+          table is corrupted. In the latter case, you should run
+          <literal>REPAIR TABLE</literal> on the table.
+        </para>
+
+        <para>
+          If <literal>ALTER TABLE</literal> dies with the following
+          error, the problem may be that MySQL crashed during an earlier
+          <literal>ALTER TABLE</literal> operation and there is an old
+          table named
+          <filename>A-<replaceable>xxx</replaceable></filename> or
+          <filename>B-<replaceable>xxx</replaceable></filename> lying
+          around:
+        </para>
+
+<programlisting>
+Error on rename of './database/name.frm'
+to './database/B-<replaceable>xxx</replaceable>.frm' (Errcode: 17)
+</programlisting>
+
+        <para>
+          In this case, go to the MySQL data directory and delete all
+          files that have names starting with <literal>A-</literal> or
+          <literal>B-</literal>. (You may want to move them elsewhere
+          instead of deleting them.)
+        </para>
+
+        <para>
+          <literal>ALTER TABLE</literal> works in the following way:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Create a new table named
+              <filename>A-<replaceable>xxx</replaceable></filename> with
+              the requested structural changes.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Copy all rows from the original table to
+              <filename>A-<replaceable>xxx</replaceable></filename>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Rename the original table to
+              <filename>B-<replaceable>xxx</replaceable></filename>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Rename
+              <filename>A-<replaceable>xxx</replaceable></filename> to
+              your original table name.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Delete
+              <filename>B-<replaceable>xxx</replaceable></filename>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          If something goes wrong with the renaming operation, MySQL
+          tries to undo the changes. If something goes seriously wrong
+          (although this shouldn't happen), MySQL may leave the old
+          table as
+          <filename>B-<replaceable>xxx</replaceable></filename>. A
+          simple rename of the table files at the system level should
+          get your data back.
+        </para>
+
+        <para>
+          If you use <literal>ALTER TABLE</literal> on a transactional
+          table or if you are using Windows or OS/2, <literal>ALTER
+          TABLE</literal> unlocks the table if you had done a
+          <literal>LOCK TABLE</literal> on it. This is done because
+          <literal>InnoDB</literal> and these operating systems cannot
+          drop a table that is in use.
+        </para>
+
+      </section>
+
+      <section id="change-column-order">
+
+        <title>How to Change the Order of Columns in a Table</title>
+
+        <indexterm>
+          <primary>reordering</primary>
+          <secondary>columns</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>columns</primary>
+          <secondary>changing</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>changing</primary>
+          <secondary>column order</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>tables</primary>
+          <secondary>changing column order</secondary>
+        </indexterm>
+
+        <para>
+          First, consider whether you really need to change the column
+          order in a table. The whole point of SQL is to abstract the
+          application from the data storage format. You should always
+          specify the order in which you wish to retrieve your data. The
+          first of the following statements returns columns in the order
+          <replaceable>col_name1</replaceable>,
+          <replaceable>col_name2</replaceable>,
+          <replaceable>col_name3</replaceable>, whereas the second
+          returns them in the order
+          <replaceable>col_name1</replaceable>,
+          <replaceable>col_name3</replaceable>,
+          <replaceable>col_name2</replaceable>:
+        </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT <replaceable>col_name1</replaceable>, <replaceable>col_name2</replaceable>, <replaceable>col_name3</replaceable> FROM <replaceable>tbl_name</replaceable>;</userinput>
+mysql&gt; <userinput>SELECT <replaceable>col_name1</replaceable>, <replaceable>col_name3</replaceable>, <replaceable>col_name2</replaceable> FROM <replaceable>tbl_name</replaceable>;</userinput>
+</programlisting>
+
+        <para>
+          If you decide to change the order of table columns anyway, you
+          can do so as follows:
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              Create a new table with the columns in the new order.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Execute this statement:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>INSERT INTO new_table</userinput>
+    -&gt; <userinput>SELECT columns-in-new-order FROM old_table;</userinput>
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              Drop or rename <literal>old_table</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Rename the new table to the original name:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>ALTER TABLE new_table RENAME old_table;</userinput>
+</programlisting>
+          </listitem>
+
+        </orderedlist>
+
+        <para>
+          <literal>SELECT *</literal> is quite suitable for testing
+          queries. However, in an application, you should
+          <emphasis>never</emphasis> rely on using <literal>SELECT
+          *</literal> and retrieving the columns based on their
+          position. The order and position in which columns are returned
+          does not remain the same if you add, move, or delete columns.
+          A simple change to your table structure could cause your
+          application to fail.
+        </para>
+
+      </section>
+
+      <section id="temporary-table-problems">
+
+        <title><literal>TEMPORARY TABLE</literal> Problems</title>
+
+        <indexterm>
+          <primary>temporary tables</primary>
+          <secondary>problems</secondary>
+        </indexterm>
+
+        <para>
+          The following list indicates limitations on the use of
+          <literal>TEMPORARY</literal> tables:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              A <literal>TEMPORARY</literal> table can only be of type
+              <literal>HEAP</literal>, <literal>ISAM</literal>,
+              <literal>MyISAM</literal>, <literal>MERGE</literal>, or
+              <literal>InnoDB</literal>.
+            </para>
+
+            <para>
+              Temporary tables are not supported for MySQL Cluster.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You cannot refer to a <literal>TEMPORARY</literal> table
+              more than once in the same query. For example, the
+              following does not work:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
+ERROR 1137: Can't reopen table: 'temp_table'
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              The <literal>SHOW TABLES</literal> statement does not list
+              <literal>TEMPORARY</literal> tables.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You cannot use <literal>RENAME</literal> to rename a
+              <literal>TEMPORARY</literal> table. However, you can use
+              <literal>ALTER TABLE</literal> instead:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>ALTER TABLE orig_name RENAME new_name;</userinput>
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              There are known issues in using temporary tables with
+              replication. See <xref linkend="replication-features"/>,
+              for more information.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+    </section>
+
+    <section id="bugs">
+
+      <title>Known Issues in MySQL</title>
+
+      <indexterm>
+        <primary>bugs</primary>
+        <secondary>known</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>errors</primary>
+        <secondary>known</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>design</primary>
+        <secondary>issues</secondary>
+      </indexterm>
+
+      <indexterm>
+        <primary>known errors</primary>
+      </indexterm>
+
+      <para>
+        This section is a list of the known issues in recent versions of
+        MySQL.
+      </para>
+
+      <para>
+        For information about platform-specific issues, see the
+        installation and porting instructions in
+        <xref linkend="operating-system-specific-notes"/>, and
+        <xref linkend="porting"/>.
+      </para>
+
+      <section id="errors-in-3-23">
+
+        <title>Issues in MySQL 3.23 Fixed in a Later MySQL Version</title>
+
+        <para>
+          The following known issues have not been fixed in MySQL 3.23
+          for various reasons, and are not classified as critical.
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.0: Avoid using spaces at the end of
+              column names because this can cause unexpected behavior.
+              (Bug #4196)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.0.12: You can get a deadlock (hung
+              thread) if you use <literal>LOCK TABLE</literal> to lock
+              multiple tables and then in the same connection use
+              <literal>DROP TABLE</literal> to drop one of them while
+              another thread is trying to lock it. (To break the
+              deadlock, you can use <literal>KILL</literal> to terminate
+              any of the threads involved.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.0.11: <literal>SELECT MAX(key_column)
+              FROM t1,t2,t3...</literal> where one of the tables are
+              empty doesn't return <literal>NULL</literal> but instead
+              returns the maximum value for the column.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>DELETE FROM heap_table</literal> without a
+              <literal>WHERE</literal> clause doesn't work on a locked
+              <literal>HEAP</literal> table.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="errors-in-4-0">
+
+        <title>Issues in MySQL 4.0 Fixed in a Later Version</title>
+
+        <para>
+          The following known issues have not been fixed in MySQL 4.0
+          for various reasons, and are not classified as critical.
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.1.10: Using <literal>HAVING</literal>,
+              you can get a crash or wrong result if you use an alias to
+              a <literal>RAND()</literal> function. This will not be
+              fixed in 4.0 because the fix may break compatability with
+              some applications.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.1.1: In a <literal>UNION</literal>, the
+              first <literal>SELECT</literal> determines the type,
+              <literal>max_length</literal>, and <literal>NULL</literal>
+              properties for the resulting columns.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.1: In <literal>DELETE</literal> with many
+              tables, you can't refer to tables to be deleted through an
+              alias.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.1.2: You cannot mix <literal>UNION
+              ALL</literal> and <literal>UNION DISTINCT</literal> in the
+              same query. If you use <literal>ALL</literal> for one
+              <literal>UNION</literal>, it is used for all of them.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>FLUSH TABLES WITH READ LOCK</literal> does not
+              block <literal>CREATE TABLE</literal>, which may cause a
+              problem with the binary log position when doing a full
+              backup of tables and the binary log.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Fixed in MySQL 4.1.8: <literal>mysqldump
+              --single-transaction --master-data</literal> behaved like
+              <literal>mysqldump --master-data</literal>, so the dump
+              was a blocking one.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              When using the <literal>RPAD()</literal> function (or any
+              function adding spaces to the right) in a query that had
+              to be resolved by using a temporary table, all resulting
+              strings had rightmost spaces removed (i.e.
+              <literal>RPAD()</literal> did not work).
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="errors-in-4-1">
+
+        <title>Issues in MySQL 4.1 Fixed in a Later Version</title>
+
+        <para>
+          The following known issues have not been fixed in MySQL 4.1
+          for various reasons, and are not classified as critical.
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Fixed in 5.0.3: <literal>VARCHAR</literal> and
+              <literal>VARBINARY</literal> did not remember end space.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+      <section id="open-bugs">
+
+        <title>Open Issues in MySQL</title>
+
+        <para>
+          The following problems are known and fixing them is a high
+          priority:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              If you compare a <literal>NULL</literal> value to a
+              subquery using <literal>ALL/ANY/SOME</literal> and the
+              subquery returns an empty result, the comparison might
+              evaluate to the non-standard result of
+              <literal>NULL</literal> rather than to
+              <literal>TRUE</literal> or <literal>FALSE</literal>. This
+              will be fixed in MySQL 5.1.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Subquery optimization for <literal>IN</literal> is not as
+              effective as for <literal>=</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Even if you use
+              <literal>lower_case_table_names=2</literal> (which enables
+              MySQL to remember the case used for databases and table
+              names), MySQL does not remember the case used for database
+              names for the function <literal>DATABASE()</literal> or
+              within the various logs (on case-insensitive systems).
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Dropping a <literal>FOREIGN KEY</literal> constraint
+              doesn't work in replication because the constraint may
+              have another name on the slave.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>REPLACE</literal> (and <literal>LOAD
+              DATA</literal> with the <literal>REPLACE</literal> option)
+              does not trigger <literal>ON DELETE CASCADE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>DISTINCT</literal> with <literal>ORDER
+              BY</literal> doesn't work inside
+              <literal>GROUP_CONCAT()</literal> if you don't use all and
+              only those columns that are in the
+              <literal>DISTINCT</literal> list.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If one user has a long-running transaction and another
+              user drops a table that is updated in the transaction,
+              there is small chance that the binary log may contain the
+              <literal>DROP TABLE</literal> command before the table is
+              used in the transaction itself. We plan to fix this by
+              having the <literal>DROP TABLE</literal> command wait
+              until the table is not being used in any transaction.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              When inserting a big integer value (between
+              2<superscript>63</superscript> and
+              2<superscript>64</superscript>&minus;1) into a decimal or
+              string column, it is inserted as a negative value because
+              the number is evaluated in a signed integer context.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>FLUSH TABLES WITH READ LOCK</literal> does not
+              block <literal>COMMIT</literal> if the server is running
+              without binary logging, which may cause a problem (of
+              consistency between tables) when doing a full backup.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>ANALYZE TABLE</literal> on a
+              <literal>BDB</literal> table may in some cases make the
+              table unusable until you restart
+              <command>mysqld</command>. If this happens, look for
+              errors of the following form in the MySQL error file:
+            </para>
+
+<programlisting>
+001207 22:07:56  bdb:  log_flush: LSN past current end-of-log
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              Don't execute <literal>ALTER TABLE</literal> on a
+              <literal>BDB</literal> table on which you are running
+              multiple-statement transactions until all those
+              transactions complete. (The transaction might be ignored.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>ANALYZE TABLE</literal>, <literal>OPTIMIZE
+              TABLE</literal>, and <literal>REPAIR TABLE</literal> may
+              cause problems on tables for which you are using
+              <literal>INSERT DELAYED</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Performing <literal>LOCK TABLE ...</literal> and
+              <literal>FLUSH TABLES ...</literal> doesn't guarantee that
+              there isn't a half-finished transaction in progress on the
+              table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>BDB</literal> tables are relatively slow to open.
+              If you have many <literal>BDB</literal> tables in a
+              database, it takes a long time to use the
+              <command>mysql</command> client on the database if you are
+              not using the <literal>-A</literal> option or if you are
+              using <literal>rehash</literal>. This is especially
+              noticeable when you have a large table cache.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Replication uses query-level logging: The master writes
+              the executed queries to the binary log. This is a very
+              fast, compact, and efficient logging method that works
+              perfectly in most cases.
+            </para>
+
+            <para>
+              It is possible for the data on the master and slave to
+              become different if a query is designed in such a way that
+              the data modification is non-deterministic (generally not
+              a recommended practice, even outside of replication).
+            </para>
+
+            <para>
+              For example:
+            </para>
+
+            <itemizedlist>
+
+              <listitem>
+                <para>
+                  <literal>CREATE ... SELECT</literal> or
+                  <literal>INSERT ... SELECT</literal> statements that
+                  insert zero or <literal>NULL</literal> values into an
+                  <literal>AUTO_INCREMENT</literal> column.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  <literal>DELETE</literal> if you are deleting rows
+                  from a table that has foreign keys with <literal>ON
+                  DELETE CASCADE</literal> properties.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  <literal>REPLACE ... SELECT</literal>, <literal>INSERT
+                  IGNORE ... SELECT</literal> if you have duplicate key
+                  values in the inserted data.
+                </para>
+              </listitem>
+
+            </itemizedlist>
+
+            <para>
+              <emphasis role="bold">If and only if the preceding queries
+              have no <literal>ORDER BY</literal> clause guaranteeing a
+              deterministic order</emphasis>.
+            </para>
+
+            <para>
+              For example, for <literal>INSERT ... SELECT</literal> with
+              no <literal>ORDER BY</literal>, the
+              <literal>SELECT</literal> may return rows in a different
+              order (which results in a row having different ranks,
+              hence getting a different number in the
+              <literal>AUTO_INCREMENT</literal> column), depending on
+              the choices made by the optimizers on the master and
+              slave.
+            </para>
+
+            <para>
+              A query is optimized differently on the master and slave
+              only if:
+            </para>
+
+            <itemizedlist>
+
+              <listitem>
+                <para>
+                  The files used by the two queries are not exactly the
+                  same; for example, <literal>OPTIMIZE TABLE</literal>
+                  was run on the master tables and not on the slave
+                  tables. (To fix this, <literal>OPTIMIZE
+                  TABLE</literal>, <literal>ANALYZE TABLE</literal>, and
+                  <literal>REPAIR TABLE</literal> are written to the
+                  binary log as of MySQL 4.1.1).
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  The table is stored using a different storage engine
+                  on the master than on the slave. (It is possible to
+                  use different storage engines on the master and slave.
+                  For example, you can use <literal>InnoDB</literal> on
+                  the master, but <literal>MyISAM</literal> on the slave
+                  if the slave has less available disk space.)
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  MySQL buffer sizes
+                  (<literal>key_buffer_size</literal>, and so on) are
+                  different on the master and slave.
+                </para>
+              </listitem>
+
+              <listitem>
+                <para>
+                  The master and slave run different MySQL versions, and
+                  the optimizer code differs between these versions.
+                </para>
+              </listitem>
+
+            </itemizedlist>
+
+            <para>
+              This problem may also affect database restoration using
+              <command>mysqlbinlog|mysql</command>.
+            </para>
+
+            <para>
+              The easiest way to avoid this problem is to add an
+              <literal>ORDER BY</literal> clause to the aforementioned
+              non-deterministic queries to ensure that the rows are
+              always stored or modified in the same order.
+            </para>
+
+            <para>
+              In future MySQL versions, we will automatically add an
+              <literal>ORDER BY</literal> clause when needed.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          The following issues are known and will be fixed in due time:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              Log filenames are based on the server hostname (if you
+              don't specify a filename with the startup option). You
+              have to use options such as
+              <option>--log-bin=<replaceable>old_host_name</replaceable>-bin</option>
+              if you change your hostname to something else. Another
+              option is to rename the old files to reflect your hostname
+              change (if these are binary logs, you need to edit the
+              binary log index file and fix the binlog names there as
+              well). See <xref linkend="server-options"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <command>mysqlbinlog</command> does not delete temporary
+              files left after a <literal>LOAD DATA INFILE</literal>
+              command. See <xref linkend="mysqlbinlog"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>RENAME</literal> doesn't work with
+              <literal>TEMPORARY</literal> tables or tables used in a
+              <literal>MERGE</literal> table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Due to the way table format (<filename>.frm</filename>)
+              files are stored, you cannot use character 255
+              (<literal>CHAR(255)</literal>) in table names, column
+              names, or enumerations. This is scheduled to be fixed in
+              version 5.1 when we implement new table definition format
+              files.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              When using <literal>SET CHARACTER SET</literal>, you can't
+              use translated characters in database, table, and column
+              names.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can't use &lsquo;<literal>_</literal>&rsquo; or
+              &lsquo;<literal>%</literal>&rsquo; with
+              <literal>ESCAPE</literal> in <literal>LIKE ...
+              ESCAPE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you have a <literal>DECIMAL</literal> column in which
+              the same number is stored in different formats (for
+              example, <literal>+01.00</literal>,
+              <literal>1.00</literal>, <literal>01.00</literal>),
+              <literal>GROUP BY</literal> may regard each value as a
+              different value.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You cannot build the server in another directory when
+              using MIT-pthreads. Because this requires changes to
+              MIT-pthreads, we are not likely to fix this. See
+              <xref linkend="mit-pthreads"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>BLOB</literal> and <literal>TEXT</literal> values
+              can't <quote>reliably</quote> be used in <literal>GROUP
+              BY</literal>, <literal>ORDER BY</literal> or
+              <literal>DISTINCT</literal>. Only the first
+              <literal>max_sort_length</literal> bytes are used when
+              comparing <literal>BLOB</literal> values in these cases.
+              The default value of <literal>max_sort_length</literal>
+              value is 1024 and can be changed at server startup time.
+              As of MySQL 4.0.3, it can be changed at runtime. For older
+              versions, a workaround is to use a substring. For example:
+            </para>
+
+<programlisting>
+SELECT DISTINCT LEFT(<replaceable>blob_col</replaceable>,2048) FROM <replaceable>tbl_name</replaceable>;
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              Numeric calculations are done with
+              <literal>BIGINT</literal> or <literal>DOUBLE</literal>
+              (both are normally 64 bits long). Which precision you get
+              depends on the function. The general rule is that bit
+              functions are performed with <literal>BIGINT</literal>
+              precision, <literal>IF</literal> and
+              <literal>ELT()</literal> with <literal>BIGINT</literal> or
+              <literal>DOUBLE</literal> precision, and the rest with
+              <literal>DOUBLE</literal> precision. You should try to
+              avoid using unsigned long long values if they resolve to
+              be larger than 63 bits (9223372036854775807) for anything
+              other than bit fields. MySQL Server 4.0 has better
+              <literal>BIGINT</literal> handling than 3.23.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can have up to 255 <literal>ENUM</literal> and
+              <literal>SET</literal> columns in one table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              In <literal>MIN()</literal>, <literal>MAX()</literal>, and
+              other aggregate functions, MySQL currently compares
+              <literal>ENUM</literal> and <literal>SET</literal> columns
+              by their string value rather than by the string's relative
+              position in the set.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <command>mysqld_safe</command> redirects all messages from
+              <command>mysqld</command> to the <command>mysqld</command>
+              log. One problem with this is that if you execute
+              <command>mysqladmin refresh</command> to close and reopen
+              the log, <literal>stdout</literal> and
+              <literal>stderr</literal> are still redirected to the old
+              log. If you use <option>--log</option> extensively, you
+              should edit <command>mysqld_safe</command> to log to
+              <filename><replaceable>host_name</replaceable>.err</filename>
+              instead of
+              <filename><replaceable>host_name</replaceable>.log</filename>
+              so that you can easily reclaim the space for the old log
+              by deleting it and executing <command>mysqladmin
+              refresh</command>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              In an <literal>UPDATE</literal> statement, columns are
+              updated from left to right. If you refer to an updated
+              column, you get the updated value instead of the original
+              value. For example, the following statement increments
+              <literal>KEY</literal> by <literal>2</literal>,
+              <emphasis role="bold">not</emphasis> <literal>1</literal>:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>UPDATE <replaceable>tbl_name</replaceable> SET KEY=KEY+1,KEY=KEY+1;</userinput>
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can refer to multiple temporary tables in the same
+              query, but you cannot refer to any given temporary table
+              more than once. For example, the following doesn't work:
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM temp_table, temp_table AS t2;</userinput>
+ERROR 1137: Can't reopen table: 'temp_table'
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              The optimizer may handle <literal>DISTINCT</literal>
+              differently when you are using <quote>hidden</quote>
+              columns in a join than when you are not. In a join, hidden
+              columns are counted as part of the result (even if they
+              are not shown), whereas in normal queries, hidden columns
+              don't participate in the <literal>DISTINCT</literal>
+              comparison. We will probably change this in the future to
+              never compare the hidden columns when executing
+              <literal>DISTINCT</literal>.
+            </para>
+
+            <para>
+              An example of this is:
+            </para>
+
+<programlisting>
+SELECT DISTINCT mp3id FROM band_downloads
+       WHERE userid = 9 ORDER BY id DESC;
+</programlisting>
+
+            <para>
+              and
+            </para>
+
+<programlisting>
+SELECT DISTINCT band_downloads.mp3id
+       FROM band_downloads,band_mp3
+       WHERE band_downloads.userid = 9
+       AND band_mp3.id = band_downloads.mp3id
+       ORDER BY band_downloads.id DESC;
+</programlisting>
+
+            <para>
+              In the second case, using MySQL Server 3.23.x, you may get
+              two identical rows in the result set (because the values
+              in the hidden <literal>id</literal> column may differ).
+            </para>
+
+            <para>
+              Note that this happens only for queries where that do not
+              have the <literal>ORDER BY</literal> columns in the
+              result.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you execute a <literal>PROCEDURE</literal> on a query
+              that returns an empty set, in some cases the
+              <literal>PROCEDURE</literal> does not transform the
+              columns.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              Creation of a table of type <literal>MERGE</literal>
+              doesn't check whether the underlying tables are compatible
+              types.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              If you use <literal>ALTER TABLE</literal> to add a
+              <literal>UNIQUE</literal> index to a table used in a
+              <literal>MERGE</literal> table and then add a normal index
+              on the <literal>MERGE</literal> table, the key order is
+              different for the tables if there was an old,
+              non-<literal>UNIQUE</literal> key in the table. This is
+              because <literal>ALTER TABLE</literal> puts
+              <literal>UNIQUE</literal> indexes before normal indexes to
+              be able to detect duplicate keys as early as possible.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
+    </section>
+
+  </section>
+
+  <section id="error-messages-server">
+
+    <title>Server Error Codes and Messages</title>
+
+    <para>
+      MySQL programs have access to several types of error information
+      when the server returns an error. For example, the
+      <command>mysql</command> client program displays errors using the
+      following format:
+    </para>
+
+<programlisting>
+shell&gt; <userinput>SELECT * FROM no_such_table;</userinput>
+ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist
+</programlisting>
+
+    <para>
+      The message displayed contains three types of information:
+    </para>
+
+    <itemizedlist>
+
+      <listitem>
+        <para>
+          A numeric error value (<literal>1146</literal>). This number
+          is MySQL-specific and is not portable to other database
+          systems.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
+          A five-character SQLSTATE value (<literal>'42S02'</literal>).
+          The values are specified by ANSI SQL and ODBC and are more
+          standardized. Not all MySQL error numbers are mapped to
+          SQLSTATE error codes. The value <literal>'HY000'</literal>
+          (general error) is used for unmapped errors.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
+          A string that provides a textual description of the error.
+        </para>
+      </listitem>
+
+    </itemizedlist>
+
+    <para>
+      Server error information comes from the following source files.
+      For details about the way that error information is defined, see
+      the MySQL Internals manual, available at
+      <ulink url="&base-url-docs;"/>.
+    </para>
+
+    <itemizedlist>
+
+      <listitem>
+        <para>
+          The Error values and the symbols in parentheses correspond to
+          definitions in the <filename>include/mysqld_error.h</filename>
+          MySQL source file.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
+          The SQLSTATE values correspond to definitions in the
+          <filename>include/sql_state.h</filename> MySQL source file.
+        </para>
+
+        <para>
+          SQLSTATE error codes are displayed only if you use MySQL
+          version 4.1 and up. SQLSTATE codes were added for
+          compatibility with X/Open, ANSI, and ODBC behavior.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
+          The Message values correspond to the error messages that are
+          listed in the <filename>sql/share/errmsg.txt</filename> file.
+          <literal>%d</literal> and <literal>%s</literal> represent
+          numbers and strings, respectively, that are substituted into
+          the messages when they are displayed.
+        </para>
+      </listitem>
+
+    </itemizedlist>
+
+    <para>
+      Because updates are frequent, it is possible that those files will
+      contain additional error information not listed here.
+    </para>
+
+    <remark role="note">
+      Include list of server error messages:
+    </remark>
+
+    <xi:include href="errmsgs-server.xml"  xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
+  </section>
+
+  <section id="error-messages-client">
+
+    <title>Client Error Codes and Messages</title>
+
+    <para>
+      Client error information comes from the following source files:
+    </para>
+
+    <itemizedlist>
+
+      <listitem>
+        <para>
+          The Error values and the symbols in parentheses correspond to
+          definitions in the <filename>include/errmsg.h</filename> MySQL
+          source file.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
+          The Message values correspond to the error messages that are
+          listed in the <filename>libmysql/errmsg.c</filename> file.
+          <literal>%d</literal> and <literal>%s</literal> represent
+          numbers and strings, respectively, that are substituted into
+          the messages when they are displayed.
+        </para>
+      </listitem>
+
+    </itemizedlist>
+
+    <para>
+      Because updates are frequent, it is possible that those files will
+      contain additional error information not listed here.
+    </para>
+
+    <remark role="note">
+      Include list of client error messages:
+    </remark>
+
+    <xi:include href="errmsgs-client.xml"  xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
+  </section>
+
+</appendix>


Modified: trunk/refman-4.1/extending-mysql.xml
===================================================================
--- trunk/refman-4.1/extending-mysql.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/extending-mysql.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 9; 1004 bytes

@@ -137,20 +137,18 @@
         connection, <literal>INSERT DELAYED</literal>, and replication
         threads.
       </para>
-      
+
       <formalpara role="mnmas">
-        
+
         <title>MySQL Enterprise</title>
-        
+
         <para>
-          For expert advice on thread management subscribe  to
-          the MySQL Network Monitoring and Advisory Service. For more
-          information see,
-          <ulink url="&base-url-enterprise;advisors.html" />.
+          For expert advice on thread management subscribe to the MySQL
+          Network Monitoring and Advisory Service. For more information
+          see, <ulink url="&base-url-enterprise;advisors.html" />.
         </para>
-        
+
       </formalpara>
-      
 
     </section>
 


Modified: trunk/refman-4.1/functions.xml
===================================================================
--- trunk/refman-4.1/functions.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/functions.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 474, Lines Deleted: 0; 17728 bytes

@@ -4432,6 +4432,480 @@
 
     </section>
 
+    <section id="regexp">
+
+      <title>Regular Expressions</title>
+
+      <indexterm>
+        <primary>REGEXP operator</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>pattern matching</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>regular expression syntax</primary>
+      </indexterm>
+
+      <indexterm>
+        <primary>syntax</primary>
+        <secondary>regular expression</secondary>
+      </indexterm>
+
+      <para>
+        A regular expression is a powerful way of specifying a pattern
+        for a complex search.
+      </para>
+
+      <para>
+        MySQL uses Henry Spencer's implementation of regular
+        expressions, which is aimed at conformance with POSIX 1003.2.
+        See <xref linkend="credits"/>. MySQL uses the extended version
+        to support pattern-matching operations performed with the
+        <literal>REGEXP</literal> operator in SQL statements. See
+        <xref linkend="pattern-matching"/>, and
+        <xref linkend="string-comparison-functions"/>.
+      </para>
+
+      <para>
+        This section is a summary, with examples, of the special
+        characters and constructs that can be used in MySQL for
+        <literal>REGEXP</literal> operations. It does not contain all
+        the details that can be found in Henry Spencer's
+        <literal>regex(7)</literal> manual page. That manual page is
+        included in MySQL source distributions, in the
+        <filename>regex.7</filename> file under the
+        <filename>regex</filename> directory.
+      </para>
+
+      <para>
+        A regular expression describes a set of strings. The simplest
+        regular expression is one that has no special characters in it.
+        For example, the regular expression <literal>hello</literal>
+        matches <literal>hello</literal> and nothing else.
+      </para>
+
+      <para>
+        Non-trivial regular expressions use certain special constructs
+        so that they can match more than one string. For example, the
+        regular expression <literal>hello|word</literal> matches either
+        the string <literal>hello</literal> or the string
+        <literal>word</literal>.
+      </para>
+
+      <para>
+        As a more complex example, the regular expression
+        <literal>B[an]*s</literal> matches any of the strings
+        <literal>Bananas</literal>, <literal>Baaaaas</literal>,
+        <literal>Bs</literal>, and any other string starting with a
+        <literal>B</literal>, ending with an <literal>s</literal>, and
+        containing any number of <literal>a</literal> or
+        <literal>n</literal> characters in between.
+      </para>
+
+      <para>
+        A regular expression for the <literal>REGEXP</literal> operator
+        may use any of the following special characters and constructs:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            <literal>^</literal>
+          </para>
+
+          <para>
+            Match the beginning of a string.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'fo\nfo' REGEXP '^fo$';</userinput>                   -&gt; 0
+mysql&gt; <userinput>SELECT 'fofo' REGEXP '^fo';</userinput>                      -&gt; 1
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>$</literal>
+          </para>
+
+          <para>
+            Match the end of a string.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'fo\no' REGEXP '^fo\no$';</userinput>                 -&gt; 1
+mysql&gt; <userinput>SELECT 'fo\no' REGEXP '^fo$';</userinput>                    -&gt; 0
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>.</literal>
+          </para>
+
+          <para>
+            Match any character (including carriage return and newline).
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'fofo' REGEXP '^f.*$';</userinput>                    -&gt; 1
+mysql&gt; <userinput>SELECT 'fo\r\nfo' REGEXP '^f.*$';</userinput>                -&gt; 1
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>a*</literal>
+          </para>
+
+          <para>
+            Match any sequence of zero or more <literal>a</literal>
+            characters.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'Ban' REGEXP '^Ba*n';</userinput>                     -&gt; 1
+mysql&gt; <userinput>SELECT 'Baaan' REGEXP '^Ba*n';</userinput>                   -&gt; 1
+mysql&gt; <userinput>SELECT 'Bn' REGEXP '^Ba*n';</userinput>                      -&gt; 1
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>a+</literal>
+          </para>
+
+          <para>
+            Match any sequence of one or more <literal>a</literal>
+            characters.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'Ban' REGEXP '^Ba+n';</userinput>                     -&gt; 1
+mysql&gt; <userinput>SELECT 'Bn' REGEXP '^Ba+n';</userinput>                      -&gt; 0
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>a?</literal>
+          </para>
+
+          <para>
+            Match either zero or one <literal>a</literal> character.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'Bn' REGEXP '^Ba?n';</userinput>                      -&gt; 1
+mysql&gt; <userinput>SELECT 'Ban' REGEXP '^Ba?n';</userinput>                     -&gt; 1
+mysql&gt; <userinput>SELECT 'Baan' REGEXP '^Ba?n';</userinput>                    -&gt; 0
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>de|abc</literal>
+          </para>
+
+          <para>
+            Match either of the sequences <literal>de</literal> or
+            <literal>abc</literal>.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'pi' REGEXP 'pi|apa';</userinput>                     -&gt; 1
+mysql&gt; <userinput>SELECT 'axe' REGEXP 'pi|apa';</userinput>                    -&gt; 0
+mysql&gt; <userinput>SELECT 'apa' REGEXP 'pi|apa';</userinput>                    -&gt; 1
+mysql&gt; <userinput>SELECT 'apa' REGEXP '^(pi|apa)$';</userinput>                -&gt; 1
+mysql&gt; <userinput>SELECT 'pi' REGEXP '^(pi|apa)$';</userinput>                 -&gt; 1
+mysql&gt; <userinput>SELECT 'pix' REGEXP '^(pi|apa)$';</userinput>                -&gt; 0
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>(abc)*</literal>
+          </para>
+
+          <para>
+            Match zero or more instances of the sequence
+            <literal>abc</literal>.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'pi' REGEXP '^(pi)*$';</userinput>                    -&gt; 1
+mysql&gt; <userinput>SELECT 'pip' REGEXP '^(pi)*$';</userinput>                   -&gt; 0
+mysql&gt; <userinput>SELECT 'pipi' REGEXP '^(pi)*$';</userinput>                  -&gt; 1
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>{1}</literal>, <literal>{2,3}</literal>
+          </para>
+
+          <para>
+            <literal>{n}</literal> or <literal>{m,n}</literal> notation
+            provides a more general way of writing regular expressions
+            that match many occurrences of the previous atom (or
+            <quote>piece</quote>) of the pattern. <literal>m</literal>
+            and <literal>n</literal> are integers.
+          </para>
+
+          <itemizedlist>
+
+            <listitem>
+              <para>
+                <literal>a*</literal>
+              </para>
+
+              <para>
+                Can be written as <literal>a{0,}</literal>.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                <literal>a+</literal>
+              </para>
+
+              <para>
+                Can be written as <literal>a{1,}</literal>.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                <literal>a?</literal>
+              </para>
+
+              <para>
+                Can be written as <literal>a{0,1}</literal>.
+              </para>
+            </listitem>
+
+          </itemizedlist>
+
+          <para>
+            To be more precise, <literal>a{n}</literal> matches exactly
+            <literal>n</literal> instances of <literal>a</literal>.
+            <literal>a{n,}</literal> matches <literal>n</literal> or
+            more instances of <literal>a</literal>.
+            <literal>a{m,n}</literal> matches <literal>m</literal>
+            through <literal>n</literal> instances of
+            <literal>a</literal>, inclusive.
+          </para>
+
+          <para>
+            <literal>m</literal> and <literal>n</literal> must be in the
+            range from <literal>0</literal> to
+            <literal>RE_DUP_MAX</literal> (default 255), inclusive. If
+            both <literal>m</literal> and <literal>n</literal> are
+            given, <literal>m</literal> must be less than or equal to
+            <literal>n</literal>.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'abcde' REGEXP 'a[bcd]{2}e';</userinput>              -&gt; 0
+mysql&gt; <userinput>SELECT 'abcde' REGEXP 'a[bcd]{3}e';</userinput>              -&gt; 1
+mysql&gt; <userinput>SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';</userinput>           -&gt; 1
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>[a-dX]</literal>, <literal>[^a-dX]</literal>
+          </para>
+
+          <para>
+            Matches any character that is (or is not, if ^ is used)
+            either <literal>a</literal>, <literal>b</literal>,
+            <literal>c</literal>, <literal>d</literal> or
+            <literal>X</literal>. A <literal>-</literal> character
+            between two other characters forms a range that matches all
+            characters from the first character to the second. For
+            example, <literal>[0-9]</literal> matches any decimal digit.
+            To include a literal <literal>]</literal> character, it must
+            immediately follow the opening bracket <literal>[</literal>.
+            To include a literal <literal>-</literal> character, it must
+            be written first or last. Any character that does not have a
+            defined special meaning inside a <literal>[]</literal> pair
+            matches only itself.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'aXbc' REGEXP '[a-dXYZ]';</userinput>                 -&gt; 1
+mysql&gt; <userinput>SELECT 'aXbc' REGEXP '^[a-dXYZ]$';</userinput>               -&gt; 0
+mysql&gt; <userinput>SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';</userinput>              -&gt; 1
+mysql&gt; <userinput>SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';</userinput>             -&gt; 0
+mysql&gt; <userinput>SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';</userinput>            -&gt; 1
+mysql&gt; <userinput>SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';</userinput>           -&gt; 0
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>[.characters.]</literal>
+          </para>
+
+          <para>
+            Within a bracket expression (written using
+            <literal>[</literal> and <literal>]</literal>), matches the
+            sequence of characters of that collating element.
+            <literal>characters</literal> is either a single character
+            or a character name like <literal>newline</literal>. You can
+            find the full list of character names in the
+            <filename>regexp/cname.h</filename> file.
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT '~' REGEXP '[[.~.]]';</userinput>                     -&gt; 1
+mysql&gt; <userinput>SELECT '~' REGEXP '[[.tilde.]]';</userinput>                 -&gt; 1
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>[=character_class=]</literal>
+          </para>
+
+          <para>
+            Within a bracket expression (written using
+            <literal>[</literal> and <literal>]</literal>),
+            <literal>[=character_class=]</literal> represents an
+            equivalence class. It matches all characters with the same
+            collation value, including itself. For example, if
+            <literal>o</literal> and <literal>(+)</literal> are the
+            members of an equivalence class, then
+            <literal>[[=o=]]</literal>, <literal>[[=(+)=]]</literal>,
+            and <literal>[o(+)]</literal> are all synonymous. An
+            equivalence class may not be used as an endpoint of a range.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>[:character_class:]</literal>
+          </para>
+
+          <para>
+            Within a bracket expression (written using
+            <literal>[</literal> and <literal>]</literal>),
+            <literal>[:character_class:]</literal> represents a
+            character class that matches all characters belonging to
+            that class. The following table lists the standard class
+            names. These names stand for the character classes defined
+            in the <literal>ctype(3)</literal> manual page. A particular
+            locale may provide other class names. A character class may
+            not be used as an endpoint of a range.
+          </para>
+
+          <informaltable>
+            <tgroup cols="2">
+              <colspec colwidth="10*"/>
+              <colspec colwidth="90*"/>
+              <tbody>
+                <row>
+                  <entry><literal>alnum</literal></entry>
+                  <entry>Alphanumeric characters</entry>
+                </row>
+                <row>
+                  <entry><literal>alpha</literal></entry>
+                  <entry>Alphabetic characters</entry>
+                </row>
+                <row>
+                  <entry><literal>blank</literal></entry>
+                  <entry>Whitespace characters</entry>
+                </row>
+                <row>
+                  <entry><literal>cntrl</literal></entry>
+                  <entry>Control characters</entry>
+                </row>
+                <row>
+                  <entry><literal>digit</literal></entry>
+                  <entry>Digit characters</entry>
+                </row>
+                <row>
+                  <entry><literal>graph</literal></entry>
+                  <entry>Graphic characters</entry>
+                </row>
+                <row>
+                  <entry><literal>lower</literal></entry>
+                  <entry>Lowercase alphabetic characters</entry>
+                </row>
+                <row>
+                  <entry><literal>print</literal></entry>
+                  <entry>Graphic or space characters</entry>
+                </row>
+                <row>
+                  <entry><literal>punct</literal></entry>
+                  <entry>Punctuation characters</entry>
+                </row>
+                <row>
+                  <entry><literal>space</literal></entry>
+                  <entry>Space, tab, newline, and carriage return</entry>
+                </row>
+                <row>
+                  <entry><literal>upper</literal></entry>
+                  <entry>Uppercase alphabetic characters</entry>
+                </row>
+                <row>
+                  <entry><literal>xdigit</literal></entry>
+                  <entry>Hexadecimal digit characters</entry>
+                </row>
+              </tbody>
+            </tgroup>
+          </informaltable>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'justalnums' REGEXP '[[:alnum:]]+';</userinput>       -&gt; 1
+mysql&gt; <userinput>SELECT '!!' REGEXP '[[:alnum:]]+';</userinput>               -&gt; 0
+</programlisting>
+        </listitem>
+
+        <listitem>
+          <para>
+            <literal>[[:&lt;:]]</literal>, <literal>[[:&gt;:]]</literal>
+          </para>
+
+          <para>
+            These markers stand for word boundaries. They match the
+            beginning and end of words, respectively. A word is a
+            sequence of word characters that is not preceded by or
+            followed by word characters. A word character is an
+            alphanumeric character in the <literal>alnum</literal> class
+            or an underscore (<literal>_</literal>).
+          </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT 'a word a' REGEXP '[[:&lt;:]]word[[:&gt;:]]';</userinput>   -&gt; 1
+mysql&gt; <userinput>SELECT 'a xword a' REGEXP '[[:&lt;:]]word[[:&gt;:]]';</userinput>  -&gt; 0
+</programlisting>
+        </listitem>
+
+      </itemizedlist>
+
+      <para>
+        To use a literal instance of a special character in a regular
+        expression, precede it by two backslash (\) characters. The
+        MySQL parser interprets one of the backslashes, and the regular
+        expression library interprets the other. For example, to match
+        the string <literal>1+2</literal> that contains the special
+        <literal>+</literal> character, only the last of the following
+        regular expressions is the correct one:
+      </para>
+
+<programlisting>
+mysql&gt; <userinput>SELECT '1+2' REGEXP '1+2';</userinput>                       -&gt; 0
+mysql&gt; <userinput>SELECT '1+2' REGEXP '1\+2';</userinput>                      -&gt; 0
+mysql&gt; <userinput>SELECT '1+2' REGEXP '1\\+2';</userinput>                     -&gt; 1
+</programlisting>
+
+    </section>
+
   </section>
 
   <section id="numeric-functions">


Modified: trunk/refman-4.1/installing.xml
===================================================================
--- trunk/refman-4.1/installing.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/installing.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 358, Lines Deleted: 0; 11040 bytes

@@ -18370,6 +18370,364 @@
 
   </section>
 
+  <section id="environment-variables">
+
+    <title>Environment Variables</title>
+
+    <indexterm>
+      <primary>environment variables</primary>
+      <secondary>list of</secondary>
+    </indexterm>
+
+    <para>
+      This section lists all the environment variables that are used
+      directly or indirectly by MySQL. Most of these can also be found
+      in other places in this manual.
+    </para>
+
+    <para>
+      Note that any options on the command line take precedence over
+      values specified in option files and environment variables, and
+      values in option files take precedence over values in environment
+      variables.
+    </para>
+
+    <para>
+      In many cases, it is preferable to use an option file instead of
+      environment variables to modify the behavior of MySQL. See
+      <xref linkend="option-files"/>.
+    </para>
+
+    <indexterm>
+      <primary>CXX environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>CXX</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>CC environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>CC</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>CFLAGS environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>CFLAGS</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>CXXFLAGS environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>CXXFLAGS</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>DBI_USER environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>DBI_USER</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>DBI_TRACE environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>DBI_TRACE</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>HOME environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>HOME</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>LD_RUN_PATH environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>LD_RUN_PATH</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_DEBUG environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_DEBUG</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_GROUP_SUFFIX environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_GROUP_SUFFIX</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_HISTFILE environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_HISTFILE</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_HOME environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_HOME</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_HOST environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_HOST</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_PS1 environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_PS1</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_PWD environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_PWD</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_TCP_PORT environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_TCP_PORT</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>MYSQL_UNIX_PORT environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>MYSQL_UNIX_PORT</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>PATH environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>PATH</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>TMPDIR environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>TMPDIR</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>TZ environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>TZ</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>UMASK_DIR environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>UMASK_DIR</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>UMASK environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>UMASK</secondary>
+    </indexterm>
+
+    <indexterm>
+      <primary>USER environment variable</primary>
+    </indexterm>
+
+    <indexterm>
+      <primary>Environment variable</primary>
+      <secondary>USER</secondary>
+    </indexterm>
+
+    <informaltable>
+      <tgroup cols="2">
+        <colspec colwidth="25*"/>
+        <colspec colwidth="70*"/>
+        <tbody>
+          <row>
+            <entry><emphasis role="bold">Variable</emphasis></entry>
+            <entry><emphasis role="bold">Description</emphasis></entry>
+          </row>
+          <row>
+            <entry><literal>CXX</literal></entry>
+            <entry>The name of your C++ compiler (for running
+              <command>configure</command>).</entry>
+          </row>
+          <row>
+            <entry><literal>CC</literal></entry>
+            <entry>The name of your C compiler (for running <command>configure</command>).</entry>
+          </row>
+          <row>
+            <entry><literal>CFLAGS</literal></entry>
+            <entry>Flags for your C compiler (for running <command>configure</command>).</entry>
+          </row>
+          <row>
+            <entry><literal>CXXFLAGS</literal></entry>
+            <entry>Flags for your C++ compiler (for running <command>configure</command>).</entry>
+          </row>
+          <row>
+            <entry><literal>DBI_USER</literal></entry>
+            <entry>The default username for Perl DBI.</entry>
+          </row>
+          <row>
+            <entry><literal>DBI_TRACE</literal></entry>
+            <entry>Trace options for Perl DBI.</entry>
+          </row>
+          <row>
+            <entry><literal>HOME</literal></entry>
+            <entry>The default path for the <command>mysql</command> history file is
+              <filename>$HOME/.mysql_history</filename>.</entry>
+          </row>
+          <row>
+            <entry><literal>LD_RUN_PATH</literal></entry>
+            <entry>Used to specify the location of <filename>libmysqlclient.so</filename>.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_DEBUG</literal></entry>
+            <entry>Debug trace options when debugging.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_GROUP_SUFFIX</literal></entry>
+            <entry>Option group suffix value (like specifying
+              <option>--defaults-group-suffix</option>).</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_HISTFILE</literal></entry>
+            <entry>The path to the <command>mysql</command> history file. If this variable
+              is set, its value overrides the default for
+              <filename>$HOME/.mysql_history</filename>.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_HOME</literal></entry>
+            <entry>The path to the directory in which the server-specific
+              <filename>my.cnf</filename> file resides (as of MySQL
+              5.0.3).</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_HOST</literal></entry>
+            <entry>The default hostname used by the <command>mysql</command> command-line
+              client.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_PS1</literal></entry>
+            <entry>The command prompt to use in the <command>mysql</command> command-line
+              client.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_PWD</literal></entry>
+            <entry>The default password when connecting to <command>mysqld</command>. Note
+              that using this is insecure. See
+              <xref linkend="password-security"/>.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_TCP_PORT</literal></entry>
+            <entry>The default TCP/IP port number.</entry>
+          </row>
+          <row>
+            <entry><literal>MYSQL_UNIX_PORT</literal></entry>
+            <entry>The default Unix socket filename; used for connections to
+              <literal>localhost</literal>.</entry>
+          </row>
+          <row>
+            <entry><literal>PATH</literal></entry>
+            <entry>Used by the shell to find MySQL programs.</entry>
+          </row>
+          <row>
+            <entry><literal>TMPDIR</literal></entry>
+            <entry>The directory where temporary files are created.</entry>
+          </row>
+          <row>
+            <entry><literal>TZ</literal></entry>
+            <entry>This should be set to your local time zone. See
+              <xref linkend="timezone-problems"/>.</entry>
+          </row>
+          <row>
+            <entry><literal>UMASK_DIR</literal></entry>
+            <entry>The user-directory creation mask when creating directories. Note that
+              this is <literal>AND</literal>ed with
+              <literal>UMASK</literal>.</entry>
+          </row>
+          <row>
+            <entry><literal>UMASK</literal></entry>
+            <entry>The user-file creation mask when creating files.</entry>
+          </row>
+          <row>
+            <entry><literal>USER</literal></entry>
+            <entry>The default username on Windows and NetWare used when connecting to
+              <command>mysqld</command>.</entry>
+          </row>
+        </tbody>
+      </tgroup>
+    </informaltable>
+
+  </section>
+
   <section id="perl-support">
 
     <title>Perl Installation Notes</title>


Modified: trunk/refman-4.1/introduction.xml
===================================================================
--- trunk/refman-4.1/introduction.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/introduction.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 2; 680 bytes

@@ -1597,8 +1597,8 @@
 
             <listitem>
               <para>
-                <literal>INSERT INTO <replaceable>tbl_name</replaceable> SET
-                <replaceable>col_name</replaceable> = ...</literal>
+                <literal>INSERT INTO <replaceable>tbl_name</replaceable>
+                SET <replaceable>col_name</replaceable> = ...</literal>
                 syntax.
               </para>
             </listitem>


Modified: trunk/refman-4.1/language-structure.xml
===================================================================
--- trunk/refman-4.1/language-structure.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/language-structure.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 8, Lines Deleted: 7; 1124 bytes

@@ -1200,18 +1200,19 @@
         affects how the server handles identifier case sensitivity, as
         described later in this section.
       </para>
-      
+
       <formalpara role ="mnmas">
-        
+
         <title>MySQL Enterprise</title>
-        
+
         <para>
-          <literal>lower_case_table_names</literal> is just one of the 
-          system variables monitored by  the MySQL Network Monitoring and Advisory
-          Service. For information about subscribing to this service see,
+          <literal>lower_case_table_names</literal> is just one of the
+          system variables monitored by the MySQL Network Monitoring and
+          Advisory Service. For information about subscribing to this
+          service see,
           <ulink url="&base-url-enterprise;advisors.html"/>.
         </para>
-        
+
       </formalpara>
 
       <para>


Modified: trunk/refman-4.1/manual.xml
===================================================================
--- trunk/refman-4.1/manual.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/manual.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 12; 1460 bytes

@@ -80,24 +80,14 @@
 
   <xi:include href="extending-mysql.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
 
-  <xi:include href="problems.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+  <xi:include href="errors-problems.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
 
-  <xi:include href="error-handling.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
   <xi:include href="news.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
 
   <xi:include href="restrictions.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
 
-  <xi:include href="porting.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+  <xi:include href="../refman-common/licenses.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
 
-  <xi:include href="../refman-common/environment-variables.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
-  <xi:include href="../refman-common/regexp.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
-  <xi:include href="../refman-common/gpl-license.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
-  <xi:include href="../refman-common/mysql-floss-license-exception.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
   <xi:include href="../refman-common/credits.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
 
   <index/>


Modified: trunk/refman-4.1/news-4.0.xml
===================================================================
--- trunk/refman-4.1/news-4.0.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/news-4.0.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 2; 761 bytes

@@ -171,8 +171,9 @@
         <para>
           Idle connections were not killed during timeout when using the
           Native POSIX Thread Library (NPTL) and
-          <command>mysqld</command>. In the course of this fix, code to detect
-          and handle the NPTL has been backported from 4.1 to 4.0. (Bug #16995)
+          <command>mysqld</command>. In the course of this fix, code to
+          detect and handle the NPTL has been backported from 4.1 to
+          4.0. (Bug #16995)
         </para>
       </listitem>
 


Modified: trunk/refman-4.1/problems.xml
===================================================================
--- trunk/refman-4.1/problems.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/problems.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 2, Lines Added: 2, Lines Deleted: 2; 674 bytes

@@ -1497,10 +1497,9 @@
       </para>
 
       <formalpara role="mnmas">
+
         <title>MySQL Enterprise</title>
 
-
-
         <para>
           For reasons of security and performance the advisors provided
           by the MySQL Network Monitoring and Advisory Service pay

@@ -1509,6 +1508,7 @@
           more information see,
           <ulink url="&base-url-enterprise;advisors.html"/>.
         </para>
+
       </formalpara>
 
       <para>


Modified: trunk/refman-4.1/restrictions.xml
===================================================================
--- trunk/refman-4.1/restrictions.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/restrictions.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 1, Lines Added: 27, Lines Deleted: 27; 1937 bytes

@@ -400,42 +400,42 @@
 
   </section>
 
-<section id="limits">
+  <section id="limits">
 
-  <title>Limits in MySQL</title>
+    <title>Limits in MySQL</title>
 
-  <indexterm>
-    <primary>limitations</primary>
-    <secondary>MySQL Limitations</secondary>
-  </indexterm>
+    <indexterm>
+      <primary>limitations</primary>
+      <secondary>MySQL Limitations</secondary>
+    </indexterm>
 
-  <indexterm>
-    <primary>limits</primary>
-    <secondary>MySQL Limits, limits in MySQL</secondary>
-  </indexterm>
-
-  <para>
-    This section lists current limits in MySQL &current-series;.
-  </para>
-
-  <section id="joins-limits">
-
-    <title>Limits of Joins</title>
-
     <indexterm>
-      <primary>maximums</primary>
-      <secondary>maximum tables per join</secondary>
+      <primary>limits</primary>
+      <secondary>MySQL Limits, limits in MySQL</secondary>
     </indexterm>
 
     <para>
-      In MySQL &current-series;, the maximum number of tables that can
-      be referenced in a single join is 61. This also applies to the
-      number of tables that can be referenced in the definition of a
-      view.
+      This section lists current limits in MySQL &current-series;.
     </para>
 
+    <section id="joins-limits">
+
+      <title>Limits of Joins</title>
+
+      <indexterm>
+        <primary>maximums</primary>
+        <secondary>maximum tables per join</secondary>
+      </indexterm>
+
+      <para>
+        In MySQL &current-series;, the maximum number of tables that can
+        be referenced in a single join is 61. This also applies to the
+        number of tables that can be referenced in the definition of a
+        view.
+      </para>
+
+    </section>
+
   </section>
 
-</section>
-
 </appendix>


Modified: trunk/refman-4.1/storage-engines.xml
===================================================================
--- trunk/refman-4.1/storage-engines.xml	2007-03-01 14:24:22 UTC (rev 5134)
+++ trunk/refman-4.1/storage-engines.xml	2007-03-01 14:37:49 UTC (rev 5135)
Changed blocks: 4, Lines Added: 6, Lines Deleted: 6; 1608 bytes

@@ -881,16 +881,16 @@
       </itemizedlist>
 
       <formalpara role="mnmas">
+
         <title>MySQL Enterprise</title>
 
-
-
         <para>
           Subscribers to MySQL Network Monitoring and Advisory Service
           receive notification if the <option>--myisam-recover</option>
           option has not been set. For more information see,
           <ulink url="&base-url-enterprise;advisors.html"/>.
         </para>
+
       </formalpara>
 
       <para>

@@ -1333,10 +1333,9 @@
         </itemizedlist>
 
         <formalpara>
+
           <title>Note</title>
 
-
-
           <para>
             While a compressed table is read-only, and you cannot
             therefore update or add rows in the table, DDL (Data

@@ -1345,6 +1344,7 @@
             the table, and <literal>TRUNCATE</literal> to empty the
             table.
           </para>
+
         </formalpara>
 
       </section>

@@ -1465,16 +1465,16 @@
         </para>
 
         <formalpara role="mnmas">
+
           <title>MySQL Enterprise</title>
 
-
-
           <para>
             Find out about problems before they occur. Subscribe to the
             MySQL Network Monitoring and Advisor Service for expert
             advice about the state of your servers. For more information
             see, <ulink url="&base-url-enterprise;advisors.html"/>.
           </para>
+
         </formalpara>
 
       </section>


Thread
svn commit - mysqldoc@docsrva: r5135 - trunk/refman-4.1mcbrown1 Mar