#At file:///export/home/x/mysql-5.5-runtime-bug57002/ based on revid:jon.hauglid@stripped
3152 Jon Olav Hauglid 2010-10-04
Bug #57002 Assert in upgrade_shared_lock_to_exclusive()
for ALTER TABLE + MERGE tables
The patch for Bug#56292 changed how metadata locks are taken for MERGE
tables. After the patch, locking the MERGE table will also lock the
children tables with the same metadata lock type. This means that
LOCK TABLES on a MERGE table also will implicitly do LOCK TABLES on
the children tables.
A consequence of this change, is that it is possible to do LOCK TABLES
on a child table both explicitly and implicitly with the same statement
and that these two locks can be of different strength. For example,
LOCK TABLES child READ, merge WRITE.
In LOCK TABLES mode, we are not allowed to take new locks and each
statement must therefore try to find an existing TABLE instance with
a suitable lock. The code that searched for a suitable TABLE instance,
only considered table level locks. If a child table was locked twice,
it was therefore possible for this code to find a TABLE instance with
suitable table level locks but without suitable metadata lock.
This problem caused the assert in upgrade_shared_lock_to_exclusive()
to be triggered as it tried to upgrade a MDL_SHARED lock to
EXCLUSIVE. The problem was a regression caused by the patch for
Bug#56292.
This patch fixes the problem by taking metadata lock type into account
when trying to find a suitable TABLE instance in LOCK TABLES mode.
Test case added to merge.test.
modified:
mysql-test/r/create.result
mysql-test/r/events_2.result
mysql-test/r/lock.result
mysql-test/r/merge.result
mysql-test/r/multi_update.result
mysql-test/t/create.test
mysql-test/t/events_2.test
mysql-test/t/lock.test
mysql-test/t/merge.test
mysql-test/t/multi_update.test
sql/sql_base.cc
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result 2010-08-25 19:00:38 +0000
+++ b/mysql-test/r/create.result 2010-10-04 09:23:26 +0000
@@ -857,10 +857,9 @@ ERROR HY000: Table 't2' was locked with
unlock tables;
lock table t1 read, t2 write;
create table t2 select * from t1;
-ERROR 42S01: Table 't2' already exists
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
create table if not exists t2 select * from t1;
-Warnings:
-Note 1050 Table 't2' already exists
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
select * from t1;
i
1
=== modified file 'mysql-test/r/events_2.result'
--- a/mysql-test/r/events_2.result 2009-12-22 09:35:56 +0000
+++ b/mysql-test/r/events_2.result 2010-10-04 09:23:26 +0000
@@ -169,15 +169,15 @@ select event_name from information_schem
event_name
e1
create event e2 on schedule every 10 hour do select 1;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
alter event e2 disable;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
alter event e2 rename to e3;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
drop event e2;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
drop event e1;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
unlock tables;
lock table t1 write, mysql.event read;
show create event e1;
@@ -187,15 +187,15 @@ select event_name from information_schem
event_name
e1
create event e2 on schedule every 10 hour do select 1;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
alter event e2 disable;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
alter event e2 rename to e3;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
drop event e2;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
drop event e1;
-ERROR HY000: Table 'event' was locked with a READ lock and can't be updated
+ERROR HY000: Table 'event' was not locked with LOCK TABLES
unlock tables;
lock table t1 read, mysql.event write;
ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
=== modified file 'mysql-test/r/lock.result'
--- a/mysql-test/r/lock.result 2010-02-01 11:43:06 +0000
+++ b/mysql-test/r/lock.result 2010-10-04 09:23:26 +0000
@@ -8,7 +8,7 @@ NULL 1
update t1 set id=-1 where id=1;
LOCK TABLE t1 READ;
update t1 set id=1 where id=1;
-ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
create table t2 SELECT * from t1;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
create temporary table t2 SELECT * from t1;
@@ -68,9 +68,9 @@ lock tables t1 write, t2 read;
delete from t1 using t1,t2 where t1.a=t2.a;
delete t1 from t1,t2 where t1.a=t2.a;
delete from t2 using t1,t2 where t1.a=t2.a;
-ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
delete t2 from t1,t2 where t1.a=t2.a;
-ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
drop table t1,t2;
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
unlock tables;
@@ -203,7 +203,7 @@ WHERE t1.table1_rowid = t2.table2_rowid
# While implementing the patch we didn't break old behavior;
# The following sub-select should still requires a write lock:
SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);
-ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP TABLE t1,t2;
End of 5.1 tests.
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result 2010-09-30 10:43:43 +0000
+++ b/mysql-test/r/merge.result 2010-10-04 09:23:26 +0000
@@ -3661,4 +3661,15 @@ REPAIR TABLE t2 USE_FRM;
Table Op Msg_type Msg_text
test.t2 repair note The storage engine for the table doesn't support repair
DROP TABLE t1, t2;
+#
+# Bug#57002 Assert in upgrade_shared_lock_to_exclusive()
+# for ALTER TABLE + MERGE tables
+#
+DROP TABLE IF EXISTS t1, m1;
+CREATE TABLE t1(a INT) engine=myisam;
+CREATE TABLE m1(a INT) engine=merge UNION(t1);
+LOCK TABLES t1 READ, m1 WRITE;
+ALTER TABLE t1 engine=myisam;
+UNLOCK TABLES;
+DROP TABLE m1, t1;
End of 6.0 tests
=== modified file 'mysql-test/r/multi_update.result'
--- a/mysql-test/r/multi_update.result 2010-09-07 08:00:52 +0000
+++ b/mysql-test/r/multi_update.result 2010-10-04 09:23:26 +0000
@@ -152,10 +152,11 @@ insert into t1 values(1,1);
insert into t2 values(1,10),(2,20);
LOCK TABLES t1 write, t2 read;
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
-ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
-ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
unlock tables;
LOCK TABLES t1 write, t2 write;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test 2010-08-18 09:35:41 +0000
+++ b/mysql-test/t/create.test 2010-10-04 09:23:26 +0000
@@ -795,9 +795,9 @@ create table t2 select * from t1;
create table if not exists t2 select * from t1;
unlock tables;
lock table t1 read, t2 write;
---error ER_TABLE_EXISTS_ERROR
+--error ER_TABLE_NOT_LOCKED
create table t2 select * from t1;
-# This is the only case which really works.
+--error ER_TABLE_NOT_LOCKED
create table if not exists t2 select * from t1;
select * from t1;
unlock tables;
=== modified file 'mysql-test/t/events_2.test'
--- a/mysql-test/t/events_2.test 2009-12-22 09:35:56 +0000
+++ b/mysql-test/t/events_2.test 2010-10-04 09:23:26 +0000
@@ -246,15 +246,15 @@ lock table t1 read, mysql.event read;
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
create event e2 on schedule every 10 hour do select 1;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
alter event e2 disable;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
alter event e2 rename to e3;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
drop event e2;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
drop event e1;
unlock tables;
#
@@ -263,15 +263,15 @@ lock table t1 write, mysql.event read;
--replace_regex /STARTS '[^']+'/STARTS '#'/
show create event e1;
select event_name from information_schema.events;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
create event e2 on schedule every 10 hour do select 1;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
alter event e2 disable;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
alter event e2 rename to e3;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
drop event e2;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
drop event e1;
unlock tables;
#
=== modified file 'mysql-test/t/lock.test'
--- a/mysql-test/t/lock.test 2010-02-01 11:43:06 +0000
+++ b/mysql-test/t/lock.test 2010-10-04 09:23:26 +0000
@@ -14,7 +14,7 @@ LOCK TABLE t1 WRITE;
select dummy1,count(distinct id) from t1 group by dummy1;
update t1 set id=-1 where id=1;
LOCK TABLE t1 READ;
---error 1099
+--error ER_TABLE_NOT_LOCKED
update t1 set id=1 where id=1;
--error 1100
create table t2 SELECT * from t1;
@@ -92,9 +92,9 @@ create table t2 ( a int(11) not null aut
lock tables t1 write, t2 read;
delete from t1 using t1,t2 where t1.a=t2.a;
delete t1 from t1,t2 where t1.a=t2.a;
---error 1099
+--error ER_TABLE_NOT_LOCKED
delete from t2 using t1,t2 where t1.a=t2.a;
---error 1099
+--error ER_TABLE_NOT_LOCKED
delete t2 from t1,t2 where t1.a=t2.a;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
drop table t1,t2;
@@ -255,7 +255,7 @@ WHERE t1.table1_rowid = t2.table2_rowid
) ;
--echo # While implementing the patch we didn't break old behavior;
--echo # The following sub-select should still requires a write lock:
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);
UNLOCK TABLES;
DROP TABLE t1,t2;
=== modified file 'mysql-test/t/merge.test'
--- a/mysql-test/t/merge.test 2010-09-30 10:43:43 +0000
+++ b/mysql-test/t/merge.test 2010-10-04 09:23:26 +0000
@@ -2776,6 +2776,26 @@ REPAIR TABLE t2 USE_FRM;
DROP TABLE t1, t2;
+--echo #
+--echo # Bug#57002 Assert in upgrade_shared_lock_to_exclusive()
+--echo # for ALTER TABLE + MERGE tables
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, m1;
+--enable_warnings
+
+CREATE TABLE t1(a INT) engine=myisam;
+CREATE TABLE m1(a INT) engine=merge UNION(t1);
+LOCK TABLES t1 READ, m1 WRITE;
+
+# This caused an assert
+ALTER TABLE t1 engine=myisam;
+
+UNLOCK TABLES;
+DROP TABLE m1, t1;
+
+
--echo End of 6.0 tests
--disable_result_log
=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test 2010-09-07 08:00:52 +0000
+++ b/mysql-test/t/multi_update.test 2010-10-04 09:23:26 +0000
@@ -160,10 +160,11 @@ create table t2 (n int(10) not null prim
insert into t1 values(1,1);
insert into t2 values(1,10),(2,20);
LOCK TABLES t1 write, t2 read;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
+--error ER_TABLE_NOT_LOCKED
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
+--error ER_TABLE_NOT_LOCKED
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
unlock tables;
LOCK TABLES t1 write, t2 write;
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2010-09-30 13:29:12 +0000
+++ b/sql/sql_base.cc 2010-10-04 09:23:26 +0000
@@ -2742,8 +2742,11 @@ bool open_table(THD *thd, TABLE_LIST *ta
distance > 0 - we have lock mode higher then we require
distance == 0 - we have lock mode exactly which we need
*/
- if ((best_distance < 0 && distance > best_distance) ||
- (distance >= 0 && distance < best_distance))
+ if (((best_distance < 0 && distance > best_distance) ||
+ (distance >= 0 && distance < best_distance)) &&
+ ((flags & (MYSQL_OPEN_FORCE_SHARED_MDL |
+ MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL)) ||
+ table->mdl_ticket->has_stronger_or_equal_type(table_list->mdl_request.type)))
{
best_distance= distance;
best_table= table;
Attachment: [text/bzr-bundle] bzr/jon.hauglid@oracle.com-20101004092326-tvocs77fs03smz4m.bundle
