MySQL Bugs: #7566: a feature to swap or switch tables easily

Bug #7566	a feature to swap or switch tables easily
Submitted:	29 Dec 2004 6:31	Modified:	29 Dec 2004 7:39
Reporter:	Stefan Praszalowicz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
I have a cron job that produces summary tables to avoid many big joins every tenth
of second...
I'm looking for a way to switch between the 'current' summary table and
the new version, just after it's generated by the script.

The idea is similar to double buffering (in UI, when you render a frame off-screen to avoid flicker)

I have tried using 'alter table rename' but I need at least two renames, which means there will be a moment where my clients will not see the table. Even if I lock the tables, queries waiting for the lock to be released return (not seeing the table) the moment I run the alter...

I'm basically thinking about a new statement that would allow for this operation to be atomic... something along the lines of "SWAP TABLES db.currentTable AND db.newTable"

Basically this would just nicelly wrap two renames, making sure it gets prioritized over other waiting queries, and running them without disrupting anything.

Thanks for taking the time to read this, I hope somebody can find it usefull !
Also thanks for your great product.

Stefan

How to repeat:
N/A

Suggested fix:
N/A

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

see http://dev.mysql.com/doc/mysql/en/RENAME_TABLE.html