You can put multiple renames in one statement, and the entire rename will be atomic..
I create "summary" tables from some of my data, and I periodically refresh them. When refreshing them I create new tables to replace the old tables with.. Then I do: rename current_table to old_table, new_table to current_table On Tue, 10 Aug 2004 13:50:34 -0700, Haitao Jiang <[EMAIL PROTECTED]> wrote: > It would be great if there is a swap table command that is atomic. > > Thanks a lot > > Haitao > > On Tue, 10 Aug 2004 13:23:30 +0300, Egor Egorov <[EMAIL PROTECTED]> wrote: > > Haitao Jiang <[EMAIL PROTECTED]> wrote: > > > > > Thanks so much for the reply! So, if I understand correctly, to swap > > > the table like I described, I should do: > > > > > > LOCK TABLES A write,A2 write; > > > FLUSH TABLES; > > > rename table A to A1; > > > rename table A2 to A; > > > UNLOCK TABLES; > > > > > > Right? If there is no write to either A or A2, then there is no need > > > to lock the table, right? > > > > You can't rename locked tables. RENAME is atomic anyway so you can safely use > > it without lock. But your software should be aware of a possible race condition > > that happens between two RENAME TABLEs. > > > > > > > > > > > Thanks! > > > > > > On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov <[EMAIL PROTECTED]> wrote: > > >> Haitao Jiang <[EMAIL PROTECTED]> wrote: > > >> > > >> > Case 1: I have a table A under a running MySQL 4.1.3b server. If I > > >> > replace A.MYD and A.MYI files without stop and start the server, will > > >> > data gets corrupted? > > >> > > >> It depends on many factors. Honestly, there are lots of cases where you will > > >> get correct data and nothing wrong will happen. But you have to be an > > >> experience Unix developer to understand Unix internals in order to dance like > > >> that. :) > > >> > > >> So the general answer is: don't, it's too dangerous. > > >> > > >> > Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same > > >> > schema but different data/index. Is it bad? Will data gets corrupted? > > >> > I tied this one, it seems ok. > > >> > > >> Yes, you're right, it just *SEEMS* ok. :) > > >> > > >> If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: > > >> > > >> http://dev.mysql.com/doc/mysql/en/FLUSH.html > > >> http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html > > >> > > >> -- > > >> For technical support contracts, goto https://order.mysql.com/?ref=ensita > > >> This email is sponsored by Ensita.net http://www.ensita.net/ > > >> __ ___ ___ ____ __ > > >> / |/ /_ __/ __/ __ \/ / Egor Egorov > > >> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > > >> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > > >> <___/ www.mysql.com > > >> > > >> > > >> -- > > >> MySQL General Mailing List > > >> For list archives: http://lists.mysql.com/mysql > > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > >> > > >> > > > > > > > -- > > For technical support contracts, goto https://order.mysql.com/?ref=ensita > > This email is sponsored by Ensita.net http://www.ensita.net/ > > __ ___ ___ ____ __ > > / |/ /_ __/ __/ __ \/ / Egor Egorov > > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > > <___/ www.mysql.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
