Mysql – Easily, quickly replace MySQL table

MySQL

We have a process which will regenerate a table from scratch, drop the original table, and move the new table into the original's place. Now this should happen very quickly, but sometimes a website request hits the table after it's been dropped but before the new one is renamed. Apart from coding the website to be more robust when there's a database error, is there an easier way to do this?

Best Answer

The RENAME TABLE syntax allows for atomic, multiple renames. See as follows:

CREATE TABLE myschema.mytable_new LIKE myschema.mytable;
RENAME TABLE myschema.mytable TO myschema.mytable_old, myschema.mytable_new to myschema.mytable;
DROP TABLE myschema.mytable_old;

In the above it is impossible for your code to access the table during the RENAME operation, and this it cannot happen that the code "does not find the table". It just blocks until RENAME operation completes, and the table is always there.

Also worth noting that unless using Percona Server with "fast DROP TABLE patch", DROPping a table might hold locks on your database (namely the table cache) for the duration of the operation. And the operation may take a while since it involves evacuating table pages from InnoDB's cache and deleting the table file(s) from the filesystem.

Waiting for some time (eg a couple hours) after the RENAME operation can do good since your old-table's pages are more likely to be flushed out by that time, reducing the overall time from DROPping the table.

Related Solutions

Mysql – the optimal solution for converting a database schema

There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

Renaming foreign key constraints to fit a new application framework.
Refactoring a significant proportion of existing tables.
Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).

MySQL – Why DROP DATABASE Takes So Long

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).

Best Answer

Related Solutions

Mysql – the optimal solution for converting a database schema

MySQL – Why DROP DATABASE Takes So Long

Related Question