1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); 7CALL mtr.add_suppression('Cannot execute statement because it needs to be written to the binary log as multiple statements'); 8CALL mtr.add_suppression('DROP DATABASE failed; some tables may have been dropped but the database directory remains.'); 9CALL mtr.add_suppression("Error dropping database"); 10CALL mtr.add_suppression("Can't drop database '.*'; database doesn't exist"); 11CALL mtr.add_suppression("Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. .* Error_code: 1756"); 12==== Case 1: CALL is split on master ==== 13---- Initialize ---- 14CREATE TABLE t (a INT); 15CREATE PROCEDURE proc () 16BEGIN 17INSERT INTO t VALUES (1); 18INSERT INTO t VALUES (2); 19END| 20---- GTID_NEXT=AUTOMATIC ---- 21include/gtid_step_reset.inc 22CALL proc(); 23include/gtid_step_assert.inc [count=2, only_count=0] 24include/assert.inc [Both rows were inserted] 25DELETE FROM t; 26---- GTID_NEXT=non-automatic ---- 27include/gtid_step_reset.inc 28SET GTID_NEXT = '#'; 29CALL proc(); 30ERROR HY000: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'MASTER_UUID:9'. 31SET GTID_NEXT= 'AUTOMATIC'; 32include/gtid_step_assert.inc [count=1, only_count=0] 33include/assert.inc [One row inserted if GTID_MODE=ON, two if GTID_MODE=OFF] 34DROP TABLE t; 35DROP PROCEDURE proc; 36==== Case 2A: DROP TABLE is split on master ==== 37---- Initialize ---- 38CREATE PROCEDURE create_tables() 39BEGIN 40CREATE TABLE base (a INT) ENGINE = InnoDB; 41CREATE TEMPORARY TABLE temp_t (a INT) ENGINE = InnoDB; 42CREATE TEMPORARY TABLE temp_n (a INT) ENGINE = MyISAM; 43END| 44CREATE PROCEDURE drop_tables() 45BEGIN 46DROP TABLE IF EXISTS base; 47DROP TABLE IF EXISTS temp_t; 48DROP TABLE IF EXISTS temp_n; 49END| 50include/rpl_sync.inc 51---- GTID_MODE=AUTOMATIC ---- 52CALL create_tables(); 53include/gtid_step_reset.inc 54DROP TABLE base, temp_t; 55include/gtid_step_assert.inc [count=2, only_count=0] 56include/assert_binlog_events.inc 57DROP TABLE temp_n; 58include/rpl_sync.inc 59include/rpl_reset.inc 60CALL create_tables(); 61include/gtid_step_reset.inc 62DROP TABLE base, temp_n; 63include/gtid_step_assert.inc [count=2, only_count=0] 64include/assert_binlog_events.inc 65DROP TABLE temp_t; 66include/rpl_sync.inc 67include/rpl_reset.inc 68CALL create_tables(); 69include/gtid_step_reset.inc 70DROP TABLE temp_t, temp_n; 71include/gtid_step_assert.inc [count=2, only_count=0] 72include/assert_binlog_events.inc 73DROP TABLE base; 74include/rpl_sync.inc 75include/rpl_reset.inc 76CALL create_tables(); 77include/gtid_step_reset.inc 78DROP TABLE base, temp_t, temp_n; 79include/gtid_step_assert.inc [count=3, only_count=0] 80include/assert_binlog_events.inc 81include/rpl_sync.inc 82include/rpl_reset.inc 83---- GTID_MODE=non-automatic ---- 84CALL create_tables(); 85include/gtid_step_reset.inc 86SET GTID_NEXT = '#'; 87DROP TABLE base, temp_t; 88ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'. 89SET GTID_NEXT = 'AUTOMATIC'; 90include/gtid_step_assert.inc [count=0, only_count=0] 91include/assert_binlog_events.inc 92DROP TABLE temp_n; 93DROP TABLE base, temp_t; 94include/rpl_sync.inc 95include/rpl_reset.inc 96CALL create_tables(); 97include/gtid_step_reset.inc 98SET GTID_NEXT = '#'; 99DROP TABLE base, temp_n; 100ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'. 101SET GTID_NEXT = 'AUTOMATIC'; 102include/gtid_step_assert.inc [count=0, only_count=0] 103include/assert_binlog_events.inc 104DROP TABLE temp_t; 105DROP TABLE base, temp_n; 106include/rpl_sync.inc 107include/rpl_reset.inc 108CALL create_tables(); 109include/gtid_step_reset.inc 110SET GTID_NEXT = '#'; 111DROP TABLE temp_t, temp_n; 112ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'. 113SET GTID_NEXT = 'AUTOMATIC'; 114include/gtid_step_assert.inc [count=0, only_count=0] 115include/assert_binlog_events.inc 116DROP TABLE base; 117DROP TABLE temp_t, temp_n; 118include/rpl_sync.inc 119include/rpl_reset.inc 120CALL create_tables(); 121include/gtid_step_reset.inc 122SET GTID_NEXT = '#'; 123DROP TABLE base, temp_t, temp_n; 124ERROR HY000: Cannot execute statement because it needs to be written to the binary log as multiple statements, and this is not allowed when @@SESSION.GTID_NEXT == 'UUID:NUMBER'. 125SET GTID_NEXT = 'AUTOMATIC'; 126include/gtid_step_assert.inc [count=0, only_count=0] 127include/assert_binlog_events.inc 128DROP TABLE base, temp_t, temp_n; 129include/rpl_sync.inc 130include/rpl_reset.inc 131==== Case 2B: DROP TABLE is split on slave ==== 132---- Initialize ---- 133CREATE TABLE dummy (a INT); 134DROP PROCEDURE create_tables; 135CREATE FUNCTION create_tables_func() RETURNS INT 136BEGIN 137IF @@GLOBAL.SERVER_ID = 2 THEN 138CREATE TEMPORARY TABLE temp_t (a INT) ENGINE = InnoDB; 139CREATE TEMPORARY TABLE temp_n (a INT) ENGINE = MyISAM; 140END IF; 141RETURN 0; 142END| 143CREATE PROCEDURE create_tables() 144BEGIN 145CREATE TABLE base (a INT); 146SET @@SESSION.SQL_LOG_BIN = 0; 147CREATE TABLE temp_t (a INT); 148CREATE TABLE temp_n (a INT); 149SET @@SESSION.SQL_LOG_BIN = 1; 150INSERT INTO dummy VALUES (create_tables_func()); 151END| 152include/rpl_sync.inc 153---- GTID_MODE=AUTOMATIC ---- 154CALL create_tables(); 155Warnings: 156Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. 157include/gtid_step_reset.inc 158DROP TABLE base, temp_t; 159include/gtid_step_assert.inc [count=1, only_count=0] 160include/assert_binlog_events.inc 161include/wait_for_slave_sql_error.inc [errno=1884] 162include/include/rpl_skip_to_end_of_relay_log.inc 163include/stop_slave.inc 164Warnings: 165Warning 3022 This operation may not be safe when the slave has temporary tables. The tables will be kept open until the server restarts or until the tables are deleted by any replicated DROP statement. Suggest to wait until slave_open_temp_tables = 0. 166RESET SLAVE; 167include/start_slave.inc 168DROP TABLE base; 169DROP TABLE IF EXISTS base, temp_t, temp_n; 170Warnings: 171Note 1051 Unknown table 'test.base' 172Note 1051 Unknown table 'test.temp_t' 173include/rpl_sync.inc 174include/rpl_reset.inc 175CALL create_tables(); 176Warnings: 177Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. 178include/gtid_step_reset.inc 179DROP TABLE base, temp_n; 180include/gtid_step_assert.inc [count=1, only_count=0] 181include/assert_binlog_events.inc 182include/wait_for_slave_sql_error.inc [errno=1884] 183include/include/rpl_skip_to_end_of_relay_log.inc 184include/stop_slave.inc 185Warnings: 186Warning 3022 This operation may not be safe when the slave has temporary tables. The tables will be kept open until the server restarts or until the tables are deleted by any replicated DROP statement. Suggest to wait until slave_open_temp_tables = 0. 187RESET SLAVE; 188include/start_slave.inc 189DROP TABLE base; 190DROP TABLE IF EXISTS base, temp_t, temp_n; 191Warnings: 192Note 1051 Unknown table 'test.base' 193Note 1051 Unknown table 'test.temp_n' 194include/rpl_sync.inc 195include/rpl_reset.inc 196CALL create_tables(); 197Warnings: 198Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. 199include/gtid_step_reset.inc 200DROP TABLE temp_t, temp_n; 201include/gtid_step_assert.inc [count=1, only_count=0] 202include/assert_binlog_events.inc 203include/wait_for_slave_sql_error.inc [errno=1884] 204include/include/rpl_skip_to_end_of_relay_log.inc 205include/stop_slave.inc 206Warnings: 207Warning 3022 This operation may not be safe when the slave has temporary tables. The tables will be kept open until the server restarts or until the tables are deleted by any replicated DROP statement. Suggest to wait until slave_open_temp_tables = 0. 208RESET SLAVE; 209include/start_slave.inc 210DROP TABLE IF EXISTS base, temp_t, temp_n; 211Warnings: 212Note 1051 Unknown table 'test.temp_t' 213Note 1051 Unknown table 'test.temp_n' 214include/rpl_sync.inc 215include/rpl_reset.inc 216CALL create_tables(); 217Warnings: 218Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave. 219include/gtid_step_reset.inc 220DROP TABLE base, temp_t, temp_n; 221include/gtid_step_assert.inc [count=1, only_count=0] 222include/assert_binlog_events.inc 223include/wait_for_slave_sql_error.inc [errno=1884] 224include/include/rpl_skip_to_end_of_relay_log.inc 225include/stop_slave.inc 226Warnings: 227Warning 3022 This operation may not be safe when the slave has temporary tables. The tables will be kept open until the server restarts or until the tables are deleted by any replicated DROP statement. Suggest to wait until slave_open_temp_tables = 0. 228RESET SLAVE; 229include/start_slave.inc 230DROP TABLE base; 231DROP TABLE IF EXISTS base, temp_t, temp_n; 232Warnings: 233Note 1051 Unknown table 'test.base' 234Note 1051 Unknown table 'test.temp_t' 235Note 1051 Unknown table 'test.temp_n' 236include/rpl_sync.inc 237include/rpl_reset.inc 238---- Clean up ---- 239DROP FUNCTION create_tables_func; 240DROP PROCEDURE create_tables; 241DROP PROCEDURE drop_tables; 242DROP TABLE dummy; 243==== Case 3: DROP DATABASE ==== 244---- Initialize ---- 245# db1, db2, db3: no tables. 246CREATE DATABASE db1; 247CREATE DATABASE db2; 248CREATE DATABASE db3; 249include/rpl_sync.inc 250# db4, db5, db6: one table. 251CREATE DATABASE db4; 252CREATE DATABASE db5; 253CREATE DATABASE db6; 254CREATE TABLE db4.t1 (a INT); 255CREATE TABLE db5.t1 (a INT); 256CREATE TABLE db6.t1 (a INT); 257include/rpl_sync.inc 258# db7, db8, db9: many tables with long names. 259CREATE DATABASE db7; 260CREATE DATABASE db8; 261CREATE DATABASE db9; 262include/rpl_sync.inc 263# db10, db11, db12: not a database, but the directory exists. 264# db13, db14, db15: not a database. db15 is a database on master. 265include/rpl_sync.inc 266---- DROP DATABASE is split on master; GTID_NEXT=AUTOMATIC ---- 267# db1: no table. 268include/save_binlog_position.inc 269SET GTID_NEXT = 'AUTOMATIC'; 270DROP DATABASE db1; 271ERROR HY000: Error dropping database (can't rmdir './db1/', errno: 17) 272include/assert_binlog_events.inc 273# db4: one table. 274include/save_binlog_position.inc 275SET GTID_NEXT = 'AUTOMATIC'; 276DROP DATABASE db4; 277ERROR HY000: Error dropping database (can't rmdir './db4/', errno: 17) 278include/assert_binlog_events.inc 279# db7: many tables with long names. 280include/save_binlog_position.inc 281SET GTID_NEXT = 'AUTOMATIC'; 282DROP DATABASE db7; 283ERROR HY000: Error dropping database (can't rmdir './db7/', errno: 17) 284include/assert_binlog_events.inc 285# db10: not a database, but directory exists. 286include/save_binlog_position.inc 287SET GTID_NEXT = 'AUTOMATIC'; 288DROP DATABASE db10; 289ERROR HY000: Error dropping database (can't rmdir './db10/', errno: 17) 290include/assert_binlog_events.inc 291# db13: not a database. 292include/save_binlog_position.inc 293SET GTID_NEXT = 'AUTOMATIC'; 294DROP DATABASE db13; 295ERROR HY000: Can't drop database 'db13'; database doesn't exist 296include/assert_binlog_events.inc 297---- DROP DATABASE is split on master; GTID_NEXT=non-automatic ---- 298# db2: no table. 299include/save_binlog_position.inc 300SET GTID_NEXT = '#'; 301DROP DATABASE db2; 302ERROR HY000: DROP DATABASE failed; some tables may have been dropped but the database directory remains. The GTID has not been added to GTID_EXECUTED and the statement was not written to the binary log. Fix this as follows: (1) remove all files from the database directory ./db2/; (2) SET GTID_NEXT='MASTER_UUID:80'; (3) DROP DATABASE `db2`. 303include/assert_binlog_events.inc 304SET GTID_NEXT = 'AUTOMATIC'; 305# db5: one table. 306include/save_binlog_position.inc 307SET GTID_NEXT = '#'; 308DROP DATABASE db5; 309ERROR HY000: DROP DATABASE failed; some tables may have been dropped but the database directory remains. The GTID has not been added to GTID_EXECUTED and the statement was not written to the binary log. Fix this as follows: (1) remove all files from the database directory ./db5/; (2) SET GTID_NEXT='MASTER_UUID:80'; (3) DROP DATABASE `db5`. 310include/assert_binlog_events.inc 311SET GTID_NEXT = 'AUTOMATIC'; 312# db8: many tables with long names. 313include/save_binlog_position.inc 314SET GTID_NEXT = '#'; 315DROP DATABASE db8; 316ERROR HY000: DROP DATABASE failed; some tables may have been dropped but the database directory remains. The GTID has not been added to GTID_EXECUTED and the statement was not written to the binary log. Fix this as follows: (1) remove all files from the database directory ./db8/; (2) SET GTID_NEXT='MASTER_UUID:80'; (3) DROP DATABASE `db8`. 317include/assert_binlog_events.inc 318SET GTID_NEXT = 'AUTOMATIC'; 319# db11: not a database, but directory exists. 320include/save_binlog_position.inc 321SET GTID_NEXT = '#'; 322DROP DATABASE db11; 323ERROR HY000: DROP DATABASE failed; some tables may have been dropped but the database directory remains. The GTID has not been added to GTID_EXECUTED and the statement was not written to the binary log. Fix this as follows: (1) remove all files from the database directory ./db11/; (2) SET GTID_NEXT='MASTER_UUID:80'; (3) DROP DATABASE `db11`. 324include/assert_binlog_events.inc 325SET GTID_NEXT = 'AUTOMATIC'; 326# db14: not a database. 327include/save_binlog_position.inc 328SET GTID_NEXT = '#'; 329DROP DATABASE db14; 330ERROR HY000: Can't drop database 'db14'; database doesn't exist 331include/assert_binlog_events.inc 332SET GTID_NEXT = 'AUTOMATIC'; 333---- DROP DATABASE is split on slave ---- 334SET GTID_NEXT = 'AUTOMATIC'; 335# db3: no table. 336DROP DATABASE db3; 337include/sync_slave_io_with_master.inc 338include/wait_for_slave_sql_error.inc [errno=3091] 339include/include/rpl_skip_to_end_of_relay_log.inc 340[connection master] 341# db6: one table. 342DROP DATABASE db6; 343include/sync_slave_io_with_master.inc 344include/wait_for_slave_sql_error.inc [errno=3091] 345include/include/rpl_skip_to_end_of_relay_log.inc 346[connection master] 347# db9: many tables with long names. 348DROP DATABASE db9; 349include/sync_slave_io_with_master.inc 350include/wait_for_slave_sql_error.inc [errno=3091] 351include/include/rpl_skip_to_end_of_relay_log.inc 352[connection master] 353# db12: not a database, but directory exists. 354DROP DATABASE db12; 355include/sync_slave_io_with_master.inc 356include/wait_for_slave_sql_error.inc [errno=3091] 357include/include/rpl_skip_to_end_of_relay_log.inc 358[connection master] 359# db15: not a database (on slave). 360DROP DATABASE db15; 361include/sync_slave_io_with_master.inc 362include/wait_for_slave_sql_error.inc [errno=1008] 363include/include/rpl_skip_to_end_of_relay_log.inc 364[connection master] 365---- Clean up ---- 366DROP DATABASE db1; 367DROP DATABASE db2; 368DROP DATABASE IF EXISTS db3; 369Warnings: 370Note 1008 Can't drop database 'db3'; database doesn't exist 371DROP DATABASE db4; 372DROP DATABASE db5; 373DROP DATABASE IF EXISTS db6; 374Warnings: 375Note 1008 Can't drop database 'db6'; database doesn't exist 376DROP DATABASE db7; 377DROP DATABASE db8; 378DROP DATABASE IF EXISTS db9; 379Warnings: 380Note 1008 Can't drop database 'db9'; database doesn't exist 381DROP DATABASE IF EXISTS db10; 382DROP DATABASE IF EXISTS db11; 383DROP DATABASE IF EXISTS db12; 384Warnings: 385Note 1008 Can't drop database 'db12'; database doesn't exist 386DROP DATABASE IF EXISTS db15; 387Warnings: 388Note 1008 Can't drop database 'db15'; database doesn't exist 389include/rpl_sync.inc 390==== Case 4: CREATE TABLE ... SELECT ==== 391See rpl_gtid_create_select.test 392include/rpl_end.inc 393