Details
-
Bug
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.11
-
None
-
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'
Attachments
Activity
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?
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.
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
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:21CURRENT_TEST: main.testmysqltest: 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