diff options
Diffstat (limited to 'mysql-test/t/rpl_ddl.test')
| -rw-r--r-- | mysql-test/t/rpl_ddl.test | 268 |
1 files changed, 268 insertions, 0 deletions
diff --git a/mysql-test/t/rpl_ddl.test b/mysql-test/t/rpl_ddl.test new file mode 100644 index 00000000000..a99a71a841b --- /dev/null +++ b/mysql-test/t/rpl_ddl.test @@ -0,0 +1,268 @@ +######################## rpl-ddl.test ######################## +# # +# DDL statements (sometimes with implicit COMMIT) executed # +# by the master and it's propagation into the slave # +# # +############################################################## + +# +# NOTE, PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !! +# +# 1. !All! objects to be dropped, renamed, altered ... must be created +# in AUTOCOMMIT= 1 mode before AUTOCOMMIT is set to 0 and the test +# sequences start. +# +# 2. Never use a test object, which was direct or indirect affected by a +# preceeding test sequence again. +# Except table d1.t1 where ONLY DML is allowed. +# +# If one preceeding test sequence hits a (sometimes not good visible, +# because the sql error code of the statement might be 0) bug +# and these rules are ignored, a following test sequence might earn ugly +# effects like failing 'sync_slave_with_master', crashes of the slave or +# abort of the test case etc.. +# +# 3. The assignment of the DDL command to be tested to $my_stmt can +# be a bit difficult. "'" must be avoided, because the test +# routine "include/rpl_stmt_seq.inc" performs a +# eval SELECT CONCAT('######## ','$my_stmt',' ########') as ""; +# + +######## tested DDL commands +# +# let $my_stmt= COMMIT; +# let $my_stmt= ROLLBACK; +# let $my_stmt= SET AUTOCOMMIT=1; +# let $my_stmt= START TRANSACTION; +# let $my_stmt= BEGIN; +# let $my_stmt= DROP TABLE d1.t2; +# let $my_stmt= RENAME TABLE d1.t3 to d1.t20; <- wrong syntax !! +# let $my_stmt= ALTER TABLE d1.t4 ADD column f2 BIGINT; +# let $my_stmt= CREATE TABLE d1.t21 (f1 BIGINT) ENGINE= "InnoDB"; +# let $my_stmt= TRUNCATE TABLE d1.t7; +# let $my_stmt= LOCK TABLES d1.t1 WRITE, d1.t8 READ; +# let $my_stmt= UNLOCK TABLES; +# let $my_stmt= DROP INDEX my_idx6 ON d1.t6; +# let $my_stmt= CREATE INDEX my_idx5 ON d1.t5(f1); +# let $my_stmt= DROP DATABASE d2; +# let $my_stmt= CREATE DATABASE d3; +# +# FIXME: @code{LOAD MASTER DATA} is not tested +# +############################################# + +--source include/have_innodb.inc +source include/master-slave.inc; + + +######## some preparations +# +SET AUTOCOMMIT = 1; +# +# 1. DROP all objects, which probably already exist, but must be created here +# +--disable_warnings +DROP DATABASE IF EXISTS d1; +DROP DATABASE IF EXISTS d2; +DROP DATABASE IF EXISTS d3; +--enable_warnings +# +# 2. CREATE all objects needed +# working database is d1 +# working (transactional!) is d1.t1 +# +CREATE DATABASE d1; +CREATE DATABASE d2; +CREATE TABLE d1.t1 (f1 BIGINT) ENGINE= "InnoDB"; +INSERT INTO d1.t1 SET f1= 0; +CREATE TABLE d1.t2 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t3 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t4 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t5 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t6 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE INDEX my_idx6 ON d1.t6(f1); +CREATE TABLE d1.t7 (f1 BIGINT) ENGINE= "InnoDB"; +INSERT INTO d1.t7 SET f1= 0; +CREATE TABLE d1.t8 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t9 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t10 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t11 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t12 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t13 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t14 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t15 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t16 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t17 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t18 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE d1.t19 (f1 BIGINT) ENGINE= "InnoDB"; +# +COMMIT; # Just to be sure +# +# 3. master sessions: never do AUTOCOMMIT +# slave sessions: do AUTOCOMMIT, because we want to see fresh values +# every time +# default database is d1 +SET AUTOCOMMIT = 0; +use d1; +sync_slave_with_master; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SET AUTOCOMMIT = 1; +use d1; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + + +# We don't want to abort the whole test if one statement sent +# to the server gets an error, because the following test +# sequences are nearly independend of the previous statements. +--disable_abort_on_error + +###### banal case: (explicit) COMMIT and ROLLBACK +# just for checking if the test sequence is usable +let $my_stmt= COMMIT; +--source include/rpl_stmt_seq.inc +let $my_stmt= ROLLBACK; +--source include/rpl_stmt_seq.inc + +###### cases with commands very similar to COMMIT +let $my_stmt= SET AUTOCOMMIT=1; +--source include/rpl_stmt_seq.inc +SET AUTOCOMMIT=0; +# +let $my_stmt= START TRANSACTION; +--source include/rpl_stmt_seq.inc +# +let $my_stmt= BEGIN; +--source include/rpl_stmt_seq.inc + +###### cases with commands, which create or drop objects +#### (BASE) TABLES and (UPDATABLE) VIEWs +let $my_stmt= DROP TABLE d1.t2; +--source include/rpl_stmt_seq.inc +SHOW TABLES LIKE 't2'; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW TABLES LIKE 't2'; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# +let $my_stmt= RENAME TABLE d1.t3 to d1.t20; +--source include/rpl_stmt_seq.inc +SHOW TABLES LIKE 't20'; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW TABLES LIKE 't20'; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# +let $my_stmt= ALTER TABLE d1.t4 ADD column f2 BIGINT; +--source include/rpl_stmt_seq.inc +describe d1.t4; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +describe d1.t4; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# +let $my_stmt= CREATE TABLE d1.t21 (f1 BIGINT) ENGINE= "InnoDB"; +--source include/rpl_stmt_seq.inc +# +let $my_stmt= TRUNCATE TABLE d1.t7; +--source include/rpl_stmt_seq.inc +SELECT * FROM d1.t7; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SELECT * FROM d1.t7; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# +# MySQL insists in locking d1.t1, because rpl_stmt_seq performs an INSERT into +# this table. +let $my_stmt= LOCK TABLES d1.t1 WRITE, d1.t8 READ; +--source include/rpl_stmt_seq.inc +UNLOCK TABLES; +# +LOCK TABLES d1.t1 WRITE, d1.t8 READ; +let $my_stmt= UNLOCK TABLES; +--source include/rpl_stmt_seq.inc +# +#### INDEXES +let $my_stmt= DROP INDEX my_idx6 ON d1.t6; +--source include/rpl_stmt_seq.inc +SHOW INDEX FROM d1.t6; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW INDEX FROM d1.t6; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# +let $my_stmt= CREATE INDEX my_idx5 ON d1.t5(f1); +--source include/rpl_stmt_seq.inc +SHOW INDEX FROM d1.t5; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW INDEX FROM d1.t5; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +#### DATABASES +let $my_stmt= DROP DATABASE d2; +--source include/rpl_stmt_seq.inc +SHOW DATABASES LIKE "d2"; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW DATABASES LIKE "d2"; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log +# +let $my_stmt= CREATE DATABASE d3; +--source include/rpl_stmt_seq.inc +SHOW DATABASES LIKE "d3"; +connection slave; +--disable_query_log +SELECT '-------- switch to slave --------' as ""; +--enable_query_log +SHOW DATABASES LIKE "d3"; +connection master; +--disable_query_log +SELECT '-------- switch to master -------' as ""; +--enable_query_log + +--disable_warnings +DROP DATABASE IF EXISTS d1; +DROP DATABASE IF EXISTS d2; +DROP DATABASE IF EXISTS d3; +--enable_warnings |