can a tabled be copied on the fly?

[sfullman]

hi,

have a table with 4million + record in it. It gets data in consntaly, sometimes 10-20 records added per second. It is read rarely.

What I want to do is as follows:

1. when the 5 millionth record is entered, lock the table.
2. rename the table from xmlresponses to xmlresponses_bk01
3. create a new table _xmlresponses exactly like the old one with AUTO_INCREMENT SET TO 5,000,001
4. UNLOCK the table

#2 and #3 I have down pat - really simple sql. My question is on #1 and #4 - ..

..at the instant the script reads that the insert_id is 5,000,000, it will take a finite amount of time (not much) to do what I want to do. How do I "stall" the other requests coming in (again, they're all separate processes and we get 10-20/second) so they are not lost duing the transition?

If they (these records) are lost, it's not a tremendous problem, but I'd like to do this the right way and lose nothing.

Thank you,
Sam Fullman

[dagon]

an option that comes to mind

check table for 5000000 rows
create xmlresponses_bk01
use insert select to copy 0-5000000
delete the 0-5000000
run OPTIMIZE

nothing should be lost in the process, the auto_inc will keep on ticking along. you may have more than 5000000 for a while depending how often you do the check, but dies this really matter? or you could write a trigger

you will need to keep track of the start, end, bk# (0,5000000,01 - first time) for the future, but that shouldn't be hard

[sfullman]

that sounds like a good way to do it versus creating a table. Excellent idea, I will use this, thank you.