Author: mcbrown
Date: 2007-10-19 17:11:56 +0200 (Fri, 19 Oct 2007)
New Revision: 8195
Log:
Enabling openbugs
Removed:
trunk/refman-5.1/errors-problems.xml
Renamed/Moved:
trunk/refman-5.1/errors-problems-core.xml (from rev 8194, trunk/refman-5.1/errors-problems.xml)
Modified:
trunk/refman-5.1/Makefile.depends
trunk/refman-5.1/manual.xml
Modified: trunk/refman-5.1/Makefile.depends
===================================================================
--- trunk/refman-5.1/errors-problems-core.xml (rev 0)
+++ trunk/refman-5.1/errors-problems-core.xml 2007-10-19 15:11:56 UTC (rev 8195)
Changed blocks: 27, Lines Added: 5534, Lines Deleted: 56; 207881 bytes
@@ -15,7 +15,7 @@
metadata/client-utility-programs.idmap \
metadata/connectors.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/language-structure.idmap \
metadata/optimization.idmap \
@@ -116,7 +116,7 @@
apis_php_SOURCES = apis-php.xml $(apis_php_INCLUDES)
apis_php_IDMAPS = \
metadata/apis-php.idmap \
- metadata/errors-problems.idmap
+ metadata/errors-problems-core.idmap
apis-php.validpure: $(apis_php_SOURCES)
apis-php.titles: $(apis_php_SOURCES)
apis-php.useless: $(apis_php_SOURCES)
@@ -391,7 +391,7 @@
metadata/charset.idmap \
metadata/connectors.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/sql-syntax.idmap
connectors.validpure: $(connectors_SOURCES)
@@ -416,7 +416,7 @@
metadata/client-utility-programs.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/language-structure.idmap \
metadata/mysql-cluster-ccsg-merge-temp.idmap \
@@ -594,7 +594,7 @@
metadata/client-utility-programs.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/events.idmap \
metadata/extending-mysql.idmap \
metadata/faqs.idmap \
@@ -625,6 +625,43 @@
dynxml-local-dba-manprepped.xml: $(dynxml_local_dba_SOURCES) $(dynxml_local_dba_IDMAPS)
dynxml-local-dba-remprepped.xml: $(dynxml_local_dba_SOURCES) $(dynxml_local_dba_IDMAPS)
+dynxml_local_errors_problems_INCLUDES = \
+ ../common/fixedchars.ent \
+ ../common/phrases.ent \
+ ../dynamic-docs/open-bugs/mysqld-versions.xml \
+ ../dynamic-docs/open-bugs/mysqld.xml \
+ ../refman-common/urls.ent \
+ all-entities.ent \
+ errmsgs-client.xml \
+ errmsgs-server.xml \
+ errors-problems-core.xml \
+ versions.ent
+dynxml_local_errors_problems_IMAGES =
+dynxml_local_errors_problems_SOURCES = dynxml-local-errors-problems.xml $(dynxml_local_errors_problems_INCLUDES)
+dynxml_local_errors_problems_IDMAPS = \
+ ../refman-common/metadata/bug-reports.idmap \
+ metadata/client-utility-programs.idmap \
+ metadata/dba-core.idmap \
+ metadata/errors-problems-core.idmap \
+ metadata/extending-mysql.idmap \
+ metadata/installing.idmap \
+ metadata/mysql-cluster-ccsg-merge-temp.idmap \
+ metadata/optimization.idmap \
+ metadata/replication-notes.idmap \
+ metadata/se-innodb.idmap \
+ metadata/se-merge.idmap \
+ metadata/sql-syntax.idmap \
+ metadata/tutorial.idmap \
+ metadata/using-mysql-programs.idmap
+dynxml-local-errors-problems.validpure: $(dynxml_local_errors_problems_SOURCES)
+dynxml-local-errors-problems.titles: $(dynxml_local_errors_problems_SOURCES)
+dynxml-local-errors-problems.useless: $(dynxml_local_errors_problems_SOURCES)
+dynxml-local-errors-problems.valid: $(dynxml_local_errors_problems_SOURCES) $(dynxml_local_errors_problems_IDMAPS)
+dynxml-local-errors-problems.validwarn: $(dynxml_local_errors_problems_SOURCES) $(dynxml_local_errors_problems_IDMAPS)
+dynxml-local-errors-problems-prepped.xml: $(dynxml_local_errors_problems_SOURCES) $(dynxml_local_errors_problems_IDMAPS)
+dynxml-local-errors-problems-manprepped.xml: $(dynxml_local_errors_problems_SOURCES) $(dynxml_local_errors_problems_IDMAPS)
+dynxml-local-errors-problems-remprepped.xml: $(dynxml_local_errors_problems_SOURCES) $(dynxml_local_errors_problems_IDMAPS)
+
dynxml_local_functions_INCLUDES = \
../common/fixedchars.ent \
../common/phrases.ent \
@@ -642,7 +679,7 @@
metadata/client-utility-programs.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/extending-mysql.idmap \
metadata/functions-core.idmap \
metadata/installing.idmap \
@@ -687,39 +724,18 @@
errmsgs-server-manprepped.xml: $(errmsgs_server_SOURCES) $(errmsgs_server_IDMAPS)
errmsgs-server-remprepped.xml: $(errmsgs_server_SOURCES) $(errmsgs_server_IDMAPS)
-errors_problems_INCLUDES = \
- ../common/fixedchars.ent \
- ../common/phrases.ent \
- ../refman-common/urls.ent \
- all-entities.ent \
- errmsgs-client.xml \
- errmsgs-server.xml \
- versions.ent
-errors_problems_IMAGES =
-errors_problems_SOURCES = errors-problems.xml $(errors_problems_INCLUDES)
-errors_problems_IDMAPS = \
- ../refman-common/metadata/bug-reports.idmap \
- metadata/client-utility-programs.idmap \
- metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
- metadata/extending-mysql.idmap \
- metadata/installing.idmap \
- metadata/mysql-cluster-ccsg-merge-temp.idmap \
- metadata/optimization.idmap \
- metadata/replication-notes.idmap \
- metadata/se-innodb.idmap \
- metadata/se-merge.idmap \
- metadata/sql-syntax.idmap \
- metadata/tutorial.idmap \
- metadata/using-mysql-programs.idmap
-errors-problems.validpure: $(errors_problems_SOURCES)
-errors-problems.titles: $(errors_problems_SOURCES)
-errors-problems.useless: $(errors_problems_SOURCES)
-errors-problems.valid: $(errors_problems_SOURCES) $(errors_problems_IDMAPS)
-errors-problems.validwarn: $(errors_problems_SOURCES) $(errors_problems_IDMAPS)
-errors-problems-prepped.xml: $(errors_problems_SOURCES) $(errors_problems_IDMAPS)
-errors-problems-manprepped.xml: $(errors_problems_SOURCES) $(errors_problems_IDMAPS)
-errors-problems-remprepped.xml: $(errors_problems_SOURCES) $(errors_problems_IDMAPS)
+errors_problems_core_INCLUDES =
+errors_problems_core_IMAGES =
+errors_problems_core_SOURCES = errors-problems-core.xml $(errors_problems_core_INCLUDES)
+errors_problems_core_IDMAPS =
+errors-problems-core.validpure: $(errors_problems_core_SOURCES)
+errors-problems-core.titles: $(errors_problems_core_SOURCES)
+errors-problems-core.useless: $(errors_problems_core_SOURCES)
+errors-problems-core.valid: $(errors_problems_core_SOURCES) $(errors_problems_core_IDMAPS)
+errors-problems-core.validwarn: $(errors_problems_core_SOURCES) $(errors_problems_core_IDMAPS)
+errors-problems-core-prepped.xml: $(errors_problems_core_SOURCES) $(errors_problems_core_IDMAPS)
+errors-problems-core-manprepped.xml: $(errors_problems_core_SOURCES) $(errors_problems_core_IDMAPS)
+errors-problems-core-remprepped.xml: $(errors_problems_core_SOURCES) $(errors_problems_core_IDMAPS)
events_INCLUDES = \
../common/fixedchars.ent \
@@ -761,10 +777,12 @@
../refman-common/metadata/bug-reports.idmap \
../refman-common/metadata/information-sources.idmap \
metadata/dba-core.idmap \
+ metadata/errors-problems-core.idmap \
metadata/extending-mysql.idmap \
metadata/functions-core.idmap \
metadata/installing.idmap \
metadata/language-structure.idmap \
+ metadata/optimization.idmap \
metadata/se-innodb.idmap \
metadata/stored-procedures.idmap
extending-mysql.validpure: $(extending_mysql_SOURCES)
@@ -797,7 +815,7 @@
metadata/charset.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/extending-mysql.idmap \
metadata/faqs.idmap \
metadata/ha.idmap \
@@ -910,7 +928,7 @@
metadata/client-utility-programs.idmap \
metadata/connectors.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/extending-mysql.idmap \
metadata/faqs.idmap \
metadata/installing.idmap \
@@ -959,7 +977,7 @@
metadata/connectors.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/events.idmap \
metadata/extending-mysql.idmap \
metadata/faqs.idmap \
@@ -1006,7 +1024,7 @@
metadata/charset.idmap \
metadata/client-utility-programs.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/introduction.idmap \
metadata/language-structure.idmap \
@@ -1049,6 +1067,8 @@
../dynamic-docs/changelog/vstudioplugin-versions.xml \
../dynamic-docs/changelog/vstudioplugin.xml \
../dynamic-docs/command-optvars/mysqld.xml \
+ ../dynamic-docs/open-bugs/mysqld-versions.xml \
+ ../dynamic-docs/open-bugs/mysqld.xml \
../dynamic-docs/opsfunctions/opfunctions.xml \
../refman-common/../common/fixedchars.ent \
../refman-common/../refman-common/connector-j-connprops.xml \
@@ -1157,10 +1177,11 @@
dynxml-common-news-mysql-proxy.xml \
dynxml-common-news-vstudioplugin.xml \
dynxml-local-dba.xml \
+ dynxml-local-errors-problems.xml \
dynxml-local-functions.xml \
errmsgs-client.xml \
errmsgs-server.xml \
- errors-problems.xml \
+ errors-problems-core.xml \
events.xml \
extending-mysql.xml \
faqs.xml \
@@ -1322,7 +1343,7 @@
metadata/connectors.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/events.idmap \
metadata/extending-mysql.idmap \
metadata/faqs.idmap \
@@ -1894,7 +1915,7 @@
metadata/client-utility-programs.idmap \
metadata/connectors.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/extending-mysql.idmap \
metadata/functions-core.idmap \
metadata/information-schema.idmap \
@@ -1932,7 +1953,7 @@
partitioning_IDMAPS = \
metadata/client-utility-programs.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/information-schema.idmap \
metadata/installing.idmap \
@@ -1998,7 +2019,7 @@
replication_configuration_SOURCES = replication-configuration.xml $(replication_configuration_INCLUDES)
replication_configuration_IDMAPS = \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/mysql-cluster-ccsg-merge-temp.idmap \
metadata/optimization.idmap \
metadata/replication-configuration.idmap \
@@ -2049,7 +2070,7 @@
replication_notes_IDMAPS = \
../refman-common/metadata/bug-reports.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/information-schema.idmap \
metadata/replication-configuration.idmap \
@@ -2128,7 +2149,7 @@
../refman-common/metadata/bug-reports.idmap \
metadata/client-utility-programs.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/information-schema.idmap \
metadata/mysql-cluster-ccsg-merge-temp.idmap \
@@ -2306,7 +2327,7 @@
se_innodb_SOURCES = se-innodb.xml $(se_innodb_INCLUDES)
se_innodb_IDMAPS = \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/installing.idmap \
metadata/se-innodb.idmap \
metadata/sql-syntax.idmap \
@@ -2375,7 +2396,7 @@
metadata/charset.idmap \
metadata/client-utility-programs.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/installing.idmap \
metadata/optimization.idmap \
metadata/sql-syntax.idmap
@@ -2425,7 +2446,7 @@
metadata/client-utility-programs.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/events.idmap \
metadata/extending-mysql.idmap \
metadata/functions-core.idmap \
@@ -2491,7 +2512,7 @@
metadata/charset.idmap \
metadata/client-utility-programs.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/faqs.idmap \
metadata/installing.idmap \
metadata/language-structure.idmap \
@@ -2585,7 +2606,7 @@
metadata/client-utility-programs.idmap \
metadata/data-types.idmap \
metadata/dba-core.idmap \
- metadata/errors-problems.idmap \
+ metadata/errors-problems-core.idmap \
metadata/functions-core.idmap \
metadata/installing.idmap \
metadata/introduction.idmap \
Copied: trunk/refman-5.1/errors-problems-core.xml (from rev 8194, trunk/refman-5.1/errors-problems.xml)
===================================================================
--- trunk/refman-5.1/errors-problems-core.xml (rev 0)
+++ trunk/refman-5.1/errors-problems-core.xml 2007-10-19 15:11:56 UTC (rev 8195)
@@ -0,0 +1,5457 @@
+<?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 % all.entities SYSTEM "all-entities.ent">
+ %all.entities;
+]>
+<appendix id="error-handling">
+
+ <title>Errors, Error Codes, and Common Problems</title>
+
+ <remark role="dynamic-dependency-list"/>
+
+ <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>
+
+ <formalpara role ="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ The MySQL Enterprise Monitor provides a <quote>Virtual DBA</quote>
+ to assist with problem solving. For more information see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ <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, you can connect via
+ TCP/IP. If the server 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>
+
+ <note>
+ <para>
+ 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>
+ </note>
+
+ <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 Enterprise Monitor 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 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
+ <ulink url="http://forge.mysql.com/wiki/MySQL_Internals_Porting">MySQL
+ Internals: Porting</ulink>.
+ </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 Enterprise Monitor 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>
+ 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>
+OS error code 28: No space left on device
+</programlisting>
+
+ <para>
+ If you get an error of the following type during startup, it
+ indicates that the filesystem and/or directory used for
+ storing data files is write protected. Providing the write
+ error is to a test file, This error is not serious and can be
+ safely ignored.
+ </para>
+
+<programlisting>Can't create test file /usr/local/mysql/data/master.lower-test</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>
+OS error code 23: File table overflow
+shell> <userinput>perror 24</userinput>
+OS error code 24: Too many open files
+shell> <userinput>perror 11</userinput>
+OS 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 <quote><literal>#</literal></quote>
+ 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>
+
+ <note>
+ <para>
+ 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>
+ </note>
+
+ </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 --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.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>
+
+ <formalpara role ="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ For expert advice on security-related issues, subscribe to
+ the MySQL Enterprise Monitor. For more information see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ <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
+ <ulink url="http://forge.mysql.com/wiki/MySQL_Internals_Porting">MySQL
+ Internals: Porting</ulink>. 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
+ <ulink url="http://forge.mysql.com/wiki/MySQL_Internals_Porting">MySQL
+ Internals: Porting</ulink>.
+ </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
+ <ulink url="http://forge.mysql.com/wiki/MySQL_Internals_Porting">MySQL
+ Internals: Porting</ulink>.
+ </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
+ <ulink url="http://forge.mysql.com/wiki/MySQL_Internals_Porting">MySQL
+ Internals: Porting</ulink>.
+ </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>
+
+ <formalpara role ="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ For early notification of possible problems with your MySQL
+ configuration subscribe to the MySQL Enterprise Monitor. For
+ more information see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ </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 (<quote><literal>:</literal></quote>) on Unix and
+ semicolon characters (<quote><literal>;</literal></quote>) on
+ Windows, NetWare, and OS/2.
+ </para>
+
+ <note>
+ <para>
+ To spread the load effectively, these paths should be
+ located on different <emphasis>physical</emphasis> disks,
+ not different partitions of the same disk.
+ </para>
+ </note>
+
+ <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 Enterprise Monitor
+ 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. 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 <quote><literal>E</literal></quote>,
+ <quote><literal>e</literal></quote>, and
+ <quote><literal>é</literal></quote>) 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>
+
+ <formalpara role ="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ Ensure that your data is adequately protected by subscribing
+ to the MySQL Enterprise Monitor. For more information see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ </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 65 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:
+ </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. See
+ <xref linkend="index-hints"/>.
+ </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>
+
+ <formalpara role ="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ For expert advice on configuring MySQL servers for optimal
+ performance, subscribe to the MySQL Enterprise Monitor. For
+ more information see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ </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
+ <ulink url="http://forge.mysql.com/wiki/MySQL_Internals_Porting">MySQL
+ Internals: Porting</ulink>.
+ </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 condition="dynamic:openbugs:openbugslist" role="5.1:mysqld:all"/>
+
+</section>
+
+<section id="open-bugs-general">
+<title>Additional Known Issues</title>
+ <para>
+ In addition, the following problems are also known and fixing them is also 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>
+ 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 <quote><literal>_</literal></quote> or
+ <quote><literal>%</literal></quote> with
+ <literal>ESCAPE</literal> in <literal>LIKE ...
+ ESCAPE</literal>.
+ </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/manual.xml
===================================================================
--- trunk/refman-5.1/manual.xml 2007-10-19 14:43:46 UTC (rev 8194)
+++ trunk/refman-5.1/manual.xml 2007-10-19 15:11:56 UTC (rev 8195)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 592 bytes
@@ -92,7 +92,7 @@
<xi:include href="faqs.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="dynxml-local-errors-problems.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
<xi:include href="news.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r8195 - trunk/refman-5.1 | mcbrown | 19 Oct |
