Replacing MYSQL table with data from another table - no downtime

Database Administrators Asked by Phil W. on September 11, 2020

I have two tables with identical structures:

Table1 and Table2


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:


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.

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

2 Answers

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.

Answered by mckenzm on September 11, 2020

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.

Answered by CalZ on September 11, 2020

Add your own answers!

Related Questions

Problems loading pgAudit library on Postgres 12.2

2  Asked on October 28, 2021 by ivanovich


How to start Neo4j on Azure

1  Asked on October 28, 2021 by david-a-stumpf


Mongodb 4.2 Brute Force Defense

0  Asked on October 28, 2021 by darii-petru


Bridging Table Properties

2  Asked on October 28, 2021 by zv426


Using Postgres param in a function

1  Asked on October 28, 2021 by ovatsug25


Cannot access MYSQL safe mode using skip-grant-tables on Windows

0  Asked on October 28, 2021 by augustus-buckman


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir