#At file:///home/anders/work/bzrwork1/mysql-trunk-wl5370/ based on revid:kostja@stripped
3076 Li-Bing.Song@stripped 2010-07-06
Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over
multiple tables
The decision was made to simplify the CREATE TABLE IF NOT EXISTS. It is not
going to do anything if the table already exists. (Before it tried to insert the
SELECT result into the existing table).
In order to keep consisteny between New master and Old slave, the statement
will not be binlogged if the table already exists.
Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
are replaced with the INSERT INTO SELECT
modified:
mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
mysql-test/include/mix1.inc
mysql-test/r/auto_increment.result
mysql-test/r/create.result
mysql-test/r/ps_ddl.result
mysql-test/r/trigger.result
mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
mysql-test/suite/innodb/r/innodb_mysql.result
mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result
mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
mysql-test/t/auto_increment.test
mysql-test/t/create.test
mysql-test/t/trigger.test
sql/handler.h
sql/sql_base.cc
sql/sql_insert.cc
sql/sql_parse.cc
sql/sql_prepare.cc
sql/sql_table.cc
sql/sql_view.cc
sql/sql_yacc.yy
sql/table.h
=== modified file 'mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test'
--- a/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test 2010-04-28 12:47:49 +0000
+++ b/mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test 2010-07-06 08:45:39 +0000
@@ -262,7 +262,6 @@ DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (4,4);
---error ER_DUP_ENTRY
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
SELECT * from t2;
TRUNCATE table t2;
@@ -277,11 +276,9 @@ CREATE TEMPORARY TABLE t2 (a int, b int,
INSERT INTO t1 values (7,7);
ROLLBACK;
INSERT INTO t1 values (8,8);
---error ER_DUP_ENTRY
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
COMMIT;
INSERT INTO t1 values (9,9);
---error ER_DUP_ENTRY
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
ROLLBACK;
SELECT * from t2;
@@ -291,11 +288,9 @@ INSERT INTO t1 values (10,10);
INSERT INTO t2 select * from t1;
SELECT * from t1;
INSERT INTO t2 values (100,100);
---error ER_DUP_ENTRY
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
COMMIT;
INSERT INTO t2 values (101,101);
---error ER_DUP_ENTRY
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
ROLLBACK;
SELECT * from t2;
=== modified file 'mysql-test/include/mix1.inc'
--- a/mysql-test/include/mix1.inc 2010-05-25 20:01:38 +0000
+++ b/mysql-test/include/mix1.inc 2010-07-06 08:45:39 +0000
@@ -742,7 +742,6 @@ drop table if exists t2;
CREATE TABLE t2 (a int, b int, primary key (a));
BEGIN;
INSERT INTO t2 values(100,100);
---error ER_DUP_ENTRY
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
SELECT * from t2;
ROLLBACK;
@@ -756,13 +755,11 @@ drop table t2;
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
BEGIN;
INSERT INTO t2 values(100,100);
---error ER_DUP_ENTRY
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
SELECT * from t2;
COMMIT;
BEGIN;
INSERT INTO t2 values(101,101);
---error ER_DUP_ENTRY
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
SELECT * from t2;
ROLLBACK;
=== modified file 'mysql-test/r/auto_increment.result'
--- a/mysql-test/r/auto_increment.result 2009-08-20 12:30:59 +0000
+++ b/mysql-test/r/auto_increment.result 2010-07-06 08:45:39 +0000
@@ -469,7 +469,7 @@ drop table t1;
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1), (1);
CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
-CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
UPDATE t2 SET a = 2;
SELECT a FROM t2;
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result 2010-06-11 08:14:38 +0000
+++ b/mysql-test/r/create.result 2010-07-06 08:45:39 +0000
@@ -1,5 +1,6 @@
drop table if exists t1,t2,t3,t4,t5;
drop database if exists mysqltest;
+drop view if exists v1;
create table t1 (b char(0));
insert into t1 values (""),(null);
select * from t1;
@@ -264,15 +265,14 @@ create table if not exists t1 select 1,2
Warnings:
Note 1050 Table 't1' already exists
create table if not exists t1 select 1,2,3,4;
-ERROR 21S01: Column count doesn't match value count at row 1
+Warnings:
+Note 1050 Table 't1' already exists
create table if not exists t1 select 1;
Warnings:
Note 1050 Table 't1' already exists
select * from t1;
1 2 3
1 2 3
-0 1 2
-0 0 1
drop table t1;
flush status;
create table t1 (a int not null, b int, primary key (a));
@@ -280,28 +280,21 @@ insert into t1 values (1,1);
create table if not exists t1 select 2;
Warnings:
Note 1050 Table 't1' already exists
-Warning 1364 Field 'a' doesn't have a default value
select * from t1;
a b
1 1
-0 2
create table if not exists t1 select 3 as 'a',4 as 'b';
Warnings:
Note 1050 Table 't1' already exists
-create table if not exists t1 select 3 as 'a',3 as 'b';
-ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
show warnings;
Level Code Message
Note 1050 Table 't1' already exists
-Error 1062 Duplicate entry '3' for key 'PRIMARY'
show status like "Opened_tables";
Variable_name Value
Opened_tables 2
select * from t1;
a b
1 1
-0 2
-3 4
drop table t1;
create table `t1 `(a int);
ERROR 42000: Incorrect table name 't1 '
@@ -811,7 +804,8 @@ create table t1 (primary key(a)) select
ERROR 42000: Key column 'a' doesn't exist in table
create table t1 (a int);
create table if not exists t1 select 1 as a, 2 as b;
-ERROR 21S01: Column count doesn't match value count at row 1
+Warnings:
+Note 1050 Table 't1' already exists
drop table t1;
create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
@@ -823,25 +817,13 @@ Warnings:
Note 1050 Table 't1' already exists
select * from t1;
i
-1
create table if not exists t1 select * from t1;
ERROR HY000: You can't specify target table 't1' for update in FROM clause
select * from t1;
i
-1
+drop table t1;
create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'coalesce'
-select * from t1;
-i
-1
-alter table t1 add primary key (i);
-create table if not exists t1 (select 2 as i) union all (select 2 as i);
-ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
-select * from t1;
-i
-1
-2
-drop table t1;
create temporary table t1 (j int);
create table if not exists t1 select 1;
select * from t1;
@@ -893,8 +875,6 @@ select * from t2;
i
1
2
-1
-2
unlock tables;
drop table t1, t2;
create table t1 (upgrade int);
@@ -1586,11 +1566,9 @@ CREATE TABLE t1 (a INTEGER AUTO_INCREMEN
INSERT IGNORE INTO t1 (b) VALUES (5);
CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT a FROM t1;
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
-SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
-SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
DROP TABLE t1, t2;
#
@@ -1620,7 +1598,7 @@ drop table if exists t2;
Warnings:
Note 1051 Unknown table 't2'
CREATE TABLE t2 (a int, b int, primary key (a));
-CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+INSERT INTO t2 select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a b
@@ -1633,13 +1611,7 @@ a b
1 1
drop table t2;
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
-CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-SELECT * from t2;
-a b
-1 1
-TRUNCATE table t2;
-INSERT INTO t2 select * from t1;
+INSERT INTO t2 SELECT * FROM t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a b
@@ -1964,11 +1936,7 @@ CREATE TRIGGER f BEFORE INSERT ON t1 FOR
BEGIN
INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ;
END ; |
-CREATE TABLE IF NOT EXISTS t1 (
-`pk` INTEGER NOT NULL AUTO_INCREMENT ,
-`int` INTEGER ,
-PRIMARY KEY ( `pk` )
-) SELECT `pk` , `int_key` FROM B ;
+INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
@@ -2088,3 +2056,37 @@ t2 CREATE TABLE `t2` (
) ENGINE=MyISAM DEFAULT CHARSET=latin1
set @@sql_mode= @old_mode;
drop tables t1, t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+INSERT INTO t1 VALUES (1), (1);
+INSERT INTO t2 VALUES (2), (2);
+CREATE VIEW v1 AS SELECT id FROM t2;
+CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1;
+Warnings:
+Note 1050 Table 'v1' already exists
+SHOW CREATE TABLE v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`id` AS `id` from `t2` latin1 latin1_swedish_ci
+SELECT * FROM t2;
+id
+2
+2
+SELECT * FROM v1;
+id
+2
+2
+DROP VIEW v1;
+CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2;
+CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1;
+Warnings:
+Note 1050 Table 'tt1' already exists
+SELECT * FROM t2;
+id
+2
+2
+SELECT * FROM tt1;
+id
+2
+2
+DROP TEMPORARY TABLE tt1;
+DROP TABLE t1, t2;
=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result 2010-02-20 10:07:32 +0000
+++ b/mysql-test/r/ps_ddl.result 2010-07-06 08:45:39 +0000
@@ -1833,7 +1833,6 @@ SUCCESS
select * from t2;
a
1
-1
execute stmt;
Warnings:
Note 1050 Table 't2' already exists
@@ -1843,8 +1842,6 @@ SUCCESS
select * from t2;
a
1
-1
-1
drop table t2;
create temporary table t2 (a varchar(10));
execute stmt;
@@ -1852,7 +1849,6 @@ Warnings:
Note 1050 Table 't2' already exists
select * from t2;
a
-1
call p_verify_reprepare_count(1);
SUCCESS
=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result 2010-05-08 22:03:35 +0000
+++ b/mysql-test/r/trigger.result 2010-07-06 08:45:39 +0000
@@ -1420,9 +1420,7 @@ create trigger t1_ai after insert on t1
create table t2 (j int);
insert into t2 values (1), (2);
set @a:="";
-create table if not exists t1 select * from t2;
-Warnings:
-Note 1050 Table 't1' already exists
+insert into t1 select * from t2;
select * from t1;
i
7
@@ -1434,9 +1432,7 @@ drop trigger t1_bi;
drop trigger t1_ai;
create table t3 (isave int);
create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
-create table if not exists t1 select * from t2;
-Warnings:
-Note 1050 Table 't1' already exists
+insert into t1 select * from t2;
select * from t1;
i
7
@@ -1622,10 +1618,8 @@ After DELETE, old=REPLACE, inserting a n
After INSERT, new=REPLACE, deleting the duplicate
truncate t1;
truncate t1_op_log;
-create table if not exists t1
+insert into t1
select NULL, "CREATE TABLE ... SELECT, inserting a new key";
-Warnings:
-Note 1050 Table 't1' already exists
set @id=last_insert_id();
select * from t1;
id operation
@@ -1635,10 +1629,8 @@ operation
Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
truncate t1_op_log;
-create table if not exists t1 replace
+replace into t1
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
-Warnings:
-Note 1050 Table 't1' already exists
select * from t1;
id operation
1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
@@ -1817,10 +1809,8 @@ After DELETE, old=REPLACE, inserting a n
After INSERT, new=REPLACE, deleting the duplicate
truncate t1;
truncate t1_op_log;
-create table if not exists v1
+insert into v1
select NULL, "CREATE TABLE ... SELECT, inserting a new key";
-Warnings:
-Note 1050 Table 'v1' already exists
set @id=last_insert_id();
select * from t1;
id operation
@@ -1830,10 +1820,8 @@ operation
Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
truncate t1_op_log;
-create table if not exists v1 replace
+replace into v1
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
-Warnings:
-Note 1050 Table 'v1' already exists
select * from t1;
id operation
1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
@@ -2083,7 +2071,8 @@ BEGIN
UPDATE a_nonextisting_table SET a = 1;
END//
CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1;
-ERROR 42S02: Table 'test.a_nonextisting_table' doesn't exist
+Warnings:
+Note 1050 Table 't2' already exists
SELECT * FROM t2;
a b
DROP TABLE t1, t2;
=== modified file 'mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result 2010-06-01 19:34:40 +0000
+++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result 2010-07-06 08:45:39 +0000
@@ -320,7 +320,8 @@ Note 1051 Unknown table 't2'
CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (4,4);
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
SELECT * from t2;
a b
TRUNCATE table t2;
@@ -338,11 +339,13 @@ Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
INSERT INTO t1 values (8,8);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
COMMIT;
INSERT INTO t1 values (9,9);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
@@ -366,11 +369,13 @@ a b
10 10
INSERT INTO t2 values (100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
COMMIT;
INSERT INTO t2 values (101,101);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
ROLLBACK;
SELECT * from t2;
a b
@@ -412,41 +417,13 @@ master-bin.000001 # Table_map # # table_
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
-master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` (
- `a` int(11) NOT NULL DEFAULT '0',
- `b` int(11) DEFAULT NULL,
- PRIMARY KEY (`a`)
-) ENGINE=InnoDB
-master-bin.000001 # Query # # COMMIT
-master-bin.000001 # Query # # BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
-master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` (
- `a` int(11) NOT NULL DEFAULT '0',
- `b` int(11) DEFAULT NULL,
- PRIMARY KEY (`a`)
-) ENGINE=InnoDB
-master-bin.000001 # Query # # ROLLBACK
-master-bin.000001 # Query # # BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # COMMIT
-master-bin.000001 # Query # # BEGIN
-master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` (
- `a` int(11) NOT NULL DEFAULT '0',
- `b` int(11) DEFAULT NULL,
- PRIMARY KEY (`a`)
-) ENGINE=InnoDB
-master-bin.000001 # Query # # COMMIT
-master-bin.000001 # Query # # BEGIN
-master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2` (
- `a` int(11) NOT NULL DEFAULT '0',
- `b` int(11) DEFAULT NULL,
- PRIMARY KEY (`a`)
-) ENGINE=InnoDB
-master-bin.000001 # Query # # ROLLBACK
master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE IF EXISTS `t2` /* generated by server */
reset master;
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2010-06-01 23:25:08 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2010-07-06 08:45:39 +0000
@@ -321,7 +321,8 @@ Note 1051 Unknown table 't2'
CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
INSERT INTO t1 VALUES (4,4);
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
SELECT * from t2;
a b
TRUNCATE table t2;
@@ -341,11 +342,13 @@ Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
INSERT INTO t1 values (8,8);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
COMMIT;
INSERT INTO t1 values (9,9);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
@@ -369,11 +372,13 @@ a b
10 10
INSERT INTO t2 values (100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
COMMIT;
INSERT INTO t2 values (101,101);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
ROLLBACK;
SELECT * from t2;
a b
@@ -418,9 +423,6 @@ master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (100,100)
master-bin.000001 # Query # # COMMIT
-master-bin.000001 # Query # # BEGIN
-master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (101,101)
-master-bin.000001 # Query # # ROLLBACK
master-bin.000001 # Query # # use `test`; DROP TABLE t1,t2
reset master;
create table t1 (a int) engine=innodb;
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-06-17 13:31:51 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2010-07-06 08:45:39 +0000
@@ -720,7 +720,8 @@ CREATE TABLE t2 (a int, b int, primary k
BEGIN;
INSERT INTO t2 values(100,100);
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
SELECT * from t2;
a b
100 100
@@ -738,7 +739,8 @@ CREATE TEMPORARY TABLE t2 (a int, b int,
BEGIN;
INSERT INTO t2 values(100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
SELECT * from t2;
a b
100 100
@@ -746,7 +748,8 @@ COMMIT;
BEGIN;
INSERT INTO t2 values(101,101);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+Warnings:
+Note 1050 Table 't2' already exists
SELECT * from t2;
a b
100 100
=== modified file 'mysql-test/suite/rpl/r/rpl_create_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2010-01-16 07:44:24 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2010-07-06 08:45:39 +0000
@@ -26,7 +26,6 @@ SHOW TABLES in mysqltest;
Tables_in_mysqltest
t
t1
-t2
SHOW EVENTS in mysqltest;
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
mysqltest e root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
@@ -65,3 +64,52 @@ c1
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
+-------------BUG#47132-------------
+# The behavior of statement 'CREATE TABLE SELECT IF NOT EXISTS' is changed.
+# After the bug, it will insert nothing and the statement will not be
+# binlogged if the table exists.
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+INSERT INTO t1 VALUES (1), (1);
+INSERT INTO t2 VALUES (2), (2);
+CREATE VIEW v1 AS SELECT id FROM t2;
+CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1;
+Warnings:
+Note 1050 Table 'v1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+SHOW CREATE TABLE v1;
+View Create View character_set_client collation_connection
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t2`.`id` AS `id` from `t2` latin1 latin1_swedish_ci
+SELECT * FROM t2;
+id
+2
+2
+SELECT * FROM v1;
+id
+2
+2
+DROP VIEW v1;
+CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2;
+CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1;
+Warnings:
+Note 1050 Table 'tt1' already exists
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+SELECT * FROM t2;
+id
+2
+2
+SELECT * FROM tt1;
+id
+2
+2
+DROP TEMPORARY TABLE tt1;
+DROP TABLE t1, t2;
=== modified file 'mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result 2009-08-13 02:48:57 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result 2010-07-06 08:45:39 +0000
@@ -19,4 +19,3 @@ master-bin.000001 # Query # # use `test`
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp
master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
-master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp
=== modified file 'mysql-test/suite/rpl/t/rpl_create_if_not_exists.test'
--- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test 2010-01-16 07:44:24 +0000
+++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test 2010-07-06 08:45:39 +0000
@@ -120,4 +120,40 @@ DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
+--echo -------------BUG#47132-------------
+--echo # The behavior of statement 'CREATE TABLE SELECT IF NOT EXISTS' is changed.
+--echo # After the bug, it will insert nothing and the statement will not be
+--echo # binlogged if the table exists.
+source include/master-slave-reset.inc;
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*");
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+INSERT INTO t1 VALUES (1), (1);
+INSERT INTO t2 VALUES (2), (2);
+
+CREATE VIEW v1 AS SELECT id FROM t2;
+--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
+CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1;
+--source include/show_binlog_events.inc
+
+SHOW CREATE TABLE v1;
+SELECT * FROM t2;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+# the warning only happens on SBR, so we disable it.
+--disable_warnings
+CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2;
+--enable_warnings
+
+--let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
+CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1;
+--source include/show_binlog_events.inc
+SELECT * FROM t2;
+SELECT * FROM tt1;
+DROP TEMPORARY TABLE tt1;
+
+DROP TABLE t1, t2;
+
source include/master-slave-end.inc;
=== modified file 'mysql-test/t/auto_increment.test'
--- a/mysql-test/t/auto_increment.test 2009-08-20 12:30:59 +0000
+++ b/mysql-test/t/auto_increment.test 2010-07-06 08:45:39 +0000
@@ -334,7 +334,7 @@ INSERT INTO t1 VALUES (1), (1);
CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
--error ER_DUP_ENTRY
-CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
UPDATE t2 SET a = 2;
=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test 2010-06-11 08:14:38 +0000
+++ b/mysql-test/t/create.test 2010-07-06 08:45:39 +0000
@@ -5,6 +5,7 @@
--disable_warnings
drop table if exists t1,t2,t3,t4,t5;
drop database if exists mysqltest;
+drop view if exists v1;
--enable_warnings
create table t1 (b char(0));
@@ -232,7 +233,6 @@ drop table t1;
create table t1 select 1,2,3;
create table if not exists t1 select 1,2;
---error 1136
create table if not exists t1 select 1,2,3,4;
create table if not exists t1 select 1;
select * from t1;
@@ -248,8 +248,6 @@ insert into t1 values (1,1);
create table if not exists t1 select 2;
select * from t1;
create table if not exists t1 select 3 as 'a',4 as 'b';
---error ER_DUP_ENTRY
-create table if not exists t1 select 3 as 'a',3 as 'b';
show warnings;
show status like "Opened_tables";
select * from t1;
@@ -730,7 +728,6 @@ create table t1 select coalesce('a' coll
create table t1 (primary key(a)) select "b" as b;
# Error in select_create::prepare() which is not related to table creation
create table t1 (a int);
---error ER_WRONG_VALUE_COUNT_ON_ROW
create table if not exists t1 select 1 as a, 2 as b;
drop table t1;
# Finally error which happens during insert
@@ -746,16 +743,10 @@ select * from t1;
--error ER_UPDATE_TABLE_USED
create table if not exists t1 select * from t1;
select * from t1;
+drop table t1;
# Error before select_create::prepare()
--error ER_CANT_AGGREGATE_2COLLATIONS
create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
-select * from t1;
-# Error which happens during insertion of rows
-alter table t1 add primary key (i);
---error ER_DUP_ENTRY
-create table if not exists t1 (select 2 as i) union all (select 2 as i);
-select * from t1;
-drop table t1;
# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
@@ -1229,11 +1220,9 @@ INSERT IGNORE INTO t1 (b) VALUES (5);
CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT a FROM t1;
--error 1062
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
- SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
--error 1062
-CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
- SELECT a FROM t1;
+INSERT INTO t2 SELECT a FROM t1;
DROP TABLE t1, t2;
@@ -1273,7 +1262,7 @@ CREATE TEMPORARY TABLE t2 (primary key (
drop table if exists t2;
CREATE TABLE t2 (a int, b int, primary key (a));
--error ER_DUP_ENTRY
-CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+INSERT INTO t2 select * from t1;
SELECT * from t2;
TRUNCATE table t2;
--error ER_DUP_ENTRY
@@ -1283,11 +1272,7 @@ drop table t2;
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
--error ER_DUP_ENTRY
-CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
-SELECT * from t2;
-TRUNCATE table t2;
---error ER_DUP_ENTRY
-INSERT INTO t2 select * from t1;
+INSERT INTO t2 SELECT * FROM t1;
SELECT * from t2;
drop table t1,t2;
@@ -1657,11 +1642,7 @@ END ; |
--delimiter ;
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
-CREATE TABLE IF NOT EXISTS t1 (
- `pk` INTEGER NOT NULL AUTO_INCREMENT ,
- `int` INTEGER ,
- PRIMARY KEY ( `pk` )
-) SELECT `pk` , `int_key` FROM B ;
+INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
--delimiter |
--error ER_NOT_SUPPORTED_YET
@@ -1675,7 +1656,6 @@ END ;|
DROP TABLE t1;
DROP TABLE B;
-
--echo #
--echo # Bug #47107 assert in notify_shared_lock on incorrect
--echo # CREATE TABLE , HANDLER
@@ -1798,3 +1778,28 @@ show create table t1;
show create table t2;
set @@sql_mode= @old_mode;
drop tables t1, t2;
+#
+# Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table
+# is view over multiple tables
+#
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+INSERT INTO t1 VALUES (1), (1);
+INSERT INTO t2 VALUES (2), (2);
+
+CREATE VIEW v1 AS SELECT id FROM t2;
+CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1;
+SHOW CREATE TABLE v1;
+SELECT * FROM t2;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2;
+CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1;
+SELECT * FROM t2;
+SELECT * FROM tt1;
+DROP TEMPORARY TABLE tt1;
+
+DROP TABLE t1, t2;
+
=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test 2010-05-07 16:17:55 +0000
+++ b/mysql-test/t/trigger.test 2010-07-06 08:45:39 +0000
@@ -1751,7 +1751,7 @@ create trigger t1_ai after insert on t1
create table t2 (j int);
insert into t2 values (1), (2);
set @a:="";
-create table if not exists t1 select * from t2;
+insert into t1 select * from t2;
select * from t1;
select @a;
# Let us check that trigger that involves table also works ok.
@@ -1759,7 +1759,7 @@ drop trigger t1_bi;
drop trigger t1_ai;
create table t3 (isave int);
create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
-create table if not exists t1 select * from t2;
+insert into t1 select * from t2;
select * from t1;
select * from t3;
drop table t1, t2, t3;
@@ -1975,7 +1975,7 @@ select * from t1_op_log;
truncate t1;
truncate t1_op_log;
-create table if not exists t1
+insert into t1
select NULL, "CREATE TABLE ... SELECT, inserting a new key";
set @id=last_insert_id();
@@ -1984,7 +1984,7 @@ select * from t1;
select * from t1_op_log;
truncate t1_op_log;
-create table if not exists t1 replace
+replace into t1
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
select * from t1;
@@ -2114,7 +2114,7 @@ select * from t1_op_log;
truncate t1;
truncate t1_op_log;
-create table if not exists v1
+insert into v1
select NULL, "CREATE TABLE ... SELECT, inserting a new key";
set @id=last_insert_id();
@@ -2123,7 +2123,7 @@ select * from t1;
select * from t1_op_log;
truncate t1_op_log;
-create table if not exists v1 replace
+replace into v1
select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
select * from t1;
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2010-06-11 01:30:49 +0000
+++ b/sql/handler.h 2010-07-06 08:45:39 +0000
@@ -1005,7 +1005,6 @@ typedef struct st_ha_create_information
uint merge_insert_method;
uint extra_size; /* length of extra data segment */
enum enum_ha_unused unused1;
- bool table_existed; /* 1 in create if table existed */
bool frm_only; /* 1 if no ha_create_table() */
bool varchar; /* 1 if table has a VARCHAR */
enum ha_storage_media storage_media; /* DEFAULT, DISK or MEMORY */
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2010-07-01 14:58:47 +0000
+++ b/sql/sql_base.cc 2010-07-06 08:45:39 +0000
@@ -2697,10 +2697,6 @@ open_table_get_mdl_lock(THD *thd, Open_t
is never opened. In both cases, metadata locks are always taken according
to the lock strategy.
- If the lock strategy is OTLS_DOWNGRADE_IF_EXISTS and opening the table
- is successful, the exclusive metadata lock acquired by the caller
- is downgraded to a shared lock.
-
RETURN
TRUE Open failed. "action" parameter may contain type of action
needed to remedy problem before retrying again.
@@ -3145,15 +3141,6 @@ bool open_table(THD *thd, TABLE_LIST *ta
mysql_mutex_unlock(&LOCK_open);
- /*
- In CREATE TABLE .. If NOT EXISTS .. SELECT we have found that
- table exists now we should downgrade our exclusive metadata
- lock on this table to SW metadata lock.
- */
- if (table_list->lock_strategy == TABLE_LIST::OTLS_DOWNGRADE_IF_EXISTS &&
- !(flags & MYSQL_OPEN_HAS_MDL_LOCK))
- mdl_ticket->downgrade_exclusive_lock(MDL_SHARED_WRITE);
-
table->mdl_ticket= mdl_ticket;
table->next= thd->open_tables; /* Link into simple list */
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2010-06-10 11:43:32 +0000
+++ b/sql/sql_insert.cc 2010-07-06 08:45:39 +0000
@@ -3592,17 +3592,6 @@ static TABLE *create_table_from_items(TH
create_info, alter_info, 0,
select_field_count))
{
- if (create_info->table_existed)
- {
- /*
- This means that someone created table underneath server
- or it was created via different mysqld front-end to the
- cluster. We don't have much options but throw an error.
- */
- my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
- DBUG_RETURN(0);
- }
-
DBUG_EXECUTE_IF("sleep_create_select_before_open", my_sleep(6000000););
if (!(create_info->options & HA_LEX_CREATE_TMP_TABLE))
@@ -3720,15 +3709,13 @@ select_create::prepare(List<Item> &value
TABLE const *const table = *tables;
if (thd->is_current_stmt_binlog_format_row() &&
- !table->s->tmp_table &&
- !ptr->get_create_info()->table_existed)
+ !table->s->tmp_table)
{
if (int error= ptr->binlog_show_create_table(tables, count))
return error;
}
return 0;
}
-
select_create *ptr;
TABLE_LIST *create_table;
TABLE_LIST *select_tables;
@@ -3751,34 +3738,15 @@ select_create::prepare(List<Item> &value
thd->binlog_start_trans_and_stmt();
}
+ DBUG_ASSERT(create_table->table == NULL);
+
DBUG_EXECUTE_IF("sleep_create_select_before_check_if_exists", my_sleep(6000000););
- if (create_table->table)
- {
- /* Table already exists and was open at open_and_lock_tables() stage. */
- if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
- {
- /* Mark that table existed */
- create_info->table_existed= 1;
- push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
- ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
- create_table->table_name);
- if (thd->is_current_stmt_binlog_format_row())
- binlog_show_create_table(&(create_table->table), 1);
- table= create_table->table;
- }
- else
- {
- my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
- DBUG_RETURN(-1);
- }
- }
- else
- if (!(table= create_table_from_items(thd, create_info, create_table,
- alter_info, &values,
- &extra_lock, hook_ptr)))
- /* abort() deletes table */
- DBUG_RETURN(-1);
+ if (!(table= create_table_from_items(thd, create_info, create_table,
+ alter_info, &values,
+ &extra_lock, hook_ptr)))
+ /* abort() deletes table */
+ DBUG_RETURN(-1);
if (extra_lock)
{
@@ -3898,10 +3866,6 @@ void select_create::send_error(uint errc
("Current table (at 0x%lu) %s a temporary (or non-existant) table",
(ulong) table,
table && !table->s->tmp_table ? "is NOT" : "is"));
- DBUG_PRINT("info",
- ("Table %s prior to executing this statement",
- get_create_info()->table_existed ? "existed" : "did not exist"));
-
/*
This will execute any rollbacks that are necessary before writing
the transcation cache.
@@ -3990,8 +3954,7 @@ void select_create::abort()
table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
table->auto_increment_field_not_null= FALSE;
- if (!create_info->table_existed)
- drop_open_table(thd, table, create_table->db, create_table->table_name);
+ drop_open_table(thd, table, create_table->db, create_table->table_name);
table=0; // Safety
}
DBUG_VOID_RETURN;
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-07-01 13:53:46 +0000
+++ b/sql/sql_parse.cc 2010-07-06 08:45:39 +0000
@@ -2612,13 +2612,7 @@ case SQLCOM_PREPARE:
}
#endif
- /* Set strategies: reset default or 'prepared' values. */
- create_table->open_strategy= TABLE_LIST::OPEN_IF_EXISTS;
- create_table->lock_strategy= TABLE_LIST::OTLS_DOWNGRADE_IF_EXISTS;
-
- /*
- Close any open handlers for the table
- */
+ /* Close any open handlers for the table. */
mysql_ha_rm_tables(thd, create_table);
if (select_lex->item_list.elements) // With select
@@ -2678,17 +2672,23 @@ case SQLCOM_PREPARE:
goto end_with_restore_list;
}
- if (!(create_info.options & HA_LEX_CREATE_TMP_TABLE))
- {
- /* Base table and temporary table are not in the same name space. */
+ /*
+ For CREATE TABLE ... SELECT, base and temporary tables are not in
+ the same name space.
+ */
+ if (create_info.options & HA_LEX_CREATE_TMP_TABLE)
+ create_table->open_type= OT_TEMPORARY_ONLY;
+ else
create_table->open_type= OT_BASE_ONLY;
- }
if (!(res= open_and_lock_tables(thd, lex->query_tables, TRUE, 0)))
{
/*
- Is table which we are changing used somewhere in other parts
- of query
+ Is table which we are changing used somewhere in other
+ parts of the query? Skip the check for tmp tables, since
+ they can't be opened twice anyway. Do the check even if the table
+ exists, to produce the error consistently regardless of
+ the state of the database.
*/
if (!(create_info.options & HA_LEX_CREATE_TMP_TABLE))
{
@@ -2700,24 +2700,27 @@ case SQLCOM_PREPARE:
goto end_with_restore_list;
}
}
- /* If we create merge table, we have to test tables in merge, too */
- if (create_info.used_fields & HA_CREATE_USED_UNION)
+ if (create_table->table)
{
- TABLE_LIST *tab;
- for (tab= create_info.merge_list.first;
- tab;
- tab= tab->next_local)
+ /*
+ Table already exists and was open at open_and_lock_tables()
+ stage.
+ */
+ if (create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS)
{
- TABLE_LIST *duplicate;
- if ((duplicate= unique_table(thd, tab, select_tables, 0)))
- {
- update_non_unique_table_error(tab, "CREATE", duplicate);
- res= 1;
- goto end_with_restore_list;
- }
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_TABLE_EXISTS_ERROR,
+ ER(ER_TABLE_EXISTS_ERROR),
+ create_table->table_name);
+ my_ok(thd);
}
+ else
+ {
+ my_error(ER_TABLE_EXISTS_ERROR, MYF(0), create_table->table_name);
+ res= 1;
+ }
+ goto end_with_restore_list;
}
-
/*
Remove target table from main select and name resolution
context. This can't be done earlier as it will break view merging in
@@ -2748,7 +2751,7 @@ case SQLCOM_PREPARE:
res= handle_select(thd, lex, result, 0);
delete result;
}
-
+
lex->link_first_table_back(create_table, link_to_local);
}
}
=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc 2010-06-17 13:31:51 +0000
+++ b/sql/sql_prepare.cc 2010-07-06 08:45:39 +0000
@@ -1684,13 +1684,12 @@ static bool mysql_test_create_table(Prep
if (create_table_precheck(thd, tables, create_table))
DBUG_RETURN(TRUE);
- /*
- The open and lock strategies will be set again once the
- statement is executed. These values are only meaningful
- for the prepare phase.
- */
+ /*
+ The open and lock strategies will be set again once the
+ statement is executed. These values are only meaningful
+ for the prepare phase.
+ */
create_table->open_strategy= TABLE_LIST::OPEN_IF_EXISTS;
- create_table->lock_strategy= TABLE_LIST::OTLS_NONE;
if (select_lex->item_list.elements)
{
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2010-07-01 13:53:46 +0000
+++ b/sql/sql_table.cc 2010-07-06 08:45:39 +0000
@@ -4042,7 +4042,6 @@ bool mysql_create_table_no_lock(THD *thd
{
if (create_info->options & HA_LEX_CREATE_IF_NOT_EXISTS)
{
- create_info->table_existed= 1; // Mark that table existed
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
alias);
@@ -4114,7 +4113,6 @@ bool mysql_create_table_no_lock(THD *thd
}
thd_proc_info(thd, "creating table");
- create_info->table_existed= 0; // Mark that table is created
#ifdef HAVE_READLINK
{
@@ -4209,7 +4207,6 @@ warn:
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
alias);
- create_info->table_existed= 1; // Mark that table existed
goto unlock_and_end;
}
@@ -5351,11 +5348,9 @@ bool mysql_create_like_table(THD* thd, T
non-temporary table.
*/
DBUG_ASSERT((create_info->options & HA_LEX_CREATE_TMP_TABLE) ||
- local_create_info.table_existed ||
thd->mdl_context.is_lock_owner(MDL_key::TABLE, table->db,
table->table_name,
MDL_EXCLUSIVE));
-
/*
We have to write the query before we unlock the tables.
*/
=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc 2010-07-01 13:53:46 +0000
+++ b/sql/sql_view.cc 2010-07-06 08:45:39 +0000
@@ -433,8 +433,6 @@ bool mysql_create_view(THD *thd, TABLE_L
goto err;
lex->link_first_table_back(view, link_to_local);
- view->open_strategy= TABLE_LIST::OPEN_STUB;
- view->lock_strategy= TABLE_LIST::OTLS_NONE;
view->open_type= OT_BASE_ONLY;
if (open_and_lock_tables(thd, lex->query_tables, TRUE, 0))
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2010-07-01 13:53:46 +0000
+++ b/sql/sql_yacc.yy 2010-07-06 08:45:39 +0000
@@ -2029,6 +2029,12 @@ create:
TL_OPTION_UPDATING,
TL_WRITE, MDL_EXCLUSIVE))
MYSQL_YYABORT;
+ /*
+ For CREATE TABLE, an non-existing table is not an error.
+ Instruct open_tables() to just take an MDL lock if the
+ table does not exist.
+ */
+ lex->query_tables->open_strategy= TABLE_LIST::OPEN_IF_EXISTS;
lex->alter_info.reset();
lex->col_list.empty();
lex->change=NullS;
@@ -13969,6 +13975,7 @@ view_tail:
TL_IGNORE,
MDL_EXCLUSIVE))
MYSQL_YYABORT;
+ lex->query_tables->open_strategy= TABLE_LIST::OPEN_STUB;
}
view_list_opt AS view_select
;
=== modified file 'sql/table.h'
--- a/sql/table.h 2010-06-22 20:32:29 +0000
+++ b/sql/table.h 2010-07-06 08:45:39 +0000
@@ -1588,23 +1588,6 @@ struct TABLE_LIST
/* Don't associate a table share. */
OPEN_STUB
} open_strategy;
- /**
- Indicates the locking strategy for the object being opened.
- */
- enum
- {
- /*
- Take metadata lock specified by 'mdl_request' member before
- the object is opened. Do nothing after that.
- */
- OTLS_NONE= 0,
- /*
- Take (exclusive) metadata lock specified by 'mdl_request' member
- before object is opened. If opening is successful, downgrade to
- a shared lock.
- */
- OTLS_DOWNGRADE_IF_EXISTS
- } lock_strategy;
/* For transactional locking. */
int lock_timeout; /* NOWAIT or WAIT [X] */
bool lock_transactional; /* If transactional lock requested. */
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20100706084539-46d9usiwosydxpoc.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk-runtime branch (Li-Bing.Song:3076) Bug#47132 | Li-Bing.Song | 6 Jul |
