diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/create-big.result (renamed from mysql-test/r/create_select-big.result) | 83 | ||||
| -rw-r--r-- | mysql-test/r/create.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/grant2.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/create-big.test (renamed from mysql-test/t/create_select-big.test) | 132 | ||||
| -rw-r--r-- | mysql-test/t/create.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/disabled.def | 1 | ||||
| -rw-r--r-- | mysql-test/t/grant2.test | 41 |
7 files changed, 277 insertions, 7 deletions
diff --git a/mysql-test/r/create_select-big.result b/mysql-test/r/create-big.result index 1c393bd2224..eb57bf59084 100644 --- a/mysql-test/r/create_select-big.result +++ b/mysql-test/r/create-big.result @@ -162,3 +162,86 @@ t1 CREATE TABLE `t1` ( `j` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; +drop table if exists t1,t2; +create table t1 (i int); +set session debug="+d,sleep_create_like_before_check_if_exists"; +reset master; +create table t2 like t1;; +insert into t1 values (1); +drop table t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2; +show binlog events in 'master-bin.000001' from 106; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 values (1) +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; drop table t1 +master-bin.000001 # Query 1 # use `test`; drop table t2 +create table t1 (i int); +set session debug="-d,sleep_create_like_before_check_if_exists:+d,sleep_create_like_before_copy"; +create table t2 like t1;; +create table if not exists t2 (j int); +Warnings: +Note 1050 Table 't2' already exists +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2; +reset master; +create table t2 like t1;; +drop table t1; +drop table t2; +show binlog events in 'master-bin.000001' from 106; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; drop table t1 +master-bin.000001 # Query 1 # use `test`; drop table t2 +create table t1 (i int); +set session debug="-d,sleep_create_like_before_copy:+d,sleep_create_like_before_ha_create"; +reset master; +create table t2 like t1;; +insert into t2 values (1); +drop table t2; +create table t2 like t1;; +drop table t2; +create table t2 like t1;; +drop table t1; +drop table t2; +show binlog events in 'master-bin.000001' from 106; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; insert into t2 values (1) +master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; drop table t1 +master-bin.000001 # Query 1 # use `test`; drop table t2 +create table t1 (i int); +set session debug="-d,sleep_create_like_before_ha_create:+d,sleep_create_like_before_binlogging"; +reset master; +create table t2 like t1;; +insert into t2 values (1); +drop table t2; +create table t2 like t1;; +drop table t2; +create table t2 like t1;; +drop table t1; +drop table t2; +show binlog events in 'master-bin.000001' from 106; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; insert into t2 values (1) +master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; drop table t2 +master-bin.000001 # Query 1 # use `test`; create table t2 like t1 +master-bin.000001 # Query 1 # use `test`; drop table t1 +master-bin.000001 # Query 1 # use `test`; drop table t2 +set session debug="-d,sleep_create_like_before_binlogging"; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index ef22b21e9fb..f570f6eb75c 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -371,7 +371,7 @@ ERROR 42S01: Table 't3' already exists create table non_existing_database.t1 like t1; ERROR 42000: Unknown database 'non_existing_database' create table t3 like non_existing_table; -ERROR 42S02: Unknown table 'non_existing_table' +ERROR 42S02: Table 'test.non_existing_table' doesn't exist create temporary table t3 like t1; ERROR 42S01: Table 't3' already exists drop table t1, t2, t3; diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 03019bd5c1f..f2722ee052e 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -381,3 +381,26 @@ drop table t2; REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; drop user `a@`@localhost; SET GLOBAL log_bin_trust_function_creators = 0; +drop database if exists mysqltest_1; +drop database if exists mysqltest_2; +drop user mysqltest_u1@localhost; +create database mysqltest_1; +create database mysqltest_2; +grant all on mysqltest_1.* to mysqltest_u1@localhost; +use mysqltest_2; +create table t1 (i int); +show create table mysqltest_2.t1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +create table t1 like mysqltest_2.t1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +grant select on mysqltest_2.t1 to mysqltest_u1@localhost; +show create table mysqltest_2.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +create table t1 like mysqltest_2.t1; +use test; +drop database mysqltest_1; +drop database mysqltest_2; +drop user mysqltest_u1@localhost; diff --git a/mysql-test/t/create_select-big.test b/mysql-test/t/create-big.test index 3fa655c5501..6cd6326cdb8 100644 --- a/mysql-test/t/create_select-big.test +++ b/mysql-test/t/create-big.test @@ -1,12 +1,17 @@ -# Tests for various aspects of CREATE TABLE ... SELECT implementation +# Tests for various concurrency-related aspects of CREATE TABLE ... SELECT +# and CREATE TABLE like implementation. # -# Note that we don't test general CREATE TABLE ... SELECT functionality here as -# it is already covered by create.test. We are more interested in extreme cases. +# Note that we don't test general CREATE TABLE ... SELECT/LIKE functionality +# here as it is already covered by create.test. We are more interested in +# extreme cases. # # This test takes rather long time so let us run it only in --big-test mode --source include/big_test.inc # We are using some debug-only features in this test --source include/have_debug.inc +# Some of tests below also use binlog to check that statements are +# executed and logged in correct order +--source include/have_binlog_format_mixed_or_statement.inc # Create auxilliary connections connect (addconroot1, localhost, root,,); @@ -20,7 +25,7 @@ drop table if exists t1,t2,t3,t4,t5; # -# Tests for concurrency problems. +# Tests for concurrency problems in CREATE TABLE ... SELECT # # We introduce delays between various stages of table creation # and check that other statements dealing with this table cannot @@ -266,3 +271,122 @@ connection default; select * from t1; show create table t1; drop table t1, t2, t3; + + +# Tests for possible concurrency issues with CREATE TABLE ... LIKE +# +# Bug #18950 "create table like does not obtain LOCK_open" +# Bug #23667 "CREATE TABLE LIKE is not isolated from alteration by other +# connections" +# +# Again the idea of this test is that we introduce artificial delays on +# various stages of table creation and check that concurrent statements +# for tables from CREATE TABLE ... LIKE are not interfering. + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# What happens if some statements sneak in right after we have +# opened source table ? +create table t1 (i int); +set session debug="+d,sleep_create_like_before_check_if_exists"; +# Reset binlog to have clear start +reset master; +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +# DML on source table should be allowed to run concurrently +insert into t1 values (1); +# And DDL should wait +drop table t1; +connection default; +--reap +show create table t2; +drop table t2; +# Let us check that statements were executed/binlogged in correct order +--replace_column 2 # 5 # +show binlog events in 'master-bin.000001' from 106; + +# Now let us check the gap between check for target table +# existance and copying of .frm file. +create table t1 (i int); +set session debug="-d,sleep_create_like_before_check_if_exists:+d,sleep_create_like_before_copy"; +# It should be impossible to create target table concurrently +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +create table if not exists t2 (j int); +connection default; +--reap +show create table t2; +drop table t2; +# And concurrent DDL on the source table should be still disallowed +reset master; +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +drop table t1; +connection default; +--reap +drop table t2; +--replace_column 2 # 5 # +show binlog events in 'master-bin.000001' from 106; + +# And now he gap between copying of .frm file and ha_create_table() call. +create table t1 (i int); +set session debug="-d,sleep_create_like_before_copy:+d,sleep_create_like_before_ha_create"; +# Both DML and DDL on target table should wait till operation completes +reset master; +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +insert into t2 values (1); +connection default; +--reap +drop table t2; +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +drop table t2; +connection default; +--reap +# Concurrent DDL on the source table still waits +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +drop table t1; +connection default; +--reap +drop table t2; +--replace_column 2 # 5 # +show binlog events in 'master-bin.000001' from 106; + +# Finally we check the gap between ha_create_table() and binlogging +create table t1 (i int); +set session debug="-d,sleep_create_like_before_ha_create:+d,sleep_create_like_before_binlogging"; +reset master; +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +insert into t2 values (1); +connection default; +--reap +drop table t2; +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +drop table t2; +connection default; +--reap +--send create table t2 like t1; +connection addconroot1; +--sleep 2 +drop table t1; +connection default; +--reap +drop table t2; +--replace_column 2 # 5 # +show binlog events in 'master-bin.000001' from 106; + +set session debug="-d,sleep_create_like_before_binlogging"; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 243cdea009e..fb589a5d11e 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -306,7 +306,7 @@ create table t3 like t1; create table t3 like mysqltest.t3; --error 1049 create table non_existing_database.t1 like t1; ---error 1051 +--error ER_NO_SUCH_TABLE create table t3 like non_existing_table; --error 1050 create temporary table t3 like t1; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index e3d72f1f7e0..12c5f38c680 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -30,7 +30,6 @@ rpl_ddl : BUG#26418 2007-03-01 mleich Slave out of sync after C rpl_ndb_innodb2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement rpl_ndb_myisam2ndb : Bug #19710 Cluster replication to partition table fails on DELETE FROM statement rpl_row_blob_innodb : BUG#18980 2006-04-10 kent Test fails randomly -synchronization : Bug#24529 Test 'synchronization' fails on Mac pushbuild; Also on Linux 64 bit. # the below testcase have been reworked to avoid the bug, test contains comment, keep bug open #ndb_binlog_ddl_multi : BUG#18976 2006-04-10 kent CRBR: multiple binlog, second binlog may miss schema log events diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 866c5a8a4b3..9b83cd5aab7 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -513,3 +513,44 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; drop user `a@`@localhost; SET GLOBAL log_bin_trust_function_creators = 0; + + +# +# Bug#25578 "CREATE TABLE LIKE does not require any privileges on source table" +# +--disable_warnings +drop database if exists mysqltest_1; +drop database if exists mysqltest_2; +--enable_warnings +--error 0,ER_CANNOT_USER +drop user mysqltest_u1@localhost; + +create database mysqltest_1; +create database mysqltest_2; +grant all on mysqltest_1.* to mysqltest_u1@localhost; +use mysqltest_2; +create table t1 (i int); + +# Connect as user with all rights on mysqltest_1 but with no rights on mysqltest_2. +connect (user1,localhost,mysqltest_u1,,mysqltest_1); +connection user1; +# As expected error is emitted +--error ER_TABLEACCESS_DENIED_ERROR +show create table mysqltest_2.t1; +# This should emit error as well +--error ER_TABLEACCESS_DENIED_ERROR +create table t1 like mysqltest_2.t1; + +# Now let us check that SELECT privilege on the source is enough +connection default; +grant select on mysqltest_2.t1 to mysqltest_u1@localhost; +connection user1; +show create table mysqltest_2.t1; +create table t1 like mysqltest_2.t1; + +# Clean-up +connection default; +use test; +drop database mysqltest_1; +drop database mysqltest_2; +drop user mysqltest_u1@localhost; |