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.
Access denied 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
Section 5.8.8, “Causes of
Access denied Errors”, and
Section 5.8.2, “How the Privilege System Works”.
A MySQL client on Unix can connect to the
mysqld server in two different ways: By using
a Unix socket file to connect through a file in the filesystem
/tmp/mysql.sock), 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
If the MySQL server is running on Windows 9x or Me, you can
connect only via TCP/IP. If the server is running on Windows NT,
2000, XP, or 2003 and is started with the
--enable-named-pipe 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
MySQL by default. If you don't give a
hostname when connecting to mysqld, 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
. as the
The error (2002)
Can't connect to ...
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.
The error (2003)
Can't connect to MySQL server on
server' (10061) 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.
Start by checking whether there is a process named mysqld running on your server host. (Use ps xa | grep mysqld on Unix or the Task Manager on Windows.) If there is no such process, you should start the server. See Section 126.96.36.199, “Starting and Troubleshooting the MySQL Server”.
If a mysqld 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.
host_ip represents the IP number of the
machine where the server is running.
mysqladmin -h `hostname` version variablesshell>
mysqladmin -h `hostname` --port=3306 versionshell>
mysqladmin -h host_ip versionshell>
mysqladmin --protocol=socket --socket=/tmp/mysql.sock version
Note the use of backticks rather than forward quotes with the
hostname command; these cause the output of
hostname (that is, the current hostname) to
be substituted into the mysqladmin command.
If you have no
hostname command or are
running on Windows, you can manually type the hostname of your
machine (without backticks) following the
option. You can also try
-h 127.0.0.1 to
connect with TCP/IP to the local host.
Here are some reasons the
Can't connect to local MySQL
server error might occur:
mysqld is not running. Check your operating system's process list to ensure the mysqld process is present.
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: Section A.2.2.1, “
Connection to MySQL Server Failing on Windows”.
You are running on a system that uses MIT-pthreads. If you are running on a system that doesn't have native threads, mysqld uses the MIT-pthreads package. See Section 2.1.1, “Operating Systems Supported by MySQL”. 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:
mysqladmin -h `hostname` version
Someone has removed the Unix socket file that mysqld uses (
/tmp/mysql.sockby default). For example, you might have a cron job that removes old files from the
/tmpdirectory. You can always run mysqladmin version to check whether the Unix socket file that mysqladmin is trying to use really exists. The fix in this case is to change the cron job to not remove
mysql.sockor to place the socket file somewhere else. See Section A.4.5, “How to Protect or Change the MySQL Unix Socket File”.
You have started the mysqld server with the
--socket=/path/to/socketoption, 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
--socketoption when you run client programs. You also need to ensure that clients have permission to access the
mysql.sockfile. To find out where the socket file is, you can do:
netstat -ln | grep mysql
You are using Linux and one server thread has died (dumped core). In this case, you must kill the other mysqld threads (for example, with
killor with the
mysql_zapscript) before you can restart the MySQL server. See Section A.4.2, “What to Do If MySQL Keeps Crashing”.
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 mysqld with a
--socketoption that specifies a socket filename in a directory where the server can create it and where client programs can access it.
If you get the error message
Can't connect to MySQL
server on some_host, you can try the following things
to find out what the problem is:
Check whether the server is running on that host by executing
telnet some_host 3306and 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
telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port.
If the server is running on the local host, try using mysqladmin -h localhost variables 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
Make sure that your mysqld server was not started with the
--skip-networkingoption. If it was, you cannot connect to it using TCP/IP.
Check to make sure that there is no firewall blocking access to MySQL. Applications such as ZoneAlarm and the Windows XP personal firewall may need to be configured to allow external access to a MySQL server.
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
Can't connect to MySQL
server error, the reason might be that Windows
doesn't allow for enough ephemeral (short-lived) ports to
serve those connections.
By default, Windows allows 5000 ephemeral (short-lived) TCP
ports to the user. After any port is closed it will remain in
TIME_WAIT 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.
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
TIME_WAIT status you
have a good chance for running out of ports. There are two
ways to address this problem:
Reduce the number of TCP ports consumed quickly by investigating connection pooling or persistent connections where possible
Tune some settings in the Windows registry (see below)
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: http://support.microsoft.com/kb/256986/EN-US/.
Start Registry Editor (
Locate the following key in the registry:
Add Value, and then add the following registry value:
Value Name: MaxUserPort Data Type: REG_DWORD Value: 65534
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).
Add Value, and then add the following registry value:
Value Name: TcpTimedWaitDelay Data Type: REG_DWORD Value: 30
This sets the number of seconds to hold a TCP port connection in
TIME_WAITstate before closing. The valid range is between 0 (zero) and 300 (decimal). The default value is 0x78 (120 decimal).
Quit Registry Editor.
Reboot the machine.
Note: Undoing the above should be as simple as deleting the registry entries you've created.
MySQL 5.0 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:
mysqlClient does not support authentication protocol requested by server; consider upgrading MySQL client
To solve this problem, you should use one of the following approaches:
Upgrade all client programs to use a 4.1.1 or newer client library.
When connecting to the server with a pre-4.1 client program, use an account that still has a pre-4.1-style password.
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
SET PASSWORDstatement and the
SET PASSWORD FOR->
some_host' = OLD_PASSWORD('
UPDATE mysql.user SET Password = OLD_PASSWORD('
WHERE Host = '
some_host' AND User = '
Substitute the password you want to use for “
newpwd” in the preceding examples. MySQL cannot tell you what the original password was, so you'll need to pick a new one.
Tell the server to use the older password hashing algorithm:
Start mysqld with the
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:
SELECT Host, User, Password FROM mysql.user->
WHERE LENGTH(Password) > 16;
For each account record displayed by the query, use the
Uservalues and assign a password using the
OLD_PASSWORD()function and either
UPDATE, as described earlier.
Note: In older versions of PHP,
mysql 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
mysql 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
mysqli 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
mysqli extension, see
It may also be possible to compile the older
mysql 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
MySQL with PHP
For additional background on password hashing and authentication, see Section 5.8.9, “Password Hashing as of MySQL 4.1”.
MySQL client programs prompt for a password when invoked with a
-p option that
has no following password value:
user_name-p Enter password:
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
password: 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.
If you get the following error, it means that
mysqld has received many connect requests
from the host
have been interrupted in the middle:
host_name' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
The number of interrupted connect requests allowed is determined
by the value of the
requests, mysqld 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
mysqladmin flush-hosts command or issue a
FLUSH HOSTS statement. See
Section 5.2.2, “Server System Variables”.
By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:
mysqld_safe --max_connect_errors=10000 &
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
If you get a
Too many connections error when
you try to connect to the mysqld server, this
means that all available connections are in use by other
The number of connections allowed is controlled by the
max_connections system variable. Its default
value is 100. If you need to support more connections, you
should restart mysqld with a larger value for
mysqld actually allows
max_connections+1 clients to connect. The
extra connection is reserved for use by accounts that have the
SUPER privilege. By granting the
SUPER privilege to administrators and not to
normal users (who should not need it), an administrator can
connect to the server and use
PROCESSLIST to diagnose problems even if the maximum
number of unprivileged clients are connected. See
Section 188.8.131.52, “
SHOW PROCESSLIST Syntax”.
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.
If you issue a query using the mysql client program and receive an error like the following one, it means that mysql does not have enough memory to store the entire query result:
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
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
mysql with the
option. This causes it to use the
mysql_use_result() C API function to retrieve
the result set, which places less of a load on the client (but
more on the server).
This section also covers the related
Lost connection to
server during query error.
The most common reason for the
MySQL server has gone
away 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):
||The client couldn't send a question to the server.|
||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.|
By default, the server closes the connection after eight hours
if nothing has happened. You can change the time limit by
wait_timeout variable when you
start mysqld. See
Section 5.2.2, “Server System Variables”.
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
Some other common reasons for the
MySQL server has gone
away error are:
You (or the db administrator) has killed the running thread with a
KILLstatement or a mysqladmin kill command.
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.
A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host.
You got a timeout from the TCP/IP connection on the client side. This may happen if you have been using the commands:
mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...). In this case increasing the timeout may help solve the problem.
You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the
reconnectflag in the
MYSQLstructure is equal to 0).
You are using a Windows client and the server had dropped the connection (probably because
wait_timeoutexpired) before the command was issued.
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 connection.
Prior to MySQL 5.0.19, even if the
reconnectflag in the
MYSQLstructure 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.
The solution to this is to either do a
mysql_pingon the connection if there has been a long time since the last query (this is what
MyODBCdoes) or set
wait_timeouton the mysqld server so high that it in practice never times out.
You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld 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
BLOBcolumns), you can increase the query limit by setting the server's
max_allowed_packetvariable, 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 Section A.2.9, “
Packet too large”.
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.
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.
You may also see the
MySQL server has gone awayerror if MySQL is started with the
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.
Another networking issue that can cause this error occurs if if the MySQL port (default 3306) is blocked by your firewall, thus preventing any connections at all to the MySQL server.
You have encountered a bug where the server died while executing the query.
You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server's uptime. If the client connection was broken because mysqld 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 Section A.4.2, “What to Do If MySQL Keeps Crashing”.
You can get more information about the lost connections by
starting mysqld with the
option. This logs some of the disconnected errors in the
hostname.err file. See
Section 5.12.1, “The Error Log”.
If you want to create a bug report regarding this problem, be sure that you include the following information:
Indicate whether the MySQL server died. You can find information about this in the server error log. See Section A.4.2, “What to Do If MySQL Keeps Crashing”.
If a specific query kills mysqld and the tables involved were checked with
CHECK TABLEbefore you ran the query, can you provide a reproducible test case? See Section E.1.6, “Making a Test Case If You Experience Table Corruption”.
What is the value of the
wait_timeoutsystem variable in the MySQL server? (mysqladmin variables gives you the value of this variable.)
Have you tried to run mysqld with the
--logoption to determine whether the problem query appears in the log?
A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.
The largest possible packet that can be transmitted to or from a MySQL 5.0 server or client is 1GB.
When a MySQL client or the mysqld server
receives a packet bigger than
max_allowed_packet bytes, it issues a
Packet too large error and closes the
connection. With some clients, you may also get a
connection to MySQL server during query error if the
communication packet is too large.
Both the client and the server have their own
max_allowed_packet variable, so if you want
to handle big packets, you must increase this variable both in
the client and in the server.
If you are using the mysql client program,
max_allowed_packet variable is
16MB. To set a larger value, start mysql like
That sets the packet size to 32MB.
The server's default
is 1MB. You can increase this if the server needs to handle big
queries (for example, if you are working with big
BLOB columns). For example, to set the
variable to 16MB, start the server like this:
You can also use an option file to set
max_allowed_packet. For example, to set the
size for the server to 16MB, add the following lines in an
It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld 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.
You can also get strange problems with large packets if you are
BLOB values but have not given
mysqld access to enough memory to handle the
query. If you suspect this is the case, try adding
ulimit -d 256000 to the beginning of the
mysqld_safe script and restarting
The server error log can be a useful source of information about
connection problems. See Section 5.12.1, “The Error Log”. If you
start the server with the
option, you might find messages like this in your error log:
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
Aborted connections messages appear in the
error log, the cause can be any of the following:
The client program did not call
The client had been sleeping more than
interactive_timeoutseconds without issuing any requests to the server. See Section 5.2.2, “Server System Variables”.
The client program ended abruptly in the middle of a data transfer.
When any of these things happen, the server increments the
Aborted_clients status variable.
The server increments the
status variable when the following things happen:
A client doesn't have privileges to connect to a database.
A client uses an incorrect password.
A connection packet doesn't contain the right information.
It takes more than
connect_timeoutseconds to get a connect packet. See Section 5.2.2, “Server System Variables”.
If these kinds of things happen, it might indicate that someone is trying to break into your server!
Other reasons for problems with aborted clients or aborted connections:
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.
Some problem with the thread library that causes interrupts on reads.
Badly configured TCP/IP.
Faulty Ethernets, hubs, switches, cables, and so forth. This can be diagnosed properly only by replacing hardware.
max_allowed_packetvariable value is too small or queries require more memory than you have allocated for mysqld. See Section A.2.9, “
Packet too large”.
There are several ways a full-table error can occur:
You are using a MySQL server older than 3.23 and an in-memory temporary table becomes larger than
tmp_table_sizebytes. To avoid this problem, you can use the
valoption to make mysqld increase the temporary table size or use the SQL option
SQL_BIG_TABLESbefore you issue the problematic query. See Section 13.5.3, “
You can also start mysqld with the
--big-tablesoption. This is exactly the same as using
SQL_BIG_TABLESfor all queries.
As of MySQL 3.23, this problem should not occur. If an in-memory temporary table becomes larger than
tmp_table_size, the server automatically converts it to a disk-based
You are using
InnoDBtables and run out of room in the
InnoDBtablespace. In this case, the solution is to extend the
InnoDBtablespace. See Section 14.2.7, “Adding and Removing
InnoDBData and Log Files”.
You are using
MyISAMtables 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.
You are using a
MyISAMtable and the space required for the table exceeds what is allowed by the internal pointer size. If you don't specify the
MAX_ROWStable option when you create a table, MySQL uses the
myisam_data_pointer_sizesystem variable. From MySQL 5.0.6 on, the default value is 6 bytes, which is enough to allow 256TB of data. Before MySQL 5.0.6, the default value is 4 bytes, which is enough to allow only 4GB of data. See Section 5.2.2, “Server System Variables”.
You can check the maximum data/index sizes by using this statement:
SHOW TABLE STATUS FROM database LIKE '
You also can use myisamchk -dv /path/to/table-index-file.
If the pointer size is too small, you can fix the problem by using
You have to specify
AVG_ROW_LENGTHonly for tables with
TEXTcolumns; in this case, MySQL can't optimize the space required based only on the number of rows.
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:
Can't create/write to file '\\sqla3fe_0.ism'.
The preceding error is a typical message for Windows; the Unix message is similar.
One fix is to start mysqld with the
--tmpdir option or to add the option to the
[mysqld] section of your option file. For
example, to specify a directory of
use these lines:
C:\temp directory must exist and have
sufficient space for the MySQL server to write to. See
Section 4.3.2, “Using Option Files”.
Another cause of this error can be permissions issues. Make sure
that the MySQL server can write to the
Check also the error code that you get with perror. One reason the server cannot write to a table is that the filesystem is full:
perror 28Error code 28: No space left on device
If you get
Commands out of sync; you can't run this
command now in your client code, you are calling
client functions in the wrong order.
This can happen, for example, if you are using
mysql_use_result() and try to execute a new
query before you have called
mysql_free_result(). It can also happen if
you try to execute two queries that return data without calling
mysql_store_result() in between.
If you get the following error, it means that when
mysqld was started or when it reloaded the
grant tables, it found an account in the
table that had an invalid password.
Found wrong password for user
As a result, the account is simply ignored by the permission system.
The following list indicates possible causes of and fixes for this problem:
You may be running a new version of mysqld with an old
usertable. You can check this by executing mysqlshow mysql user to see whether the
Passwordcolumn is shorter than 16 characters. If so, you can correct this condition by running the
The account has an old password (eight characters long) and you didn't start mysqld with the
--old-protocoloption. Update the account in the
usertable to have a new password or restart mysqld with the
You have specified a password in the
usertable without using the
PASSWORD()function. Use mysql to update the account in the
usertable with a new password, making sure to use the
UPDATE user SET Password=PASSWORD('
some_user' AND Host='
If you get either of the following errors, it usually means that no table exists in the default database with the given name:
tbl_name' doesn't exist Can't find file: '
tbl_name' (errno: 2)
In some cases, it may be that the table does exist but that you are referring to it incorrectly:
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.
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.
You can check which tables are in the default database with
SHOW TABLES. See Section 13.5.4, “
You might see an error like this if you have character set problems:
MySQL Connection Failed: Can't initialize character set
This error can have any of the following causes:
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 configure with the
charset_nameoption. See Section 2.9.2, “Typical configure Options”.
All standard MySQL binaries are compiled with
--with-extra-character-sets=complex, which enables support for all multi-byte character sets. See Section 5.11.1, “The Character Set Used for Data and Sorting”.
The character set is a simple character set that is not compiled into mysqld, and the character set definition files are not in the place where the client expects to find them.
In this case, you need to use one of the following methods to solve the problem:
Recompile the client with support for the character set. See Section 2.9.2, “Typical configure Options”.
Specify to the client the directory where the character set definition files are located. For many clients, you can do this with the
Copy the character definition files to the path where the client expects them to be.
If you get
ERROR '...' not found (errno: 23),
Can't open file: ... (errno: 24), or any
other error with
errno 23 or
24 from MySQL, it means that you haven't allocated
enough file descriptors for the MySQL server. You can use the
perror utility to get a description of what
the error number means:
perror 23Error code 23: File table overflow shell>
perror 24Error code 24: Too many open files shell>
perror 11Error code 11: Resource temporarily unavailable
The problem here is that mysqld is trying to keep open too many files simultaneously. You can either tell mysqld not to open so many files at once or increase the number of file descriptors available to mysqld.
To tell mysqld to keep open fewer files at a
time, you can make the table cache smaller by reducing the value
table_cache system variable (the
default value is 64). Reducing the value of
max_connections also reduces the number of
open files (the default value is 100).
To change the number of file descriptors available to
mysqld, you can use the
--open-files-limit option to
mysqld_safe or (as of MySQL 3.23.30) set the
open_files_limit system variable. See
Section 5.2.2, “Server System Variables”. The easiest way to
set these values is to add an option to your option file. See
Section 4.3.2, “Using Option Files”. If you have an old version of
mysqld that doesn't support setting the open
files limit, you can edit the mysqld_safe
script. There is a commented-out line ulimit -n
256 in the script. You can remove the
#’ character to uncomment this
line, and change the number
256 to set the
number of file descriptors to be made available to
ulimit can increase the number of file
descriptors, but only up to the limit imposed by the operating
system. There is also a “hard” limit that can be
overridden only if you start mysqld_safe or
remember that you also need to start the server with the
--user option in this case so that it does not
continue to run as
root 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.
Note: If you run the tcsh shell, ulimit does not work! tcsh also reports incorrect values when you ask for the current limits. In this case, you should start mysqld_safe using sh.