3.7. Troubleshooting MySQL

Some of the more common MySQL installation problems are described in this section.

3.7.1. Displays error message: Access denied

When you attempt to access your MySQL server, an error message similar to the following is displayed:

Access denied for user 'root'@'localhost' (using password: YES)

The error message means that MySQL did not recognize the account name and password. The message gives as much information as possible. In this case, the message shows that access was attempted from localhost using the account name root and using a password. If you accessed using a blank password, the message would show using password: NO. Either MySQL didn't recognize the account name, the account name isn't allowed to access from this host, or the password is incorrect.

Sometimes the error message shows the account name as ODBC. This is a default account name that MySQL uses. Usually this means that the MySQL server didn't receive any account name/password information at all . . . or none that it could understand.

MySQL access is described in Book III; for more about account names and passwords, see Book III, Chapter 2.

3.7.2. Displays error message: Client does not support authentication protocol

MySQL passwords are stored in a table in the mysql database. When MySQL was updated to version 4.1, the password encryption was changed, making the passwords more secure. However, older MySQL clients don't understand the new password encryption, and they display an error similar to the following:

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

In particular, using the mysql client with MySQL 4.1 or later sometimes results in this problem. The best solution is to upgrade to PHP 5 and use the mysqli functions. If you can't upgrade for some reason, you need to use a function called OLD_PASSWORD with the SET PASSWORD command to set the password for any accounts that are causing problems. You might use a command similar to the following:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd'),

Setting passwords is described in detail in Book III, Chapter 2.

3.7.3. Displays error message: Can't connect to . . .

An error message 2003, as shown here, generally means that the MySQL server isn't running:

(2003): Can't connect to MySQL server on 'localhost'

To correct this problem, start the server as follows:

• Windows: Choose StartControl PanelAdministrative ToolsServices. Find the MySQL service and click Start.

• Linux/Mac: Type mysql.server start. You might need to be in the directory where the mysql.server script resides.

3.7.4. MySQL error log

MySQL writes messages to a log file when it starts or stops. It also writes a message when an error occurs. If MySQL stops running unexpectedly, you should always look in the error log for clues.

The following are some messages you might find in the error log:

070415 17:17:01 InnoDB: Started; log sequence number 0 189675
070415 18:01:05 InnoDB: Starting shutdown

The error logs are stored in a subdirectory named data in the directory where MySQL is installed. The error log has the .err file extension.