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?
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.