diff options
| author | Sergei Golubchik <sergii@pisem.net> | 2014-02-06 16:38:40 +0100 |
|---|---|---|
| committer | Sergei Golubchik <sergii@pisem.net> | 2014-02-06 16:38:40 +0100 |
| commit | 1b3c15f1995531a1263139fe1bdde570f1b93b19 (patch) | |
| tree | f4bc1013d3b67a3b971f66b553ce0e1728529cc8 /mysql-test | |
| parent | c73718d917e903bddf7059cd8a515066f04311d1 (diff) | |
| parent | 313f18be5a4b9c56d9c7331227f72e3f2fa4f9fe (diff) | |
| download | mariadb-git-1b3c15f1995531a1263139fe1bdde570f1b93b19.tar.gz | |
merge with 10.0-monty
Diffstat (limited to 'mysql-test')
65 files changed, 1879 insertions, 240 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_log.test b/mysql-test/extra/rpl_tests/rpl_log.test index deff9d653e0..01e8497e4de 100644 --- a/mysql-test/extra/rpl_tests/rpl_log.test +++ b/mysql-test/extra/rpl_tests/rpl_log.test @@ -18,8 +18,12 @@ start slave; --source include/wait_for_slave_to_start.inc let $VERSION=`select version()`; - +# Lets run this test in STRICT MODE (DROP TABLE is not DROP TABLE IF EXISTS) +connection slave; +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; connection master; + eval create table t1(n int not null auto_increment primary key)ENGINE=$engine_type; insert into t1 values (NULL); drop table t1; @@ -141,3 +145,5 @@ drop table t1; # End of 4.1 tests sync_slave_with_master; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; +connection master; diff --git a/mysql-test/extra/rpl_tests/rpl_stop_slave.test b/mysql-test/extra/rpl_tests/rpl_stop_slave.test index b226f4f22f1..0f09faa0301 100644 --- a/mysql-test/extra/rpl_tests/rpl_stop_slave.test +++ b/mysql-test/extra/rpl_tests/rpl_stop_slave.test @@ -59,3 +59,6 @@ source include/wait_for_slave_sql_to_stop.inc; connection slave; START SLAVE SQL_THREAD; source include/wait_for_slave_sql_to_start.inc; + +connection master; +sync_slave_with_master; diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index bdb6f48f095..e72ebba8527 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -751,7 +751,7 @@ call p_verify_status_increment(4, 4, 4, 4); --echo # Sic: no table is created. create table if not exists t2 (a int) select 6 union select 7; --echo # Sic: first commits the statement, and then the transaction. -call p_verify_status_increment(2, 0, 2, 0); +call p_verify_status_increment(0, 0, 0, 0); create table t3 select a from t2; call p_verify_status_increment(2, 0, 4, 4); alter table t3 add column (b int); diff --git a/mysql-test/include/have_metadata_lock_info.inc b/mysql-test/include/have_metadata_lock_info.inc new file mode 100644 index 00000000000..51fae1c62f0 --- /dev/null +++ b/mysql-test/include/have_metadata_lock_info.inc @@ -0,0 +1,4 @@ +if (!`SELECT count(*) FROM information_schema.plugins WHERE + (PLUGIN_STATUS = 'ACTIVE') AND PLUGIN_NAME = 'METADATA_LOCK_INFO'`){ + skip Need archive METADATA_LOCK_INFO plugin; +} diff --git a/mysql-test/include/have_metadata_lock_info.opt b/mysql-test/include/have_metadata_lock_info.opt new file mode 100644 index 00000000000..677c4ec01be --- /dev/null +++ b/mysql-test/include/have_metadata_lock_info.opt @@ -0,0 +1,2 @@ +--loose-metadata-lock-info +--plugin-load-add=$METADATA_LOCK_INFO_SO diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 5a5f2655fda..0e0dac16aa3 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -6188,6 +6188,13 @@ sub valgrind_arguments { mtr_add_arg($args, "--num-callers=16"); mtr_add_arg($args, "--suppressions=%s/valgrind.supp", $glob_mysql_test_dir) if -f "$glob_mysql_test_dir/valgrind.supp"; + + # Ensure the jemalloc works with mysqld + if ($mysqld_variables{'version-malloc-library'} ne "system" && + $$exe =~ /mysqld/) + { + mtr_add_arg($args, "--soname-synonyms=somalloc=NONE" ); + } } # Add valgrind options, can be overriden by user @@ -6318,7 +6325,20 @@ sub usage ($) { $0 [ OPTIONS ] [ TESTCASE ] -Options to control what engine/variation to run +Where test case can be specified as: + +testcase[.test] Runs the test case named 'testcase' from all suits +path-to-testcase +[suite.]testcase[,combination] + +Examples: + +alias +main.alias 'main' is the name of the suite for the 't' directory. +rpl.rpl_invoked_features,mix,xtradb_plugin +suite/rpl/t/rpl.rpl_invoked_features + +Options to control what engine/variation to run: embedded-server Use the embedded server, i.e. no mysqld daemons ps-protocol Use the binary protocol between client and server diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 3583e8ed396..1e173221b15 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -830,7 +830,7 @@ create table if not exists t2 (a int) select 6 union select 7; Warnings: Note 1050 Table 't2' already exists # Sic: first commits the statement, and then the transaction. -call p_verify_status_increment(2, 0, 2, 0); +call p_verify_status_increment(0, 0, 0, 0); SUCCESS create table t3 select a from t2; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 7eba25d8ea3..41a2200c13f 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2602,6 +2602,8 @@ create table t1 (a int, b int) select 2,2; ERROR 42S01: Table 't1' already exists create table t1 like t2; ERROR 42S01: Table 't1' already exists +create or replace table t1 (a int, b int) select 2,2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; a b 1 1 diff --git a/mysql-test/r/create_or_replace.result b/mysql-test/r/create_or_replace.result new file mode 100644 index 00000000000..228086bd62c --- /dev/null +++ b/mysql-test/r/create_or_replace.result @@ -0,0 +1,362 @@ +drop table if exists t1,t2,t3; +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES(1),(2),(3); +# +# Check first syntax and wrong usage +# +CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int); +ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS +create or replace trigger trg before insert on t1 for each row set @a:=1; +ERROR HY000: Incorrect usage of OR REPLACE and TRIGGERS / SP / EVENT +create or replace table mysql.general_log (a int); +ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled +create or replace table mysql.slow_log (a int); +ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled +# +# Usage when table doesn't exist +# +CREATE OR REPLACE TABLE t1 (a int); +CREATE TABLE t1 (a int); +ERROR 42S01: Table 't1' already exists +DROP TABLE t1; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE TEMPORARY TABLE t1 (a int, b int, c int); +ERROR 42S01: Table 't1' already exists +DROP TEMPORARY TABLE t1; +# +# Testing with temporary tables +# +CREATE OR REPLACE TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TEMPORARY TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TEMPORARY TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +create temporary table t1 (i int) engine=InnoDB; +create or replace temporary table t1 (a int, b int) engine=InnoDB; +create or replace temporary table t1 (j int); +show create table t1; +Table Create Table +t1 CREATE TEMPORARY TABLE `t1` ( + `j` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TEMPORARY TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TEMPORARY TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE OR REPLACE TABLE t2 (a int); +ERROR HY000: Table 't2' was not locked with LOCK TABLES +DROP TABLE t1; +UNLOCK TABLES; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2; +SELECT * FROM t1; +a +1 +2 +3 +CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2; +SELECT * FROM t1; +b a +NULL 1 +NULL 2 +NULL 3 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TEMPORARY TABLE `t1` ( + `b` int(11) DEFAULT NULL, + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 AS SELECT a FROM t2; +CREATE TEMPORARY TABLE IF NOT EXISTS t1(a int, b int) SELECT 1,2 FROM t2; +Warnings: +Note 1050 Table 't1' already exists +DROP TABLE t1; +CREATE TABLE t1 (a int); +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `1` int(1) NOT NULL DEFAULT '0' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +create table t1 (a int); +create or replace table t1 as select * from t1; +ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data +create or replace table t1 as select a from (select a from t1) as t3; +ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data +create or replace table t1 as select a from t2 where t2.a in (select a from t1); +ERROR HY000: Table 't1' is specified twice, both as a target for 'CREATE' and as a separate source for data +drop table t1; +# +# Testing with normal tables +# +CREATE OR REPLACE TABLE t1 (a int); +CREATE OR REPLACE TABLE t1 (a int, b int); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a int) SELECT * from t2; +SELECT * FROM t1; +a +1 +2 +3 +TRUNCATE TABLE t1; +CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2; +Warnings: +Note 1050 Table 't1' already exists +SELECT * FROM t1; +a +DROP TABLE t1; +CREATE TABLE t1 (i int); +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `1` int(1) NOT NULL DEFAULT '0' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write; +CREATE OR REPLACE TABLE t1 (a int, b int); +SELECT * FROM t1; +a b +INSERT INTO t1 values(1,1); +CREATE OR REPLACE TABLE t1 (a int, b int, c int); +INSERT INTO t1 values(1,1,1); +CREATE OR REPLACE TABLE t3 (a int); +ERROR HY000: Table 't3' was not locked with LOCK TABLES +UNLOCK TABLES; +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write; +CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2; +SELECT * FROM t2; +a +1 +2 +3 +SELECT * FROM t1; +b a 1 +NULL 1 1 +NULL 2 1 +NULL 3 1 +SELECT * FROM t1; +b a 1 +NULL 1 1 +NULL 2 1 +NULL 3 1 +INSERT INTO t1 values(1,1,1); +CREATE OR REPLACE TABLE t1 (a int, b int, c int, d int); +INSERT INTO t1 values(1,1,1,1); +CREATE OR REPLACE TABLE t3 (a int); +ERROR HY000: Table 't3' was not locked with LOCK TABLES +UNLOCK TABLES; +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write, t1 as t1_read read; +CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2; +SELECT * FROM t1; +b a 1 +NULL 1 1 +NULL 2 1 +NULL 3 1 +SELECT * FROM t2; +a +1 +2 +3 +SELECT * FROM t1 as t1_read; +ERROR HY000: Table 't1_read' was not locked with LOCK TABLES +DROP TABLE t1; +UNLOCK TABLES; +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLE t1 WRITE; +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SELECT * from t1; +1 +1 +SELECT * from t2; +ERROR HY000: Table 't2' was not locked with LOCK TABLES +DROP TABLE t1; +# +# Test also with InnoDB (transactional engine) +# +create table t1 (i int) engine=innodb; +lock table t1 write; +create or replace table t1 (j int); +unlock tables; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `j` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (i int) engine=InnoDB; +lock table t1 write, t2 write; +create or replace table t1 (j int) engine=innodb; +unlock tables; +drop table t1; +create table t1 (i int) engine=InnoDB; +create table t3 (i int) engine=InnoDB; +insert into t3 values(1),(2),(3); +lock table t1 write, t2 write, t3 write; +create or replace table t1 (a int, i int) engine=innodb select t2.a,t3.i from t2,t3; +unlock tables; +select * from t1 order by a,i; +a i +1 1 +1 2 +1 3 +2 1 +2 2 +2 3 +3 1 +3 2 +3 3 +drop table t1,t3; +# +# Testing CREATE .. LIKE +# +create or replace table t1 like t2; +create or replace table t1 like t2; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (b int); +lock tables t1 write, t2 read; +create or replace table t1 like t2; +SELECT * FROM t1; +a +INSERT INTO t1 values(1); +CREATE OR REPLACE TABLE t1 like t2; +INSERT INTO t1 values(2); +unlock tables; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create or replace table t1 like t2; +create or replace table t1 like t1; +ERROR 42000: Not unique table/alias: 't1' +drop table t1; +CREATE TEMPORARY TABLE t1 like t2; +CREATE OR REPLACE TABLE t1 like t1; +ERROR 42000: Not unique table/alias: 't1' +CREATE OR REPLACE TABLE t1 like t1; +ERROR 42000: Not unique table/alias: 't1' +drop table t1; +CREATE TEMPORARY TABLE t1 like t2; +CREATE OR REPLACE TEMPORARY TABLE t3 like t1; +CREATE OR REPLACE TEMPORARY TABLE t3 like t3; +ERROR 42000: Not unique table/alias: 't3' +drop table t1,t3; +# +# Test with prepared statements +# +prepare stmt1 from 'create or replace table t1 select * from t2'; +execute stmt1; +select * from t1; +a +1 +2 +3 +execute stmt1; +select * from t1; +a +1 +2 +3 +drop table t1; +execute stmt1; +select * from t1; +a +1 +2 +3 +deallocate prepare stmt1; +drop table t1; +# +# Test with views +# +create view t1 as select 1; +create table if not exists t1 (a int); +Warnings: +Note 1050 Table 't1' already exists +create or replace table t1 (a int); +ERROR 42S02: 'test.t1' is a view +drop table t1; +ERROR 42S02: 'test.t1' is a view +drop view t1; +# +# MDEV-5602 CREATE OR REPLACE obtains stricter locks than the +# connection had before +# +create table t1 (a int); +lock table t1 write, t2 read; +select * from information_schema.metadata_lock_info; +THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 +# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +create or replace table t1 (i int); +select * from information_schema.metadata_lock_info; +THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 +# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +create or replace table t1 like t2; +select * from information_schema.metadata_lock_info; +THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 +# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +create or replace table t1 select 1 as f1; +select * from information_schema.metadata_lock_info; +THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock +# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 +# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +drop table t1; +unlock tables; +DROP TABLE t2; diff --git a/mysql-test/r/lowercase_table2.result b/mysql-test/r/lowercase_table2.result index 58b976413de..f75eed41d9f 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -274,7 +274,7 @@ Database Table In_use Name_locked test t_bug44738_uppercase 0 0 # So attempt to create table with the same name should fail. create table t_bug44738_UPPERCASE (i int); -ERROR HY000: Can't find file: './test/t_bug44738_uppercase.MYI' (errno: 2 "No such file or directory") +ERROR 42S01: Table 't_bug44738_uppercase' already exists # And should succeed after FLUSH TABLES. flush tables; create table t_bug44738_UPPERCASE (i int); diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index 33c87ec101c..f43c39c4fc1 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -20,13 +20,13 @@ ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table ' update v2Aa set col1 = (select max(col1) from t1Aa); ERROR HY000: The definition of table 'v2Aa' prevents operation UPDATE on table 'v2Aa'. update v2aA set col1 = (select max(col1) from v2Aa); -ERROR HY000: You can't specify target table 'v2aA' for update in FROM clause +ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v1aA) where v2aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v2aA'. update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v1Aa) where t1aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1Aa' prevents operation UPDATE on table 't1aA'. update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v1aA) where v1Aa.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 'v1aA' for update in FROM clause +ERROR HY000: Table 'v1aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from v1aA) where v2Aa.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 't2Aa'. update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from v1Aa) where t1Aa.col1 = t2aA.col1; @@ -36,17 +36,17 @@ ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table ' update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from t1aA) where v2aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v2aA'. update t1Aa,t2Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 't1Aa' for update in FROM clause +ERROR HY000: Table 't1Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from t1Aa) where v1aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1aA' prevents operation UPDATE on table 'v1aA'. update t2Aa,v2Aa set v2aA.col1 = (select max(col1) from t1aA) where v2Aa.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 't2Aa' for update in FROM clause +ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t2Aa,t1Aa set t1aA.col1 = (select max(col1) from t1Aa) where t1aA.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 't2Aa' for update in FROM clause +ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t2Aa,v1Aa set v1aA.col1 = (select max(col1) from t1Aa) where v1Aa.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 't2Aa' for update in FROM clause +ERROR HY000: Table 't2Aa' is specified twice, both as a target for 'UPDATE' and as a separate source for data update v2aA,t2Aa set v2Aa.col1 = (select max(col1) from v2aA) where v2Aa.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 'v2aA' for update in FROM clause +ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1aA,t2Aa set t1Aa.col1 = (select max(col1) from v2aA) where t1aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 't1aA'. update v1aA,t2Aa set v1Aa.col1 = (select max(col1) from v2Aa) where v1aA.col1 = t2aA.col1; @@ -64,27 +64,27 @@ ERROR HY000: The definition of table 'v3aA' prevents operation UPDATE on table ' update v3aA set v3Aa.col1 = (select max(col1) from v2aA); ERROR HY000: The definition of table 'v2aA' prevents operation UPDATE on table 'v3aA'. update v3aA set v3Aa.col1 = (select max(col1) from v3aA); -ERROR HY000: You can't specify target table 'v3aA' for update in FROM clause +ERROR HY000: Table 'v3aA' is specified twice, both as a target for 'UPDATE' and as a separate source for data delete from v2Aa where col1 = (select max(col1) from v1Aa); ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 'v2Aa'. delete from v2aA where col1 = (select max(col1) from t1Aa); ERROR HY000: The definition of table 'v2aA' prevents operation DELETE on table 'v2aA'. delete from v2Aa where col1 = (select max(col1) from v2aA); -ERROR HY000: You can't specify target table 'v2Aa' for update in FROM clause +ERROR HY000: Table 'v2Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v2Aa from v2aA,t2Aa where (select max(col1) from v1aA) > 0 and v2Aa.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1aA' prevents operation DELETE on table 'v2aA'. delete t1aA from t1Aa,t2Aa where (select max(col1) from v1Aa) > 0 and t1aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 't1Aa'. delete v1aA from v1Aa,t2Aa where (select max(col1) from v1aA) > 0 and v1Aa.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 'v1Aa' for update in FROM clause +ERROR HY000: Table 'v1Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v2aA from v2Aa,t2Aa where (select max(col1) from t1Aa) > 0 and v2aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v2Aa' prevents operation DELETE on table 'v2Aa'. delete t1aA from t1Aa,t2Aa where (select max(col1) from t1aA) > 0 and t1Aa.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 't1Aa' for update in FROM clause +ERROR HY000: Table 't1Aa' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v1aA from v1Aa,t2Aa where (select max(col1) from t1aA) > 0 and v1aA.col1 = t2aA.col1; ERROR HY000: The definition of table 'v1Aa' prevents operation DELETE on table 'v1Aa'. delete v2Aa from v2aA,t2Aa where (select max(col1) from v2Aa) > 0 and v2aA.col1 = t2aA.col1; -ERROR HY000: You can't specify target table 'v2aA' for update in FROM clause +ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t1Aa from t1aA,t2Aa where (select max(col1) from v2Aa) > 0 and t1Aa.col1 = t2aA.col1; ERROR HY000: The definition of table 'v2Aa' prevents operation DELETE on table 't1aA'. delete v1Aa from v1aA,t2Aa where (select max(col1) from v2aA) > 0 and v1Aa.col1 = t2aA.col1; @@ -98,15 +98,15 @@ ERROR HY000: The definition of table 'v1aA' prevents operation INSERT on table ' insert into v2Aa values ((select max(col1) from t1Aa)); ERROR HY000: The definition of table 'v2Aa' prevents operation INSERT on table 'v2Aa'. insert into t1aA values ((select max(col1) from t1Aa)); -ERROR HY000: You can't specify target table 't1aA' for update in FROM clause +ERROR HY000: Table 't1aA' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into v2aA values ((select max(col1) from t1aA)); ERROR HY000: The definition of table 'v2aA' prevents operation INSERT on table 'v2aA'. insert into v2Aa values ((select max(col1) from v2aA)); -ERROR HY000: You can't specify target table 'v2Aa' for update in FROM clause +ERROR HY000: Table 'v2Aa' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into t1Aa values ((select max(col1) from v2Aa)); ERROR HY000: The definition of table 'v2Aa' prevents operation INSERT on table 't1Aa'. insert into v2aA values ((select max(col1) from v2Aa)); -ERROR HY000: You can't specify target table 'v2aA' for update in FROM clause +ERROR HY000: Table 'v2aA' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into v3Aa (col1) values ((select max(col1) from v1Aa)); ERROR HY000: The definition of table 'v1Aa' prevents operation INSERT on table 'v3Aa'. insert into v3aA (col1) values ((select max(col1) from t1aA)); diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 0feb1cdce98..89aaf48219e 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -3657,85 +3657,85 @@ insert into tmp (b) values (1); insert into t1 (a) values (1); insert into t3 (b) values (1); insert into m1 (a) values ((select max(a) from m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. insert into m1 (a) values ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. update m1 set a = ((select max(a) from m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. update m1 set a = ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. delete from m1 where a = (select max(a) from m1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from m2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, m1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, m2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, t1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, t2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, m1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, m2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, t1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, t2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from v1); ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. delete from m1 where a = (select max(a) from tmp, v1); diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index f49998da5f4..ff0aa828636 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -439,9 +439,9 @@ drop table t1, t2, t3; create table t1 (col1 int); create table t2 (col1 int); update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table t1,t2; create table t1 ( aclid bigint not null primary key, @@ -457,7 +457,7 @@ drop table t1, t2; create table t1(a int); create table t2(a int); delete from t1,t2 using t1,t2 where t1.a=(select a from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data drop table t1, t2; create table t1 ( c char(8) not null ) engine=innodb; insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index e5d6cbc0e31..53048939fa9 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -860,13 +860,22 @@ The following options may be given as the first argument: --skip-slave-start If set, slave is not autostarted. --slave-compressed-protocol Use compression on master/slave protocol + --slave-ddl-exec-mode=name + Modes for how replication events should be executed. + Legal values are STRICT and IDEMPOTENT (default). In + IDEMPOTENT mode, replication will not stop for DDL + operations that are idempotent. This means that CREATE + TABLE is treated CREATE TABLE OR REPLACE and DROP TABLE + is threated as DROP TABLE IF EXISTS. --slave-exec-mode=name Modes for how replication events should be executed. Legal values are STRICT (default) and IDEMPOTENT. In IDEMPOTENT mode, replication will not stop for operations - that are idempotent. In STRICT mode, replication will - stop on any unexpected difference between the master and - the slave + that are idempotent. For example, in row based + replication attempts to delete rows that doesn't exist + will be ignored.In STRICT mode, replication will stop on + any unexpected difference between the master and the + slave --slave-load-tmpdir=name The location where the slave should put its temporary files when replicating a LOAD DATA INFILE command @@ -1023,7 +1032,7 @@ auto-increment-increment 1 auto-increment-offset 1 autocommit TRUE automatic-sp-privileges TRUE -back-log 50 +back-log 150 big-tables FALSE bind-address (No default value) binlog-annotate-row-events FALSE @@ -1226,6 +1235,7 @@ port-open-timeout 0 preload-buffer-size 32768 profiling-history-size 15 progress-report-time 56 +protocol-version 10 query-alloc-block-size 8192 query-cache-limit 1048576 query-cache-min-res-unit 4096 @@ -1264,6 +1274,7 @@ skip-networking FALSE skip-show-database FALSE skip-slave-start FALSE slave-compressed-protocol FALSE +slave-ddl-exec-mode IDEMPOTENT slave-exec-mode STRICT slave-max-allowed-packet 1073741824 slave-net-timeout 3600 diff --git a/mysql-test/r/partition_exchange.result b/mysql-test/r/partition_exchange.result index 36499004869..fec08e99c72 100644 --- a/mysql-test/r/partition_exchange.result +++ b/mysql-test/r/partition_exchange.result @@ -1088,7 +1088,7 @@ ALTER TABLE t PARTITION BY RANGE (UNIX_TIMESTAMP(event_time) DIV 1) (PARTITION p0 VALUES LESS THAN (123456789), PARTITION pMAX VALUES LESS THAN MAXVALUE); ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE general_log; -ERROR HY000: Incorrect usage of PARTITION and log table +ERROR HY000: You cannot 'ALTER PARTITION' a log table if logging is enabled ALTER TABLE general_log ENGINE = CSV; SET @@global.general_log = @old_general_log_state; DROP TABLE t; diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result index 8284e974574..dec0d12c455 100644 --- a/mysql-test/r/ps_ddl.result +++ b/mysql-test/r/ps_ddl.result @@ -1930,7 +1930,7 @@ SUCCESS execute stmt; ERROR 42S01: Table 't2' already exists -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); SUCCESS execute stmt; @@ -1946,7 +1946,7 @@ SUCCESS execute stmt; ERROR 42S01: Table 't2' already exists -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); SUCCESS drop temporary table t2; @@ -1964,7 +1964,7 @@ drop table t2; create view t2 as select 1; execute stmt; Got one of the listed errors -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); SUCCESS execute stmt; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index fc98607e38a..effa77a5eb4 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -579,7 +579,7 @@ a b 1 11 2 12 update t1 set b= (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1 set b= (select b from t2); ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); @@ -602,7 +602,7 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); @@ -628,7 +628,7 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: You can't specify target table 't12' for update in FROM clause +ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); @@ -647,7 +647,7 @@ create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); @@ -697,7 +697,7 @@ insert into t3 values (1),(2); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); @@ -765,9 +765,9 @@ SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); id 2 INSERT INTO t2 VALUES ((SELECT * FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t2 VALUES ((SELECT id FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data SELECT * FROM t2; id 1 diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result index a70e6df3d00..bc9db36c8ce 100644 --- a/mysql-test/r/subselect_exists_to_in.result +++ b/mysql-test/r/subselect_exists_to_in.result @@ -583,7 +583,7 @@ a b 1 11 2 12 update t1 set b= (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1 set b= (select b from t2); ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); @@ -606,7 +606,7 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); @@ -632,7 +632,7 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: You can't specify target table 't12' for update in FROM clause +ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); @@ -651,7 +651,7 @@ create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); @@ -701,7 +701,7 @@ insert into t3 values (1),(2); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); @@ -769,9 +769,9 @@ SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); id 2 INSERT INTO t2 VALUES ((SELECT * FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t2 VALUES ((SELECT id FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data SELECT * FROM t2; id 1 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 16f67820e0e..975d90b8358 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -586,7 +586,7 @@ a b 1 11 2 12 update t1 set b= (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1 set b= (select b from t2); ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); @@ -609,7 +609,7 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); @@ -635,7 +635,7 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: You can't specify target table 't12' for update in FROM clause +ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); @@ -654,7 +654,7 @@ create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); @@ -704,7 +704,7 @@ insert into t3 values (1),(2); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); @@ -772,9 +772,9 @@ SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); id 2 INSERT INTO t2 VALUES ((SELECT * FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t2 VALUES ((SELECT id FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data SELECT * FROM t2; id 1 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index bc2cc71b0fa..f9ea3c45ee3 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -582,7 +582,7 @@ a b 1 11 2 12 update t1 set b= (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1 set b= (select b from t2); ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); @@ -605,7 +605,7 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); @@ -631,7 +631,7 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: You can't specify target table 't12' for update in FROM clause +ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); @@ -650,7 +650,7 @@ create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); @@ -700,7 +700,7 @@ insert into t3 values (1),(2); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); @@ -768,9 +768,9 @@ SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); id 2 INSERT INTO t2 VALUES ((SELECT * FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t2 VALUES ((SELECT id FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data SELECT * FROM t2; id 1 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 40a63afb1c6..2bc182ac6e9 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -585,7 +585,7 @@ a b 1 11 2 12 update t1 set b= (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1 set b= (select b from t2); ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); @@ -608,7 +608,7 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); @@ -634,7 +634,7 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: You can't specify target table 't12' for update in FROM clause +ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); @@ -653,7 +653,7 @@ create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); @@ -703,7 +703,7 @@ insert into t3 values (1),(2); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); @@ -771,9 +771,9 @@ SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); id 2 INSERT INTO t2 VALUES ((SELECT * FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t2 VALUES ((SELECT id FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data SELECT * FROM t2; id 1 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 1fc2d98c0e8..a41c7636038 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -582,7 +582,7 @@ a b 1 11 2 12 update t1 set b= (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1 set b= (select b from t2); ERROR 21000: Subquery returns more than 1 row update t1 set b= (select b from t2 where t1.a = t2.a); @@ -605,7 +605,7 @@ select * from t1 where b = (select b from t2 where t1.a = t2.a); a b 2 12 delete from t1 where b in (select b from t1); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from t1 where b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete from t1 where b = (select b from t2 where t1.a = t2.a); @@ -631,7 +631,7 @@ a b 22 11 2 12 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a); -ERROR HY000: You can't specify target table 't12' for update in FROM clause +ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2); ERROR 21000: Subquery returns more than 1 row delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a); @@ -650,7 +650,7 @@ create table t3 (b int); insert into t2 values (1); insert into t3 values (1),(2); INSERT INTO t1 (x) VALUES ((SELECT x FROM t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t1 (x) VALUES ((SELECT b FROM t3)); ERROR 21000: Subquery returns more than 1 row INSERT INTO t1 (x) VALUES ((SELECT a FROM t2)); @@ -700,7 +700,7 @@ insert into t3 values (1),(2); select * from t1; x y replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2)); ERROR 21000: Subquery returns more than 1 row replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2)); @@ -768,9 +768,9 @@ SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); id 2 INSERT INTO t2 VALUES ((SELECT * FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data INSERT INTO t2 VALUES ((SELECT id FROM t2)); -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data SELECT * FROM t2; id 1 diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 9b1c1b6955e..eb896082810 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -1137,12 +1137,12 @@ ERROR HY000: Variable 'ft_stopword_file' is a read only variable # SHOW VARIABLES like 'back_log'; Variable_name Value -back_log 50 +back_log 150 SELECT @@session.back_log; ERROR HY000: Variable 'back_log' is a GLOBAL variable SELECT @@global.back_log; @@global.back_log -50 +150 SET @@session.back_log= 7; ERROR HY000: Variable 'back_log' is a read only variable SET @@global.back_log= 7; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 19fc1bda402..5b5410f4bd6 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -205,7 +205,7 @@ ERROR 42S02: Unknown table 'v100' drop view t1; ERROR HY000: 'test.t1' is not VIEW drop table v1; -ERROR 42S02: Unknown table 'test.v1' +ERROR 42S02: 'test.v1' is a view drop view v1,v2; drop table t1; create table t1 (a int); @@ -931,13 +931,13 @@ ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2 update v2 set col1 = (select max(col1) from t1); ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'. update v2 set col1 = (select max(col1) from v2); -ERROR HY000: You can't specify target table 'v2' for update in FROM clause +ERROR HY000: Table 'v2' is specified twice, both as a target for 'UPDATE' and as a separate source for data update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v2'. update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't1'. update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; -ERROR HY000: You can't specify target table 'v1' for update in FROM clause +ERROR HY000: Table 'v1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2'. update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; @@ -947,17 +947,17 @@ ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 't2 update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v2'. update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'v1'. update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; -ERROR HY000: You can't specify target table 't2' for update in FROM clause +ERROR HY000: Table 't2' is specified twice, both as a target for 'UPDATE' and as a separate source for data update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; -ERROR HY000: You can't specify target table 'v2' for update in FROM clause +ERROR HY000: Table 'v2' is specified twice, both as a target for 'UPDATE' and as a separate source for data update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 't1'. update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; @@ -975,27 +975,27 @@ ERROR HY000: The definition of table 'v3' prevents operation UPDATE on table 'v3 update v3 set v3.col1 = (select max(col1) from v2); ERROR HY000: The definition of table 'v2' prevents operation UPDATE on table 'v3'. update v3 set v3.col1 = (select max(col1) from v3); -ERROR HY000: You can't specify target table 'v3' for update in FROM clause +ERROR HY000: Table 'v3' is specified twice, both as a target for 'UPDATE' and as a separate source for data delete from v2 where col1 = (select max(col1) from v1); ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'. delete from v2 where col1 = (select max(col1) from t1); ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'. delete from v2 where col1 = (select max(col1) from v2); -ERROR HY000: You can't specify target table 'v2' for update in FROM clause +ERROR HY000: Table 'v2' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v2'. delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 't1'. delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1; -ERROR HY000: You can't specify target table 'v1' for update in FROM clause +ERROR HY000: Table 'v1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 'v2'. delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1; -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1; ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'v1'. delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1; -ERROR HY000: You can't specify target table 'v2' for update in FROM clause +ERROR HY000: Table 'v2' is specified twice, both as a target for 'DELETE' and as a separate source for data delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1; ERROR HY000: The definition of table 'v2' prevents operation DELETE on table 't1'. delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1; @@ -1009,15 +1009,15 @@ ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v2 insert into v2 values ((select max(col1) from t1)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2'. insert into t1 values ((select max(col1) from t1)); -ERROR HY000: You can't specify target table 't1' for update in FROM clause +ERROR HY000: Table 't1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into v2 values ((select max(col1) from t1)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 'v2'. insert into v2 values ((select max(col1) from v2)); -ERROR HY000: You can't specify target table 'v2' for update in FROM clause +ERROR HY000: Table 'v2' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into t1 values ((select max(col1) from v2)); ERROR HY000: The definition of table 'v2' prevents operation INSERT on table 't1'. insert into v2 values ((select max(col1) from v2)); -ERROR HY000: You can't specify target table 'v2' for update in FROM clause +ERROR HY000: Table 'v2' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into v3 (col1) values ((select max(col1) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'v3'. insert into v3 (col1) values ((select max(col1) from t1)); diff --git a/mysql-test/suite/archive/discover.result b/mysql-test/suite/archive/discover.result index c4f4bb4104f..726c8712917 100644 --- a/mysql-test/suite/archive/discover.result +++ b/mysql-test/suite/archive/discover.result @@ -135,4 +135,7 @@ select * from t1; a flush tables; create table t1 (a int) engine=archive; +ERROR 42S01: Table 't1' already exists +flush tables; +create table t1 (a int) engine=archive; drop table t1; diff --git a/mysql-test/suite/archive/discover.test b/mysql-test/suite/archive/discover.test index 8dfe09f7b33..144a5dbdcf9 100644 --- a/mysql-test/suite/archive/discover.test +++ b/mysql-test/suite/archive/discover.test @@ -125,6 +125,10 @@ create table t1 (a int) engine=archive; select * from t1; flush tables; remove_file $mysqld_datadir/test/t1.ARZ; +--error ER_TABLE_EXISTS_ERROR +create table t1 (a int) engine=archive; +remove_file $mysqld_datadir/test/t1.frm; +flush tables; create table t1 (a int) engine=archive; drop table t1; diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 96b6d3171f0..e6d98159b39 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -7579,7 +7579,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM test.tb2 limit 2' at line 1 CREATE OR REPLACE TEMPORARY VIEW test.v1 AS SELECT * FROM test.tb2 limit 2 ; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TEMPORARY VIEW test.v1 AS +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VIEW test.v1 AS SELECT * FROM test.tb2 limit 2' at line 1 Drop view if exists test.v1 ; Use test; diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index ddde31b76d1..21990c2bd9d 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -7580,7 +7580,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM test.tb2 limit 2' at line 1 CREATE OR REPLACE TEMPORARY VIEW test.v1 AS SELECT * FROM test.tb2 limit 2 ; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TEMPORARY VIEW test.v1 AS +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VIEW test.v1 AS SELECT * FROM test.tb2 limit 2' at line 1 Drop view if exists test.v1 ; Use test; diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index 39782f8d2c5..55704135530 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -8400,7 +8400,7 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM test.tb2 limit 2' at line 1 CREATE OR REPLACE TEMPORARY VIEW test.v1 AS SELECT * FROM test.tb2 limit 2 ; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TEMPORARY VIEW test.v1 AS +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VIEW test.v1 AS SELECT * FROM test.tb2 limit 2' at line 1 Drop view if exists test.v1 ; Use test; diff --git a/mysql-test/suite/rpl/disabled.def b/mysql-test/suite/rpl/disabled.def index 5cc3916b614..de3091a56e5 100644 --- a/mysql-test/suite/rpl/disabled.def +++ b/mysql-test/suite/rpl/disabled.def @@ -10,7 +10,6 @@ # ############################################################################## -rpl_row_create_table : Bug#11759274 2010-02-27 andrei failed different way than earlier with bug#45576 rpl_spec_variables : BUG#11755836 2009-10-27 jasonh rpl_spec_variables fails on PB2 hpux rpl_get_master_version_and_clock : Bug#11766137 Jan 05 2011 joro Valgrind warnings rpl_get_master_version_and_clock rpl_partition_archive : MDEV-5077 2013-09-27 svoj Cannot exchange partition with archive table diff --git a/mysql-test/suite/rpl/r/create_or_replace_mix.result b/mysql-test/suite/rpl/r/create_or_replace_mix.result new file mode 100644 index 00000000000..99de4ba729d --- /dev/null +++ b/mysql-test/suite/rpl/r/create_or_replace_mix.result @@ -0,0 +1,162 @@ +include/rpl_init.inc [topology=1->2] +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; +# +# Check how create table and create or replace table are logged +# +create table t1 (to_be_deleted int); +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# +# Ensure that also failed create_or_replace are logged +# +create table t1 (a int); +create or replace table t1; +ERROR 42000: A table must have at least 1 column +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create table t1 (a int); +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +show tables; +Tables_in_test +t1 +t2 +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Ensure that CREATE are run as CREATE OR REPLACE on slave +# +create table t1 (server_2_to_be_delete int); +create table t1 (new_table int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `new_table` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# Ensure that DROP TABLE is run as DROP IF NOT EXISTS +# +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); +drop table t1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `server_2_ver_2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) +drop table t1; +# +# Ensure that CREATE ... SELECT is recorded as one GTID on the slave +# +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (a int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Xid # # COMMIT /* XID */ +drop table t1; +drop table t2,t3; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/create_or_replace_row.result b/mysql-test/suite/rpl/r/create_or_replace_row.result new file mode 100644 index 00000000000..7c3a27573e5 --- /dev/null +++ b/mysql-test/suite/rpl/r/create_or_replace_row.result @@ -0,0 +1,184 @@ +include/rpl_init.inc [topology=1->2] +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; +# +# Check how create table and create or replace table are logged +# +create table t1 (to_be_deleted int); +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `a_in_temporary` int(11) DEFAULT NULL +) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `f1` int(1) NOT NULL DEFAULT '0' +) +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `a_in_temporary` int(11) DEFAULT NULL +) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# +# Ensure that also failed create_or_replace are logged +# +create table t1 (a int); +create or replace table t1; +ERROR 42000: A table must have at least 1 column +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create table t1 (a int); +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +show tables; +Tables_in_test +t1 +t2 +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Ensure that CREATE are run as CREATE OR REPLACE on slave +# +create table t1 (server_2_to_be_delete int); +create table t1 (new_table int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `new_table` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# Ensure that DROP TABLE is run as DROP IF NOT EXISTS +# +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); +drop table t1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `server_2_ver_2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) +drop table t1; +# +# Ensure that CREATE ... SELECT is recorded as one GTID on the slave +# +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (a int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Table_map # # table_id: # (test.t1) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB +slave-bin.000001 # Table_map # # table_id: # (test.t2) +slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +slave-bin.000001 # Xid # # COMMIT /* XID */ +drop table t1; +drop table t2,t3; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/create_or_replace_statement.result b/mysql-test/suite/rpl/r/create_or_replace_statement.result new file mode 100644 index 00000000000..f5d77a0f697 --- /dev/null +++ b/mysql-test/suite/rpl/r/create_or_replace_statement.result @@ -0,0 +1,144 @@ +include/rpl_init.inc [topology=1->2] +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; +# +# Check how create table and create or replace table are logged +# +create table t1 (to_be_deleted int); +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 (a int) engine=myisam +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; insert into t2 values (0),(1),(2),(2) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create temporary table t3 (a_in_temporary int) engine=myisam +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (to_be_deleted int) +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 AS SELECT 1 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t2 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; CREATE OR REPLACE table t1 like t3 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# +# Ensure that also failed create_or_replace are logged +# +create table t1 (a int); +create or replace table t1; +ERROR 42000: A table must have at least 1 column +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +create table t1 (a int); +create or replace table t1 (a int primary key) select a from t2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +binlog from server 1 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create table t1 (a int) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; create or replace table t1 (a int primary key) select a from t2 +show tables; +Tables_in_test +t2 +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1' +# +# Ensure that CREATE are run as CREATE OR REPLACE on slave +# +create table t1 (server_2_to_be_delete int); +create table t1 (new_table int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `new_table` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# Ensure that DROP TABLE is run as DROP IF NOT EXISTS +# +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); +drop table t1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `server_2_ver_2` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1`,`t4` /* generated by server */ +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (server_2_ver_2 int) +drop table t1; +# +# Ensure that CREATE ... SELECT is recorded as one GTID on the slave +# +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +binlog from server 2 +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t1 (a int) +slave-bin.000001 # Gtid # # BEGIN GTID #-#-# +slave-bin.000001 # Query # # use `test`; insert into t1 values (0),(1),(2) +slave-bin.000001 # Query # # COMMIT +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create table t2 engine=myisam select * from t1 +slave-bin.000001 # Gtid # # GTID #-#-# +slave-bin.000001 # Query # # use `test`; create or replace table t2 engine=innodb select * from t1 +drop table t1; +drop table t2,t3; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_startpos.result b/mysql-test/suite/rpl/r/rpl_gtid_startpos.result index 9be5903b2e9..1c9f771b5cc 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_startpos.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_startpos.result @@ -110,6 +110,8 @@ DROP TABLE t1; SET SQL_LOG_BIN=1; RESET SLAVE; SET GLOBAL gtid_slave_pos=""; +SET @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +SET GLOBAL slave_ddl_exec_mode=STRICT; include/start_slave.inc SELECT * FROM t1 ORDER BY a; a @@ -225,4 +227,5 @@ a 1 2 DROP TABLE t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index 393e2fdb851..07822a39b46 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -1,23 +1,24 @@ -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; -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc -CREATE TABLE t1 (a INT, b INT); +include/master-slave.inc +[connection master] +include/wait_for_slave_to_stop.inc +include/wait_for_slave_to_start.inc +include/rpl_reset.inc +CREATE TABLE t1 (a INT); +CREATE OR REPLACE TABLE t1 (a INT, b INT); CREATE TABLE t2 (a INT, b INT) ENGINE=Merge; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 (a INT, b INT) +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8 **** On Master **** SHOW CREATE TABLE t1; @@ -111,15 +112,10 @@ NULL 3 6 NULL 4 2 NULL 5 10 NULL 6 12 -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3; ERROR 23000: Duplicate entry '2' for key 'b' -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info +include/show_binlog_events.inc CREATE TABLE t7 (a INT, b INT UNIQUE); INSERT INTO t7 SELECT a,b FROM tt3; ERROR 23000: Duplicate entry '2' for key 'b' @@ -128,23 +124,20 @@ a b 1 2 2 4 3 6 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t7) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT SELECT * FROM t7 ORDER BY a,b; a b 1 2 2 4 3 6 -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc CREATE TEMPORARY TABLE tt4 (a INT, b INT); INSERT INTO tt4 VALUES (4,8), (5,10), (6,12); BEGIN; @@ -152,11 +145,11 @@ INSERT INTO t7 SELECT a,b FROM tt4; ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t7) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT SELECT * FROM t7 ORDER BY a,b; a b @@ -174,11 +167,7 @@ a b 4 8 5 10 6 12 -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc CREATE TABLE t8 LIKE t4; CREATE TABLE t9 LIKE tt4; CREATE TEMPORARY TABLE tt5 LIKE t4; @@ -197,9 +186,11 @@ Create Table CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t8 LIKE t4 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL @@ -219,15 +210,12 @@ Create Table CREATE TABLE `t9` ( ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9; STOP SLAVE; +include/wait_for_slave_to_stop.inc SET GLOBAL storage_engine=@storage_engine; START SLAVE; +include/wait_for_slave_to_start.inc ================ BUG#22864 ================ -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; +include/rpl_reset.inc SET AUTOCOMMIT=0; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); @@ -270,37 +258,38 @@ a 1 2 3 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t3` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t3) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB master-bin.000001 # Table_map # # table_id: # (test.t4) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT SHOW TABLES; Tables_in_test @@ -333,10 +322,7 @@ a 3 DROP TABLE IF EXISTS t1,t2,t3,t4; SET AUTOCOMMIT=1; -STOP SLAVE; -RESET SLAVE; -RESET MASTER; -START SLAVE; +include/rpl_reset.inc CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (a INT) ENGINE=INNODB; @@ -355,19 +341,21 @@ a 4 6 9 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Table_map # # table_id: # (test.t2) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ SELECT * FROM t2 ORDER BY a; a @@ -377,11 +365,7 @@ a 6 9 TRUNCATE TABLE t2; -**** Resetting master and slave **** -include/stop_slave.inc -RESET SLAVE; -RESET MASTER; -include/start_slave.inc +include/rpl_reset.inc BEGIN; INSERT INTO t2 SELECT a*a FROM t1; CREATE TEMPORARY TABLE tt2 @@ -394,8 +378,14 @@ Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT * FROM t2 ORDER BY a; a -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK SELECT * FROM t2 ORDER BY a; a DROP TABLE t1,t2; @@ -412,35 +402,28 @@ a 1 2 DROP TABLE t1; -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; +include/rpl_reset.inc DROP DATABASE IF EXISTS mysqltest1; CREATE DATABASE mysqltest1; CREATE TABLE mysqltest1.without_select (f1 BIGINT); CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # DROP DATABASE IF EXISTS mysqltest1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # CREATE DATABASE mysqltest1 +master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE mysqltest1.without_select (f1 BIGINT) -master-bin.000001 # Query # # BEGIN +master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE `mysqltest1`.`with_select` ( `f1` int(1) NOT NULL DEFAULT '0' ) master-bin.000001 # Table_map # # table_id: # (mysqltest1.with_select) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT DROP DATABASE mysqltest1; -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; +include/rpl_reset.inc CREATE TEMPORARY TABLE t7(c1 INT); CREATE TABLE t5(c1 INT); CREATE TABLE t4(c1 INT); @@ -461,4 +444,5 @@ DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3; DROP TEMPORARY TABLES t7; DROP TABLES t4, t5; DROP TABLES IF EXISTS bug48506_t4; +include/rpl_end.inc end of the tests diff --git a/mysql-test/suite/rpl/r/rpl_row_log.result b/mysql-test/suite/rpl/r/rpl_row_log.result index d3d5b42c80a..83ec26486e0 100644 --- a/mysql-test/suite/rpl/r/rpl_row_log.result +++ b/mysql-test/suite/rpl/r/rpl_row_log.result @@ -6,6 +6,8 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM; insert into t1 values (NULL); drop table t1; @@ -287,4 +289,5 @@ a b 5 1 6 1 drop table t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_log_innodb.result b/mysql-test/suite/rpl/r/rpl_row_log_innodb.result index 5dd5ff1b4ff..3b9733a18e8 100644 --- a/mysql-test/suite/rpl/r/rpl_row_log_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_log_innodb.result @@ -6,6 +6,8 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; create table t1(n int not null auto_increment primary key)ENGINE=InnoDB; insert into t1 values (NULL); drop table t1; @@ -287,4 +289,5 @@ a b 5 1 6 1 drop table t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result b/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result index 9872316dc97..a132b50a471 100644 --- a/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result +++ b/mysql-test/suite/rpl/r/rpl_row_show_relaylog_events.result @@ -172,7 +172,7 @@ include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000002 # Binlog_checkpoint # # slave-bin.000002 slave-bin.000002 # Gtid # # GTID #-#-# -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info @@ -181,7 +181,7 @@ slave-bin.000002 # Gtid # # GTID #-#-# ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2,3 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info diff --git a/mysql-test/suite/rpl/r/rpl_stm_log.result b/mysql-test/suite/rpl/r/rpl_stm_log.result index 360a0e526de..da925035c9c 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_log.result +++ b/mysql-test/suite/rpl/r/rpl_stm_log.result @@ -6,6 +6,8 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc +set @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +set @@global.slave_ddl_exec_mode=STRICT; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM; insert into t1 values (NULL); drop table t1; @@ -286,4 +288,5 @@ a b 5 1 6 1 drop table t1; +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result b/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result index 57d84eef52e..69e144b5cd1 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result +++ b/mysql-test/suite/rpl/r/rpl_stm_mix_show_relaylog_events.result @@ -154,7 +154,7 @@ include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info slave-bin.000002 # Binlog_checkpoint # # slave-bin.000002 slave-bin.000002 # Gtid # # GTID #-#-# -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info @@ -163,7 +163,7 @@ slave-bin.000002 # Gtid # # GTID #-#-# ******** [slave] SHOW BINLOG EVENTS IN <FILE> LIMIT 2,3 ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info -slave-bin.000002 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +slave-bin.000002 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ ******** [slave] SHOW BINLOG EVENTS ******** include/show_events.inc Log_name Pos Event_type Server_id End_log_pos Info diff --git a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result index a0102baacfe..1933b0b82f8 100644 --- a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result +++ b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result @@ -62,7 +62,7 @@ slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT slave-bin.000001 # Gtid # # GTID #-#-# slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */ slave-bin.000001 # Gtid # # GTID #-#-# -slave-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ slave-bin.000001 # Gtid # # BEGIN GTID #-#-# slave-bin.000001 # Table_map # # table_id: # (test.t1) slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F @@ -73,7 +73,7 @@ slave-bin.000001 # Gtid # # BEGIN GTID #-#-# slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2) slave-bin.000001 # Xid # # COMMIT /* XID */ slave-bin.000001 # Gtid # # GTID #-#-# -slave-bin.000001 # Query # # use `test`; DROP TABLE `t3`,`t1` /* generated by server */ +slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t3`,`t1` /* generated by server */ # Bug#55478 Row events wrongly apply on the temporary table of the same name # ========================================================================== diff --git a/mysql-test/suite/rpl/t/create_or_replace.inc b/mysql-test/suite/rpl/t/create_or_replace.inc new file mode 100644 index 00000000000..dad705403ed --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace.inc @@ -0,0 +1,137 @@ +# Test CREATE OR REPLACE TABLE in replication +--source include/have_innodb.inc + +--let $rpl_topology=1->2 +--source include/rpl_init.inc + +# Create help tables +create table t2 (a int) engine=myisam; +insert into t2 values (0),(1),(2),(2); +create temporary table t3 (a_in_temporary int) engine=myisam; + +--echo # +--echo # Check how create table and create or replace table are logged +--echo # + +save_master_pos; +connection server_2; +sync_with_master; +create table t1 (to_be_deleted int); + +connection server_1; +CREATE TABLE t1 AS SELECT 1 AS f1; +CREATE OR REPLACE TABLE t1 AS SELECT 2 AS f1; +CREATE OR REPLACE table t1 like t2; +CREATE OR REPLACE table t1 like t3; +drop table t1; + +--echo binlog from server 1 +--source include/show_binlog_events.inc +save_master_pos; +connection server_2; +sync_with_master; +--echo binlog from server 2 +--source include/show_binlog_events.inc + +connection server_1; + +--echo # +--echo # Ensure that also failed create_or_replace are logged +--echo # + +--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) + +create table t1 (a int); +--error ER_TABLE_MUST_HAVE_COLUMNS +create or replace table t1; +drop table if exists t1; +# The following is not logged as t1 does not exists; +--error ER_DUP_ENTRY +create or replace table t1 (a int primary key) select a from t2; + +create table t1 (a int); +# This should be logged as we will delete t1 +--error ER_DUP_ENTRY +create or replace table t1 (a int primary key) select a from t2; + +--echo binlog from server 1 +--source include/show_binlog_events.inc +save_master_pos; +connection server_2; +sync_with_master; +show tables; +connection server_1; + +drop table if exists t1,t2; + +--echo # +--echo # Ensure that CREATE are run as CREATE OR REPLACE on slave +--echo # + +save_master_pos; +connection server_2; +sync_with_master; +create table t1 (server_2_to_be_delete int); +connection server_1; +create table t1 (new_table int); + +save_master_pos; +connection server_2; +sync_with_master; + +show create table t1; +connection server_1; +drop table t1; + +--echo # +--echo # Ensure that DROP TABLE is run as DROP IF NOT EXISTS +--echo # + +create table t1 (server_1_ver_1 int); +create table t4 (server_1_ver_2 int); + +save_master_pos; +connection server_2; +sync_with_master; +--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) + +# Drop the table on the slave +drop table t1; +connection server_1; +drop table t1,t4; +create table t1 (server_2_ver_2 int); +save_master_pos; +connection server_2; +sync_with_master; +show create table t1; +--echo binlog from server 2 +--source include/show_binlog_events.inc +connection server_1; +drop table t1; + +--echo # +--echo # Ensure that CREATE ... SELECT is recorded as one GTID on the slave +--echo # + +save_master_pos; +connection server_2; +sync_with_master; +--let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) +connection server_1; + +create table t1 (a int); +insert into t1 values (0),(1),(2); +create table t2 engine=myisam select * from t1; +create or replace table t2 engine=innodb select * from t1; +save_master_pos; +connection server_2; +sync_with_master; +--echo binlog from server 2 +--source include/show_binlog_events.inc +connection server_1; +drop table t1; + +# Clean up +drop table t2,t3; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/create_or_replace_mix.cnf b/mysql-test/suite/rpl/t/create_or_replace_mix.cnf new file mode 100644 index 00000000000..03d69b2864f --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace_mix.cnf @@ -0,0 +1,9 @@ +!include suite/rpl/my.cnf + +[mysqld.1] +log-slave-updates +loose-innodb + +[mysqld.2] +log-slave-updates +loose-innodb diff --git a/mysql-test/suite/rpl/t/create_or_replace_mix.test b/mysql-test/suite/rpl/t/create_or_replace_mix.test new file mode 100644 index 00000000000..0cabef15ad7 --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace_mix.test @@ -0,0 +1,4 @@ +# Testing create or replace table in mixed mode. + +--source include/have_binlog_format_mixed.inc +--source create_or_replace.inc diff --git a/mysql-test/suite/rpl/t/create_or_replace_row.cnf b/mysql-test/suite/rpl/t/create_or_replace_row.cnf new file mode 100644 index 00000000000..03d69b2864f --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace_row.cnf @@ -0,0 +1,9 @@ +!include suite/rpl/my.cnf + +[mysqld.1] +log-slave-updates +loose-innodb + +[mysqld.2] +log-slave-updates +loose-innodb diff --git a/mysql-test/suite/rpl/t/create_or_replace_row.test b/mysql-test/suite/rpl/t/create_or_replace_row.test new file mode 100644 index 00000000000..88dd8fd2d74 --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace_row.test @@ -0,0 +1,4 @@ +# Testing create or replace table in mixed mode. + +--source include/have_binlog_format_row.inc +--source create_or_replace.inc diff --git a/mysql-test/suite/rpl/t/create_or_replace_statement.cnf b/mysql-test/suite/rpl/t/create_or_replace_statement.cnf new file mode 100644 index 00000000000..03d69b2864f --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace_statement.cnf @@ -0,0 +1,9 @@ +!include suite/rpl/my.cnf + +[mysqld.1] +log-slave-updates +loose-innodb + +[mysqld.2] +log-slave-updates +loose-innodb diff --git a/mysql-test/suite/rpl/t/create_or_replace_statement.test b/mysql-test/suite/rpl/t/create_or_replace_statement.test new file mode 100644 index 00000000000..2709e4142f4 --- /dev/null +++ b/mysql-test/suite/rpl/t/create_or_replace_statement.test @@ -0,0 +1,4 @@ +# Testing create or replace table in mixed mode. + +--source include/have_binlog_format_statement.inc +--source create_or_replace.inc diff --git a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test index 72f1201c93c..b27250f908f 100644 --- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test +++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test @@ -52,6 +52,8 @@ CREATE DATABASE IF NOT EXISTS mysqltest; USE mysqltest; CREATE TABLE IF NOT EXISTS t(c1 int); CREATE TABLE IF NOT EXISTS t1 LIKE t; +# The following will not be logged because t2 existed and we will not +# put the data of SELECT into the binary log CREATE TABLE IF NOT EXISTS t2 SELECT * FROM t; CREATE EVENT IF NOT EXISTS e ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR @@ -104,7 +106,7 @@ SELECT * FROM t1; SELECT * FROM t2; sync_slave_with_master; -# In these two statements, t1 and t2 are the base table. The recoreds of t2 +# In these two statements, t1 and t2 are the base table. The records of t2 # are inserted into it when CREATE TABLE ... SELECT was executed. SELECT * FROM t1; SELECT * FROM t2; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_startpos.test b/mysql-test/suite/rpl/t/rpl_gtid_startpos.test index 0797eead0ee..323e86426ce 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_startpos.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_startpos.test @@ -165,7 +165,7 @@ SET GLOBAL gtid_slave_pos=""; SELECT * FROM t1 ORDER BY a; -# Same thing, but this time using SQL_LOG_BIN=0 to avoid polliting the +# Same thing, but this time using SQL_LOG_BIN=0 to avoid polluting the # slave binlog. --connection server_2 @@ -175,6 +175,9 @@ DROP TABLE t1; SET SQL_LOG_BIN=1; RESET SLAVE; SET GLOBAL gtid_slave_pos=""; +# Ensure that the slave fails because of missing table to be dropped +SET @save_slave_ddl_exec_mode=@@global.slave_ddl_exec_mode; +SET GLOBAL slave_ddl_exec_mode=STRICT; --source include/start_slave.inc --sync_with_master @@ -349,6 +352,7 @@ SELECT * FROM t1 ORDER BY a; # Clean up. --connection server_1 DROP TABLE t1; - +--connection server_2 +set @@global.slave_ddl_exec_mode=@save_slave_ddl_exec_mode; --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_create_table.test b/mysql-test/suite/rpl/t/rpl_row_create_table.test index ef3c0758643..da73d753dcd 100644 --- a/mysql-test/suite/rpl/t/rpl_row_create_table.test +++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test @@ -28,7 +28,8 @@ START SLAVE; --source include/rpl_reset.inc connection master; -CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t1 (a INT); +CREATE OR REPLACE TABLE t1 (a INT, b INT); CREATE TABLE t2 (a INT, b INT) ENGINE=Merge; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8; diff --git a/mysql-test/suite/rpl/t/rpl_stm_log.test b/mysql-test/suite/rpl/t/rpl_stm_log.test index 7e7e1d4033f..23130528465 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_log.test +++ b/mysql-test/suite/rpl/t/rpl_stm_log.test @@ -4,7 +4,5 @@ let $engine_type=MyISAM; -- source extra/rpl_tests/rpl_log.test - # End of 4.1 tests -# Adding comment for force manual merge 5.0 -> wl1012: Delete me --source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/back_log_basic.result b/mysql-test/suite/sys_vars/r/back_log_basic.result index 5cfb0da65d6..d68e72a8a4a 100644 --- a/mysql-test/suite/sys_vars/r/back_log_basic.result +++ b/mysql-test/suite/sys_vars/r/back_log_basic.result @@ -1,20 +1,20 @@ select @@global.back_log; @@global.back_log -50 +150 select @@session.back_log; ERROR HY000: Variable 'back_log' is a GLOBAL variable show global variables like 'back_log'; Variable_name Value -back_log 50 +back_log 150 show session variables like 'back_log'; Variable_name Value -back_log 50 +back_log 150 select * from information_schema.global_variables where variable_name='back_log'; VARIABLE_NAME VARIABLE_VALUE -BACK_LOG 50 +BACK_LOG 150 select * from information_schema.session_variables where variable_name='back_log'; VARIABLE_NAME VARIABLE_VALUE -BACK_LOG 50 +BACK_LOG 150 set global back_log=1; ERROR HY000: Variable 'back_log' is a read only variable set session back_log=1; diff --git a/mysql-test/suite/sys_vars/r/slave_ddl_exec_mode_basic.result b/mysql-test/suite/sys_vars/r/slave_ddl_exec_mode_basic.result new file mode 100644 index 00000000000..e758e61f648 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/slave_ddl_exec_mode_basic.result @@ -0,0 +1,39 @@ +SET @start_value = @@global.slave_ddl_exec_mode; +SELECT @@global.slave_ddl_exec_mode; +@@global.slave_ddl_exec_mode +IDEMPOTENT +SELECT @@slave_ddl_exec_mode = @@GLOBAL.slave_ddl_exec_mode; +@@slave_ddl_exec_mode = @@GLOBAL.slave_ddl_exec_mode +1 +1 Expected +SELECT COUNT(@@slave_ddl_exec_mode); +COUNT(@@slave_ddl_exec_mode) +1 +1 Expected +SELECT COUNT(@@local.slave_ddl_exec_mode); +ERROR HY000: Variable 'slave_ddl_exec_mode' is a GLOBAL variable +Expected error 'Variable is a GLOBAL variable' +SELECT COUNT(@@SESSION.slave_ddl_exec_mode); +ERROR HY000: Variable 'slave_ddl_exec_mode' is a GLOBAL variable +Expected error 'Variable is a GLOBAL variable' +SELECT COUNT(@@GLOBAL.slave_ddl_exec_mode); +COUNT(@@GLOBAL.slave_ddl_exec_mode) +1 +1 Expected +SELECT slave_ddl_exec_mode = @@SESSION.version; +ERROR 42S22: Unknown column 'slave_ddl_exec_mode' in 'field list' +Expected error 'Readonly variable' +SET @@GLOBAL.slave_ddl_exec_mode=STRICT; +SELECT @@GLOBAL.slave_ddl_exec_mode; +@@GLOBAL.slave_ddl_exec_mode +STRICT +SET @@GLOBAL.slave_ddl_exec_mode=IDEMPOTENT; +SELECT @@GLOBAL.slave_ddl_exec_mode; +@@GLOBAL.slave_ddl_exec_mode +IDEMPOTENT +SET @@GLOBAL.slave_ddl_exec_mode=XXX; +ERROR 42000: Variable 'slave_ddl_exec_mode' can't be set to the value of 'XXX' +SELECT @@GLOBAL.slave_ddl_exec_mode; +@@GLOBAL.slave_ddl_exec_mode +IDEMPOTENT +SET @@global.slave_ddl_exec_mode= @start_value; diff --git a/mysql-test/suite/sys_vars/r/version_malloc_library_basic.result b/mysql-test/suite/sys_vars/r/version_malloc_library_basic.result new file mode 100644 index 00000000000..5895b8122d9 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/version_malloc_library_basic.result @@ -0,0 +1,53 @@ +'#---------------------BS_STVARS_053_01----------------------#' +SELECT COUNT(@@GLOBAL.version_malloc_library); +COUNT(@@GLOBAL.version_malloc_library) +1 +1 Expected +'#---------------------BS_STVARS_053_02----------------------#' +SET @@GLOBAL.version_malloc_library=1; +ERROR HY000: Variable 'version_malloc_library' is a read only variable +Expected error 'Read only variable' +SELECT COUNT(@@GLOBAL.version_malloc_library); +COUNT(@@GLOBAL.version_malloc_library) +1 +1 Expected +'#---------------------BS_STVARS_053_03----------------------#' +SELECT @@GLOBAL.version_malloc_library = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='version_malloc_library'; +@@GLOBAL.version_malloc_library = VARIABLE_VALUE +1 +1 Expected +SELECT COUNT(@@GLOBAL.version_malloc_library); +COUNT(@@GLOBAL.version_malloc_library) +1 +1 Expected +SELECT COUNT(VARIABLE_VALUE) +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='version_malloc_library'; +COUNT(VARIABLE_VALUE) +1 +1 Expected +'#---------------------BS_STVARS_053_04----------------------#' +SELECT @@version_malloc_library = @@GLOBAL.version_malloc_library; +@@version_malloc_library = @@GLOBAL.version_malloc_library +1 +1 Expected +'#---------------------BS_STVARS_053_05----------------------#' +SELECT COUNT(@@version_malloc_library); +COUNT(@@version_malloc_library) +1 +1 Expected +SELECT COUNT(@@local.version_malloc_library); +ERROR HY000: Variable 'version_malloc_library' is a GLOBAL variable +Expected error 'Variable is a GLOBAL variable' +SELECT COUNT(@@SESSION.version_malloc_library); +ERROR HY000: Variable 'version_malloc_library' is a GLOBAL variable +Expected error 'Variable is a GLOBAL variable' +SELECT COUNT(@@GLOBAL.version_malloc_library); +COUNT(@@GLOBAL.version_malloc_library) +1 +1 Expected +SELECT version_malloc_library = @@SESSION.version; +ERROR 42S22: Unknown column 'version_malloc_library' in 'field list' +Expected error 'Readonly variable' diff --git a/mysql-test/suite/sys_vars/t/slave_ddl_exec_mode_basic.test b/mysql-test/suite/sys_vars/t/slave_ddl_exec_mode_basic.test new file mode 100644 index 00000000000..64f8a75f443 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/slave_ddl_exec_mode_basic.test @@ -0,0 +1,67 @@ +############## mysql-test\t\slave_ddl_exec_mode_basic.test #################### +# # +# Variable Name: slave_ddl_exec_mode # +# Scope: GLOBAL & SESSION # +# Access Type: Dynamic # +# Data Type: Numeric # +# Default Value: 1 # +# Range: 1 - 65536 # +# # +# # +# Description: Test Cases of Dynamic System Variable slave_ddl_exec_mode # +# that checks the behavior of this variable in the following ways# +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Data Integrity # +# # +############################################################################### + +--source include/not_embedded.inc +--source include/load_sysvars.inc + +######################################################################## +# START OF slave_ddl_exec_mode TESTS # +######################################################################## + +SET @start_value = @@global.slave_ddl_exec_mode; + +SELECT @@global.slave_ddl_exec_mode; + +SELECT @@slave_ddl_exec_mode = @@GLOBAL.slave_ddl_exec_mode; +--echo 1 Expected + +SELECT COUNT(@@slave_ddl_exec_mode); +--echo 1 Expected + +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT COUNT(@@local.slave_ddl_exec_mode); +--echo Expected error 'Variable is a GLOBAL variable' + +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT COUNT(@@SESSION.slave_ddl_exec_mode); +--echo Expected error 'Variable is a GLOBAL variable' + +SELECT COUNT(@@GLOBAL.slave_ddl_exec_mode); +--echo 1 Expected + +--Error ER_BAD_FIELD_ERROR +SELECT slave_ddl_exec_mode = @@SESSION.version; +--echo Expected error 'Readonly variable' + + +SET @@GLOBAL.slave_ddl_exec_mode=STRICT; +SELECT @@GLOBAL.slave_ddl_exec_mode; + +SET @@GLOBAL.slave_ddl_exec_mode=IDEMPOTENT; +SELECT @@GLOBAL.slave_ddl_exec_mode; + +--error ER_WRONG_VALUE_FOR_VAR +SET @@GLOBAL.slave_ddl_exec_mode=XXX; +SELECT @@GLOBAL.slave_ddl_exec_mode; + +SET @@global.slave_ddl_exec_mode= @start_value; + +######################################################################## +# END OF slave_ddl_exec_mode TESTS # +######################################################################## diff --git a/mysql-test/suite/sys_vars/t/version_malloc_library_basic.test b/mysql-test/suite/sys_vars/t/version_malloc_library_basic.test new file mode 100644 index 00000000000..6318ca4abd5 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/version_malloc_library_basic.test @@ -0,0 +1,90 @@ +################## mysql-test\t\version_malloc_library.test ################### +# # +# Variable Name: version_malloc_library # +# Scope: Global # +# Access Type: Static # +# Data Type: String # +# # +# Description:Test Cases of Dynamic System Variable version # +# that checks the behavior of this variable in the following ways # +# * Value Check # +# * Scope Check # +# # +############################################################################### + +--echo '#---------------------BS_STVARS_053_01----------------------#' +#################################################################### +# Displaying default value # +#################################################################### +SELECT COUNT(@@GLOBAL.version_malloc_library); +--echo 1 Expected + + +--echo '#---------------------BS_STVARS_053_02----------------------#' +#################################################################### +# Check if Value can set # +#################################################################### + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@GLOBAL.version_malloc_library=1; +--echo Expected error 'Read only variable' + +SELECT COUNT(@@GLOBAL.version_malloc_library); +--echo 1 Expected + + + + +--echo '#---------------------BS_STVARS_053_03----------------------#' +################################################################# +# Check if the value in GLOBAL Table matches value in variable # +################################################################# + +SELECT @@GLOBAL.version_malloc_library = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='version_malloc_library'; +--echo 1 Expected + +SELECT COUNT(@@GLOBAL.version_malloc_library); +--echo 1 Expected + +SELECT COUNT(VARIABLE_VALUE) +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='version_malloc_library'; +--echo 1 Expected + + + +--echo '#---------------------BS_STVARS_053_04----------------------#' +############################################################################### +# Check if accessing variable with and without GLOBAL point to same variable # +############################################################################### +SELECT @@version_malloc_library = @@GLOBAL.version_malloc_library; +--echo 1 Expected + + + +--echo '#---------------------BS_STVARS_053_05----------------------#' +############################################################################### +# Check if version_malloc_library can be accessed with and without @@ sign # +############################################################################### + +SELECT COUNT(@@version_malloc_library); +--echo 1 Expected + +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT COUNT(@@local.version_malloc_library); +--echo Expected error 'Variable is a GLOBAL variable' + +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT COUNT(@@SESSION.version_malloc_library); +--echo Expected error 'Variable is a GLOBAL variable' + +SELECT COUNT(@@GLOBAL.version_malloc_library); +--echo 1 Expected + +--Error ER_BAD_FIELD_ERROR +SELECT version_malloc_library = @@SESSION.version; +--echo Expected error 'Readonly variable' + + diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index ebcad5f4af4..8bb7339ce83 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -2014,6 +2014,8 @@ create table t1 (a int, b int); create table t1 (a int, b int) select 2,2; --error ER_TABLE_EXISTS_ERROR create table t1 like t2; +--error ER_LOCK_WAIT_TIMEOUT +create or replace table t1 (a int, b int) select 2,2; disconnect user1; connection default; select * from t1; diff --git a/mysql-test/t/create_or_replace-master.opt b/mysql-test/t/create_or_replace-master.opt new file mode 100644 index 00000000000..e94228f2f33 --- /dev/null +++ b/mysql-test/t/create_or_replace-master.opt @@ -0,0 +1 @@ +--log-output=TABLE,FILE --general-log=1 --slow-query-log=1 diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test new file mode 100644 index 00000000000..88fbdb179e0 --- /dev/null +++ b/mysql-test/t/create_or_replace.test @@ -0,0 +1,293 @@ +# +# Check CREATE OR REPLACE ALTER TABLE +# + +--source include/have_innodb.inc +--source include/have_metadata_lock_info.inc +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings + +# +# Create help table +# + +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES(1),(2),(3); + +--echo # +--echo # Check first syntax and wrong usage +--echo # + +--error ER_WRONG_USAGE +CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int); +--error ER_WRONG_USAGE +create or replace trigger trg before insert on t1 for each row set @a:=1; + +# check that we don't try to create a log table in use +--error ER_BAD_LOG_STATEMENT +create or replace table mysql.general_log (a int); +--error ER_BAD_LOG_STATEMENT +create or replace table mysql.slow_log (a int); + +--echo # +--echo # Usage when table doesn't exist +--echo # + +CREATE OR REPLACE TABLE t1 (a int); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 (a int); +DROP TABLE t1; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +--error ER_TABLE_EXISTS_ERROR +CREATE TEMPORARY TABLE t1 (a int, b int, c int); +DROP TEMPORARY TABLE t1; + +--echo # +--echo # Testing with temporary tables +--echo # + +CREATE OR REPLACE TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int); +SHOW CREATE TABLE t1; +DROP TEMPORARY TABLE t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Test also with InnoDB +create temporary table t1 (i int) engine=InnoDB; +create or replace temporary table t1 (a int, b int) engine=InnoDB; +create or replace temporary table t1 (j int); +show create table t1; +drop table t1; + +# Using lock tables on normal tables with create or replace on temp tables +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int); +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine= innodb; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int) engine= innodb; +CREATE OR REPLACE TEMPORARY TABLE t1 (a int, b int) engine=myisam; +SHOW CREATE TABLE t1; +DROP TEMPORARY TABLE t1; +SHOW CREATE TABLE t1; +# Verify that table is still locked +--error ER_TABLE_NOT_LOCKED +CREATE OR REPLACE TABLE t2 (a int); +DROP TABLE t1; +UNLOCK TABLES; + +# +# Using CREATE SELECT +# + +CREATE OR REPLACE TEMPORARY TABLE t1 (a int) SELECT * from t2; +SELECT * FROM t1; +CREATE OR REPLACE TEMPORARY TABLE t1 (b int) SELECT * from t2; +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 AS SELECT a FROM t2; +CREATE TEMPORARY TABLE IF NOT EXISTS t1(a int, b int) SELECT 1,2 FROM t2; +DROP TABLE t1; + +CREATE TABLE t1 (a int); +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +create table t1 (a int); +--error ER_UPDATE_TABLE_USED +create or replace table t1 as select * from t1; +--error ER_UPDATE_TABLE_USED +create or replace table t1 as select a from (select a from t1) as t3; +--error ER_UPDATE_TABLE_USED +create or replace table t1 as select a from t2 where t2.a in (select a from t1); +drop table t1; + +--echo # +--echo # Testing with normal tables +--echo # + +CREATE OR REPLACE TABLE t1 (a int); +CREATE OR REPLACE TABLE t1 (a int, b int); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a int) SELECT * from t2; +SELECT * FROM t1; +TRUNCATE TABLE t1; +CREATE TABLE IF NOT EXISTS t1 (a int) SELECT * from t2; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (i int); +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# Using lock tables with CREATE OR REPLACE +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write; +CREATE OR REPLACE TABLE t1 (a int, b int); +# Verify if table is still locked +SELECT * FROM t1; +INSERT INTO t1 values(1,1); +CREATE OR REPLACE TABLE t1 (a int, b int, c int); +INSERT INTO t1 values(1,1,1); +--error ER_TABLE_NOT_LOCKED +CREATE OR REPLACE TABLE t3 (a int); +UNLOCK TABLES; +DROP TABLE t1; + +# Using lock tables with CREATE OR REPLACE ... SELECT +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write; +CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2; +# Verify if table is still locked +SELECT * FROM t2; +SELECT * FROM t1; +SELECT * FROM t1; +INSERT INTO t1 values(1,1,1); +CREATE OR REPLACE TABLE t1 (a int, b int, c int, d int); +INSERT INTO t1 values(1,1,1,1); +--error ER_TABLE_NOT_LOCKED +CREATE OR REPLACE TABLE t3 (a int); +UNLOCK TABLES; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLES t1 write,t2 write, t1 as t1_read read; +CREATE OR REPLACE TABLE t1 (a int, b int) select a,1 from t2; +SELECT * FROM t1; +SELECT * FROM t2; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t1 as t1_read; +DROP TABLE t1; +UNLOCK TABLES; + +CREATE OR REPLACE TABLE t1 (a int); +LOCK TABLE t1 WRITE; +CREATE OR REPLACE TABLE t1 AS SELECT 1; +SELECT * from t1; +--error ER_TABLE_NOT_LOCKED +SELECT * from t2; +DROP TABLE t1; + +--echo # +--echo # Test also with InnoDB (transactional engine) +--echo # + +create table t1 (i int) engine=innodb; +lock table t1 write; +create or replace table t1 (j int); +unlock tables; +show create table t1; +drop table t1; + +create table t1 (i int) engine=InnoDB; +lock table t1 write, t2 write; +create or replace table t1 (j int) engine=innodb; +unlock tables; +drop table t1; + +create table t1 (i int) engine=InnoDB; +create table t3 (i int) engine=InnoDB; +insert into t3 values(1),(2),(3); +lock table t1 write, t2 write, t3 write; +create or replace table t1 (a int, i int) engine=innodb select t2.a,t3.i from t2,t3; +unlock tables; +select * from t1 order by a,i; +drop table t1,t3; + +--echo # +--echo # Testing CREATE .. LIKE +--echo # + +create or replace table t1 like t2; +create or replace table t1 like t2; +show create table t1; +drop table t1; +create table t1 (b int); +lock tables t1 write, t2 read; +create or replace table t1 like t2; +SELECT * FROM t1; +INSERT INTO t1 values(1); +CREATE OR REPLACE TABLE t1 like t2; +INSERT INTO t1 values(2); +unlock tables; +show create table t1; +drop table t1; + +create or replace table t1 like t2; +--error ER_NONUNIQ_TABLE +create or replace table t1 like t1; +drop table t1; + +CREATE TEMPORARY TABLE t1 like t2; +--error ER_NONUNIQ_TABLE +CREATE OR REPLACE TABLE t1 like t1; +--error ER_NONUNIQ_TABLE +CREATE OR REPLACE TABLE t1 like t1; +drop table t1; + +CREATE TEMPORARY TABLE t1 like t2; +CREATE OR REPLACE TEMPORARY TABLE t3 like t1; +--error ER_NONUNIQ_TABLE +CREATE OR REPLACE TEMPORARY TABLE t3 like t3; +drop table t1,t3; + +--echo # +--echo # Test with prepared statements +--echo # + +prepare stmt1 from 'create or replace table t1 select * from t2'; +execute stmt1; +select * from t1; +execute stmt1; +select * from t1; +drop table t1; +execute stmt1; +select * from t1; +deallocate prepare stmt1; +drop table t1; + +--echo # +--echo # Test with views +--echo # + +create view t1 as select 1; +create table if not exists t1 (a int); +--error ER_IT_IS_A_VIEW +create or replace table t1 (a int); +--error ER_IT_IS_A_VIEW +drop table t1; +drop view t1; + +--echo # +--echo # MDEV-5602 CREATE OR REPLACE obtains stricter locks than the +--echo # connection had before +--echo # + +create table t1 (a int); +lock table t1 write, t2 read; +--replace_column 1 # +select * from information_schema.metadata_lock_info; +create or replace table t1 (i int); +--replace_column 1 # +select * from information_schema.metadata_lock_info; +create or replace table t1 like t2; +--replace_column 1 # +select * from information_schema.metadata_lock_info; +create or replace table t1 select 1 as f1; +--replace_column 1 # +select * from information_schema.metadata_lock_info; +drop table t1; +unlock tables; + +# +# Cleanup +# +DROP TABLE t2; diff --git a/mysql-test/t/mysqld--help.test b/mysql-test/t/mysqld--help.test index 1b119ca7ea6..c27fa58f935 100644 --- a/mysql-test/t/mysqld--help.test +++ b/mysql-test/t/mysqld--help.test @@ -20,7 +20,9 @@ perl; # their paths may vary: @skipvars=qw/basedir open-files-limit general-log-file log plugin-dir log-slow-queries pid-file slow-query-log-file log-basename - datadir slave-load-tmpdir tmpdir socket thread-pool-size/; + datadir slave-load-tmpdir tmpdir socket thread-pool-size + large-files-support lower-case-file-system system-time-zone + version.*/; # Plugins which may or may not be there: @plugins=qw/innodb ndb archive blackhole federated partition ndbcluster diff --git a/mysql-test/t/partition_exchange.test b/mysql-test/t/partition_exchange.test index d7dfd6f543e..e538bee16cd 100644 --- a/mysql-test/t/partition_exchange.test +++ b/mysql-test/t/partition_exchange.test @@ -439,7 +439,7 @@ CREATE TABLE t LIKE general_log; ALTER TABLE t PARTITION BY RANGE (UNIX_TIMESTAMP(event_time) DIV 1) (PARTITION p0 VALUES LESS THAN (123456789), PARTITION pMAX VALUES LESS THAN MAXVALUE); ---error ER_WRONG_USAGE +--error ER_BAD_LOG_STATEMENT ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE general_log; ALTER TABLE general_log ENGINE = CSV; SET @@global.general_log = @old_general_log_state; diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test index c34800976c7..21355ca42b7 100644 --- a/mysql-test/t/ps_ddl.test +++ b/mysql-test/t/ps_ddl.test @@ -1610,7 +1610,7 @@ call p_verify_reprepare_count(0); # Base table with name of table to be created exists --error ER_TABLE_EXISTS_ERROR execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); --error ER_TABLE_EXISTS_ERROR execute stmt; call p_verify_reprepare_count(0); @@ -1622,7 +1622,7 @@ execute stmt; call p_verify_reprepare_count(0); --error ER_TABLE_EXISTS_ERROR execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); drop temporary table t2; --error ER_TABLE_EXISTS_ERROR execute stmt; @@ -1641,7 +1641,7 @@ drop table t2; create view t2 as select 1; --error ER_TABLE_EXISTS_ERROR,9999 execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); --error ER_TABLE_EXISTS_ERROR,9999 execute stmt; call p_verify_reprepare_count(0); diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index c7a7e332236..633624bf4bf 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -141,7 +141,7 @@ drop view v100; drop view t1; # try to drop VIEW with DROP TABLE --- error ER_BAD_TABLE_ERROR +-- error ER_IT_IS_A_VIEW drop table v1; # try to drop table with DROP VIEW diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index bf865a53998..25f1c403a61 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -118,6 +118,15 @@ } { + pthread memalign memory loss2 + Memcheck:Leak + fun:memalign + fun:tls_get_addr_tail + ... + fun:*ha_initialize_handlerton* +} + +{ pthread pthread_key_create Memcheck:Leak fun:malloc |