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.

MySQL Workbench - Run Query

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.

MySQL Workbench - Copy and Paste the Statements

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.

,

6 Responses to MySQL Workbench: Repairing All Tables In A Database

  1. Chee-Leong LAM 2013-02-26 at 09:21 #

    Thanks! Very helpful SQL snippet.

  2. John 2013-05-21 at 14:38 #

    What would the query look like to repair a single table?

    • jasonjudge 2013-05-22 at 22:15 #

      Can’t be simpler:

      REPAIR TABLE {table-name};

  3. Clinton 2013-08-28 at 21:32 #

    I get the error that the database name is missin’ when i copy all the repair statements

    • Jason Judge 2013-08-28 at 21:37 #

      Are you connected to a database when you run them?

  4. Chris 2014-12-23 at 10:26 #

    Great tip, I was looking for an easy bulk way to do this.

    Works a treat.

Leave a Reply