Access System
 
Connection Problems
 

The following list gives the typical causes of problems in establishing connections. Note that a particular error message can arise from one of several different causes.
 
The MySQL server does not run:
 
If you attempt a connection with mysql, you obtain error 2002 (Can't connect to MySQL server on 'hostname') or error 2003 (Can't connect to local MySQL server through socket /var/lib/mysql/mysql.sock)
 

Under Windows, you can tell whether the server is running by looking in the task manager, while under Unix you use the command ps | grep -i mysql. As result, a list of processes should appear (since the server divides itself into a number of processes for reasons of efficiency). If that does not occur, then the server must be started (under Linux with the command /etc/init.d/mysql[d] start).
 
The client program does not find the socket file:
 
  • Under Unix/Linux, communication takes place mostly over a socket file if server and client are running on the same computer. For this to function, both programs must agree on the location of this file. When problems arise, you should ensure that there is an entry socket=filename in the configuration file /etc/my.cnf in the section [client], where filename specifies the actual location of the socket file. Normally, this file has the name /var/lib/mysql/mysql.sock.
    •  
    . The network connection between client and server is broken:
     
    If your program is running on a different computer from that of the MySQL server, execute on the client computer the command ping serverhostname to test whether a connection to the server computer exists. If that is not the case, you must first repair the network configuration.
     
    . MySQL accepts no connections over the network (over TCP/IP):
     

    This can be achieved with the option –skip-networking or a corresponding setting in my.cnf. This setting is often chosen to give MySQL maximum security. A database connection is then available only from the local computer and only via a socket file.
     

    The problem is generally recognizable from error 2003 (Can't connect to MySQL server). A solution is to remove the option my.cnf from the start script.
     
    . MySQL accepts no connections from your computer:
     
    This problem generally arises when the MySQL server is running on the computer of an Internet service provider. There the server is generally so configured that only connections from local computers (or a local network) are allowed. For administration you must therefore either create a telnet-/ssh connection or use a program that is executed locally on the server and is served via the internet (e.g., phpMyAdmin).
     
    . Name resolution of host names does not work correctly:
     
    In establishing a connection over a network, error 1130 arises (Host n.n.n.n' is not allowed to connect to this MySQL server). The most likely cause of this error is either the incorrect specification of the host name in the mysql.user table or an incorrect name-server configuration. Depending on the network configuration, the host names must be given in the column user.Host with or (more seldom) without domain name.
     
    A solution is to add domain names to the host names in the mysql.user table (uranus ? uranus.sol) or to remove this (uranus.sol -- > uranus) and try again. (Do not forget FLUSH PRIVILEGES.)
     

    If that does not work, you can test with the commands hostname, host, and resolveip whether there are problems with name resolution. An emergency solution that almost always works (but is inflexible) is to give the IP number instead of the host name in the user.Host column. There are many other suggestions about dealing with host-name problems earlier in this chapter.
     
    . User name or password are incorrect:
     
    Watch out for typos! Note as well that not only user name and password must correspond, but the host name as well. (Thus a connection is usually possible only from certain specific computers.) Also, read the previous point relating to resolution of the host name; perhaps that is where the problem resides.
     
    Note that the user.Password column does not contain passwords in plain text, but in encrypted form. If you wish to change a password with SQL commands, you must use the function PASSWORD("xxx").
     
    . An incorrect entry was used in the mysql.user table:
     
    When user x attempts to register with computer y, the MySQL server compares the entries in the user table in a particular order: First, entries are considered whose Host character string is unique, and only then Host entries with wild cards (% and _). Within these two groups, again unique User strings are preferred to those with wild cards.
     
    The result of this order of precedence is that user abc on the local computer (localhost) will be unable under certain circumstances to register, although in the user table there is an entry Host='%' / User='abc'. The reason is that in the default setting of access privileges there is also an entry Host='localhost' / User=''. This entry is given precedence to the first one because there the host name is given explicitly.
     
    To solve the problem, either add a second entry Host='localhost' / User='abc' to the user table or delete the entry Host='localhost' / User=''. I would recommend the second variant, since that entry represents a security risk.
     
    . No user name was specified:
     
    If you do not specify a user name in your program for making a connection, then the login name of the account under which the program was launched is given automatically. In programs that are launched interactively, this is your login name.
     
    With programs that run over a web server (PHP or JSP scripts, Perl CGI files, etc.), the account name of the web server is used. For security reasons the web server usually runs not as root (Linux) or with administrator privileges (Windows), but in a separate account, such as wwwrun or apache. The problem is now that in the mysql access tables, the user wwwrun or apache is unknown. Therefore, access to the database is denied. Therefore, do not forget in script files to specify the user name for the connection to MySQL explicitly.
     
    . The connection succeeds, but access to the database is impossible: This error occurs immediately if you specify the desired database during the establishment of the connection. However, the error cannot occur until you select the desired database (USE dbname). Error message 1045 is, for example, Access denied for user ... to database ... .
     
    The most likely cause of the error is that the user in fact does not have access rights to the database. Perhaps in GRANT you have specified only the Usage privilege (which allows a login, but not the actual use of a database). Execute the command GRANT SELECT, INSERT ... ON dbname.* TO name@hostname to allow database access to dbname.
     
    If the MySQL server is running on the computer of an Internet service provider (ISP),then the server is generally so configured that only local access is possible. In other words, your PHP or Perl scripts run without problems (because they are executed on the same computer), but you cannot access your databases from home, say, with the MySQL Control Center.
     
    Here the issue is the correct (because secure) setting of the access privileges. You will find scarcely an ISP that allows MySQL connections from an arbitrary computer on the Intenet. You must therefore use programs for administration that run locally on the computer of the ISP (e.g., phpMyAdmin).
     
    . It is impossible to create a local TCP/IP connection:
     
    This problem usually occurs under Unix/Linux. A local connection succeeds only if with option -h no computer name or IP number is specified. The most likely cause is a problem with the resolution of the host name. As a rule, you must add the domain name in the column user.Host, and then it works. Another cause can be the local network configuration (file /etc/hosts). We have already given some tips in this chapter especially for Red Hat and SuSE Linux.
     
    . The local connection fails for Java programs:
     
    This problem is usually connected with the previous point, since Java programs, in contrast to most other MySQL clients, generally use TCP/IP (and not a socket file).. Another cause of error can be the incorrect installation of Connector/J, but then an error occurs in the attempt to use JDBC (java.lang.ClassNotFoundException: com.mysql.jdbc.Driver).
     
    . Port 3306 is blocked:
     
    Between the MySQL server and your program there is a firewall that is blocking port 3306. This problem can occur only when your program and the MySQL server are running on different computers. If you manage the firewall yourself, you must clear port 3306; otherwise, you must ask the administrator to do so.
     
    . The MySQL server crashes at every attempt at a TCP/IP connection:
     
    This problem occurred in the past with Linux distributions. Since the server was immediately restarted, the problem was not always correctly identified. (The error message is usually Lost connection to MySQL server during query.)
     
    A solution to this problem is frequently an update of the glib library or the installation of the MySQL package from www.mysql.com. (The MySQL packages included in the distributions are not always optimally configured. This has been particularly bad in the case of Red Hat 8.0 and SuSE 8.1. The MySQL documentation therefore recommends using only versions of MySQL compiled by the MySQL team, and after a number of negative experiences, I agree.)
     
    . An update of the MySQL server causes problems:
     
    In MySQL 4.0 the security system was greatly expanded. If you are moving your database system from version 3.23.n to 4.n, you must be sure to bring the mysql tables into conformity with the new security system. For this, there is provided the script mysql_fix_privilege_tables. This script creates new columns (vis-ŕ-vis MySQL 3.23) in the mysql tables, but leaves the settings of these columns in the default setting N. You may need to take a close look at the new access privileges and explicitly grant certain individual new privileges, such as Lock, Create_tmp_table for normal users, and Super for administrators.
     
    The Special Case of Named Pipes
     
    Named pipes represent a mechanism by which two programs can communicate under Windows NT/2000/XP. Data exchange follows the first-in-first-out (FIFO) principle.
     
    MySQL supports named pipes only under Windows NT/2000/XP, and not under Windows 9x/ME or under Unix/Linux. Named pipes can be used only when the server is appropriately compiled (mysqld-nt and mysqld-max, but not mysqld-opt) and when the configuration file Windows\my.ini contains the option enable-named-pipe in the group [mysqld]. By default, this is not the case, since in the past, open named pipes to the MySQL server caused problems in shutting down Windows.
     
    If you wish to establish an explicit named-pipe connection, specify a period (.) as host name or use the option --pipe. Even if you specify no options, a named pipe will be used if the server permits it:
     
    C:\> mysql -u name -p
    C:\> mysql -h . -u name -p
    C:\> mysql -h localhost --pipe -u name -p
     
    If you wish to create a connection explicitly over TCP/IP, you must specify the host name:
     
    C:\> mysql -h computername -u name -p
    C:\> mysql -h localhost -u name -p
     
    Whether named pipes are permitted can be determined in the program mysql with the command status.
     
    Further Tips for Error-Checking
     

    If you believe that your mysql database is properly configured, yet database access fails nonetheless, stop the MySQL server, add temporarily skip-grant-tables to the [mysql] section in my.cn or my.ini and restart the server. Now everyone has access to all data. If access now succeeds, you at least know with certainty that the problem resides in the mysql tables. Do not forget to remove skip-grant-tables from the configuration file.
     
    MySQL manages temporary storage (cache) with a list of most recently used IP addresses and associated host names. This cache makes it possible for the usually time-consuming process of name resolution to proceed efficiently. However, if you change your network configuration without restarting the MySQL server, it can happen that this cache contains incorrect, that is, no longer valid, entries. To run the MySQL server without this cache, add skip-host-cache in the [mysql[ section of my.cnf or my.ini and restart the server.
     
    Unfortunately, there is no way to make the MySQL server save precise information as to why a login attempt failed. In the error log is mentioned only the IP address of the login attempt, but not the course of name resolution, which entries in the mysql tables were looked at, etc.