MySQL Workbench: Repairing All Tables In A Database

phpMyAdmin is a web-based application, written in PHP, that help you to manage MySQL databases. One useful feature is being able to repair all tables on a database when tables get corrupted.

Now, this does not happen often, at least it should not. We had an experience with VPS.net cloud servers where the server would go down once every couple of days and needed a reboot. Doing that resulted in many corrupted database tables, every damn time. I’m really glad to be shot of that cloud server – never known anything like it. But I digress.

phpMyAdmin allowed us to select all tables in a database and hit “repair”. That then ran a REPAIR TABLE command against each of the tables in turn.

I was asked today how to do this in MySQL Workbench, and that left me scratching my head a little.

For those not familiar with it, MySQL Workbench is a local (i.e. client) application that can connect to remote databases. It is great for designing databases, testing queries, manipulating data – in fact, just brilliant at all these things, and I use it a lot. One thing it cannot do though, is repair tables in bulk.

There is an easy way around this. First execute this SQL query in an SQL window:

SELECT CONCAT('REPAIR TABLE ', table_name, ';')
 FROM INFORMATION_SCHEMA.TABLES
 WHERE table_schema = '{your database name}'
 ;

Don’t forget to substitute in the name of the database you want to repair. That will list a REPAIR TABLE statement for each table that it finds in that database.

Now you can select all the results, copy them, then past them into a new SQL window. Then just execute everything in that window using the “lightning” execute button.

Being able to freely move query results to the query statement window is a great technique for running many updates, or many DML statements, in bulk.