Re: What would happen in these two cases?
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? 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
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]
Re: What would happen in these two cases?
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]
Re: What would happen in these two cases?
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]
Re: What would happen in these two cases?
That is what I need! Thanks so much again to everyone! HT On Tue, 10 Aug 2004 14:32:13 -0700, Justin Swanhart [EMAIL PROTECTED] wrote: 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]
Re: What would happen in these two cases?
FYI, the atomicity of rename and using it to swap tables is discussed in the manual here: http://dev.mysql.com/doc/mysql/en/RENAME_TABLE.html Justin 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What would happen in these two cases?
The rename scenario is your best option. Just make sure you flush the tables to ensure that users are seeing current data. You normally do not want to `pull the rug` out from your users by deleting a table that they may be attempting to access. -Original Message- From: Haitao Jiang To: [EMAIL PROTECTED] Sent: 8/9/04 12:42 AM Subject: What would happen in these two cases? I hope this is not a so dumb question: 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? 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. In both cases, there will be some queries on A, but no updates. Thanks Haitao -- 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]
Re: What would happen in these two cases?
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]
What would happen in these two cases?
I hope this is not a so dumb question: 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? 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. In both cases, there will be some queries on A, but no updates. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
