1

I have two tables with identical structures:

Table1 and Table2

id INT PRIMARY KEY, 
a TINYINT, 
b TINYINT, 
c TINYINT  

Table1 is actively being used by queries from other sessions; Table2 is not referenced/accessed by anything else. My goal is to end up with just Table1, but having the data from Table2.
(Note that Table2 and Table1 do not have the same set of id-PRIMARY KEY.)

What is the best way to do this without risking Table1 not existing momentarily for other sessions?

I know I can do something like:

DROP TABLE Table1; 
ALTER TABLE Table2 RENAME Table1;

But am not sure how to ensure nothing tries to query Table1 while it doesn't exist or is empty. Do I use transactions or lock the tables or something else? (Ideally, I'd want any other sessions' queries to return either old Table1 or new Table1 data rather than failing.)

I am using CodeIgniter in PHP to make the calls.

UPDATE: Looks like this post answers it using a method from the MySQL RENAME TABLE Documentation:

RENAME TABLE Table1 TO Table1_old, Table2 To Table1;

And then I can DROP Table1_old at my leisure

0

3 Answers 3

0

How about a view? Point your application at the view and then alter the view at an instant when there is no access to point to table2. Once that is done, you could drop table1.

1
  • Thanks for your answer. I haven't used views before, so for now I'm going to try using the suggestion from the post I just found: RENAME TABLE Table1 TO Table1_old, Table2 To Table1;
    – Phil W.
    Apr 4, 2017 at 19:37
0

A number of enterprise systems I have seen use a set of tables, as few as two, and use dynamic SQL for the queries. The current table name is retrieved and used.

This works well for log swapping or rotation on large DB2 systems. a simple mod check can be used in support to work out which table will be in use on a future date.

0

Like Oracle's other Database product, MySql does not support "wrapping" DDL within a Transaction. So that's the best option straight out of the window.

Your next best choice would probably be to explicitly lock the table and rename one "over" the other. I'm not sure how MySql would take to locking a table and then replacing it wholesale with a another table, but I doubt it would be that big a deal.

A View might be a good choice of you were doing this sort of thing regularly, say as part of an Extract-Transform-Load (ETL), bulk data transfer process.
Your Application would run against one table, you would load fresh data into the other and then, when you want to start using the new data, you would "flip" the view to look at the newly-loaded table. Of course, that means you have the old and new data in the database at the same time, so you'll need [at least] twice the storage space for this table, but the "flip" is pretty much instantaneous.

The last option, just for completeness, would be to use delete or truncate one table and insert all the new data into it from the other.
If you used a transaction, the database would take care of the locking for you but everything else would stop dead while this was happening. It'll take ages to do, compared to the other options, and it will thrash your database, with all the logging it will generate, but it is another option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.