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?


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.