The main problem is not the fact that you are receiving the error when issuing service mysql status, but the fact that the database is stuck and responding very slowly.
You can get the specified error after updating Mysql server to 10.0.x and this happens due to innodb storage formats. Probably the previous Mysql server version used Barracuda file format and after the update you use the Antelope storage format.
What you want to do is change the format to Barracuda and make this setting global.
The first thing to do is to backup all your databases !!
Edit your Mysql configuration file (/etc/mysql/my.cnf or other- depending on distro) and add:
innodb-rollback-segments=256
innodb_file_format=Barracuda
Save and restart mysql process.
The login to the Mysql server and issue :
mysql -u admin -p`cat /etc/psa/.psa.shadow` //for Plesk
mysql -u admin -p // without Plesk
show variables like “%innodb_file%”;
If you see Antelope, then do this:
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@’localhost’;
FLUSH PRIVILEGES;
Restart mysql (and do a reboot?).
In the end you should have something like this:
If this doesn’t work, you might want to try THIS first and restart the process described above.
I get these errors
mysql: unknown variable ‘innodb-rollback-segments=256’
mysql: unknown variable ‘innodb_file_format=Barracuda’
Maybe you are not using the Barracuda file format?
Hi,
In your explaination you quote :
GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@’localhost’;
What do you mean by that ?
Is it a specific database, our server count dozens of database and more users.
Do we have to grant priviledge to every user using a database?
Sorry but this is not clear for me.
I appreciate your help, but can’t complete the sequence without this informaiton.
Hi,
What I did is issue that command for each database on my server.
For example:
GRANT ALL PRIVILEGES ON apsc.* TO ‘myuser’@’localhost’;
GRANT ALL PRIVILEGES ON information_schema.* TO ‘myuser’@’localhost’;
GRANT ALL PRIVILEGES ON mysql.* TO ‘myuser’@’localhost’;
GRANT ALL PRIVILEGES ON psa.* TO ‘fcsuser’@’localhost’;
FLUSH PRIVILEGES;
I guess you have to do that for every server.
But be careful, I am not an expert in databases, but in the end this worked for me.