Error Codes

Version 8

    Error Codes

    • Errors, Error Codes, and Common Problems information can be found here:
    • The MySQL server tends to spit out errors from time to time, and these are common errors that one can usually solve fairly easily.

    Using perror

    • This is a tool to explain error codes. Everytime the server spits out a system error, you can run it through perror from the command line, and get a reason as to why the server has found an error. For example, if the server spits out error 13, you run:perror 13
    • And it will return the fact that there was an OS error code, which translates to being Permission Denied. For more usage information, use the manual page (man perror) or visit it online in the manual.

    Error Messages

    Error: Cannot create Windows service for MySql. Error: 0

    • If you run perror 0, it would seem that MySQL succeeded. However, on Windows, this is an error that occurs when you upgrade MySQL from time-to-time. Read Error: Cannot create Windows service for MySql. Error: 0 below

    Error 2003 (HY000): Can't connect to MySQL server on 'hostname' (10060)

    • The client failed to make a connection to the server. In other words, no MySQL server responded to a connection attempt. Read Error: Cannot create Windows service for MySql. Error: 0 belowfor a troubleshooting guide.

    Error: Cannot create Windows service for MySql. Error: 0

    • This error is one you will encounter on Windows when you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service. It is a very common error state, as going by the forum and mailing list postings.
    • To solve this, Mike Hillyer has created Mike's Service Remover. Simply extract the tool and run it.

    Error 2003: Can't Connect to MySQL server on 'hostname'

    Nature of problem

    • This client error appears when no MySQL server responds to a connection attempt. That is, a client attempts to connect to MySQL Server at a specified host and port number, but the connection is not answered at all. This is not an error that comes from a server, since no server was reached. The error is generated by the client (like all MySQL error numbers in the 2000's).
    • There are many possible causes for this error. The best way to diagnose and correct the error is to test these possibilities and eliminate them one by one. The guide below describes tests and remedies for some of the potential causes.
    • Also see the page of the MySQL manual that pertain to this error:  http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html


    Troubleshooting Causes

    MySQL Server is not running on the server host

    • Make sure you have started the MySQL Server on the server host. If it is not running, it cannot respond to client connection requests.
    • Note that the MySQL Server is a different program than the mysql client. MySQL Server is a service; just because you have started the mysql client does not mean that the MySQL Server is running.


    Verify that MySQL Server is running on Windows

    • Windows has a command sc with which you can query the state of services. Open a command shell (Start->Run->cmd) and use the sc command as follows:C:\> sc query MySQL

    SERVICE_NAME: MySQL
    TYPE : 10 WIN32_OWN_PROCESS
    STATE : 4 '''RUNNING'''
    (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
    WIN32_EXIT_CODE : 0 (0x0)
    SERVICE_EXIT_CODE : 0 (0x0)
    CHECKPOINT : 0x0
    WAIT_HINT : 0x0

    • An important detail in that output is that the service is '''RUNNING'''. If it were not running, the STATE would be '''STOPPED'''.
    • Note that "MySQL" is the default name for the Windows service. If you chose a different service name when you installed, specify it instead in the sc command.
    • You can also get information about the configuration of the MySQL service:


    C:\> sc qc MySQL
    [SC] GetServiceConfig SUCCESS
    SERVICE_NAME: MySQL
    TYPE : 10 WIN32_OWN_PROCESS
    START_TYPE : 2 AUTO_START
    ERROR_CONTROL : 1 NORMAL
    BINARY_PATH_NAME : "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt"
    --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini" MySQL
    LOAD_ORDER_GROUP :
    TAG : 0
    DISPLAY_NAME : MySQL
    DEPENDENCIES :
    SERVICE_START_NAME : LocalSystem

    • You can see in this output the location of the MySQL Server binary, and the arguments it is given when it is started. Note the location of the my.ini file. This is useful for examining other server configuration options.
    • If the MySQL service is not running, you can start it:


    C:\> sc start MySQL
    SERVICE_NAME: MySQL
    TYPE : 10 WIN32_OWN_PROCESS
    STATE : 2 START_PENDING
    (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
    WIN32_EXIT_CODE : 0 (0x0)
    SERVICE_EXIT_CODE : 0 (0x0)
    CHECKPOINT : 0x0
    WAIT_HINT : 0x7d0
    PID : 2840
    FLAGS :

    • You can also start and stop services in the Windows control panel GUI. Open Start->Control Panel->Administrative Tools->Services.

    Verify that MySQL Server is running on Linux/UNIX

    You can view a listing of the processes running on the server at a command prompt with the ps command:

    1. ps -ef | grep mysqld
      ...
      root 2435 2342 0 15:49 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
      --datadir=/usr/local/var/ --user=mysql
      mysql 2480 2435 0 15:49 pts/1 00:00:00 /usr/local/mysql/libexec/mysqld
      --basedir=/usr/local/mysql --datadir=/usr/local/var/ --user=mysql ...
      root 2624 2342 0 15:57 pts/1 00:00:00 grep mysqld
      ...

    Note that the processes include the mysqld_safe script that starts the service, the mysqld executable itself with various options specified (yours may appear different from the example above), and the grep command which also contains the word mysqld.You can also use the netstat command to list running services:

    1. netstat -lnp | grep mysql
      tcp 0 0 0.0.0.0:'''3306''' 0.0.0.0:* LISTEN 2480/mysqld
      unix 2 [ ACC ] STREAM LISTENING 8101 2480/mysqld '''/tmp/mysql.sock'''

    Note in the above example two important values. '''3306''' is the network port on which the MySQL Server is listening. '''/tmp/mysql.sock''' is the socket file MySQL Server is using for local connections.If the MySQL daemon is not running, the output of the commands above won't show lines for mysqld. So you need to start it.# /etc/init.d/mysql startSome versions of Linux (e.g. Redhat) also have a convenience program called service which you can use. It effectively does the same thing as running the respective script in the /etc/init.d directory.# service mysql start.


    MySQL Server is running, but not on the default port

    Clients can connect to the MySQL Server ''locally'' or ''remotely''.A ''remote'' connection uses TCP/IP networking. It is used by default on Windows, and always when the client is a Java applications, and when the client and the server are on different hosts. Even if the client and the server are on the same host, a connection using TCP/IP is called a remote connection. In this mode, the client and server must agree on the network port number.The default port on which MySQL Server listens is 3306. This can be configured on a per-instance basis. So the server may be listening on a different port, even if the client assumes that it is using the default 3306. If the client and the server do not agree on the port to use, connections cannot succeed.Check what port the server is using. If you have a running MySQL Server instance and you can connect to it, you can use the following statement to inquire the port it is listening to.SHOW VARIABLES LIKE 'port';It will probably respond '''3306'''.If you can't connect, you can still find the port number by checking the my.ini or my.cnf configuration file on the server. Look for the port option within the [ mysqld ] section:
    [ mysqld ]
    ...
    port=3306
    ...
    The port can also be specified on the command-line. This overrides the entry in the options file. So double-check the arguments to the mysqld program, which you can see in the ps output. You can also read the output of netstat to see the port that the server process is listening to.


    MySQL Server is running, but not with the default socket file

    A ''local'' connection is an optimization available on UNIX/Linux using a ''socket'', which is another mechanism of interprocess communication. This kind of socket is faster than TCP/IP, but it only works when both client and server are on the same host. Like the port number for remote connections, a client and server connecting locally must agree on the path to the socket file. This path is typically something like /tmp/mysql.sock or /var/lib/mysql.sock. The path to the socket file is configurable, so it may be different on different instances of MySQL Server.Check what socket file path the server is using. If you can connect and issue a query, use this:SHOW VARIABLES LIKE 'socket';The socket file path is also part of the output of the netstat -lnp command we used above. It can also appear in the my.cnf configuration file:
    [ mysqld ]
    ...
    socket=/tmp/mysql.sock
    ...
    It can also appear on the command-line, so check the ps output for an option to mysqld like --socket=/tmp/mysql.sock.As noted earlier, the client and the server must agree on the location of the socket file. The socket file can appear in the my.cnf file, in the [ mysqld ] client section (this is different from the [ mysqld ] server section):
    [ mysqld ]
    ...
    socket=/tmp/mysql.sock
    ...
    You can also specify the socket path on the command-line: # mysql --socket=/tmp/mysql.sock ''...options...''


    MySQL Server accepts clients only on same host

    For security reasons, some MySQL Server instances allow remote clients only if the client is running on a single specified host. For instance, typically the same host, so web applications can connect to MySQL Server on the same host, even if the connection is made via TCP/IP and therefore is a ''remote'' connection.There is a configuration option to restrict client connections to a single originating IP address. This option can appear in the /etc/mysql/my.cnf:
    [ mysqld ]
    ...
    bind_address=127.0.0.1
    ...
    The option can also be specified on the command-line, but it is spelled with a hyphen instead of an underscore: mysqld ... --bind-address=127.0.0.1The address 127.0.0.1 is a convention that is used on all TCP/IP hosts; it means "this host". The configuration option means that only clients connecting from a host at the specified IP address are permitted to connect. All other clients are refused. This helps block unauthorized attackers from connecting to your instance of MySQL Server, but it also prevent direct connections from any other host in your network.To disable this restriction, comment out or delete the line from the configuration file, and restart MySQL Server. But be cautious before doing this, because permitting clients from other hosts to connect could expose your MySQL Server to attackers from outside your network.


    MySQL Server accepts no remote clients, only local clients

    Another configuration option restricts MySQL Server so that only clients using the ''local'' connection method are allowed. Any clients using the ''remote'' connection method, even clients running on the same host, are refused. This option appears in the my.cnf:
    [ mysqld ]
    ...
    skip_networking
    ...
    The option can also be specified on the command-line, but it is spelled with a hyphen instead of an underscore: mysqld ... --skip-networking


    A firewall blocks incoming connections to the server host

    Firewalls are services that intercept and block network connection attempts.A firewall on the MySQL Server host can block incoming connections. The client fails to connect, and returns the same error as if any of the preceding causes had prevented the client from connecting to the server.Firewall can be software running on the host running MySQL Server, or another host that routes network traffic between your host and the host running MySQL Server. A firewall can also be a dedicated network device.


    A firewall on the client host blocks outgoing connections

    A firewall on the client host can also block ''outgoing'' connections. This is not uncommon these days to run client-side firewalls, because automated spam software can hijack your desktop computer to send spam. Blocking unauthorized programs from initiating outgoing network activity can help to reduce this risk.It also means that legitimate applications are denied access to the network, until they are authorized by some deliberate action that you need to do. The effect is that a client can be blocked from making the connection, and instead returns the same error as if any of the preceding causes had prevented the client from connecting to the server.There are many such client firewall products, and it is beyond the scope of this FAQ to tell you how to use all of them. You must be aware of what software is running on your computer, and how to configure the client firewall software to block the connections of unauthorized applications, but permit legitimate applications.Here are links to common firewall software: