| Bug #24738 | create .. select is not isolated properly | ||
|---|---|---|---|
| Submitted: | 30 Nov 2006 22:28 | Modified: | 16 May 2007 19:32 |
| Reporter: | Andrei Elkin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.14 | OS: | Any |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
| Tags: | rt_q1_2007 | ||
[30 Nov 2006 22:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/16260 ChangeSet@1.2374, 2006-12-01 00:52:50+02:00, aelkin@dsl-hkibras-fe30f900-107.dhcp.inet.fi +1 -0 Bug #24738 create .. select is not isolated properly The query constists of two separate parts: create new and insert selected. The new table can be modified of dropped by an external connection before the second inserting part acquires the lock for new table. There are several at least undesired things: 1. The user can end up to see ERROR 1146 (42S02): Table 'new_table' does not exists. 2. new table can be DROPed by external connection, and to footprint the fact in binlog, whereas CREATE..SELECT would end with the error and did not leave anything in binlog. Eventually slave will stop because DROP won't find the new table on slave; many others. Fixed with lock_and_wait_for_table_name before mysql_create_table and unlock_table_name after mysql_lock_tables at create_table_from_items.
[5 Dec 2006 12:55]
Dmitry Lenev
ChangeSet@1.2342, 2006-12-05 15:44:34+03:00, dlenev@mockturtle.local +12 -0
Proposed patch for bugs:
#20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
with locked tables"
#20903 "Crash when using CREATE TABLE .. SELECT and triggers"
#24738 "CREATE TABLE ... SELECT is not isolated properly"
http://lists.mysql.com/commits/16454
[25 Apr 2007 23:40]
Konstantin Osipov
Approved the second patch by email.
[16 May 2007 13:48]
Bugs System
Pushed into 5.0.42
[16 May 2007 13:49]
Bugs System
Pushed into 5.1.19-beta
[16 May 2007 19:32]
Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs. Concurrent execution of CREATE TABLE ... SELECT and other statements involving the target table suffered from various race conditions, some of which might have led to deadlocks.
Description: to allow modification and DROPping the new table before select's part of the query will start. Consider the following: connection create; connection abuser; create table n3 (id int primary key) \ drop table n3; // OK select * from o1; // ERROR 1146 (42S02): Table 'test.n3' // doesn't exist Rather sofisticated objection imo, indeed there were no table `n3' and I was going to create it. How to repeat: 1. visual check gets there is no look for new table at create_table_from_items since mysql_create_table and till mysql_lock_tables. 2. we need either some high level of concurrency with create .. select connection , e.g many droppers of new table I caught the warning at description at the first execution of the following (no loop to catch the exact line, which must be of connection create) connect (create,localhost,root,,); connect (drop0,localhost,root,,); connect (drop1,localhost,root,,); connect (drop2,localhost,root,,); connect (drop3,localhost,root,,); connect (drop4,localhost,root,,); connect (drop5,localhost,root,,); connect (drop6,localhost,root,,); connect (drop7,localhost,root,,); drop table if exists old, new; create table old (n int); connection drop0; send drop table if exists new; connection drop1; send drop table if exists new; connection drop2; send drop table if exists new; connection drop3; send drop table if exists new; connection create; send create table new select * from old; connection drop4; send drop table if exists new; connection drop5; send drop table if exists new; connection drop6; send drop table if exists new; connection drop7; send drop table if exists new; connection drop0; reap; connection drop1; reap; connection drop2; reap; connection drop3; reap; connection create; reap; connection drop4; reap; connection drop5; reap; connection drop6; reap; connection drop7; reap; connection drop0; send drop table if exists new; connection drop1; send drop table if exists new; connection drop2; send drop table if exists new; connection drop3; send drop table if exists new; connection create; send create table new select * from old; connection drop4; send drop table if exists new; connection drop5; send drop table if exists new; connection drop6; send drop table if exists new; connection drop7; send drop table if exists new; connection drop0; reap; connection drop1; reap; connection drop2; reap; connection drop3; reap; connection create; reap; connection drop4; reap; connection drop5; reap; connection drop6; reap; connection drop7; reap; connection drop0; send drop table if exists new; connection drop1; send drop table if exists new; connection drop2; send drop table if exists new; connection drop3; send drop table if exists new; connection create; send create table new select * from old; connection drop4; send drop table if exists new; connection drop5; send drop table if exists new; connection drop6; send drop table if exists new; connection drop7; send drop table if exists new; connection drop0; reap; connection drop1; reap; connection drop2; reap; connection drop3; reap; connection create; reap; connection drop4; reap; connection drop5; reap; connection drop6; reap; connection drop7; reap; Suggested fix: lock_and_wait_for_table_name before mysql_create_table and unlock_table_name after mysql_lock_tables.