[MDEV-29588] Swap atomically a view in place of an altered table Created: 2022-09-21  Updated: 2024-01-19

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.11
Fix Version/s: 10.11

Type: Bug Priority: Critical
Reporter: Michael Widenius Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

In some scenarios it there is a need to add/drop columns for a table and replace it with a view to allow old applications to work unchanged until they are updated.

As MariaDB does not have transactional DDL, we have to do it in some other way.

The following 'almost' works:

create table t1 (a int);
 
create table t2 like t1;
 
create table v1 select a from t2;
 
drop table t2;
 
lock table t1 write, v1 write;
 
alter table t1 add column (b int);
 
rename table t1 to t2, v1 to t1;
 
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

The task is to allow the above rename table to succeed.

Another possible solution to do this nicely (from the user's point of view) is to extend alter table:

alter table t1 add column (b int) rename as t1 replace with v1;

Where the 'replace with v1 means: rename v1 to t1'

 Comments   
Comment by Oleksandr Byelkin [ 2024-01-15 ]

actually this shows better the first problem:

create table t1 (a int);
 
create table v1 select a from t1;
 
lock table t1 write, v1 write;
 
alter table t1 add column (b int);
 
rename table t1 to t2;
 
rename table v1 to t1;

it breaks here:

create table t1 (a int);
 
create table v1 select a from t1;
 
lock table t1 write, v1 write;
 
alter table t1 add column (b int);
 
rename table t1 to t2;
 
main.test                                [ fail ]
 
        Test ended at 2024-01-15 10:26:21
 
 
 
CURRENT_TEST: main.test
 
mysqltest: At line 5: query 'rename table t1 to t2' failed: ER_LOCK_OR_ACTIVE_TRANSACTION (1192): Can't execute the given command because you have active locked tables or an active transaction

Comment by Oleksandr Byelkin [ 2024-01-15 ]

i.e. we are trying to rename table under lock. IMHO it hange semantic of our lock:

create table t1 (a int);
 
lock table t1 write;
 
rename table t1 to t2;
 
main.test                                [ fail ]
 
        Test ended at 2024-01-15 10:32:08
 
 
 
CURRENT_TEST: main.test
 
mysqltest: At line 3: query 'rename table t1 to t2' failed: ER_LOCK_OR_ACTIVE_TRANSACTION (1192): Can't execute the given command because you have active locked tables or an active transaction

or we should rewrite our admin operation so it will not be sequence of action but renaming several tables will be looked as one action?

Comment by Michael Widenius [ 2024-01-19 ]

Note that this works on a locked table:

create or replace table t1 (a int);
 
lock table t1 write;
 
alter table t1 rename to t2;

This transforms the lock from t1 to t2.
Part of fixing the problem is to for rename table what we do for alter table when it comes to locked tables.

We still have a problem with the original problem as v1 refers to t1.

The correct query to solve is the following:

drop table if exists t1,t,2,v1;
 
create table t1 (a int);
 
create table t2 like t1;
 
create table v1 select a from t2;
 
drop table t2;
 
lock table t1 write, v1 write;
 
alter table t1 add column (b int);
 
rename table t1 to t2, v1 to t1;
 
unlock tables;

We cannot unfortunately use:

alter table t1 add column (b int), rename to t2;
 
rename table t1 to t2, v1 to t1;

As then for a short time 't1' would not be locked and anyone trying to access it would get a table does not exist error.

Comment by Sergei Golubchik [ 2024-01-19 ]

What about

drop table if exists t1,t2,v1;
 
create table t1 (a int);
 
create table t2 like t1;
 
create table v1 select a from t2;
 
drop table t2;
 
 
 
rename table t1 to t2, v1 to t1;
 
alter table t2 add column (b int);

this works without any table locks whatsoever

Generated at Thu Feb 08 10:09:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.