3457 Jon Olav Hauglid 2011-12-20
WL#5534 Online ALTER, Phase 1.
Patch #70: Review changes:
- Extend test coverage of MDL_SHARED_UPGRADABLE
modified:
mysql-test/r/mdl_sync.result
mysql-test/t/mdl_sync.test
3456 Jon Olav Hauglid 2011-12-20
WL#5534 Online ALTER, Phase 1.
Patch #69: Review changes:
- Introduce Alter_table_ctx class, representing
ALTER TABLE runtime context.
- Move datetime_field / error_of_not_empty from
Alter_info to Alter_table_ctx.
- Move db/table_name/alias/new_db/new_name/new_alias
to Alter_table_ctx.
modified:
sql/sql_alter.cc
sql/sql_alter.h
sql/sql_partition_admin.cc
sql/sql_table.cc
sql/sql_table.h
=== modified file 'mysql-test/r/mdl_sync.result'
--- a/mysql-test/r/mdl_sync.result 2011-12-14 18:48:50 +0000
+++ b/mysql-test/r/mdl_sync.result 2011-12-20 15:11:54 +0000
@@ -48,8 +48,13 @@ select count(*) from t1;
count(*)
0
insert into t1 values (1), (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
# Check that SNW lock is compatible with it. To do this use ALTER TABLE
-# which will fail after opening the table and thus obtaining SNW metadata
+# which will fail during copying the table and thus obtaining SNW metadata
# lock.
alter table t1 add primary key (c1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
@@ -139,8 +144,13 @@ select count(*) from t1;
count(*)
3
insert into t1 values (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
# Check that SNW lock is compatible with it. To do this use ALTER TABLE
-# which will fail after opening the table and thus obtaining SNW metadata
+# which will fail during copying the table and thus obtaining SNW metadata
# lock.
alter table t1 add primary key (c1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
@@ -244,8 +254,13 @@ select count(*) from t1;
count(*)
3
insert into t1 values (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
# Check that SNW lock is compatible with it. To do this use ALTER TABLE
-# which will fail after opening the table and thus obtaining SNW metadata
+# which will fail during copying the table and thus obtaining SNW metadata
# lock.
alter table t1 add primary key (c1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
@@ -334,8 +349,13 @@ c1
# effects of concurrent insert.
select * from t1;
insert into t1 values (1);
+# Check that SU lock is compatible with it. To do this use ALTER TABLE
+# which will fail when constructing .frm and thus obtaining SU metadata
+# lock.
+alter table t1 add index (not_exist);
+ERROR 42000: Key column 'not_exist' doesn't exist in table
# Check that SNW lock is not compatible with SW lock.
-# Again we use ALTER TABLE which fails after opening
+# Again we use ALTER TABLE which fails during copying
# the table to avoid upgrade of SNW -> X.
# Sending:
alter table t1 add primary key (c1);;
@@ -527,13 +547,13 @@ ERROR 23000: Duplicate entry '1' for key
# Reaping DELETE.
#
# Switching to connection 'default'.
-set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
# Sending:
-alter table t1 add primary key (c1);;
+alter table t1 add primary key (c1), lock=shared, algorithm=copy;;
#
# Switching to connection 'mdl_con1'.
set debug_sync= 'now WAIT_FOR locked';
-# Check that SNW lock is incompatible with SNW lock.
+# Check that SU lock is incompatible with SNW lock.
# Sending:
alter table t1 add primary key (c1);;
#
@@ -550,10 +570,14 @@ ERROR 23000: Duplicate entry '1' for key
# Reaping another ALTER TABLE.
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
#
+# Note that we can't easily check SNW vs SNW locks since
+# SNW is only used by ALTER TABLE after upgrading from SU
+# and SU is also incompatible with SNW.
+#
# Switching to connection 'default'.
-set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
# Sending:
-alter table t1 add primary key (c1);;
+alter table t1 add primary key (c1), lock=shared, algorithm=copy;;
#
# Switching to connection 'mdl_con1'.
set debug_sync= 'now WAIT_FOR locked';
@@ -654,12 +678,12 @@ unlock tables;
lock table t1 write;
#
# Switching to connection 'mdl_con1'.
-# Check that SNW lock is incompatible with SNRW lock.
+# Check that SU lock is incompatible with SNRW lock.
# Sending:
alter table t1 add primary key (c1);;
#
# Switching to connection 'default'.
-# Check that the above ALTER is blocked because of UNWR lock.
+# Check that the above ALTER is blocked because of SNRW lock.
# Unblock ALTER.
unlock tables;
#
@@ -667,6 +691,10 @@ unlock tables;
# Reaping ALTER TABLE.
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
#
+# Note that we can't easily check SNW vs SNRW locks since
+# SNW is only used by ALTER TABLE after upgrading from SU
+# and SU is also incompatible with SNRW.
+#
# Switching to connection 'default'.
lock table t1 write;
#
@@ -710,7 +738,7 @@ rename table t2 to t1;
# Switching to connection 'default'.
#
#
-# 7) Now do the same round of tests for X lock. We use additional
+# 8) Now do the same round of tests for X lock. We use additional
# table to get long-lived lock of this type.
#
create table t2 (c1 int);
@@ -838,7 +866,7 @@ rename table t1 to t2;;
#
# Switching to connection 'mdl_con1'.
# Check that RENAME has acquired X lock on t1 and is waiting for t2.
-# Check that SNW lock is incompatible with X lock.
+# Check that SU lock is incompatible with X lock.
# Sending:
alter table t1 add primary key (c1);;
#
@@ -855,7 +883,11 @@ ERROR 42S01: Table 't2' already exists
# Switching to connection 'mdl_con1'.
# Reaping ALTER.
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-#
+#
+# Note that we can't easily check SNW vs X locks since
+# SNW is only used by ALTER TABLE after upgrading from SU
+# and SU is also incompatible with X.
+#
# Switching to connection 'mdl_con2'.
# Prepare for blocking RENAME TABLE.
lock tables t2 read;
@@ -916,6 +948,9 @@ rename table t3 to t1;
# are pending. I.e. let us test rules for priorities between
# different types of metadata locks.
#
+# Note: No tests for pending SU lock as this lock requires
+# even stronger active or pending lock.
+#
#
# Switching to connection 'mdl_con2'.
#
@@ -1232,6 +1267,9 @@ unlock tables;
# transactional context. Obviously we are mostly interested
# in conflicting types of locks.
#
+# Note: No tests for active/pending SU lock since
+# ALTER TABLE is in its own transaction.
+#
#
# 1) Let us check how various locks used within transactional
# context interact with active/pending SNW lock.
=== modified file 'mysql-test/t/mdl_sync.test'
--- a/mysql-test/t/mdl_sync.test 2011-12-14 18:48:50 +0000
+++ b/mysql-test/t/mdl_sync.test 2011-12-20 15:11:54 +0000
@@ -110,8 +110,13 @@ select column_name from information_sche
table_schema='test' and table_name='t1';
select count(*) from t1;
insert into t1 values (1), (1);
+--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail when constructing .frm and thus obtaining SU metadata
+--echo # lock.
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 add index (not_exist);
--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
---echo # which will fail after opening the table and thus obtaining SNW metadata
+--echo # which will fail during copying the table and thus obtaining SNW metadata
--echo # lock.
--error ER_DUP_ENTRY
alter table t1 add primary key (c1);
@@ -230,8 +235,13 @@ select column_name from information_sche
table_schema='test' and table_name='t1';
select count(*) from t1;
insert into t1 values (1);
+--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail when constructing .frm and thus obtaining SU metadata
+--echo # lock.
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 add index (not_exist);
--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
---echo # which will fail after opening the table and thus obtaining SNW metadata
+--echo # which will fail during copying the table and thus obtaining SNW metadata
--echo # lock.
--error ER_DUP_ENTRY
alter table t1 add primary key (c1);
@@ -359,8 +369,13 @@ select column_name from information_sche
table_schema='test' and table_name='t1';
select count(*) from t1;
insert into t1 values (1);
+--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail when constructing .frm and thus obtaining SU metadata
+--echo # lock.
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 add index (not_exist);
--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
---echo # which will fail after opening the table and thus obtaining SNW metadata
+--echo # which will fail during copying the table and thus obtaining SNW metadata
--echo # lock.
--error ER_DUP_ENTRY
alter table t1 add primary key (c1);
@@ -477,8 +492,13 @@ select column_name from information_sche
select * from t1;
--enable_result_log
insert into t1 values (1);
+--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE
+--echo # which will fail when constructing .frm and thus obtaining SU metadata
+--echo # lock.
+--error ER_KEY_COLUMN_DOES_NOT_EXITS
+alter table t1 add index (not_exist);
--echo # Check that SNW lock is not compatible with SW lock.
---echo # Again we use ALTER TABLE which fails after opening
+--echo # Again we use ALTER TABLE which fails during copying
--echo # the table to avoid upgrade of SNW -> X.
--echo # Sending:
--send alter table t1 add primary key (c1);
@@ -740,14 +760,14 @@ connection mdl_con1;
--echo #
--echo # Switching to connection 'default'.
connection default;
-set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
--echo # Sending:
---send alter table t1 add primary key (c1);
+--send alter table t1 add primary key (c1), lock=shared, algorithm=copy;
--echo #
--echo # Switching to connection 'mdl_con1'.
connection mdl_con1;
set debug_sync= 'now WAIT_FOR locked';
---echo # Check that SNW lock is incompatible with SNW lock.
+--echo # Check that SU lock is incompatible with SNW lock.
--echo # Sending:
--send alter table t1 add primary key (c1);
--echo #
@@ -774,11 +794,15 @@ connection mdl_con1;
--error ER_DUP_ENTRY
--reap
--echo #
+--echo # Note that we can't easily check SNW vs SNW locks since
+--echo # SNW is only used by ALTER TABLE after upgrading from SU
+--echo # and SU is also incompatible with SNW.
+--echo #
--echo # Switching to connection 'default'.
connection default;
-set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish';
--echo # Sending:
---send alter table t1 add primary key (c1);
+--send alter table t1 add primary key (c1), lock=shared, algorithm=copy;
--echo #
--echo # Switching to connection 'mdl_con1'.
connection mdl_con1;
@@ -920,13 +944,13 @@ lock table t1 write;
--echo #
--echo # Switching to connection 'mdl_con1'.
connection mdl_con1;
---echo # Check that SNW lock is incompatible with SNRW lock.
+--echo # Check that SU lock is incompatible with SNRW lock.
--echo # Sending:
--send alter table t1 add primary key (c1);
--echo #
--echo # Switching to connection 'default'.
connection default;
---echo # Check that the above ALTER is blocked because of UNWR lock.
+--echo # Check that the above ALTER is blocked because of SNRW lock.
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock" and
@@ -941,6 +965,10 @@ connection mdl_con1;
--error ER_DUP_ENTRY
--reap
--echo #
+--echo # Note that we can't easily check SNW vs SNRW locks since
+--echo # SNW is only used by ALTER TABLE after upgrading from SU
+--echo # and SU is also incompatible with SNRW.
+--echo #
--echo # Switching to connection 'default'.
connection default;
lock table t1 write;
@@ -1005,7 +1033,7 @@ rename table t2 to t1;
connection default;
--echo #
--echo #
---echo # 7) Now do the same round of tests for X lock. We use additional
+--echo # 8) Now do the same round of tests for X lock. We use additional
--echo # table to get long-lived lock of this type.
--echo #
create table t2 (c1 int);
@@ -1209,7 +1237,7 @@ select count(*) = 1 from information_sch
where state = "Waiting for table metadata lock" and
info = "rename table t1 to t2";
--source include/wait_condition.inc
---echo # Check that SNW lock is incompatible with X lock.
+--echo # Check that SU lock is incompatible with X lock.
--echo # Sending:
--send alter table t1 add primary key (c1);
--echo #
@@ -1236,7 +1264,11 @@ connection mdl_con1;
--echo # Reaping ALTER.
--error ER_DUP_ENTRY
--reap
---echo #
+--echo #
+--echo # Note that we can't easily check SNW vs X locks since
+--echo # SNW is only used by ALTER TABLE after upgrading from SU
+--echo # and SU is also incompatible with X.
+--echo #
--echo # Switching to connection 'mdl_con2'.
connection mdl_con2;
--echo # Prepare for blocking RENAME TABLE.
@@ -1334,6 +1366,9 @@ rename table t3 to t1;
--echo # are pending. I.e. let us test rules for priorities between
--echo # different types of metadata locks.
--echo #
+--echo # Note: No tests for pending SU lock as this lock requires
+--echo # even stronger active or pending lock.
+--echo #
--echo #
--echo # Switching to connection 'mdl_con2'.
@@ -1783,6 +1818,9 @@ connection default;
--echo # transactional context. Obviously we are mostly interested
--echo # in conflicting types of locks.
--echo #
+--echo # Note: No tests for active/pending SU lock since
+--echo # ALTER TABLE is in its own transaction.
+--echo #
--echo #
--echo # 1) Let us check how various locks used within transactional
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk-wl5534 branch (jon.hauglid:3456 to 3457) WL#5534 | Jon Olav Hauglid | 21 Dec |
