Author: mcbrown
Date: 2007-02-19 15:29:57 +0100 (Mon, 19 Feb 2007)
New Revision: 4988
Log:
Reordering, merging and updating appendix order and structure
Added:
trunk/refman-5.1/errors-problems.xml
Modified:
trunk/refman-5.1/Makefile
trunk/refman-5.1/functions.xml
trunk/refman-5.1/installing.xml
trunk/refman-5.1/manual.xml
trunk/refman-5.1/renamed-nodes.txt
trunk/refman-5.1/restrictions.xml
Modified: trunk/refman-5.1/Makefile
===================================================================
--- trunk/refman-5.1/Makefile 2007-02-19 14:27:57 UTC (rev 4987)
+++ trunk/refman-5.1/Makefile 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 10317 bytes
@@ -42,7 +42,7 @@
MANUAL_SRCS_EXTRA = versions.ent ../common/fixedchars.ent ../refman-common/urls.ent ../common/phrases.ent
-MANUAL_SRCS = $(MANUAL_SRCS_EXTRA) manual.xml ../refman-common/bug-reports.xml ../refman-common/connector-j.xml ../refman-common/connector-mxj.xml ../refman-common/connector-net.xml ../refman-common/connector-odbc.xml ../refman-common/connector-php.xml ../refman-common/connector-vstudioplugin.xml ../refman-common/credits.xml ../refman-common/environment-variables.xml ../refman-common/gpl-license.xml ../refman-common/images/cmxj-overview.png ../refman-common/images/cnet-win-install-step1.png ../refman-common/images/cnet-win-install-step2.png ../refman-common/images/cnet-win-install-step3.png ../refman-common/images/cnet-win-install-step4.png ../refman-common/images/myaccess-odbc.png ../refman-common/images/myaccess.png ../refman-common/images/myarchitecture.png ../refman-common/images/myflowchart.png ../refman-common/images/myodbc-crystal-1.png ../refman-common/images/myodbc-crystal-2.png ../refman-common/images/myodbc-crystal-3.png ../refman-common/images/myodbc-crystal-4.p!
ng ../refman-common/images/myodbc-crystal-5.png ../refman-common/images/myodbc-crystal-6.png ../refman-common/images/myodbc-excel-step1.png ../refman-common/images/myodbc-excel-step2.png ../refman-common/images/myodbc-excel-step3.png ../refman-common/images/myodbc-excel-step4.png ../refman-common/images/myodbc-excel-step5.png ../refman-common/images/myodbc-link-step1.png ../refman-common/images/myodbc-link-step3.png ../refman-common/images/myodbc-link-step4.png ../refman-common/images/myodbc-link-step5.png ../refman-common/images/myodbc-macosx-install-step1.png ../refman-common/images/myodbc-macosx-install-step2.png ../refman-common/images/myodbc-macosx-install-step3.png ../refman-common/images/myodbc-macosx-install-step4.png ../refman-common/images/myodbc-macosx-install-step5.png ../refman-common/images/myodbc-macosx-install-step6.png ../refman-common/images/myodbc-macosx-odbcadmin-adddsn.png ../refman-common/images/myodbc-macosx-odbcadmin-main.png ../refman-common/images/!
myodbc-macosx-odbcadmin-sampledsn.png ../refman-common/images/!
myodbc-m
acosx-odbcadmin-tracing.png ../refman-common/images/myodbc-win-install-step1.png ../refman-common/images/myodbc-win-install-step2.png ../refman-common/images/myodbc-win-install-step3.png ../refman-common/images/myodbc-win-install-step4.png ../refman-common/images/myodbc-win-odbcadmin-adddsn.png ../refman-common/images/myodbc-win-odbcadmin-advanced.png ../refman-common/images/myodbc-win-odbcadmin-driverselect.png ../refman-common/images/myodbc-win-odbcadmin-main.png ../refman-common/images/myodbc-win-odbcadmin-options.png ../refman-common/images/myodbc-win-odbcadmin-sampledsn.png ../refman-common/images/myodbc-win-tracing.png ../refman-common/images/mysql-vstudioplugin-1.png ../refman-common/images/mysql-vstudioplugin-3.png ../refman-common/images/mysql-vstudioplugin-4.png ../refman-common/images/se-federated-structure.png ../refman-common/information-sources.xml ../refman-common/manual-conventions.en.xml ../refman-common/maxdb.en.xml ../refman-common/mysql-floss-license-exce!
ption.xml ../refman-common/news-connector-j.xml ../refman-common/news-connector-net.xml ../refman-common/news-myodbc.xml ../refman-common/news-vstudioplugin.xml ../refman-common/regexp.xml ../refman-common/replication-graphics/multi-db.png ../refman-common/replication-graphics/redundancy-after.png ../refman-common/replication-graphics/redundancy-before.png ../refman-common/replication-graphics/scaleout.png ../refman-common/replication-graphics/submaster-performance.png ../refman-common/what-is-mysql-ab.en.xml ../refman-common/what-is.en.xml apis-c.xml apis-cplusplus.xml apis-eiffel.xml apis-libmysqld.xml apis-perl.xml apis-php.xml apis-progutils.xml apis-python.xml apis-tcl.xml apis.xml charset.xml client-utility-programs.xml connectors.xml copyright.xml data-types.xml database-administration.xml dba-optvars-table.xml errmsgs-client.xml errmsgs-server.xml error-handling.xml events.xml extending-mysql.xml faqs.xml functions.xml images/PSEArch.png images/blackhole-1.png image!
s/cluster-components-1.png images/cluster-replication-binlog-i!
njector.
png images/cluster-replication-overview.png images/cluster-upgrade-downgrade-table.png images/multi-comp-1.png images/ndb-size-pl-1.png images/replicas-groups-1-1.png images/replicas-groups-1-2.png images/rolling-restarts.png information-schema.xml installing.xml introduction.xml language-structure.xml legalnotice.en.xml limits.xml mysql-cluster.xml news-5.1.xml news.xml optimization.xml partitioning.xml porting.xml precision-math.xml preface.xml problems.xml replication-configuration.xml replication-implementation.xml replication-notes.xml replication-solutions.xml replication.xml reserved-new-5.1.xml reservedwords.xml restrictions.xml se-archive.xml se-blackhole.xml se-csv.xml se-example.xml se-federated.xml se-innodb.xml se-memory.xml se-merge.xml se-myisam.xml spatial-extensions.xml sql-syntax.xml storage-engines.xml stored-procedures.xml triggers.xml tutorial.xml using-mysql-programs.xml views.xml
+MANUAL_SRCS = $(MANUAL_SRCS_EXTRA) manual.xml ../refman-common/bug-reports.xml ../refman-common/connector-j.xml ../refman-common/connector-mxj.xml ../refman-common/connector-net.xml ../refman-common/connector-odbc.xml ../refman-common/connector-php.xml ../refman-common/connector-vstudioplugin.xml ../refman-common/credits.xml ../refman-common/images/cmxj-overview.png ../refman-common/images/cnet-win-install-step1.png ../refman-common/images/cnet-win-install-step2.png ../refman-common/images/cnet-win-install-step3.png ../refman-common/images/cnet-win-install-step4.png ../refman-common/images/myaccess-odbc.png ../refman-common/images/myaccess.png ../refman-common/images/myarchitecture.png ../refman-common/images/myflowchart.png ../refman-common/images/myodbc-crystal-1.png ../refman-common/images/myodbc-crystal-2.png ../refman-common/images/myodbc-crystal-3.png ../refman-common/images/myodbc-crystal-4.png ../refman-common/images/myodbc-crystal-5.png ../refman-common/images/myod!
bc-crystal-6.png ../refman-common/images/myodbc-excel-step1.png ../refman-common/images/myodbc-excel-step2.png ../refman-common/images/myodbc-excel-step3.png ../refman-common/images/myodbc-excel-step4.png ../refman-common/images/myodbc-excel-step5.png ../refman-common/images/myodbc-link-step1.png ../refman-common/images/myodbc-link-step3.png ../refman-common/images/myodbc-link-step4.png ../refman-common/images/myodbc-link-step5.png ../refman-common/images/myodbc-macosx-install-step1.png ../refman-common/images/myodbc-macosx-install-step2.png ../refman-common/images/myodbc-macosx-install-step3.png ../refman-common/images/myodbc-macosx-install-step4.png ../refman-common/images/myodbc-macosx-install-step5.png ../refman-common/images/myodbc-macosx-install-step6.png ../refman-common/images/myodbc-macosx-odbcadmin-adddsn.png ../refman-common/images/myodbc-macosx-odbcadmin-main.png ../refman-common/images/myodbc-macosx-odbcadmin-sampledsn.png ../refman-common/images/myodbc-macosx-!
odbcadmin-tracing.png ../refman-common/images/myodbc-win-insta!
ll-step1
.png ../refman-common/images/myodbc-win-install-step2.png ../refman-common/images/myodbc-win-install-step3.png ../refman-common/images/myodbc-win-install-step4.png ../refman-common/images/myodbc-win-odbcadmin-adddsn.png ../refman-common/images/myodbc-win-odbcadmin-advanced.png ../refman-common/images/myodbc-win-odbcadmin-driverselect.png ../refman-common/images/myodbc-win-odbcadmin-main.png ../refman-common/images/myodbc-win-odbcadmin-options.png ../refman-common/images/myodbc-win-odbcadmin-sampledsn.png ../refman-common/images/myodbc-win-tracing.png ../refman-common/images/mysql-vstudioplugin-1.png ../refman-common/images/mysql-vstudioplugin-3.png ../refman-common/images/mysql-vstudioplugin-4.png ../refman-common/images/se-federated-structure.png ../refman-common/information-sources.xml ../refman-common/licenses.xml ../refman-common/manual-conventions.en.xml ../refman-common/maxdb.en.xml ../refman-common/news-connector-j.xml ../refman-common/news-connector-net.xml ../refman!
-common/news-myodbc.xml ../refman-common/news-vstudioplugin.xml ../refman-common/replication-graphics/multi-db.png ../refman-common/replication-graphics/redundancy-after.png ../refman-common/replication-graphics/redundancy-before.png ../refman-common/replication-graphics/scaleout.png ../refman-common/replication-graphics/submaster-performance.png ../refman-common/what-is-mysql-ab.en.xml ../refman-common/what-is.en.xml apis-c.xml apis-cplusplus.xml apis-eiffel.xml apis-libmysqld.xml apis-perl.xml apis-php.xml apis-progutils.xml apis-python.xml apis-tcl.xml apis.xml charset.xml client-utility-programs.xml connectors.xml copyright.xml data-types.xml database-administration.xml dba-optvars-table.xml errmsgs-client.xml errmsgs-server.xml errors-problems.xml events.xml extending-mysql.xml faqs.xml functions.xml images/PSEArch.png images/blackhole-1.png images/cluster-components-1.png images/cluster-replication-binlog-injector.png images/cluster-replication-overview.png images/clu!
ster-upgrade-downgrade-table.png images/multi-comp-1.png image!
s/ndb-si
ze-pl-1.png images/replicas-groups-1-1.png images/replicas-groups-1-2.png images/rolling-restarts.png information-schema.xml installing.xml introduction.xml language-structure.xml legalnotice.en.xml mysql-cluster.xml news-5.1.xml news.xml optimization.xml partitioning.xml precision-math.xml preface.xml replication-configuration.xml replication-implementation.xml replication-notes.xml replication-solutions.xml replication.xml reserved-new-5.1.xml reservedwords.xml restrictions.xml se-archive.xml se-blackhole.xml se-csv.xml se-example.xml se-federated.xml se-innodb.xml se-memory.xml se-merge.xml se-myisam.xml spatial-extensions.xml sql-syntax.xml storage-engines.xml stored-procedures.xml triggers.xml tutorial.xml using-mysql-programs.xml views.xml
manual-prepped.xml: $(MANUAL_SRCS) $(IDMAP_OBJS)
manual-manprepped.xml: $(MANUAL_SRCS) $(IDMAP_OBJS)
Added: trunk/refman-5.1/errors-problems.xml
===================================================================
--- trunk/refman-5.1/errors-problems.xml (rev 0)
+++ trunk/refman-5.1/errors-problems.xml 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 1, Lines Added: 5360, Lines Deleted: 0; 186797 bytes
@@ -0,0 +1,5360 @@
+<?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="errors-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> <userinput>mysqladmin version</userinput>
+shell> <userinput>mysqladmin variables</userinput>
+shell> <userinput>mysqladmin -h `hostname` version variables</userinput>
+shell> <userinput>mysqladmin -h `hostname` --port=3306 version</userinput>
+shell> <userinput>mysqladmin -h host_ip version</userinput>
+shell> <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> <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> <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 ¤t-series; uses an authentication protocol based on
+ a password hashing algorithm that is incompatible with that used
+ by older (pre-4.1) 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> <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> <userinput>SET PASSWORD FOR</userinput>
+ -> <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> <userinput>UPDATE mysql.user SET Password = OLD_PASSWORD('<replaceable>newpwd</replaceable>')</userinput>
+ -> <userinput>WHERE Host = '<replaceable>some_host</replaceable>' AND User = '<replaceable>some_user</replaceable>';</userinput>
+mysql> <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> <userinput>SELECT Host, User, Password FROM mysql.user</userinput>
+ -> <userinput>WHERE LENGTH(Password) > 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 older versions of PHP,
+ the <literal>mysql</literal> extension does not support the
+ 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
+ may 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"; added in PHP 5) is 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 MySQL
+ client library. 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> <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> <userinput>mysqld_safe --max_connect_errors=10000 &</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. Beginning
+ with MySQL 5.1.15, its default value is 151 to improve
+ performance when MySQL is used with the Apache Web server.
+ (Previously, the default was 100.) If you need to support more
+ connections, you should restart <command>mysqld</command> with a
+ larger value for this variable.
+ </para>
+
+ <formalpara role="mnmas">
+ <title>MySQL Enterprise</title>
+
+
+
+ <para>
+ Subscribers to the MySQL Network Monitoring and Advisory
+ Service receive advice on dynamically configuring the
+ <literal>max_connections</literal> variable — avoiding
+ failed connection attempts. For more information see,
+ <ulink url="&base-url-enterprise;advisors.html" />.
+ </para>
+ </formalpara>
+
+ <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>
+ Prior to MySQL 5.1.8, 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 — 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>
+ The largest possible packet that can be transmitted to or from a
+ MySQL ¤t-series; server or client is 1GB.
+ </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. To set a larger value, start <command>mysql</command> like
+ this:
+ </para>
+
+<programlisting>
+shell> <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> <userinput>mysqld --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>
+ It is 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 do not 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"/>. If you
+ start the server with the <option>--log-warnings</option>
+ option, 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>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 256GB by default, but this limit can
+ be changed up to the maximum allowable size of 65,536TB
+ (256<superscript>7</superscript> − 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>
+ </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> <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> <userinput>UPDATE user SET Password=PASSWORD('<replaceable>newpwd</replaceable>')</userinput>
+ -> <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> <userinput>perror 23</userinput>
+Error code 23: File table overflow
+shell> <userinput>perror 24</userinput>
+Error code 24: Too many open files
+shell> <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_open_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
+ ‘<literal>#</literal>’ 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> <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> <userinput>UMASK=384 # = 600 in octal</userinput>
+shell> <userinput>export UMASK</userinput>
+shell> <userinput>mysqld_safe &</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 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> <userinput>UMASK_DIR=504 # = 770 in octal</userinput>
+shell> <userinput>export UMASK_DIR</userinput>
+shell> <userinput>mysqld_safe &</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 -> Control Panel -> Administrative Tools -> 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 -> Run -> 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:\> <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>
+ If you install MySQL using the MySQL Installation Wizard,
+ you may need to specify a <option>--defaults-file</option>
+ option:
+ </para>
+
+<programlisting>
+C:\> <userinput>"C:\Program Files\MySQL\MySQL Server ¤t-series;\bin\mysqld-nt.exe"</userinput>
+ <userinput>--defaults-file="C:\Program Files\MySQL\MySQL Server ¤t-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 -> Control Panel -> Administrative Tools -> 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> <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> <userinput>mysqld_safe --init-file=~/mysql-init &</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> <userinput>mysql -u root</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Issue the following statements in the
+ <command>mysql</command> client:
+ </para>
+
+<programlisting>
+mysql> <userinput>UPDATE mysql.user SET Password=PASSWORD('<replaceable>newpwd</replaceable>')</userinput>
+ -> <userinput>WHERE User='root';</userinput>
+mysql> <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 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. 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>
+ In MySQL ¤t-series;, 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 (‘<literal>:</literal>’) on Unix and
+ semicolon characters (‘<literal>;</literal>’) 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>
+
+ <formalpara role="mnmas">
+ <title>MySQL Enterprise</title>
+
+
+
+ <para>
+ Advisors provided by the MySQL Network Monitoring and Advisory
+ Service automatically detect excessive temporary table storage
+ to disk. For more information see,
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+ </formalpara>
+
+ <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> <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> <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>
+ Simple comparison operations (<literal>>=, >, =, <,
+ <=</literal>, sorting, and grouping) are based on each
+ character's <quote>sort value.</quote> Characters with the same
+ sort value (such as ‘<literal>E</literal>’,
+ ‘<literal>e</literal>’, and
+ ‘<literal>Ãĩ</literal>’) 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> <userinput>SELECT * FROM <replaceable>tbl_name</replaceable> WHERE date >= '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><</literal>, <literal><=</literal>,
+ <literal>=</literal>, <literal>>=</literal>,
+ <literal>></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> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES (19970505);</userinput>
+mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('19970505');</userinput>
+mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('97-05-05');</userinput>
+mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('1997.05.05');</userinput>
+mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('1997 05 05');</userinput>
+mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (idate) VALUES ('0000-00-00');</userinput>
+
+mysql> <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE idate >= '1997-05-05';</userinput>
+mysql> <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE idate >= 19970505;</userinput>
+mysql> <userinput>SELECT MOD(idate,100) FROM <replaceable>tbl_name</replaceable> WHERE idate >= 19970505;</userinput>
+mysql> <userinput>SELECT idate FROM <replaceable>tbl_name</replaceable> WHERE idate >= '19970505';</userinput>
+</programlisting>
+
+ <para>
+ However, the following does not work:
+ </para>
+
+<programlisting>
+mysql> <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 you are using the <literal>ALLOW_INVALID_DATES</literal> SQL
+ mode, MySQL allows you to store dates that are given only
+ limited checking: MySQL requires only that the day is in the
+ range from 1 to 31 and the month is in the range from 1 to 12.
+ </para>
+
+ <para>
+ This makes MySQL very convenient for Web applications where you
+ obtain year, month, and day in three different fields and you
+ want to store exactly what the user inserted (without date
+ validation).
+ </para>
+
+ <para>
+ If you are not using the <literal>NO_ZERO_IN_DATE</literal> SQL
+ mode, the day or month part can be zero. This is convenient if
+ you want to store a birthdate in a <literal>DATE</literal>
+ column and you know only part of the date.
+ </para>
+
+ <para>
+ If you are not using the <literal>NO_ZERO_DATE</literal> SQL
+ mode, MySQL also allows you to store
+ <literal>'0000-00-00'</literal> as a <quote>dummy date.</quote>
+ This is in some cases more convenient than using
+ <literal>NULL</literal> values.
+ </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>
+
+ <para>
+ If you want MySQL to check all dates and accept only legal dates
+ (unless overridden by IGNORE), you should set
+ <literal>sql_mode</literal> to
+ <literal>"NO_ZERO_IN_DATE,NO_ZERO_DATE"</literal>.
+ </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> <userinput>INSERT INTO my_table (phone) VALUES (NULL);</userinput>
+mysql> <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> <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> <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> <userinput>SELECT * FROM my_table WHERE phone IS NULL;</userinput>
+mysql> <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 the
+ <literal>MyISAM</literal>, <literal>InnoDB</literal>, or
+ <literal>MEMORY</literal> storage engine. 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.
+ </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> <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 > 0;
+SELECT id, COUNT(*) AS cnt FROM <replaceable>tbl_name</replaceable> GROUP BY id HAVING cnt > 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 > 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>
+ You can also use the following statement, 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>
+ 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>
+ </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 <
+ 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> >
+ 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> <userinput>mysqladmin create test2</userinput>
+shell> <userinput>mysql test2 < 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. For <literal>DECIMAL</literal> columns, MySQL
+ performs operations with a precision of 64 decimal digits, which
+ should solve most common inaccuracy problems.
+ </para>
+
+ <para>
+ The following example demonstrates the problem using
+ <literal>DOUBLE</literal>. It shows that are calculations that
+ are done using floating-point operations are subject to
+ floating-point error.
+ </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+-->
+mysql> <userinput>CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);</userinput>
+mysql> <userinput>INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),</userinput>
+ -> <userinput>(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),</userinput>
+ -> <userinput>(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),</userinput>
+ -> <userinput>(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),</userinput>
+ -> <userinput>(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),</userinput>
+ -> <userinput>(6, 0.00, 0.00), (6, -51.40, 0.00);</userinput>
+
+mysql> <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b</userinput>
+ -> <userinput>FROM t1 GROUP BY i HAVING a <> b;</userinput>
+
++------+-------+------+
+| i | a | b |
++------+-------+------+
+| 1 | 21.4 | 21.4 |
+| 2 | 76.8 | 76.8 |
+| 3 | 7.4 | 7.4 |
+| 4 | 15.4 | 15.4 |
+| 5 | 7.2 | 7.2 |
+| 6 | -51.4 | 0 |
++------+-------+------+
+</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>
+ If columns <literal>d1</literal> and <literal>d2</literal> had
+ been defined as <literal>DECIMAL</literal> rather than
+ <literal>DOUBLE</literal>, the result of the
+ <literal>SELECT</literal> query would have contained only one
+ row — the last one shown above.
+ </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> <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1</userinput>
+ -> <userinput>GROUP BY i HAVING ABS(a - b) > 0.0001;</userinput>
++------+-------+------+
+| i | a | b |
++------+-------+------+
+| 6 | -51.4 | 0 |
++------+-------+------+
+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> <userinput>SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1</userinput>
+ -> <userinput>GROUP BY i HAVING ABS(a - b) <= 0.0001;</userinput>
++------+------+------+
+| i | a | b |
++------+------+------+
+| 1 | 21.4 | 21.4 |
+| 2 | 76.8 | 76.8 |
+| 3 | 7.4 | 7.4 |
+| 4 | 15.4 | 15.4 |
+| 5 | 7.2 | 7.2 |
++------+------+------+
+5 rows in set (0.03 sec)
+</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> <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> <userinput>SELECT <replaceable>col_name1</replaceable>, <replaceable>col_name2</replaceable>, <replaceable>col_name3</replaceable> FROM <replaceable>tbl_name</replaceable>;</userinput>
+mysql> <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> <userinput>INSERT INTO new_table</userinput>
+ -> <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> <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>MEMORY</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> <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> <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="open-bugs">
+
+ <title>Open Issues in MySQL</title>
+
+ <remark role="todo">
+ [js] These need to be checked against 5.1 bugfixes, and moved to
+ the Limitations or Restriction appendices as appropriate (*if*
+ still relevant).
+ </remark>
+
+ <para>
+ The following problems are known and fixing them is a high
+ priority:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ MySQL Cluster fails to recover from an out-of-disk failure
+ when using disk data. (Bug #17614)
+ </para>
+ </listitem>
+
+ <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>−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>, <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>
+ 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 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 ‘<literal>_</literal>’ or
+ ‘<literal>%</literal>’ 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 reliably 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> is 1024 and can be
+ changed at server startup time or at runtime.
+ </para>
+ </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.
+ </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> <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> <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> <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>
+ Error message information is listed in the
+ <filename>share/errmsg.txt</filename> file.
+ <literal>%d</literal> and <literal>%s</literal> represent
+ numbers and strings, respectively, that are substituted into
+ the Message values when they are displayed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The Error values listed in
+ <filename>share/errmsg.txt</filename> are used to generate the
+ definitions in the <filename>include/mysqld_error.h</filename>
+ and <filename>include/mysqld_ername.h</filename> MySQL source
+ files.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The SQLSTATE values listed in
+ <filename>share/errmsg.txt</filename> are used to generate the
+ definitions in the <filename>include/sql_state.h</filename>
+ MySQL source file.
+ </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-5.1/functions.xml
===================================================================
--- trunk/refman-5.1/functions.xml 2007-02-19 14:27:57 UTC (rev 4987)
+++ trunk/refman-5.1/functions.xml 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 1, Lines Added: 468, Lines Deleted: 0; 16344 bytes
@@ -4480,6 +4480,474 @@
</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> <userinput>SELECT 'fo\nfo' REGEXP '^fo$';</userinput> -> 0
+mysql> <userinput>SELECT 'fofo' REGEXP '^fo';</userinput> -> 1
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>$</literal>
+ </para>
+
+ <para>
+ Match the end of a string.
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 'fo\no' REGEXP '^fo\no$';</userinput> -> 1
+mysql> <userinput>SELECT 'fo\no' REGEXP '^fo$';</userinput> -> 0
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>.</literal>
+ </para>
+
+ <para>
+ Match any character (including carriage return and newline).
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 'fofo' REGEXP '^f.*$';</userinput> -> 1
+mysql> <userinput>SELECT 'fo\r\nfo' REGEXP '^f.*$';</userinput> -> 1
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>a*</literal>
+ </para>
+
+ <para>
+ Match any sequence of zero or more <literal>a</literal>
+ characters.
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 'Ban' REGEXP '^Ba*n';</userinput> -> 1
+mysql> <userinput>SELECT 'Baaan' REGEXP '^Ba*n';</userinput> -> 1
+mysql> <userinput>SELECT 'Bn' REGEXP '^Ba*n';</userinput> -> 1
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>a+</literal>
+ </para>
+
+ <para>
+ Match any sequence of one or more <literal>a</literal>
+ characters.
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 'Ban' REGEXP '^Ba+n';</userinput> -> 1
+mysql> <userinput>SELECT 'Bn' REGEXP '^Ba+n';</userinput> -> 0
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>a?</literal>
+ </para>
+
+ <para>
+ Match either zero or one <literal>a</literal> character.
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 'Bn' REGEXP '^Ba?n';</userinput> -> 1
+mysql> <userinput>SELECT 'Ban' REGEXP '^Ba?n';</userinput> -> 1
+mysql> <userinput>SELECT 'Baan' REGEXP '^Ba?n';</userinput> -> 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> <userinput>SELECT 'pi' REGEXP 'pi|apa';</userinput> -> 1
+mysql> <userinput>SELECT 'axe' REGEXP 'pi|apa';</userinput> -> 0
+mysql> <userinput>SELECT 'apa' REGEXP 'pi|apa';</userinput> -> 1
+mysql> <userinput>SELECT 'apa' REGEXP '^(pi|apa)$';</userinput> -> 1
+mysql> <userinput>SELECT 'pi' REGEXP '^(pi|apa)$';</userinput> -> 1
+mysql> <userinput>SELECT 'pix' REGEXP '^(pi|apa)$';</userinput> -> 0
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>(abc)*</literal>
+ </para>
+
+ <para>
+ Match zero or more instances of the sequence
+ <literal>abc</literal>.
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT 'pi' REGEXP '^(pi)*$';</userinput> -> 1
+mysql> <userinput>SELECT 'pip' REGEXP '^(pi)*$';</userinput> -> 0
+mysql> <userinput>SELECT 'pipi' REGEXP '^(pi)*$';</userinput> -> 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> <userinput>SELECT 'abcde' REGEXP 'a[bcd]{2}e';</userinput> -> 0
+mysql> <userinput>SELECT 'abcde' REGEXP 'a[bcd]{3}e';</userinput> -> 1
+mysql> <userinput>SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';</userinput> -> 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> <userinput>SELECT 'aXbc' REGEXP '[a-dXYZ]';</userinput> -> 1
+mysql> <userinput>SELECT 'aXbc' REGEXP '^[a-dXYZ]$';</userinput> -> 0
+mysql> <userinput>SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';</userinput> -> 1
+mysql> <userinput>SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';</userinput> -> 0
+mysql> <userinput>SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';</userinput> -> 1
+mysql> <userinput>SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';</userinput> -> 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> <userinput>SELECT '~' REGEXP '[[.~.]]';</userinput> -> 1
+mysql> <userinput>SELECT '~' REGEXP '[[.tilde.]]';</userinput> -> 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> <userinput>SELECT 'justalnums' REGEXP '[[:alnum:]]+';</userinput> -> 1
+mysql> <userinput>SELECT '!!' REGEXP '[[:alnum:]]+';</userinput> -> 0
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>[[:<:]]</literal>, <literal>[[:>:]]</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> <userinput>SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';</userinput> -> 1
+mysql> <userinput>SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';</userinput> -> 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> <userinput>SELECT '1+2' REGEXP '1+2';</userinput> -> 0
+mysql> <userinput>SELECT '1+2' REGEXP '1\+2';</userinput> -> 0
+mysql> <userinput>SELECT '1+2' REGEXP '1\\+2';</userinput> -> 1
+</programlisting>
+
+</section>
+
</section>
<section id="numeric-functions">
Modified: trunk/refman-5.1/installing.xml
===================================================================
--- trunk/refman-5.1/installing.xml 2007-02-19 14:27:57 UTC (rev 4987)
+++ trunk/refman-5.1/installing.xml 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 1, Lines Added: 357, Lines Deleted: 0; 10431 bytes
@@ -17067,6 +17067,363 @@
</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-5.1/manual.xml
===================================================================
--- trunk/refman-5.1/manual.xml 2007-02-19 14:27:57 UTC (rev 4987)
+++ trunk/refman-5.1/manual.xml 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 1, Lines Added: 3, Lines Deleted: 15; 1623 bytes
@@ -95,27 +95,15 @@
<xi:include href="faqs.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="../refman-common/credits.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="porting.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="limits.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="../refman-common/gpl-license.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/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-5.1/renamed-nodes.txt
===================================================================
--- trunk/refman-5.1/renamed-nodes.txt 2007-02-19 14:27:57 UTC (rev 4987)
+++ trunk/refman-5.1/renamed-nodes.txt 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 1, Lines Added: 13, Lines Deleted: 1; 1544 bytes
@@ -368,4 +368,16 @@
mysqld-max mysqld
replication-upgrade-5-1 replication-upgrade
replication-row-based replication-formats
-myodbc-examples-tools-tested myodbc-examples-tools
\ No newline at end of file
+myodbc-examples-tools-tested myodbc-examples-tools
+porting http://dev.mysql.com/doc/internals/en/porting.html
+debugging-server http://dev.mysql.com/doc/internals/en/debugging-server
+compiling-for-debugging http://dev.mysql.com/doc/internals/en/compiling-for-debugging
+making-trace-files http://dev.mysql.com/doc/internals/en/making-trace-files
+using-gdb-on-mysqld http://dev.mysql.com/doc/internals/en/using-gdb-on-mysqld
+using-stack-trace http://dev.mysql.com/doc/internals/en/using-stack-trace
+using-log-files http://dev.mysql.com/doc/internals/en/using-log-files
+reproducible-test-case http://dev.mysql.com/doc/internals/en/reproducible-test-case
+debugging-client http://dev.mysql.com/doc/internals/en/debugging-client
+the-dbug-package http://dev.mysql.com/doc/internals/en/the-dbug-package
+rts-threads http://dev.mysql.com/doc/internals/en/rts-threads
+thread-packages http://dev.mysql.com/doc/internals/en/thread-packages
\ No newline at end of file
Modified: trunk/refman-5.1/restrictions.xml
===================================================================
--- trunk/refman-5.1/restrictions.xml 2007-02-19 14:27:57 UTC (rev 4987)
+++ trunk/refman-5.1/restrictions.xml 2007-02-19 14:29:57 UTC (rev 4988)
Changed blocks: 2, Lines Added: 38, Lines Deleted: 1; 1309 bytes
@@ -11,7 +11,7 @@
]>
<appendix id="restrictions">
- <title>Feature Restrictions</title>
+ <title>Limits and Restrictions</title>
<para>
The discussion here describes restrictions that apply to the use of
@@ -1112,4 +1112,41 @@
</section>
+<section id="limits">
+
+ <title>Limits in MySQL</title>
+
+ <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 ¤t-series;.
+ </para>
+
+ <section id="joins-limits">
+
+ <title>Limits of Joins</title>
+
+ <indexterm>
+ <primary>maximums</primary>
+ <secondary>maximum tables per join</secondary>
+ </indexterm>
+
+ <para>
+ 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>
+
</appendix>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r4988 - trunk/refman-5.1 | mcbrown | 19 Feb |
