Problem After MySQL Upgrade

If you got the following problem after you upgrade your MySQL RPM packages :

$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)

Then you need to troubleshoot that. :D

Here are several steps that you can use to help you troubleshoot that :

  • First, we want to check the arguments used by MySQL server. These arguments are from the configuration file (my.cnf). You can use the mysqld command with –print-defaults as the argument :


    # mysqld --print-defaults
    mysqld would have been started with the following arguments:
    --port=3306 --socket=/var/lib/mysql/mysql.sock --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --bind-address=127.0.0.1 --server-id=1

  • Next, check the client side :
    # mysql --print-defaults
    mysql would have been started with the following arguments:
    --port=3306 --socket=/var/lib/mysql/mysql.sock --no-auto-rehash
  • After those two commands, you will see that there is a disparency between the configuration file and the client. They’re looking for the socket file in the different locations.

  • Now, let’s give the correct socket file location to the MySQL client :


    $ mysql --socket=/var/lib/mysql/mysql.sock
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.1.33 SUSE MySQL RPM

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

    mysql> exit
    Bye

    From the output above, we can conclude that the configuration file is wrong.

  • We then check the configuration directory (/etc). And we found out that there are two configuration files :


    -rw-rw----  1 mysql  mysql      6273 2009-04-23 14:15 my.cnf
    -rw-rw----  1 mysql  mysql      6363 2009-04-03 23:08 my.cnf.rpmnew

    The my.cnf.rpmnew is the new configuration file installed by the new package.

  • Now let’s see those two configuration files. Here is the content of my.cnf:

    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock

    And here is the content of my.cnf.rpmsave :

    [client]
    #password       = your_password
    port            = 3306
    socket          = /var/run/mysql/mysql.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port            = 3306
    socket          = /var/run/mysql/mysql.sock

    Can you spot the problem ?

  • To fix this, just use the my.cnf.rpmsave as my.cnf file. Before doing anything to the configuration files, please backup and stop the MySQL server first.

    Here is the output after I change the configuration file to the new version :


    $ mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.1.33-log SUSE MySQL RPM

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

    mysql> exit
    Bye

The problem has been solved.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s