diff options
| author | unknown <mleich@four.local.lan> | 2007-03-06 18:15:31 +0100 |
|---|---|---|
| committer | unknown <mleich@four.local.lan> | 2007-03-06 18:15:31 +0100 |
| commit | 6d28e54d19a46a6d81eaf44fcb9741bc5472533a (patch) | |
| tree | e09fe92134448d23ead2916f74a5254a8191338e | |
| parent | fa7c007ed45dd9f108c5d17ea5a836961c72085d (diff) | |
| download | mariadb-git-6d28e54d19a46a6d81eaf44fcb9741bc5472533a.tar.gz | |
Bug#18946 Test case rpl_ndb_ddl disabled
1. Fixes within the testscripts (affects rpl_ddl.test and rpl_ndb_ddl.test)
- slave connection is only an observer (-> AUTOCOMMIT = 0)
This removes the problem with the hanging test around DROP DATABASE (NDB). The hanging test around DROP DATABASE is a difference to InnoDB/MyISAm behaviour but fare
away of a clear bug. IMHO this behaviour does not violate the SQL standard and should be therefore simply accepted.
- removal of wrong comments
- CREATE/DROP TEMPORARY TABLE must not cause implicit commit of the current transaction.
NDB behaves here correct and InnoDB/Falcon wrong.
- Add a missing connection slave
- Reenable the test rpl_ndb_ddl.
2. Disable rpl_ddl.test because of Bug#26418.
3. Reenable rpl_ndb_ddl.test
4. Improvements (affect rpl_ddl.test and rpl_ndb_ddl.test)
- Better + extended comments which should prevent that somebody accidently destroys the logics of the test
- Replace SELECT's printing comments by "--echo" (decreases the number of auxiliary SQL commands)
- Remove the need for include/rpl_stmt_seq2.inc (was mostly redundant to rpl_stmt_seq.inc)
- Remove extra/rpl_tests/rpl_ndb_ddl.test (corrected extra/rpl_tests/rpl_ddl.test is sufficient)
- Shift assignment of values to $show_binlog, $manipulate (variables useful for debugging) into the toplevel scripts
- The temporary tables get now their storage engine from the variable $temp_engine_type. (more deterministic testing conditions)
- Add additional protocol line if the connection is switched (was partially missing)
- Add two DML commands for comparison purposes
BitKeeper/deleted/.del-rpl_ndb_ddl.test:
Delete: mysql-test/extra/rpl_tests/rpl_ndb_ddl.test
BitKeeper/deleted/.del-rpl_stmt_seq2.inc:
Delete: mysql-test/include/rpl_stmt_seq2.inc
mysql-test/extra/rpl_tests/rpl_ddl.test:
Improvements + Fixes
mysql-test/include/rpl_stmt_seq.inc:
Improvements
mysql-test/r/rpl_ddl.result:
Updated expected results
mysql-test/r/rpl_ndb_ddl.result:
Updated expected results
mysql-test/t/disabled.def:
Reenable rpl_ndb_ddl because of bug fix.
Disable rpl_ddl because of Bug#26418.
mysql-test/t/rpl_ddl.test:
Improvements
mysql-test/t/rpl_ndb_ddl.test:
Improvements
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_ddl.test | 395 | ||||
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_ndb_ddl.test | 507 | ||||
| -rw-r--r-- | mysql-test/include/rpl_stmt_seq.inc | 114 | ||||
| -rw-r--r-- | mysql-test/include/rpl_stmt_seq2.inc | 201 | ||||
| -rw-r--r-- | mysql-test/r/rpl_ddl.result | 445 | ||||
| -rw-r--r-- | mysql-test/r/rpl_ndb_ddl.result | 434 | ||||
| -rw-r--r-- | mysql-test/t/disabled.def | 2 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ddl.test | 12 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ndb_ddl.test | 17 |
9 files changed, 693 insertions, 1434 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_ddl.test b/mysql-test/extra/rpl_tests/rpl_ddl.test index 15794e5e035..e40532f005f 100644 --- a/mysql-test/extra/rpl_tests/rpl_ddl.test +++ b/mysql-test/extra/rpl_tests/rpl_ddl.test @@ -1,31 +1,128 @@ -######################## rpl_ddl.test ######################## -# # -# DDL statements (sometimes with implicit COMMIT) executed # -# by the master and it's propagation into the slave # -# # -############################################################## - +################# extra/rpl_tests/rpl_ddl.test ######################## +# # +# DDL statements (sometimes with implicit COMMIT) and other stuff # +# executed on the master and it's propagation into the slave. # +# # +# The variables # +# $engine_type -- storage engine to be tested/used for the # +# permanent tables within the master # +# $temp_engine_type -- storage engine which supports TEMPORARY # +# tables <> $engine_type # +# $temp_engine_type must point to an all # +# time available storage engine # +# 2007-02 MySQL 5.1 MyISAM and MEMORY only # +# $show_binlog -- print binlog entries # +# 0 - no (default) + fits to the file with # +# results # +# 1 - yes (important for debugging) # +# This variable is used within # +# include/rpl_stmt_seq.inc. # +# $manipulate -- Manipulation of the binary logs # +# 0 - do nothing # +# 1 - so that the output of SHOW BINLOG # +# EVENTS IN <current log> contains only # +# commands of the current test sequence # +# This is especially useful, if the # +# $show_binlog is set to 1 and many # +# subtest are executed. # +# This variable is used within # +# include/rpl_stmt_seq.inc. # +# have to be set before sourcing this script. # +# # +# General assumption about the ideal replication behaviour: # +# Whatever on the master is executed the content of the slave must # +# be in sync with it. # +# # +# Tests of special interest: # +# a) Which DDL commands cause an implicit COMMIT ? # +# This is also of interest outside of replication. # +# b) Transactions modifying table content ending with # +# - explicit COMMIT or ROLLBACK # +# - implicit COMMIT because the connection to the master # +# executed a corresponding DDL statement or runs in # +# AUTOCOMMIT mode # +# - something similar to "implicit COMMIT" if the storage # +# engine (master) is not transactional # +# c) Command which change no data like SELECT or SHOW # +# They do not change anything within the master but # +# this must be also valid for the slave. # +# # +####################################################################### + +# Last update: +# 2007-02-12 ML: - slave needs AUTOCOMMIT = 1, because we want to check only +# the propagation of actions of the master connection. +# - replace comments via SQL by "--echo ..." +# - remove some bugs within the testscripts +# - remove the use of include/rpl_stmt_seq2.inc +# +# +# NOTES: +# 2006-11-15 Lars: Matthias (ML) is the "owner" of this test case. +# So, please get him to review it whenever you want to +# do changes to it. +# +# PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !! # -# NOTE, PLEASE BE CAREFUL, WHEN MODIFYING THE TESTS !! +# Typical test architecture (--> include/rpl_stmt_seq.inc) +# -------------------------------------------------------- +# 1. Master (no AUTOCOMMIT!): INSERT INTO mysqltest1.t1 without commit +# 2. Master and slave: Check the content of mysqltest1.t1 +# 3. Master (no AUTOCOMMIT!): EXECUTE the statement to be tested +# 4. Master and slave: Check the content of mysqltest1.t1 +# 5. Master (no AUTOCOMMIT!): ROLLBACK +# 6. Master and slave: Check the content of mysqltest1.t1 +# If the previous into mysqltest1.t1 inserted row is visible, +# than the statement to be tested caused an explicit COMMIT +# (statement = COMMIT) or an implicit COMMIT (example CREATE TABLE). +# If the previous into mysqltest1.t1 inserted row is not visible, +# than the statement to be tested caused either an explicit ROLLBACK +# (statement = ROLLBACK), an implicit ROLLBACK (deadlock etc. but +# not tested here) or it does not cause any transaction end. +# 7. Flush the logs +# +# Some rules: +# ----------- +# 1. Any use of mysqltest1.t1 within the statement to be tested must be +# avoided if possible. The only known exception is around LOCK TABLE. +# +# 2. The test logics needs for +# master connection: AUTOCOMMIT = 0 +# slave connection: AUTOCOMMIT = 1 +# The master connection is the actor and the slave connection is +# only an observer. I.e. the slave connection must not influence +# the activities of master connection. # -# 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. +# 3. !All! objects to be dropped, renamed, altered ... must be created +# before the tests start. +# --> less switching of AUTOCOMMIT mode on master side. # -# 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. +# 4. Never use a test object, which was direct or indirect affected by a +# preceeding test sequence again. +# If one preceeding test sequence hits a (sometimes not 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.. This means during analysis the first look +# points into a totally wrong area. +# Except table mysqltest1.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.. +# 5. This file is used in several tests (t/rpl_ddl_<whatever>.test). +# Please be aware that every change of the current file affects +# the results of these tests. # -# 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 ""; +# ML: Some maybe banal hints: +# 1. The fact that we have here a master - slave replication does +# not cause that many general MySQL properties do not apply. +# Example: +# The connection to the slave is just a simple session and not a however +# magic working "copy" of the master session or something similar. +# - TEMPORARY TABLES and @variables are session specific +# - the slave session cannot see these things of the master. +# 2. The slave connection must not call sync_slave_with_master. +# 3. SHOW STATUS SLAVE must be run within the slave connection. +# 4. Testcase analysis becomes much more comfortable if +# $show_binlog within include/rpl_stmt_seq.inc is set to 1. # ############################################################### @@ -33,8 +130,10 @@ ############################################################### # The sync_slave_with_master is needed to make the xids deterministic. sync_slave_with_master; -connection master; +--echo +--echo -------- switch to master ------- +connection master; SET AUTOCOMMIT = 1; # # 1. DROP all objects, which probably already exist, but must be created here @@ -47,7 +146,7 @@ DROP DATABASE IF EXISTS mysqltest3; # # 2. CREATE all objects needed # working database is mysqltest1 -# working (transactional!) is mysqltest1.t1 +# working table (transactional!) is mysqltest1.t1 # CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; @@ -73,25 +172,23 @@ eval CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE=$engine_type; eval CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE=$engine_type; eval CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE=$engine_type; eval CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE=$engine_type; -CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT); +eval CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) ENGINE=$temp_engine_type; # # 3. master sessions: never do AUTOCOMMIT -# slave sessions: never do AUTOCOMMIT +# slave sessions: do AUTOCOMMIT # SET AUTOCOMMIT = 0; use mysqltest1; sync_slave_with_master; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SET AUTOCOMMIT = 0; +SET AUTOCOMMIT = 1; use mysqltest1; +--echo +--echo -------- switch to master ------- 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 @@ -100,6 +197,21 @@ SELECT '-------- switch to master -------' as ""; --disable_abort_on_error ############################################################### +# Banal case: commands which should never commit +# Just for checking if the test sequence is usable +############################################################### + +let $my_stmt= SELECT 1; +let $my_master_commit= false; +let $my_slave_commit= false; +--source include/rpl_stmt_seq.inc + +let $my_stmt= SELECT COUNT(*) FROM t1; +let $my_master_commit= false; +let $my_slave_commit= false; +--source include/rpl_stmt_seq.inc + +############################################################### # Banal case: (explicit) COMMIT and ROLLBACK # Just for checking if the test sequence is usable ############################################################### @@ -143,84 +255,64 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW TABLES LIKE 't2'; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW TABLES LIKE 't2'; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -# Note: Since this test is executed with a skip-innodb slave, the -# slave incorrectly commits the insert. One can *not* have InnoDB on -# master and MyISAM on slave and expect that a transactional rollback -# after a CREATE TEMPORARY TABLE should work correctly on the slave. -# For this to work properly the handler on the slave must be able to -# handle transactions (e.g. InnoDB or NDB). + let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23; let $my_master_commit= false; -let $my_slave_commit= true; +let $my_slave_commit= false; --source include/rpl_stmt_seq.inc SHOW TABLES LIKE 't23'; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW TABLES LIKE 't23'; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20; let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW TABLES LIKE 't20'; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW TABLES LIKE 't20'; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log let $my_stmt= ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT; let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc describe mysqltest1.t4; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log describe mysqltest1.t4; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log -let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE=; +let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= $engine_type; let $my_master_commit= true; let $my_slave_commit= true; ---source include/rpl_stmt_seq2.inc - -# Note: Since this test is executed with a skip-innodb slave, the -# slave incorrectly commits the insert. One can *not* have InnoDB on -# master and MyISAM on slave and expect that a transactional rollback -# after a CREATE TEMPORARY TABLE should work correctly on the slave. -# For this to work properly the handler on the slave must be able to -# handle transactions (e.g. InnoDB or NDB). +--source include/rpl_stmt_seq.inc + let $engine=''; let $eng_type=''; -let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT); +let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=$temp_engine_type; let $my_master_commit= false; -let $my_slave_commit= true; +let $my_slave_commit= false; --source include/rpl_stmt_seq.inc let $my_stmt= TRUNCATE TABLE mysqltest1.t7; @@ -228,9 +320,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SELECT * FROM mysqltest1.t7; ---echo -------- switch to slave -------- sync_slave_with_master; +--echo +--echo -------- switch to slave -------- +connection slave; SELECT * FROM mysqltest1.t7; +--echo --echo -------- switch to master ------- connection master; @@ -238,8 +333,13 @@ connection master; # Cases with LOCK/UNLOCK ############################################################### -# MySQL insists in locking mysqltest1.t1, because rpl_stmt_seq performs an -# INSERT into this table. +# Attention: +# We have to LOCK mysqltest1.t1 here, though it violates the testing +# philosophy. +# Mysql response in case without previous LOCK TABLES mysqltest1.t1 +# is: +# SELECT MAX(...) FROM mysqltest1.t1 is +# ERROR HY000: Table 't1' was not locked with LOCK TABLES let $my_stmt= LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; let $my_master_commit= true; let $my_slave_commit= true; @@ -253,8 +353,9 @@ let $my_slave_commit= false; --source include/rpl_stmt_seq.inc # With prior read locking -# Note that this test generate an error since the rpl_stmt_seq.inc -# tries to insert into t1. +# Attention: +# This subtest generates an error since the rpl_stmt_seq.inc +# tries to insert into t1. LOCK TABLES mysqltest1.t1 READ; let $my_stmt= UNLOCK TABLES; let $my_master_commit= false; @@ -277,30 +378,26 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW INDEX FROM mysqltest1.t6; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW INDEX FROM mysqltest1.t6; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log let $my_stmt= CREATE INDEX my_idx5 ON mysqltest1.t5(f1); let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW INDEX FROM mysqltest1.t5; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW INDEX FROM mysqltest1.t5; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log ############################################################### # Cases with DATABASE @@ -311,35 +408,31 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW DATABASES LIKE "mysqltest2"; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW DATABASES LIKE "mysqltest2"; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log let $my_stmt= CREATE DATABASE mysqltest3; let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW DATABASES LIKE "mysqltest3"; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log SHOW DATABASES LIKE "mysqltest3"; +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log # End of 4.1 tests ############################################################### -# Cases with stored procedures +# Cases with STORED PROCEDUREs ############################################################### let $my_stmt= CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1"; let $my_master_commit= true; @@ -348,12 +441,13 @@ let $my_slave_commit= true; --vertical_results --replace_column 5 # 6 # SHOW PROCEDURE STATUS LIKE 'p1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; --replace_column 5 # 6 # SHOW PROCEDURE STATUS LIKE 'p1'; +--echo +--echo -------- switch to master ------- connection master; --horizontal_results @@ -364,12 +458,13 @@ let $my_slave_commit= true; --vertical_results --replace_column 5 # 6 # SHOW PROCEDURE STATUS LIKE 'p1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; --replace_column 5 # 6 # SHOW PROCEDURE STATUS LIKE 'p1'; +--echo +--echo -------- switch to master ------- connection master; --horizontal_results @@ -379,11 +474,12 @@ let $my_slave_commit= true; --source include/rpl_stmt_seq.inc --vertical_results SHOW PROCEDURE STATUS LIKE 'p1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SHOW PROCEDURE STATUS LIKE 'p1'; +--echo +--echo -------- switch to master ------- connection master; --horizontal_results @@ -395,11 +491,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW CREATE VIEW v1; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SHOW CREATE VIEW v1; +--echo +--echo -------- switch to master ------- connection master; let $my_stmt= ALTER VIEW v1 AS select f1 from t1; @@ -407,11 +504,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW CREATE VIEW v1; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SHOW CREATE VIEW v1; +--echo +--echo -------- switch to master ------- connection master; let $my_stmt= DROP VIEW IF EXISTS v1; @@ -420,12 +518,13 @@ let $my_slave_commit= true; --source include/rpl_stmt_seq.inc --error 1146 SHOW CREATE VIEW v1; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; --error 1146 SHOW CREATE VIEW v1; +--echo +--echo -------- switch to master ------- connection master; ############################################################### @@ -436,11 +535,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW TRIGGERS; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SHOW TRIGGERS; +--echo +--echo -------- switch to master ------- connection master; let $my_stmt= DROP TRIGGER trg1; @@ -448,11 +548,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SHOW TRIGGERS; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SHOW TRIGGERS; +--echo +--echo -------- switch to master ------- connection master; ############################################################### @@ -463,11 +564,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SELECT user FROM mysql.user WHERE user = 'user1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SELECT user FROM mysql.user WHERE user = 'user1'; +--echo +--echo -------- switch to master ------- connection master; let $my_stmt= RENAME USER user1@localhost TO rename1@localhost; @@ -475,11 +577,12 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SELECT user FROM mysql.user WHERE user = 'rename1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SELECT user FROM mysql.user WHERE user = 'rename1'; +--echo +--echo -------- switch to master ------- connection master; let $my_stmt= DROP USER rename1@localhost; @@ -487,21 +590,21 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SELECT user FROM mysql.user WHERE user = 'rename1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log +--echo +--echo -------- switch to slave -------- connection slave; SELECT user FROM mysql.user WHERE user = 'rename1'; -connection master; ############################################################### # Cleanup ############################################################### ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; +use test; +--echo +--echo -------- switch to master ------- +connection master; +DROP DATABASE mysqltest1; +# mysqltest2 was alreday DROPPED some tests before. +DROP DATABASE mysqltest3; --enable_warnings -- source include/master-slave-end.inc - diff --git a/mysql-test/extra/rpl_tests/rpl_ndb_ddl.test b/mysql-test/extra/rpl_tests/rpl_ndb_ddl.test deleted file mode 100644 index 26c368589ba..00000000000 --- a/mysql-test/extra/rpl_tests/rpl_ndb_ddl.test +++ /dev/null @@ -1,507 +0,0 @@ -######################## 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 ""; -# - -############################################################### -# Some preparations -############################################################### -# The sync_slave_with_master is needed to make the xids deterministic. -sync_slave_with_master; -connection master; - -SET AUTOCOMMIT = 1; -# -# 1. DROP all objects, which probably already exist, but must be created here -# ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; ---enable_warnings -# -# 2. CREATE all objects needed -# working database is mysqltest1 -# working (transactional!) is mysqltest1.t1 -# -CREATE DATABASE mysqltest1; -CREATE DATABASE mysqltest2; -eval CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=$engine_type; -INSERT INTO mysqltest1.t1 SET f1= 0; -eval CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE=$engine_type; -CREATE INDEX my_idx6 ON mysqltest1.t6(f1); -eval CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE=$engine_type; -INSERT INTO mysqltest1.t7 SET f1= 0; -eval CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE=$engine_type; -eval CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE=$engine_type; -CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT); - -# -# 3. master sessions: never do AUTOCOMMIT -# slave sessions: never do AUTOCOMMIT -# -SET AUTOCOMMIT = 0; -use mysqltest1; -sync_slave_with_master; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SET AUTOCOMMIT = 0; -use mysqltest1; -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; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc - -let $my_stmt= ROLLBACK; -let $my_master_commit= false; -let $my_slave_commit= false; ---source include/rpl_stmt_seq.inc - -############################################################### -# Cases with commands very similar to COMMIT -############################################################### - -let $my_stmt= SET AUTOCOMMIT=1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SET AUTOCOMMIT=0; - -let $my_stmt= START TRANSACTION; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc - -let $my_stmt= BEGIN; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc - -############################################################### -# Cases with (BASE) TABLES and (UPDATABLE) VIEWs -############################################################### - -let $my_stmt= DROP TABLE mysqltest1.t2; -let $my_master_commit= true; -let $my_slave_commit= true; ---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 - -# Note: Since this test is executed with a skip-innodb slave, the -# slave incorrectly commits the insert. One can *not* have InnoDB on -# master and MyISAM on slave and expect that a transactional rollback -# after a CREATE TEMPORARY TABLE should work correctly on the slave. -# For this to work properly the handler on the slave must be able to -# handle transactions (e.g. InnoDB or NDB). -let $my_stmt= DROP TEMPORARY TABLE mysqltest1.t23; -let $my_master_commit= false; -let $my_slave_commit= false; ---source include/rpl_stmt_seq.inc -SHOW TABLES LIKE 't23'; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SHOW TABLES LIKE 't23'; -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -let $my_stmt= RENAME TABLE mysqltest1.t3 to mysqltest1.t20; -let $my_master_commit= true; -let $my_slave_commit= true; ---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 mysqltest1.t4 ADD column f2 BIGINT; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -describe mysqltest1.t4; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -describe mysqltest1.t4; -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -let $my_stmt= CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE=; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq2.inc - -# Note: Since this test is executed with a skip-innodb slave, the -# slave incorrectly commits the insert. One can *not* have InnoDB on -# master and MyISAM on slave and expect that a transactional rollback -# after a CREATE TEMPORARY TABLE should work correctly on the slave. -# For this to work properly the handler on the slave must be able to -# handle transactions (e.g. InnoDB or NDB). -let $engine=''; -let $eng_type=''; - -let $my_stmt= CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT); -let $my_master_commit= false; -let $my_slave_commit= false; ---source include/rpl_stmt_seq.inc - -let $my_stmt= TRUNCATE TABLE mysqltest1.t7; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SELECT * FROM mysqltest1.t7; ---echo -------- switch to slave -------- -sync_slave_with_master; -SELECT * FROM mysqltest1.t7; ---echo -------- switch to master ------- -connection master; - -############################################################### -# Cases with LOCK/UNLOCK -############################################################### - -# MySQL insists in locking mysqltest1.t1, because rpl_stmt_seq performs an -# INSERT into this table. -let $my_stmt= LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -UNLOCK TABLES; - -# No prior locking -let $my_stmt= UNLOCK TABLES; -let $my_master_commit= false; -let $my_slave_commit= false; ---source include/rpl_stmt_seq.inc - -# With prior read locking -# Note that this test generate an error since the rpl_stmt_seq.inc -# tries to insert into t1. -LOCK TABLES mysqltest1.t1 READ; -let $my_stmt= UNLOCK TABLES; -let $my_master_commit= false; -let $my_slave_commit= false; ---source include/rpl_stmt_seq.inc - -# With prior write locking -LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; -let $my_stmt= UNLOCK TABLES; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc - -############################################################### -# Cases with INDEXES -############################################################### - -let $my_stmt= DROP INDEX my_idx6 ON mysqltest1.t6; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW INDEX FROM mysqltest1.t6; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SHOW INDEX FROM mysqltest1.t6; -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -let $my_stmt= CREATE INDEX my_idx5 ON mysqltest1.t5(f1); -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW INDEX FROM mysqltest1.t5; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SHOW INDEX FROM mysqltest1.t5; -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -############################################################### -# Cases with DATABASE -############################################################### - -let $my_stmt= DROP DATABASE mysqltest2; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW DATABASES LIKE "mysqltest2"; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SHOW DATABASES LIKE "mysqltest2"; -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -let $my_stmt= CREATE DATABASE mysqltest3; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW DATABASES LIKE "mysqltest3"; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -SHOW DATABASES LIKE "mysqltest3"; -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log - -# End of 4.1 tests - -############################################################### -# Cases with stored procedures -############################################################### -let $my_stmt= CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1"; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc ---vertical_results ---replace_column 5 # 6 # -SHOW PROCEDURE STATUS LIKE 'p1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; ---replace_column 5 # 6 # -SHOW PROCEDURE STATUS LIKE 'p1'; -connection master; ---horizontal_results - -let $my_stmt= ALTER PROCEDURE p1 COMMENT "I have been altered"; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc ---vertical_results ---replace_column 5 # 6 # -SHOW PROCEDURE STATUS LIKE 'p1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; ---replace_column 5 # 6 # -SHOW PROCEDURE STATUS LIKE 'p1'; -connection master; ---horizontal_results - -let $my_stmt= DROP PROCEDURE p1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc ---vertical_results -SHOW PROCEDURE STATUS LIKE 'p1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SHOW PROCEDURE STATUS LIKE 'p1'; -connection master; ---horizontal_results - -############################################################### -# Cases with VIEWs -############################################################### -let $my_stmt= CREATE OR REPLACE VIEW v1 as select * from t1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW CREATE VIEW v1; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SHOW CREATE VIEW v1; -connection master; - -let $my_stmt= ALTER VIEW v1 AS select f1 from t1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW CREATE VIEW v1; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SHOW CREATE VIEW v1; -connection master; - -let $my_stmt= DROP VIEW IF EXISTS v1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc ---error 1146 -SHOW CREATE VIEW v1; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; ---error 1146 -SHOW CREATE VIEW v1; -connection master; - -############################################################### -# Cases with TRIGGERs -############################################################### -let $my_stmt= CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW TRIGGERS; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SHOW TRIGGERS; -connection master; - -let $my_stmt= DROP TRIGGER trg1; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SHOW TRIGGERS; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SHOW TRIGGERS; -connection master; - -############################################################### -# Cases with USERs -############################################################### -let $my_stmt= CREATE USER user1@localhost; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SELECT user FROM mysql.user WHERE user = 'user1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SELECT user FROM mysql.user WHERE user = 'user1'; -connection master; - -let $my_stmt= RENAME USER user1@localhost TO rename1@localhost; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SELECT user FROM mysql.user WHERE user = 'rename1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SELECT user FROM mysql.user WHERE user = 'rename1'; -connection master; - -let $my_stmt= DROP USER rename1@localhost; -let $my_master_commit= true; -let $my_slave_commit= true; ---source include/rpl_stmt_seq.inc -SELECT user FROM mysql.user WHERE user = 'rename1'; ---disable_query_log -SELECT '-------- switch to slave -------' as ""; ---enable_query_log -connection slave; -SELECT user FROM mysql.user WHERE user = 'rename1'; -connection master; - -############################################################### -# Cleanup -############################################################### ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; ---enable_warnings - --- source include/master-slave-end.inc - diff --git a/mysql-test/include/rpl_stmt_seq.inc b/mysql-test/include/rpl_stmt_seq.inc index 3c91505d0d6..6c944dc4729 100644 --- a/mysql-test/include/rpl_stmt_seq.inc +++ b/mysql-test/include/rpl_stmt_seq.inc @@ -1,30 +1,51 @@ -# include/rpl_stmt_seq.inc -# -# Please be very careful when editing this routine, because the handling of -# the $variables is extreme sensitive. -# +################### include/rpl_stmt_seq.inc ########################### +# # +# Check if a given SQL statement (->$my_stmt) / AUTOCOMMIT mode / # +# storage engine somehow involved causes COMMIT or ROLLBACK. # +# # +# # +# The typical test sequence # +# ------------------------- # +# 1. master connection: INSERT without commit # +# check table content of master and slave # +# 2. master connection: EXECUTE the statement # +# check table content of master and slave # +# 3. master connection: ROLLBACK # +# check table content of master and slave # +# 4. flush the logs # +# # +# The variables # +# $show_binlog -- print binlog entries # +# 0 - default + fits to the file with # +# results # +# 1 - useful for debugging # +# This variable is used within # +# include/rpl_stmt_seq.inc. # +# $manipulate -- Manipulation of the binary logs # +# 0 - do nothing # +# 1 - so that the output of SHOW BINLOG # +# EVENTS IN <current log> contains only # +# commands of the current test sequence # +# This is especially useful, if the # +# $show_binlog is set to 1 and many # +# subtest are executed. # +# This variable is used within # +# include/rpl_stmt_seq.inc. # +# have to be set before sourcing this script. # +# # +# Please be very careful when editing this routine, because the # +# handling of the $variables is extreme sensitive. # +# # +######################################################################## -############################################################### -# Debug options : To debug this test script -############################################################### -let $show_binlog= 0; -let $manipulate= 1; - -######## The typical test sequence -# 1. INSERT without commit -# check table content of master and slave -# 2. EXECUTE the statement -# check table content of master and slave -# 3. ROLLBACK -# check table content of master and slave -# 4. flush the logs +# Last update: +# 2007-02-12 ML Replace comments via SQL by "--echo ..." +# let $VERSION=`select version()`; ---disable_query_log -# SELECT '######## new test sequence ########' as ""; -eval SELECT CONCAT('######## ','$my_stmt',' ########') as ""; ---enable_query_log +--echo +--echo ######## $my_stmt ######## ############################################################### @@ -49,11 +70,10 @@ let $_log_num_s= `select @aux`; ############################################################### # INSERT ############################################################### +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log -# Maybe it would be smarter to use a table with autoincrement column. +# Maybe it would be smarter to use a table with an autoincrement column. let $MAX= `SELECT MAX(f1) FROM t1` ; eval INSERT INTO t1 SET f1= $MAX + 1; # results before DDL(to be tested) @@ -66,10 +86,9 @@ eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; } sync_slave_with_master; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log # results before DDL(to be tested) SELECT MAX(f1) FROM t1; if ($show_binlog) @@ -82,10 +101,9 @@ eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; ############################################################### # command to be tested ############################################################### +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log eval $my_stmt; # Devaluate $my_stmt, to detect script bugs let $my_stmt= ERROR: YOU FORGOT TO FILL IN THE STATEMENT; @@ -99,10 +117,9 @@ eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; } sync_slave_with_master; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log # results after DDL(to be tested) SELECT MAX(f1) FROM t1; if ($show_binlog) @@ -115,10 +132,9 @@ eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; ############################################################### # ROLLBACK ############################################################### +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log ROLLBACK; # results after final ROLLBACK SELECT MAX(f1) FROM t1; @@ -140,10 +156,9 @@ eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; } sync_slave_with_master; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log # results after final ROLLBACK SELECT MAX(f1) FROM t1; --disable_query_log @@ -172,19 +187,17 @@ if ($manipulate) # - flush the master and the slave log # ---> both start to write into new logs with incremented number # - increment $_log_num_n +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log flush logs; # sleep 1; # eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; sync_slave_with_master; +--echo +--echo -------- switch to slave -------- connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log # the final content of the binary log flush logs; # The next sleep is urgent needed. @@ -195,7 +208,6 @@ flush logs; inc $_log_num_n; } +--echo +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log diff --git a/mysql-test/include/rpl_stmt_seq2.inc b/mysql-test/include/rpl_stmt_seq2.inc deleted file mode 100644 index 7671a6a857c..00000000000 --- a/mysql-test/include/rpl_stmt_seq2.inc +++ /dev/null @@ -1,201 +0,0 @@ -# include/rpl_stmt_seq.inc -# -# Please be very careful when editing this routine, because the handling of -# the $variables is extreme sensitive. -# - -############################################################### -# Debug options : To debug this test script -############################################################### -let $show_binlog= 0; -let $manipulate= 1; - -######## The typical test sequence -# 1. INSERT without commit -# check table content of master and slave -# 2. EXECUTE the statement -# check table content of master and slave -# 3. ROLLBACK -# check table content of master and slave -# 4. flush the logs - -let $VERSION=`select version()`; - ---disable_query_log -# SELECT '######## new test sequence ########' as ""; -eval SELECT CONCAT('######## ','$my_stmt',' $engine_type',' ########') as ""; ---enable_query_log - - -############################################################### -# Predict the number of the current log -############################################################### -# Disable the logging of the log number computation. ---disable_query_log -# $_log_num_n should contain the number of the current binlog in numeric style. -# If this routine is called for the first time, $_log_num will not initialized -# and contain the value '' instead of '1'. So we will correct it here. -# -eval set @aux= IF('$_log_num_n' = '', '1', '$_log_num_n'); -let $_log_num_n= `SELECT @aux`; -eval set @aux= LPAD('$_log_num_n',6,'0'); -# SELECT @aux AS "@aux is"; -# -# $_log_num_s should contain the number of the current binlog in string style. -let $_log_num_s= `select @aux`; -# eval SELECT '$log_num' ; ---enable_query_log - -############################################################### -# INSERT -############################################################### -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log -# Maybe it would be smarter to use a table with autoincrement column. -let $MAX= `SELECT MAX(f1) FROM t1` ; -eval INSERT INTO t1 SET f1= $MAX + 1; -# results before DDL(to be tested) -SELECT MAX(f1) FROM t1; -if ($show_binlog) -{ ---replace_result $VERSION VERSION ---replace_column 2 # 5 # -eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; -} -sync_slave_with_master; - -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -# results before DDL(to be tested) -SELECT MAX(f1) FROM t1; -if ($show_binlog) -{ ---replace_result $VERSION VERSION ---replace_column 2 # 5 # -eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; -} - -############################################################### -# command to be tested -############################################################### -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log -eval $my_stmt $engine_type; -# Devaluate $my_stmt, to detect script bugs -let $my_stmt= ERROR: YOU FORGOT TO FILL IN THE STATEMENT; -# results after DDL(to be tested) -SELECT MAX(f1) FROM t1; -if ($show_binlog) -{ ---replace_result $VERSION VERSION ---replace_column 2 # 5 # -eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; -} -sync_slave_with_master; - -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -# results after DDL(to be tested) -SELECT MAX(f1) FROM t1; -if ($show_binlog) -{ ---replace_result $VERSION VERSION ---replace_column 2 # 5 # -eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; -} - -############################################################### -# ROLLBACK -############################################################### -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log -ROLLBACK; -# results after final ROLLBACK -SELECT MAX(f1) FROM t1; -# Try to detect if the DDL command caused that the INSERT is committed -# $MAX holds the highest/last value just before the insert of MAX + 1 ---disable_query_log -eval SELECT CONCAT(CONCAT('TEST-INFO: MASTER: The INSERT is ', - IF(MAX(f1) = $MAX + 1, 'committed', 'not committed')), - IF((MAX(f1) = $MAX + 1) XOR NOT $my_master_commit, - ' (Succeeded)', - ' (Failed)')) AS "" - FROM mysqltest1.t1; ---enable_query_log -if ($show_binlog) -{ ---replace_result $VERSION VERSION ---replace_column 2 # 5 # -eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; -} -sync_slave_with_master; - -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -# results after final ROLLBACK -SELECT MAX(f1) FROM t1; ---disable_query_log -eval SELECT CONCAT(CONCAT('TEST-INFO: SLAVE: The INSERT is ', - IF(MAX(f1) = $MAX + 1, 'committed', 'not committed')), - IF((MAX(f1) = $MAX + 1) XOR NOT $my_slave_commit, - ' (Succeeded)', - ' (Failed)')) AS "" - FROM mysqltest1.t1; ---enable_query_log -if ($show_binlog) -{ ---replace_result $VERSION VERSION ---replace_column 2 # 5 # -eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; -} - -############################################################### -# Manipulate binlog -############################################################### -if ($manipulate) -{ -#### Manipulate the binary logs, -# so that the output of SHOW BINLOG EVENTS IN <current log> -# contains only commands of the current test sequence. -# - flush the master and the slave log -# ---> both start to write into new logs with incremented number -# - increment $_log_num_n -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log -flush logs; -# sleep 1; -# eval SHOW BINLOG EVENTS IN 'master-bin.$_log_num_s'; -sync_slave_with_master; - -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log -# the final content of the binary log -flush logs; -# The next sleep is urgent needed. -# Without this sleep the slaves crashes often, when the SHOW BINLOG -# is executed. :-( -# sleep 1; -# eval SHOW BINLOG EVENTS IN 'slave-bin.$_log_num_s'; -inc $_log_num_n; -} - -connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result index ace86532b12..d41462de621 100644 --- a/mysql-test/r/rpl_ddl.result +++ b/mysql-test/r/rpl_ddl.result @@ -4,45 +4,47 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; + +-------- switch to master ------- SET AUTOCOMMIT = 1; DROP DATABASE IF EXISTS mysqltest1; DROP DATABASE IF EXISTS mysqltest2; DROP DATABASE IF EXISTS mysqltest3; CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; -CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB"; +CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=InnoDB; INSERT INTO mysqltest1.t1 SET f1= 0; -CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE="InnoDB"; +CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE=InnoDB; CREATE INDEX my_idx6 ON mysqltest1.t6(f1); -CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE="InnoDB"; +CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE=InnoDB; INSERT INTO mysqltest1.t7 SET f1= 0; -CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE="InnoDB"; -CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT); +CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE=InnoDB; +CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) ENGINE=MEMORY; SET AUTOCOMMIT = 0; use mysqltest1; -------- switch to slave -------- -SET AUTOCOMMIT = 0; +SET AUTOCOMMIT = 1; use mysqltest1; -------- switch to master ------- -######## COMMIT ######## +######## SELECT 1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 0 + 1; @@ -56,7 +58,9 @@ MAX(f1) 0 -------- switch to master ------- -COMMIT; +SELECT 1; +1 +1 SELECT MAX(f1) FROM t1; MAX(f1) 1 @@ -64,32 +68,110 @@ MAX(f1) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) -1 +0 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) +0 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) + +-------- switch to master ------- + +######## SELECT COUNT(*) FROM t1 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 0 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) 1 -TEST-INFO: MASTER: The INSERT is committed (Succeeded) +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +-------- switch to master ------- +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) +0 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) + +-------- switch to master ------- + +######## COMMIT ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 0 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) 1 -TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +-------- switch to master ------- +COMMIT; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 -------- switch to master ------- -flush logs; +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- -flush logs; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -######## ROLLBACK ######## +######## ROLLBACK ######## -------- switch to master ------- INSERT INTO t1 SET f1= 1 + 1; @@ -129,14 +211,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; --------- switch to slave -------- -flush logs; - --------- switch to master ------- - -######## SET AUTOCOMMIT=1 ######## +######## SET AUTOCOMMIT=1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 1 + 1; @@ -176,15 +252,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SET AUTOCOMMIT=0; -######## START TRANSACTION ######## +######## START TRANSACTION ######## -------- switch to master ------- INSERT INTO t1 SET f1= 2 + 1; @@ -224,14 +294,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; --------- switch to slave -------- -flush logs; - --------- switch to master ------- - -######## BEGIN ######## +######## BEGIN ######## -------- switch to master ------- INSERT INTO t1 SET f1= 3 + 1; @@ -271,14 +335,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- -######## DROP TABLE mysqltest1.t2 ######## +######## DROP TABLE mysqltest1.t2 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 4 + 1; @@ -318,12 +376,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TABLES LIKE 't2'; Tables_in_mysqltest1 (t2) @@ -333,7 +385,7 @@ Tables_in_mysqltest1 (t2) -------- switch to master ------- -######## DROP TEMPORARY TABLE mysqltest1.t23 ######## +######## DROP TEMPORARY TABLE mysqltest1.t23 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 5 + 1; @@ -368,15 +420,9 @@ TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) -6 - -TEST-INFO: SLAVE: The INSERT is committed (Succeeded) - --------- switch to master ------- -flush logs; +5 --------- switch to slave -------- -flush logs; +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- SHOW TABLES LIKE 't23'; @@ -388,7 +434,7 @@ Tables_in_mysqltest1 (t23) -------- switch to master ------- -######## RENAME TABLE mysqltest1.t3 to mysqltest1.t20 ######## +######## RENAME TABLE mysqltest1.t3 to mysqltest1.t20 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 5 + 1; @@ -399,7 +445,7 @@ MAX(f1) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) -6 +5 -------- switch to master ------- RENAME TABLE mysqltest1.t3 to mysqltest1.t20; @@ -428,12 +474,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TABLES LIKE 't20'; Tables_in_mysqltest1 (t20) t20 @@ -445,7 +485,7 @@ t20 -------- switch to master ------- -######## ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT ######## +######## ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT ######## -------- switch to master ------- INSERT INTO t1 SET f1= 6 + 1; @@ -485,12 +525,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- describe mysqltest1.t4; Field Type Null Key Default Extra f1 bigint(20) YES NULL @@ -504,7 +538,7 @@ f2 bigint(20) YES NULL -------- switch to master ------- -######## CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB" ######## +######## CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= InnoDB ######## -------- switch to master ------- INSERT INTO t1 SET f1= 7 + 1; @@ -518,7 +552,7 @@ MAX(f1) 7 -------- switch to master ------- -CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "InnoDB"; +CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= InnoDB; SELECT MAX(f1) FROM t1; MAX(f1) 8 @@ -544,14 +578,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- -######## CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ######## +######## CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=MEMORY ######## -------- switch to master ------- INSERT INTO t1 SET f1= 8 + 1; @@ -565,7 +593,7 @@ MAX(f1) 8 -------- switch to master ------- -CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT); +CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=MEMORY; SELECT MAX(f1) FROM t1; MAX(f1) 9 @@ -586,19 +614,13 @@ TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) -9 - -TEST-INFO: SLAVE: The INSERT is committed (Succeeded) - --------- switch to master ------- -flush logs; +8 --------- switch to slave -------- -flush logs; +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -######## TRUNCATE TABLE mysqltest1.t7 ######## +######## TRUNCATE TABLE mysqltest1.t7 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 8 + 1; @@ -609,7 +631,7 @@ MAX(f1) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) -9 +8 -------- switch to master ------- TRUNCATE TABLE mysqltest1.t7; @@ -638,20 +660,16 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT * FROM mysqltest1.t7; f1 + -------- switch to slave -------- SELECT * FROM mysqltest1.t7; f1 + -------- switch to master ------- -######## LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ ######## +######## LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ ######## -------- switch to master ------- INSERT INTO t1 SET f1= 9 + 1; @@ -691,15 +709,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- UNLOCK TABLES; -######## UNLOCK TABLES ######## +######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; @@ -739,15 +751,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- LOCK TABLES mysqltest1.t1 READ; -######## UNLOCK TABLES ######## +######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; @@ -788,15 +794,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; -######## UNLOCK TABLES ######## +######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; @@ -836,14 +836,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; --------- switch to slave -------- -flush logs; - --------- switch to master ------- - -######## DROP INDEX my_idx6 ON mysqltest1.t6 ######## +######## DROP INDEX my_idx6 ON mysqltest1.t6 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 11 + 1; @@ -883,12 +877,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW INDEX FROM mysqltest1.t6; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment @@ -898,7 +886,7 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par -------- switch to master ------- -######## CREATE INDEX my_idx5 ON mysqltest1.t5(f1) ######## +######## CREATE INDEX my_idx5 ON mysqltest1.t5(f1) ######## -------- switch to master ------- INSERT INTO t1 SET f1= 12 + 1; @@ -938,12 +926,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW INDEX FROM mysqltest1.t5; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t5 1 my_idx5 1 f1 A 0 NULL NULL YES BTREE @@ -955,7 +937,7 @@ t5 1 my_idx5 1 f1 A NULL NULL NULL YES BTREE -------- switch to master ------- -######## DROP DATABASE mysqltest2 ######## +######## DROP DATABASE mysqltest2 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 13 + 1; @@ -995,12 +977,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW DATABASES LIKE "mysqltest2"; Database (mysqltest2) @@ -1010,7 +986,7 @@ Database (mysqltest2) -------- switch to master ------- -######## CREATE DATABASE mysqltest3 ######## +######## CREATE DATABASE mysqltest3 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 14 + 1; @@ -1050,12 +1026,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW DATABASES LIKE "mysqltest3"; Database (mysqltest3) mysqltest3 @@ -1067,7 +1037,7 @@ mysqltest3 -------- switch to master ------- -######## CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1" ######## +######## CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1" ######## -------- switch to master ------- INSERT INTO t1 SET f1= 15 + 1; @@ -1107,12 +1077,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1122,7 +1086,8 @@ Modified # Created # Security_type DEFINER Comment - -------- switch to slave ------- + +-------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1133,7 +1098,9 @@ Created # Security_type DEFINER Comment -######## ALTER PROCEDURE p1 COMMENT "I have been altered" ######## +-------- switch to master ------- + +######## ALTER PROCEDURE p1 COMMENT "I have been altered" ######## -------- switch to master ------- INSERT INTO t1 SET f1= 16 + 1; @@ -1173,12 +1140,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1188,7 +1149,8 @@ Modified # Created # Security_type DEFINER Comment I have been altered - -------- switch to slave ------- + +-------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1199,7 +1161,9 @@ Created # Security_type DEFINER Comment I have been altered -######## DROP PROCEDURE p1 ######## +-------- switch to master ------- + +######## DROP PROCEDURE p1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 17 + 1; @@ -1239,17 +1203,14 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; +SHOW PROCEDURE STATUS LIKE 'p1'; -------- switch to slave -------- -flush logs; +SHOW PROCEDURE STATUS LIKE 'p1'; -------- switch to master ------- -SHOW PROCEDURE STATUS LIKE 'p1'; - -------- switch to slave ------- -SHOW PROCEDURE STATUS LIKE 'p1'; -######## CREATE OR REPLACE VIEW v1 as select * from t1 ######## +######## CREATE OR REPLACE VIEW v1 as select * from t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 18 + 1; @@ -1289,22 +1250,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` --------- switch to slave ------- +-------- switch to slave -------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` -######## ALTER VIEW v1 AS select f1 from t1 ######## +-------- switch to master ------- + +######## ALTER VIEW v1 AS select f1 from t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 19 + 1; @@ -1344,22 +1301,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` --------- switch to slave ------- +-------- switch to slave -------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` -######## DROP VIEW IF EXISTS v1 ######## +-------- switch to master ------- + +######## DROP VIEW IF EXISTS v1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 20 + 1; @@ -1399,20 +1352,16 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW CREATE VIEW v1; ERROR 42S02: Table 'mysqltest1.v1' doesn't exist --------- switch to slave ------- +-------- switch to slave -------- SHOW CREATE VIEW v1; ERROR 42S02: Table 'mysqltest1.v1' doesn't exist -######## CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ######## +-------- switch to master ------- + +######## CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 21 + 1; @@ -1452,22 +1401,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost --------- switch to slave ------- +-------- switch to slave -------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost -######## DROP TRIGGER trg1 ######## +-------- switch to master ------- + +######## DROP TRIGGER trg1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 22 + 1; @@ -1507,20 +1452,16 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer --------- switch to slave ------- +-------- switch to slave -------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer -######## CREATE USER user1@localhost ######## +-------- switch to master ------- + +######## CREATE USER user1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 23 + 1; @@ -1560,22 +1501,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'user1'; user user1 --------- switch to slave ------- +-------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'user1'; user user1 -######## RENAME USER user1@localhost TO rename1@localhost ######## +-------- switch to master ------- + +######## RENAME USER user1@localhost TO rename1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 24 + 1; @@ -1615,22 +1552,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'rename1'; user rename1 --------- switch to slave ------- +-------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'rename1'; user rename1 -######## DROP USER rename1@localhost ######## +-------- switch to master ------- + +######## DROP USER rename1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 25 + 1; @@ -1670,18 +1603,14 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'rename1'; user --------- switch to slave ------- +-------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'rename1'; user -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; +use test; + +-------- switch to master ------- +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest3; diff --git a/mysql-test/r/rpl_ndb_ddl.result b/mysql-test/r/rpl_ndb_ddl.result index f5d8073be94..aeaca1e7de0 100644 --- a/mysql-test/r/rpl_ndb_ddl.result +++ b/mysql-test/r/rpl_ndb_ddl.result @@ -4,45 +4,47 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; + +-------- switch to master ------- SET AUTOCOMMIT = 1; DROP DATABASE IF EXISTS mysqltest1; DROP DATABASE IF EXISTS mysqltest2; DROP DATABASE IF EXISTS mysqltest3; CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; -CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="NDB"; +CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=NDB; INSERT INTO mysqltest1.t1 SET f1= 0; -CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE="NDB"; +CREATE TABLE mysqltest1.t2 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t3 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t4 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t5 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t6 (f1 BIGINT) ENGINE=NDB; CREATE INDEX my_idx6 ON mysqltest1.t6(f1); -CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE="NDB"; +CREATE TABLE mysqltest1.t7 (f1 BIGINT) ENGINE=NDB; INSERT INTO mysqltest1.t7 SET f1= 0; -CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE="NDB"; -CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE="NDB"; -CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT); +CREATE TABLE mysqltest1.t8 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t9 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t10 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t11 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t12 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t13 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t14 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t15 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t16 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t17 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t18 (f1 BIGINT) ENGINE=NDB; +CREATE TABLE mysqltest1.t19 (f1 BIGINT) ENGINE=NDB; +CREATE TEMPORARY TABLE mysqltest1.t23 (f1 BIGINT) ENGINE=MEMORY; SET AUTOCOMMIT = 0; use mysqltest1; -------- switch to slave -------- -SET AUTOCOMMIT = 0; +SET AUTOCOMMIT = 1; use mysqltest1; -------- switch to master ------- -######## COMMIT ######## +######## SELECT 1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 0 + 1; @@ -56,7 +58,9 @@ MAX(f1) 0 -------- switch to master ------- -COMMIT; +SELECT 1; +1 +1 SELECT MAX(f1) FROM t1; MAX(f1) 1 @@ -64,32 +68,110 @@ MAX(f1) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) -1 +0 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) +0 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) + +-------- switch to master ------- + +######## SELECT COUNT(*) FROM t1 ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 0 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) 1 -TEST-INFO: MASTER: The INSERT is committed (Succeeded) +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +-------- switch to master ------- +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +SELECT MAX(f1) FROM t1; +MAX(f1) +1 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) +0 + +-------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +TEST-INFO: MASTER: The INSERT is not committed (Succeeded) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 + +TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) + +-------- switch to master ------- + +######## COMMIT ######## + +-------- switch to master ------- +INSERT INTO t1 SET f1= 0 + 1; +SELECT MAX(f1) FROM t1; +MAX(f1) 1 -TEST-INFO: SLAVE: The INSERT is committed (Succeeded) +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +0 -------- switch to master ------- -flush logs; +COMMIT; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 -------- switch to slave -------- -flush logs; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 -------- switch to master ------- +ROLLBACK; +SELECT MAX(f1) FROM t1; +MAX(f1) +1 -######## ROLLBACK ######## +TEST-INFO: MASTER: The INSERT is committed (Succeeded) + +-------- switch to slave -------- +SELECT MAX(f1) FROM t1; +MAX(f1) +1 + +TEST-INFO: SLAVE: The INSERT is committed (Succeeded) + +-------- switch to master ------- + +######## ROLLBACK ######## -------- switch to master ------- INSERT INTO t1 SET f1= 1 + 1; @@ -129,14 +211,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- -######## SET AUTOCOMMIT=1 ######## +######## SET AUTOCOMMIT=1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 1 + 1; @@ -176,15 +252,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SET AUTOCOMMIT=0; -######## START TRANSACTION ######## +######## START TRANSACTION ######## -------- switch to master ------- INSERT INTO t1 SET f1= 2 + 1; @@ -224,14 +294,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- -######## BEGIN ######## +######## BEGIN ######## -------- switch to master ------- INSERT INTO t1 SET f1= 3 + 1; @@ -271,14 +335,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; --------- switch to slave -------- -flush logs; - --------- switch to master ------- - -######## DROP TABLE mysqltest1.t2 ######## +######## DROP TABLE mysqltest1.t2 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 4 + 1; @@ -318,12 +376,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TABLES LIKE 't2'; Tables_in_mysqltest1 (t2) @@ -333,7 +385,7 @@ Tables_in_mysqltest1 (t2) -------- switch to master ------- -######## DROP TEMPORARY TABLE mysqltest1.t23 ######## +######## DROP TEMPORARY TABLE mysqltest1.t23 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 5 + 1; @@ -373,12 +425,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TABLES LIKE 't23'; Tables_in_mysqltest1 (t23) @@ -388,7 +434,7 @@ Tables_in_mysqltest1 (t23) -------- switch to master ------- -######## RENAME TABLE mysqltest1.t3 to mysqltest1.t20 ######## +######## RENAME TABLE mysqltest1.t3 to mysqltest1.t20 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 5 + 1; @@ -428,12 +474,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TABLES LIKE 't20'; Tables_in_mysqltest1 (t20) t20 @@ -445,7 +485,7 @@ t20 -------- switch to master ------- -######## ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT ######## +######## ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT ######## -------- switch to master ------- INSERT INTO t1 SET f1= 6 + 1; @@ -485,12 +525,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- describe mysqltest1.t4; Field Type Null Key Default Extra f1 bigint(20) YES NULL @@ -504,7 +538,7 @@ f2 bigint(20) YES NULL -------- switch to master ------- -######## CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "NDB" ######## +######## CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= NDB ######## -------- switch to master ------- INSERT INTO t1 SET f1= 7 + 1; @@ -518,7 +552,7 @@ MAX(f1) 7 -------- switch to master ------- -CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= "NDB"; +CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= NDB; SELECT MAX(f1) FROM t1; MAX(f1) 8 @@ -544,14 +578,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- -######## CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ######## +######## CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=MEMORY ######## -------- switch to master ------- INSERT INTO t1 SET f1= 8 + 1; @@ -565,7 +593,7 @@ MAX(f1) 8 -------- switch to master ------- -CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT); +CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=MEMORY; SELECT MAX(f1) FROM t1; MAX(f1) 9 @@ -591,14 +619,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- -######## TRUNCATE TABLE mysqltest1.t7 ######## +######## TRUNCATE TABLE mysqltest1.t7 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 8 + 1; @@ -638,20 +660,16 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT * FROM mysqltest1.t7; f1 + -------- switch to slave -------- SELECT * FROM mysqltest1.t7; f1 + -------- switch to master ------- -######## LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ ######## +######## LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ ######## -------- switch to master ------- INSERT INTO t1 SET f1= 9 + 1; @@ -691,15 +709,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- UNLOCK TABLES; -######## UNLOCK TABLES ######## +######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; @@ -739,15 +751,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- LOCK TABLES mysqltest1.t1 READ; -######## UNLOCK TABLES ######## +######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; @@ -788,15 +794,9 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; -######## UNLOCK TABLES ######## +######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; @@ -836,14 +836,8 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; --------- switch to slave -------- -flush logs; - --------- switch to master ------- - -######## DROP INDEX my_idx6 ON mysqltest1.t6 ######## +######## DROP INDEX my_idx6 ON mysqltest1.t6 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 11 + 1; @@ -883,12 +877,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW INDEX FROM mysqltest1.t6; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment @@ -898,7 +886,7 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par -------- switch to master ------- -######## CREATE INDEX my_idx5 ON mysqltest1.t5(f1) ######## +######## CREATE INDEX my_idx5 ON mysqltest1.t5(f1) ######## -------- switch to master ------- INSERT INTO t1 SET f1= 12 + 1; @@ -938,12 +926,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW INDEX FROM mysqltest1.t5; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t5 1 my_idx5 1 f1 A 0 NULL NULL YES BTREE @@ -955,7 +937,7 @@ t5 1 my_idx5 1 f1 A 0 NULL NULL YES BTREE -------- switch to master ------- -######## DROP DATABASE mysqltest2 ######## +######## DROP DATABASE mysqltest2 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 13 + 1; @@ -995,12 +977,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW DATABASES LIKE "mysqltest2"; Database (mysqltest2) @@ -1010,7 +986,7 @@ Database (mysqltest2) -------- switch to master ------- -######## CREATE DATABASE mysqltest3 ######## +######## CREATE DATABASE mysqltest3 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 14 + 1; @@ -1050,12 +1026,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW DATABASES LIKE "mysqltest3"; Database (mysqltest3) mysqltest3 @@ -1067,7 +1037,7 @@ mysqltest3 -------- switch to master ------- -######## CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1" ######## +######## CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1" ######## -------- switch to master ------- INSERT INTO t1 SET f1= 15 + 1; @@ -1107,12 +1077,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1122,7 +1086,8 @@ Modified # Created # Security_type DEFINER Comment - -------- switch to slave ------- + +-------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1133,7 +1098,9 @@ Created # Security_type DEFINER Comment -######## ALTER PROCEDURE p1 COMMENT "I have been altered" ######## +-------- switch to master ------- + +######## ALTER PROCEDURE p1 COMMENT "I have been altered" ######## -------- switch to master ------- INSERT INTO t1 SET f1= 16 + 1; @@ -1173,12 +1140,6 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1188,7 +1149,8 @@ Modified # Created # Security_type DEFINER Comment I have been altered - -------- switch to slave ------- + +-------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 @@ -1199,7 +1161,9 @@ Created # Security_type DEFINER Comment I have been altered -######## DROP PROCEDURE p1 ######## +-------- switch to master ------- + +######## DROP PROCEDURE p1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 17 + 1; @@ -1239,17 +1203,14 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; +SHOW PROCEDURE STATUS LIKE 'p1'; -------- switch to slave -------- -flush logs; +SHOW PROCEDURE STATUS LIKE 'p1'; -------- switch to master ------- -SHOW PROCEDURE STATUS LIKE 'p1'; - -------- switch to slave ------- -SHOW PROCEDURE STATUS LIKE 'p1'; -######## CREATE OR REPLACE VIEW v1 as select * from t1 ######## +######## CREATE OR REPLACE VIEW v1 as select * from t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 18 + 1; @@ -1289,22 +1250,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` --------- switch to slave ------- +-------- switch to slave -------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` -######## ALTER VIEW v1 AS select f1 from t1 ######## +-------- switch to master ------- + +######## ALTER VIEW v1 AS select f1 from t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 19 + 1; @@ -1344,22 +1301,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` --------- switch to slave ------- +-------- switch to slave -------- SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` -######## DROP VIEW IF EXISTS v1 ######## +-------- switch to master ------- + +######## DROP VIEW IF EXISTS v1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 20 + 1; @@ -1399,20 +1352,16 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW CREATE VIEW v1; ERROR 42S02: Table 'mysqltest1.v1' doesn't exist --------- switch to slave ------- +-------- switch to slave -------- SHOW CREATE VIEW v1; ERROR 42S02: Table 'mysqltest1.v1' doesn't exist -######## CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ######## +-------- switch to master ------- + +######## CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 21 + 1; @@ -1452,22 +1401,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost --------- switch to slave ------- +-------- switch to slave -------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost -######## DROP TRIGGER trg1 ######## +-------- switch to master ------- + +######## DROP TRIGGER trg1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 22 + 1; @@ -1507,20 +1452,16 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer --------- switch to slave ------- +-------- switch to slave -------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer -######## CREATE USER user1@localhost ######## +-------- switch to master ------- + +######## CREATE USER user1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 23 + 1; @@ -1560,22 +1501,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'user1'; user user1 --------- switch to slave ------- +-------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'user1'; user user1 -######## RENAME USER user1@localhost TO rename1@localhost ######## +-------- switch to master ------- + +######## RENAME USER user1@localhost TO rename1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 24 + 1; @@ -1615,22 +1552,18 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'rename1'; user rename1 --------- switch to slave ------- +-------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'rename1'; user rename1 -######## DROP USER rename1@localhost ######## +-------- switch to master ------- + +######## DROP USER rename1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 25 + 1; @@ -1670,19 +1603,14 @@ MAX(f1) TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- -flush logs; - --------- switch to slave -------- -flush logs; - --------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'rename1'; user --------- switch to slave ------- +-------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'rename1'; user -DROP DATABASE IF EXISTS mysqltest1; -DROP DATABASE IF EXISTS mysqltest2; -DROP DATABASE IF EXISTS mysqltest3; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +use test; + +-------- switch to master ------- +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest3; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index ab3892cd5ca..7174dcada9e 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -24,7 +24,7 @@ partition_03ndb : BUG#16385 2006-03-24 mikael Partitions: crash when up rpl_ndb_2innodb : BUG#19227 2006-04-20 pekka pk delete apparently not replicated rpl_ndb_2myisam : BUG#19227 Seems to pass currently rpl_ndb_dd_partitions : BUG#19259 2006-04-21 rpl_ndb_dd_partitions fails on s/AMD -rpl_ndb_ddl : BUG#18946 result file needs update + test needs to checked +rpl_ddl : BUG#26418 2007-03-01 mleich Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK on master 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 diff --git a/mysql-test/t/rpl_ddl.test b/mysql-test/t/rpl_ddl.test index ca1c25c5f09..80df16a7a00 100644 --- a/mysql-test/t/rpl_ddl.test +++ b/mysql-test/t/rpl_ddl.test @@ -22,13 +22,11 @@ # 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 ""; -# --source include/not_ndb_default.inc ---source include/have_innodb.inc --source include/master-slave.inc -let $engine_type= "InnoDB"; +--source include/have_innodb.inc +let $engine_type= InnoDB; +let $temp_engine_type= MEMORY; +let $show_binlog = 0; +let $manipulate = 0; -- source extra/rpl_tests/rpl_ddl.test diff --git a/mysql-test/t/rpl_ndb_ddl.test b/mysql-test/t/rpl_ndb_ddl.test index 0c503e56c9c..ca7a4ce4968 100644 --- a/mysql-test/t/rpl_ndb_ddl.test +++ b/mysql-test/t/rpl_ndb_ddl.test @@ -1,4 +1,4 @@ -######################## rpl_ddl.test ######################## +#################### rpl_ndb_ddl.test ######################## # # # DDL statements (sometimes with implicit COMMIT) executed # # by the master and it's propagation into the slave # @@ -22,14 +22,11 @@ # 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 ""; -# ---source include/have_ndb.inc --source include/master-slave.inc -let $engine_type= "NDB"; --- source extra/rpl_tests/rpl_ndb_ddl.test - +--source include/have_ndb.inc +let $engine_type= NDB; +let $temp_engine_type= MEMORY; +let $show_binlog = 0; +let $manipulate = 0; +-- source extra/rpl_tests/rpl_ddl.test |