I need to rename two tables in one atomic operation so that user will never be able to see the database in its intermediate state.
I'm using MySQL and noticed that this case is perfectly described in the documentation:
13.3.3 Statements That Cause an Implicit Commit
The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement
[...]
Data definition language (DDL) statements that define or modify database objects. ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, INSTALL PLUGIN (as of MySQL 5.7.6), RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN (as of MySQL 5.7.6).
But maybe there's some kind of workaround or something like this?
My situation looks like this:
current
next
current
table to the current_%current_date_time%
and the next
table to the current
in one atomic operationMySQL allows to create a temporary table with the same name as a permanent table.
You can have tables of the same name only if they are in separate databases, and you use the database name as a qualifier.
Click the SQL tab at the top. In the text box, enter the following command: ALTER TABLE exampletable RENAME TO new_table_name; Replace exampletable with your table's name and replace new_table_name with the new name for your table.
Well, easy...
RENAME TABLE current TO current_20151221, next TO current;
as is stated in the manual. There it says that it's an atomic operation. Just to clear this up, implicit commits have nothing to do with it. That's a different story. That just says, that those statements end an open transaction.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With