Top Nav

Archive | MySQL


Here’s a one-liner that will check all tables in a database:

And here’s an article that shows the use of mysqlcheck:

mysqlcheck, unlike myisamchk, can be run when the mysql daemon is running

This command will check, repair and optimize all tables in all databases:



MySQL Error: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

Encountered this error when dropping a table on a database that had recently been migrated from 5.0 to 5.5:

The migration used a “mysqldump –all-databases” so it included the “mysql” database. Apparently the “mysql.proc” table changed between the versions. The fix on the new server is to run:

I didn’t figure this out … instead Daniel Tillett gets the credit.


MySQL – Access Denied On Grants

Interesting problem where we got access denied errors when trying to grant privileges on MySQL server:

But we’re logged in as root so it should work. Turns out the problem is the mysql database needs to be upgraded. Try this:

Notice that it lists the individual privileges instead of saying all privileges. Now upgrade the mysql database by running mysql_upgrade:

Next restart mysql server, login and try showing grants again:

Notice now that it shows “ALL PRIVILEGES” and your grant statements will work.




Convert MyISAM Tables to InnoDB

The command to convert a single table from MyISAM to InnoDB looks like:

This is described suscintly by Major.IO:

Kevin van Zonneveld goes further in his blog:

and provides some scripts to convert all tables instead of doing one at a time.

I like the following:

This variation shows the table name before starting the conversion so I can see progress. The query output is pipped right back in to mysql to execute.


MySQL Monitoring With myq_gadgets

Great set of utilities for monitoring MySQL performance:

Here are the steps to install:

The tools will be in the myq_gadgets-master folder.

There’s no installer so you can just start using the tools immediately.

For instructions see the enclosed README.txt.

Also this presentation demonstrates many of the features: