include/master-slave.inc [connection master] -------- switch to master ------- SET AUTOCOMMIT = 1; CREATE DATABASE mysqltest1; CREATE DATABASE mysqltest2; 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 INDEX my_idx6 ON mysqltest1.t6(f1); 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) ENGINE=MEMORY; SET AUTOCOMMIT = 0; use mysqltest1; -------- switch to slave -------- SET AUTOCOMMIT = 1; use mysqltest1; -------- switch to master ------- ######## SELECT 1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 0 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 1 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 0 -------- switch to master ------- SELECT 1; 1 1 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 ------- ######## SELECT COUNT(*) FROM t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 0 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 1 -------- 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 -------- 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 ------- ROLLBACK; 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) 1 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- ######## ROLLBACK ######## -------- switch to master ------- INSERT INTO t1 SET f1= 1 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 2 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 1 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 1 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 1 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 1 TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 1 TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- ######## SET AUTOCOMMIT=1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 1 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 2 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 1 -------- switch to master ------- SET AUTOCOMMIT=1; SELECT MAX(f1) FROM t1; MAX(f1) 2 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 2 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 2 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 2 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SET AUTOCOMMIT=0; ######## START TRANSACTION ######## -------- switch to master ------- INSERT INTO t1 SET f1= 2 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 3 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 2 -------- switch to master ------- START TRANSACTION; SELECT MAX(f1) FROM t1; MAX(f1) 3 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 3 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 3 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 3 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- ######## BEGIN ######## -------- switch to master ------- INSERT INTO t1 SET f1= 3 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 4 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 3 -------- switch to master ------- BEGIN; SELECT MAX(f1) FROM t1; MAX(f1) 4 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 4 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 4 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 4 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- ######## DROP TABLE mysqltest1.t2 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 4 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 5 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 4 -------- switch to master ------- DROP TABLE mysqltest1.t2; SELECT MAX(f1) FROM t1; MAX(f1) 5 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 5 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 5 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 5 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW TABLES LIKE 't2'; Tables_in_mysqltest1 (t2) -------- switch to slave -------- SHOW TABLES LIKE 't2'; Tables_in_mysqltest1 (t2) -------- switch to master ------- ######## DROP TEMPORARY TABLE mysqltest1.t23 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 5 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 6 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 5 -------- switch to master ------- DROP TEMPORARY TABLE mysqltest1.t23; SELECT MAX(f1) FROM t1; MAX(f1) 6 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 5 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 5 TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 5 TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- SHOW TABLES LIKE 't23'; Tables_in_mysqltest1 (t23) -------- switch to slave -------- SHOW TABLES LIKE 't23'; Tables_in_mysqltest1 (t23) -------- switch to master ------- ######## RENAME TABLE mysqltest1.t3 to mysqltest1.t20 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 5 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 6 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 5 -------- switch to master ------- RENAME TABLE mysqltest1.t3 to mysqltest1.t20; SELECT MAX(f1) FROM t1; MAX(f1) 6 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 6 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 6 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 6 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW TABLES LIKE 't20'; Tables_in_mysqltest1 (t20) t20 -------- switch to slave -------- SHOW TABLES LIKE 't20'; Tables_in_mysqltest1 (t20) t20 -------- switch to master ------- ######## ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT ######## -------- switch to master ------- INSERT INTO t1 SET f1= 6 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 7 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 6 -------- switch to master ------- ALTER TABLE mysqltest1.t4 ADD column f2 BIGINT; SELECT MAX(f1) FROM t1; MAX(f1) 7 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 7 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 7 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 7 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- describe mysqltest1.t4; Field Type Null Key Default Extra f1 bigint(20) YES NULL f2 bigint(20) YES NULL -------- switch to slave -------- describe mysqltest1.t4; Field Type Null Key Default Extra f1 bigint(20) YES NULL f2 bigint(20) YES NULL -------- switch to master ------- ######## CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= NDB ######## -------- switch to master ------- INSERT INTO t1 SET f1= 7 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 8 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 7 -------- switch to master ------- CREATE TABLE mysqltest1.t21 (f1 BIGINT) ENGINE= NDB; SELECT MAX(f1) FROM t1; MAX(f1) 8 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 8 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 8 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 8 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- ######## CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=MEMORY ######## -------- switch to master ------- INSERT INTO t1 SET f1= 8 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 9 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 8 -------- switch to master ------- CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT) ENGINE=MEMORY; SELECT MAX(f1) FROM t1; MAX(f1) 9 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 8 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 8 TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 8 TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- ######## TRUNCATE TABLE mysqltest1.t7 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 8 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 9 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 8 -------- switch to master ------- TRUNCATE TABLE mysqltest1.t7; SELECT MAX(f1) FROM t1; MAX(f1) 9 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 9 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 9 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 9 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- 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 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 9 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 9 -------- switch to master ------- LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 10 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- UNLOCK TABLES; ######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 11 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to master ------- UNLOCK TABLES; SELECT MAX(f1) FROM t1; MAX(f1) 11 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 10 TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- LOCK TABLES mysqltest1.t1 READ; ######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; ERROR HY000: Table 't1' was locked with a READ lock and can't be updated SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to master ------- UNLOCK TABLES; SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 10 TEST-INFO: MASTER: The INSERT is not committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 TEST-INFO: SLAVE: The INSERT is not committed (Succeeded) -------- switch to master ------- LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ; ######## UNLOCK TABLES ######## -------- switch to master ------- INSERT INTO t1 SET f1= 10 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 11 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 10 -------- switch to master ------- UNLOCK TABLES; SELECT MAX(f1) FROM t1; MAX(f1) 11 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 11 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 11 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 11 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- ######## DROP INDEX my_idx6 ON mysqltest1.t6 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 11 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 12 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 11 -------- switch to master ------- DROP INDEX my_idx6 ON mysqltest1.t6; SELECT MAX(f1) FROM t1; MAX(f1) 12 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 12 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 12 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 12 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- 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 -------- switch to slave -------- SHOW INDEX FROM mysqltest1.t6; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -------- switch to master ------- ######## CREATE INDEX my_idx5 ON mysqltest1.t5(f1) ######## -------- switch to master ------- INSERT INTO t1 SET f1= 12 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 13 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 12 -------- switch to master ------- CREATE INDEX my_idx5 ON mysqltest1.t5(f1); SELECT MAX(f1) FROM t1; MAX(f1) 13 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 13 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 13 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 13 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- 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 NULL NULL NULL YES BTREE -------- switch to slave -------- 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 NULL NULL NULL YES BTREE -------- switch to master ------- ######## DROP DATABASE mysqltest2 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 13 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 14 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 13 -------- switch to master ------- DROP DATABASE mysqltest2; SELECT MAX(f1) FROM t1; MAX(f1) 14 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 14 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 14 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 14 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW DATABASES LIKE "mysqltest2"; Database (mysqltest2) -------- switch to slave -------- SHOW DATABASES LIKE "mysqltest2"; Database (mysqltest2) -------- switch to master ------- ######## CREATE DATABASE mysqltest3 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 14 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 15 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 14 -------- switch to master ------- CREATE DATABASE mysqltest3; SELECT MAX(f1) FROM t1; MAX(f1) 15 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 15 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 15 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 15 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW DATABASES LIKE "mysqltest3"; Database (mysqltest3) mysqltest3 -------- switch to slave -------- SHOW DATABASES LIKE "mysqltest3"; Database (mysqltest3) mysqltest3 -------- switch to master ------- ######## CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1" ######## -------- switch to master ------- INSERT INTO t1 SET f1= 15 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 16 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 15 -------- switch to master ------- CREATE PROCEDURE p1() READS SQL DATA SELECT "this is p1"; SELECT MAX(f1) FROM t1; MAX(f1) 16 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 16 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 16 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 16 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 Type PROCEDURE Definer root@localhost Modified # Created # Security_type DEFINER Comment character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci -------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 Type PROCEDURE Definer root@localhost Modified # Created # Security_type DEFINER Comment character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci -------- switch to master ------- ######## ALTER PROCEDURE p1 COMMENT "I have been altered" ######## -------- switch to master ------- INSERT INTO t1 SET f1= 16 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 17 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 16 -------- switch to master ------- ALTER PROCEDURE p1 COMMENT "I have been altered"; SELECT MAX(f1) FROM t1; MAX(f1) 17 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 17 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 17 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 17 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 Type PROCEDURE Definer root@localhost Modified # Created # Security_type DEFINER Comment I have been altered character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci -------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; Db mysqltest1 Name p1 Type PROCEDURE Definer root@localhost Modified # Created # Security_type DEFINER Comment I have been altered character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci -------- switch to master ------- ######## DROP PROCEDURE p1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 17 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 18 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 17 -------- switch to master ------- DROP PROCEDURE p1; SELECT MAX(f1) FROM t1; MAX(f1) 18 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 18 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 18 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 18 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW PROCEDURE STATUS LIKE 'p1'; -------- switch to slave -------- SHOW PROCEDURE STATUS LIKE 'p1'; -------- switch to master ------- ######## CREATE OR REPLACE VIEW v1 as select * from t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 18 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 19 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 18 -------- switch to master ------- CREATE OR REPLACE VIEW v1 as select * from t1; SELECT MAX(f1) FROM t1; MAX(f1) 19 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 19 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 19 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 19 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -------- switch to slave -------- SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -------- switch to master ------- ######## ALTER VIEW v1 AS select f1 from t1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 19 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 20 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 19 -------- switch to master ------- ALTER VIEW v1 AS select f1 from t1; SELECT MAX(f1) FROM t1; MAX(f1) 20 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 20 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 20 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 20 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -------- switch to slave -------- SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci -------- switch to master ------- ######## DROP VIEW IF EXISTS v1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 20 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 21 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 20 -------- switch to master ------- DROP VIEW IF EXISTS v1; SELECT MAX(f1) FROM t1; MAX(f1) 21 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 21 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 21 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 21 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW CREATE VIEW v1; ERROR 42S02: Table 'mysqltest1.v1' doesn't exist -------- switch to slave -------- SHOW CREATE VIEW v1; ERROR 42S02: Table 'mysqltest1.v1' doesn't exist -------- 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; SELECT MAX(f1) FROM t1; MAX(f1) 22 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 21 -------- switch to master ------- CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; SELECT MAX(f1) FROM t1; MAX(f1) 22 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 22 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 22 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 22 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -------- switch to slave -------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation trg1 INSERT t1 SET @a:=1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -------- switch to master ------- ######## DROP TRIGGER trg1 ######## -------- switch to master ------- INSERT INTO t1 SET f1= 22 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 23 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 22 -------- switch to master ------- DROP TRIGGER trg1; SELECT MAX(f1) FROM t1; MAX(f1) 23 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 23 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 23 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 23 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -------- switch to slave -------- SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -------- switch to master ------- ######## CREATE USER user1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 23 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 24 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 23 -------- switch to master ------- CREATE USER user1@localhost; SELECT MAX(f1) FROM t1; MAX(f1) 24 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 24 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 24 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 24 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'user1'; user user1 -------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'user1'; user user1 -------- switch to master ------- ######## RENAME USER user1@localhost TO rename1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 24 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 25 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 24 -------- switch to master ------- RENAME USER user1@localhost TO rename1@localhost; SELECT MAX(f1) FROM t1; MAX(f1) 25 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 25 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 25 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 25 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'rename1'; user rename1 -------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'rename1'; user rename1 -------- switch to master ------- ######## DROP USER rename1@localhost ######## -------- switch to master ------- INSERT INTO t1 SET f1= 25 + 1; SELECT MAX(f1) FROM t1; MAX(f1) 26 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 25 -------- switch to master ------- DROP USER rename1@localhost; SELECT MAX(f1) FROM t1; MAX(f1) 26 -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 26 -------- switch to master ------- ROLLBACK; SELECT MAX(f1) FROM t1; MAX(f1) 26 TEST-INFO: MASTER: The INSERT is committed (Succeeded) -------- switch to slave -------- SELECT MAX(f1) FROM t1; MAX(f1) 26 TEST-INFO: SLAVE: The INSERT is committed (Succeeded) -------- switch to master ------- SELECT user FROM mysql.user WHERE user = 'rename1'; user -------- switch to slave -------- SELECT user FROM mysql.user WHERE user = 'rename1'; user use test; -------- switch to master ------- DROP DATABASE mysqltest1; DROP DATABASE mysqltest3; include/rpl_end.inc