Below is the list of changes that have just been committed into a local
5.1 repository of mleich. When mleich does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-03-06 18:15:31+01:00, mleich@stripped +9 -0
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@stripped, 2007-03-06 17:48:03+01:00, mleich@stripped +0 -0
Delete: mysql-test/extra/rpl_tests/rpl_ndb_ddl.test
BitKeeper/deleted/.del-rpl_stmt_seq2.inc@stripped, 2007-03-06 17:48:21+01:00, mleich@stripped +0 -0
Delete: mysql-test/include/rpl_stmt_seq2.inc
mysql-test/extra/rpl_tests/rpl_ddl.test@stripped, 2007-03-06 18:15:29+01:00, mleich@stripped +253 -150
Improvements + Fixes
mysql-test/include/rpl_stmt_seq.inc@stripped, 2007-03-06 18:15:29+01:00, mleich@stripped +63 -51
Improvements
mysql-test/r/rpl_ddl.result@stripped, 2007-03-06 18:15:30+01:00, mleich@stripped +187 -258
Updated expected results
mysql-test/r/rpl_ndb_ddl.result@stripped, 2007-03-06 18:15:30+01:00, mleich@stripped +181 -253
Updated expected results
mysql-test/t/disabled.def@stripped, 2007-03-06 18:15:30+01:00, mleich@stripped +1 -1
Reenable rpl_ndb_ddl because of bug fix.
Disable rpl_ddl because of Bug#26418.
mysql-test/t/rpl_ddl.test@stripped, 2007-03-06 18:15:30+01:00, mleich@stripped +5 -7
Improvements
mysql-test/t/rpl_ndb_ddl.test@stripped, 2007-03-06 18:15:30+01:00, mleich@stripped +7 -10
Improvements
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mleich
# Host: four.local.lan
# Root: /WORK/mysql-5.1-rpl/src-rpl-ddl
--- 1.2/mysql-test/r/rpl_ndb_ddl.result 2007-03-06 18:15:37 +01:00
+++ 1.3/mysql-test/r/rpl_ndb_ddl.result 2007-03-06 18:15:37 +01:00
@@ -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 C
-------- 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 BTRE
-------- 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;
--- 1.2/mysql-test/t/rpl_ndb_ddl.test 2007-03-06 18:15:37 +01:00
+++ 1.3/mysql-test/t/rpl_ndb_ddl.test 2007-03-06 18:15:37 +01:00
@@ -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
--- 1.233/mysql-test/t/disabled.def 2007-03-06 18:15:37 +01:00
+++ 1.234/mysql-test/t/disabled.def 2007-03-06 18:15:37 +01:00
@@ -24,7 +24,7 @@ partition_03ndb : BUG#16385 200
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
--- 1.4/mysql-test/t/rpl_ddl.test 2007-03-06 18:15:37 +01:00
+++ 1.5/mysql-test/t/rpl_ddl.test 2007-03-06 18:15:37 +01:00
@@ -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
--- 1.6/mysql-test/include/rpl_stmt_seq.inc 2007-03-06 18:15:37 +01:00
+++ 1.7/mysql-test/include/rpl_stmt_seq.inc 2007-03-06 18:15:37 +01:00
@@ -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.$
}
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.$_
###############################################################
# 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.$
}
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.$_
###############################################################
# 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.$
}
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
--- 1.17/mysql-test/r/rpl_ddl.result 2007-03-06 18:15:37 +01:00
+++ 1.18/mysql-test/r/rpl_ddl.result 2007-03-06 18:15:37 +01:00
@@ -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 com
-------- 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 com
-------- 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 C
-------- 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 B
-------- 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;
--- 1.12/mysql-test/extra/rpl_tests/rpl_ddl.test 2007-03-06 18:15:37 +01:00
+++ 1.13/mysql-test/extra/rpl_tests/rpl_ddl.test 2007-03-06 18:15:37 +01:00
@@ -1,31 +1,128 @@
-######################## 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 "";
+################# 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 !!
+#
+# 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.
+#
+# 3. !All! objects to be dropped, renamed, altered ... must be created
+# before the tests start.
+# --> less switching of AUTOCOMMIT mode on master side.
+#
+# 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.
+#
+# 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.
+#
+# 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 BIG
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 ------
--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
+--source include/rpl_stmt_seq.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_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
-
| Thread |
|---|
| • bk commit into 5.1 tree (mleich:1.2472) BUG#18946 | mleich | 6 Mar |
