A.2. Errores comunes al usar programas MySQL

MySQL 5.0

A.2. Errores comunes al usar programas MySQL

Esta sección enumera algunos errores que los usuarios encuentran de manera frecuente cuando ejecutan programas MySQL. Aunque los problemas se muestran cuando intenta ejecutar programas cliente, las soluciones a muchos de los problems pasan por cambios en la configuración del servidor MySQL.

A.2.1. Access denied

Un error de puede tener muchas causas. Frecuentemente el problema está relacionado con las cuentas MySQL a las que el servidor deja que se conecten los programas cliente. Consulte Sección 5.6.8, “Causas de errores . Consulte Sección 5.6.2, “Cómo funciona el sistema de privilegios”.

A.2.2. Can't connect to [local] MySQL server

Un cliente MySQL en Unix puede conectarse al servidor mysqld de dos maneras diferentes: Utilizando un archivo socket de Unix para conectarse a través de un archivo en el sistema de ficheros (por defecto ), o utilizando TCP/IP, que se conecta a través de un número de puerto. Una conexión a través de archivo socket de Unix es más rápida que a través de TCP/IP, pero solo puede ser utilizada cuando se conecta a un servidor en la misma máquina. Se utiliza un archivo de socket Unix siempre que no se especifique un nombre de servidor o si se especifica el nombre de servidor especial .

Si el servidor MySQL está ejecutándose en Windows 9x o Me, puede conectarse únicamente a través de TCP/IP. Si el servidor se está ejecutando sobre Windows NT, 2000, XP, o 2003 y ha sido iniciado con la opción , puede también conectarse a través de named pipes si el cliente se está ejecutando en la misma máquina que el servidor. El nombre de la named pipe es por defecto . Si no se especifica un nombre de servidor al conectar a mysqld, un cliente MySQL intenta primero conectarse a la named pipe. Si esto no funciona, se conecta al puerto TCP/IP. Usted puede forzar la utilizaciónde named pipes en windows utilizando como el nombre de servidor. hostname.

El error (2002) normalmente significa que no hay un servidor MySQL ejecutándose en el sistema o que usted está especificando un archivo de socket Unix o número de puerto TCP/IP al intentar conectarse al servidor.

Comience por comprobar si hay un proceso llamado mysqld ejecutándose en el servidor. (Utilice ps xa | grep mysqld en Unix o el Administrador de tareas en Windows). Si no existe ese proceso, debería iniciar el servidor. Consulte Sección 2.9.2.3, “Arrancar y resolver problemas del servidor MySQL”.

Si hay un proceso mysqld ejecutándose, puede comprobarlo ejecutando los siguientes comandos. El número de puerto o nombre del archivo socket de Unix pueden ser diferentes en su configuración. representa el número de IP de la máquina donde se está ejecutando el servidor.represents the IP number of the machine where the server is running.

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version

Tenga en cuenta la utilización de acentos abiertos en vez de comillas en el comando ; esto provoca que la salida de (es decir, el nombre de máquina actual) sea sustituida en el comando mysqladmin. Si no tiene ningún comando o está ejecutando sobre Windows, puede escribir manualmente el nombre de su máquina (sin acentos abiertos) tra la opción . También puede intentarlo con para conectarse con TCP/IP a la máquina local.

Aquí hay algunas razones por las que el error podría ocurrir:

  • mysqld no se está ejecutando. Compruebe la lista de procesos de sus sistema operativo para asegurarse de que el proceso mysqld está presente.

  • Usted está ejecutando un sistema que utiliza hilos tipo MIT-pthreads. Si está ejecutando un sistema qeu no tiene hilos antivos, mysqld utiliza el paquete de MIT-pthreads package. Consulte Sección 2.1.1, “Sistemas operativos que MySQL soporta”. Aún así, no todas las versiones de MIT-pthreads soportan los archivos socket de Unix. En un sistema sin soporte para archivos socket, siempre debe especificar el nombre de máquina explícitamente cuando se conecte al servidor. Intente utilizar este comando para comprobar la conexión con el servidor:

    shell> mysqladmin -h `hostname` version
    
  • Alguien ha borrado el archivo socket de Unix que mysqld utiliza ( por defecto). Por ejemplo, usted podría tener un trabajo de cron que elimine los archivos antiguos del directorio . Siempre puede ejecutar mysqladmin version para comprobar si el archivo socket de Unix que to check whether the Unix socket file that mysqladmin está intentando utilizar existe realmente. La solución en este caso es cambiar el trabajo de cron para que no elimine o colocar el archivo socket en algún otro lugar. Consulte Sección A.4.5, “Cómo proteger o cambiar el fichero socket de MySQL .

  • Usted ha iniciado el servidor mysqld con la opción , pero ha olvidado decirle al programa cliente el nuevo nombre del archivo socket. Si cambia la ruta del socket en el servidor, también tiene que notificárselo a los programas cliente. Puede hacer esto proporcionándole al cliente la misma opción al ejecutarlo. También debe asegurarse de que los programas cliente tienen permiso para acceder al archivo . Para averiguar donde está almacenado el archivo, puede hacer:

    shell> netstat -ln | grep mysql
    

    Consulte Sección A.4.5, “Cómo proteger o cambiar el fichero socket de MySQL .

  • Usted está ejecutando Linux y un hilo del servidor ha muerto (volcado de memoria). En este caso, usted debe matar el resto de hilos de mysqld (por ejemplo, con o con el script ) antes de que pueda reiniciar el servidor MySQL. Consulte Sección A.4.2, “Qué hacer si MySQL sigue fallando (crashing)”.

  • El servidor o el programa cliente podrían no tener los privilegios de acceso adecuados para el directorio que almacena el archivo socket de Unix, o para el archivo mismo. En este caso, usted debe cambiar los privilegios del directorio o los del archivo mismo para que el servidor y los clientes puedan acceder a ellos, o reiniciar mysqld con una opción que especifique un nombre de archivo de socket en un directorio donde el servidor pueda crearlo y los programas cliente puedan acceder a él.

Si usted obtiene el mensaje de error , puede intentar los siguientes procedimientos para averiguar cual es el problema:

  • Compruebe si el servidor se está ejecutando en esa máquina mediante la ejecución de y presionando la tecla Enter unas cuantas veces. (3306 es el puerto por defecto de MySQL. Cambie el valor si su servidor está escuchando en un puerto diferente.) Si hay un servidor MySQL ejecutándose y escuchando al puerto, debería obtener una respuesta que incluyera el número de versión del servidor. Si obtiene un error como , entonces no hay ningún servidor ejecutándose en el puerto dado.

  • Si el servidor está ejecutándose en la máquina local, intente utilizar mysqladmin -h localhost variables para conectar utilizando el archivo socket de Unix. Colmpruebe el número de puerto TCP/IP al que el servidor está configurado para escuchar (es el valor de la variable .)

  • Asegúrese de que su servidor mysqld no fue iniciado utilizando la opción . Si lo fue no puede conectarse a él utilizando TCP/IP.

  • Compruebe que no hay un cortafuegos bloqueando el acceso a MySQL. Aplicaciones como ZoneAlarm o el cortafuegos personal de Windows XP podría necesitar ser configurados para permitir el acceso externo a un servidor MySQL.

A.2.3. Client does not support authentication protocol

Las versiones de MySQL número 4.1 y superiores utilizan un protocolo de autentificación basado en un algoritmo de hash de la clave que es incompatible con el que se utiliza en los clientes anteriores. Si actualiza su servidor a 4.1, los intentos de conectarse a él desde un cliente más viejo pueden fallar con el siguiente mensaje:

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

Para resolver este problema, debería utilizar alguno de los siguientes métodos:

  • Actualizar todos los programas clientes para que utilicen la librería de cliente 4.1.1 o posterior.

  • Cuando se conecte al servidor con un programa cliente anterior al 4.1, utilice una cuenta que todavía mantenga una clave al estilo pre-4.1.

  • Reestablezca la clave al estilo pre-4.1 para cada usuario que necesite utilizar un programa cliente anterior a la versión 4.1. Esto puede hacerse utilizando la sentencia y la función :

    mysql> SET PASSWORD FOR
        -> ''@'' = OLD_PASSWORD('');
    

    Una alternativa es utilizar y :

    mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('')
        -> WHERE Host = '' AND User = '';
    mysql> FLUSH PRIVILEGES;
    

    Sustituya la clave que quiera utilizar por “” en los ejemplos precedentes. MySQL no puede retornar la clave original, así que es necesario introducir una clave nueva.

  • Indique al servidor que utilice el algoritmo de hashing de claves antiguo:

    1. Inicie mysqld con la opción .

    2. Asigne una clave con formato antiguo a cada cuenta que tenga su clave actualizada al formato más largo de la versión 4.1. Puede identificar estas cuentas con la siguiente consulta:

      mysql> SELECT Host, User, Password FROM mysql.user
          -> WHERE LENGTH(Password) > 16;
      

      Para cada registro de cuentas que se muestre en la consulta, utilice los valores de y y asigne una clave utilizando la función y o , tal como se ha explicado previamente.

Nota: En PHP, la extensión no soporta el nuevo protocolo de autentificación en MySQL 4.1.1 y superior. Esto es así independientemente de la versión de PHP utilizada. Si desea poder utilizar la extensión con MySQL 4.1 seguir alguna de las indicaciones explicadas arriba para configurar MySQL con clientes antiguos. La extensión (que significa "MySQL mejorado" - "MySQL Improved"; nueva en PHP 5) es compatible con el nuevo algoritmo de hashing mejorado empleado en MySQL 4.1 y superiores, y sin ninguna configuración especial necesaria que deba hacerse para utilizar esta nueva librería cliente de MySQL para PHP. Para más información sobre la extensión consulte http://php.net/mysqli.

For additional background on password hashing and authentication, see Sección 5.6.9, “Hashing de contraseñas en MySQL 4.1”.

A.2.4. La contraseña falla cuando se introduce interactivamente

MySQL client programs prompt for a password when invoked with a or option that has no following password value:

shell> mysql -u  -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 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.

A.2.5. Host 'host_name' is blocked

If you get the following error, it means that mysqld has received many connect requests from the host ' that have been interrupted in the middle:

Host '' 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 system variable. After failed 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 statement. See Sección 5.3.3, “Variables de sistema del servidor”.

By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell> 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 variable.

A.2.6. Too many connections

If you get a error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections allowed is controlled by the system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.

mysqld actually allows clients to connect. The extra connection is reserved for use by accounts that have the privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use to diagnose problems even if the maximum number of unprivileged clients are connected. See Sección 13.5.4.16, “Sintaxis de .

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.

A.2.7. Out of memory

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 C API function to retrieve the result set, which places less of a load on the client (but more on the server).

A.2.8. MySQL server has gone away

This section also covers the related error.

The most common reason for the 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):

Error Code Description
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 setting the variable when you start mysqld. See Sección 5.3.3, “Variables de sistema del servidor”.

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 command-line client).

Some other common reasons for the error are:

  • You (or the db administrator) has killed the running thread with a statement 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.

  • You got a timeout from the TCP/IP connection on the client side. This may happens if you have been using the commands: or . 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 flag in the structure is equal to 0).

  • You are using a windows client and the server had dropped the connection (probably because expired) 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.

    In this case, even if the flag in the 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.

    The solution to this is to either do a on the connection if there has been a long time since the last query (this is what does) or set on 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 columns), you can increase the query limit by setting the server's 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 Sección A.2.9, “.

  • 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.

  • You may also see the error if MySQL is started with the option.

  • 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 Sección A.4.2, “Qué hacer si MySQL sigue fallando (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 file. See Sección 5.10.1, “El registro de errroes (Error Log)”.

If you want to create a bug report regarding this problem, be sure that you include the following information:

See also See Sección A.2.10, “Errores de comunicación y conexiones abortadas”.

See Sección 1.6.1.2, “Hacer preguntas y reportar bugs”.

A.2.9. Packet too large

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.

When a MySQL client or the mysqld server receives a packet bigger than bytes, it issues a error and closes the connection. With some clients, you may also get a error if the communication packet is too large.

Both the client and the server have their own 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, its default variable is 16MB. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, start mysql like this:

mysql> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big columns). For example, to set the variable to 16MB, start the server like this:

mysql> mysqld --max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

mysql> mysqld --set-variable=max_allowed_packet=16M

You can also use an option file to set . For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]
max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = max_allowed_packet=16M

It's 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 don't run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large 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 mysqld.

A.2.10. Errores de comunicación y conexiones abortadas

The server error log can be a useful source of information about connection problems. See Sección 5.10.1, “El registro de errroes (Error Log)”. Starting with MySQL 3.23.40, if you start the server with the option (or from MySQL 4.0.3 on), you might find messages like this in your error log:

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

If messages appear in the error log, the cause can be any of the following:

  • The client program did not call before exiting.

  • The client had been sleeping more than or seconds without issuing any requests to the server. See Sección 5.3.3, “Variables de sistema del servidor”.

  • The client program ended abruptly in the middle of a data transfer.

When any of these things happen, the server increments the 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 seconds to get a connect packet. See Sección 5.3.3, “Variables de sistema del servidor”.

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.

  • The variable value is too small or queries require more memory than you have allocated for mysqld. See Sección A.2.9, “.

See also See Sección A.2.8, “.

A.2.11. The table is full

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 bytes. To avoid this problem, you can use the option to make mysqld increase the temporary table size or use the SQL option before you issue the problematic query. See Sección 13.5.3, “Sintaxis de .

    You can also start mysqld with the option. This is exactly the same as using for all queries.

    As of MySQL 3.23, this problem should not occur. If an in-memory temporary table becomes larger than , the server automatically converts it to a disk-based table.

  • You are using tables and run out of room in the tablespace. In this case, the solution is to extend the tablespace. See Sección 15.7, “Añadir y suprimir registros y ficheros de datos .

  • You are using or 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.

  • You are using a table and the space required for the table exceeds what is allowed by the internal pointer size. If you don't specify the table option when you create a table, MySQL uses the system variable. From MySQL 5.0.6 on, the default value is 6 bytes, which is enough to allow 65,536TB of data. Before MySQL 5.0.6, the default value is 4 bytes, which is enough to allow only 4GB of data. See Sección 5.3.3, “Variables de sistema del servidor”.

    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 :

    ALTER TABLE  MAX_ROWS=1000000000 AVG_ROW_LENGTH=;
    

    You have to specify only for tables with or columns; in this case, MySQL can't optimize the space required based only on the number of rows.

A.2.12. Can't create/write to file

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 option or to add the option to the section of your option file. For example, to specify a directory of , use these lines:

[mysqld]
tmpdir=C:/temp

The directory must exist and have sufficient space for the MySQL server to write to. See Sección 4.3.2, “Usar ficheros de opciones”.

Another cause of this error can be permissions issues. Make sure that the MySQL server can write to the directory.

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:

shell> perror 28
Error code  28:  No space left on device

A.2.13. Commands out of sync

If you get in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using and try to execute a new query before you have called . It can also happen if you try to execute two queries that return data without calling or in between.

A.2.14. Ignoring user

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.

'@''; ignoring 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 table. You can check this by executing mysqlshow mysql user to see whether the column is shorter than 16 characters. If so, you can correct this condition by running the script.

  • The account has an old password (eight characters long) and you didn't start mysqld with the option. Update the account in the table to have a new password or restart mysqld with the option.

  • You have specified a password in the table without using the function. Use mysql to update the account in the table with a new password, making sure to use the function:

    mysql> UPDATE user SET Password=PASSWORD('')
        -> WHERE User='' AND Host='';
    

A.2.15. Table 'nombre_de_tabla' doesn't exist

If you get either of the following errors, it usually means that no table exists in the current database with the given name:

Table '' doesn't exist
Can't find file: '' (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 current database with . See Sección 13.5.4, “Sintaxis de .

A.2.16. Can't initialize character set

You might see an error like this if you have character set problems:

MySQL Connection Failed: Can't initialize character set charset_name

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 or option. See Sección 2.8.2, “Opciones típicas de configure.

    All standard MySQL binaries are compiled with , which enables support for all multi-byte character sets. See Sección 5.9.1, “El conjunto de caracteres utilizado para datos y ordenación”.

  • 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 Sección 2.8.2, “Opciones típicas de configure.

    • Specify to the client the directory where the character set definition files are located. For many clients, you can do this with the option.

    • Copy the character definition files to the path where the client expects them to be.

A.2.17. No se encontró el fichero

If you get , , or any other error with or 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:

shell> perror 23
Error code  23:  File table overflow
shell> perror 24
Error code  24:  Too many open files
shell> perror 11
Error 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 of the system variable (the default value is 64). Reducing the value of 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 option to mysqld_safe or (as of MySQL 3.23.30) set the system variable. See Sección 5.3.3, “Variables de sistema del servidor”. The easiest way to set these values is to add an option to your option file. See Sección 4.3.2, “Usar ficheros de opciones”. 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 to set the number of file descriptors to be made available to mysqld.

and 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 mysqld as (just remember that you also need to start the server with the option in this case so that it does not continue to run as 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.