Archive for the ‘MySQL’ Category

This occurred after an update was run with Yum. Two MySQL versions were present and the errmsg.sys file from the old or “bad” version was used.

I was able to resolve this by tracking down the correct errmsg.sys file for the “good” version of MySQL. Full instructions for this can be found below.

Full error message:

120327 9:18:48 [ERROR] Error message file ‘/usr/share/mysql/english/errmsg.sys’ had only 481 error messages,
but it should contain at least 641 error messages.
Check that the above file is the right version for this program!
/usr/libexec/mysqld: Unknown error 1146
120327 9:18:48 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.

1. Determine the correct version of MySQL:

yum list installed | grep mysql

Example output:

mysql.i386                              5.0.95-1.el5_7.1               installed
mysql.x86_64                            5.1.58-jason.1                 installed

2. Find the tar.gz installer file for your specific version of MySQL. Change the version in the following URL as needed:
http://mysql.llarian.net/Downloads/MySQL-5.1/

The example output above confirms there are two versions installed. The newer one is the “correct” version (5.1.58). You are looking for the .tar.gz file with no additional details in the name, for example “mysql-5.1.58.tar.gz”.

3. Extract the newly downloaded install files:

tar -zxf mysql-5.1.58.tar.gz

4. Make a copy of the old errmsg.sys file just in case:

mv /usr/share/mysql/english/errmsg.sys /usr/share/mysql/english/errmsg.sys.old

5. Copy the new errmsg.sys file to this same location:

cp mysql-5.1.58/sql/share/english/errmsg.sys /usr/share/mysql/english/errmsg.sys

6. Attempt to start the MySQL service again:

/etc/init.d/mysqld start

If this process does not work the best option may be to uninstall and reinstall MySQL (after backing everything up of course).

For version 5.1.6 and above you can enable slow query logging without restarting the MySQL service. For versions before 5.1.6 a service restart will be needed. Note that slow query logging should not remain enabled for a long period of time, especially on production environments. This could result in poor performance and the log files can grow rather quickly.

Version 5.1.6 and above:


1. Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:

     Log details for queries expected to retrieve all rows instead of using an index:
     set global log_queries_not_using_indexes = 'ON'

     Set the path to the slow query log:
     set global slow_query_log_file ='/var/log/mysql/slow-query.log';

     Set the amount of time a query needs to run before being logged:
     set global long_query_time = '20';
     (default is 10 seconds)

3. Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';

Versions below 5.1.6:


1. Edit the /etc/my.cnf file with your favorite text editor
vi /etc/my.cnf

2. Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:
log-slow-queries=/var/log/mysql/slow-query.log

3. Enable additional options as needed. Here are the same commonly used examples from above:

     Set the amount of time a query needs to run before being logged:
     long_query_time=20
     (default is 10 seconds)

     Log details for queries expected to retrieve all rows instead of using an index:
     log-queries-not-using-indexes

4. Restart the MySQL service:
service mysqld restart

5. Confirm the change is active by entering the MySQL shell and running the following:
show variables like '%slow%';

First try to restart the Plesk service to see if that clears the issue:

/etc/init.d/psa restart

If you see any of the following errors that means the Plesk password is out of sync with the MySQL database which holds that info:
*Unable to connect to database
*login.php3: Unable to connect to database: Permission denied
*ERROR 1045: Access denied for user: ‘admin@localhost’ (Using password: YES)

This does happen from time to time which is why Plesk provides a tool to re-sync this:

export PSA_PASSWORD=’NEW_PASSWORD’ ; /usr/local/psa/admin/sbin/ch_admin_passwd; unset PSA_PASSWORD

Of course make sure to replace NEW_PASSWORD with the current working one, or a new one if needed.

This came up recently and I was happy to find a nice command to perform the task. This will run through every table in every database on a MySQL server and auto-repair and optimize them.

Depending on the size and number of databases this may take a while to complete:

mysqlcheck -u root -p -Aro

-A = Check all databases
-r = Repair databases
-o = Optimize databases

So if you only want to run the repair, take out the “o” argument and if you only want to optimize, take out the “r” argument.