1# ==== Purpose ==== 2# 3# Verify that a partially failed statement can consume 4# its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED 5# and mysql.gtid_executed table when binlog is disabled 6# if it did the same when binlog is enabled. 7# 8# ==== Implementation ==== 9# 10# 1) SET SESSION GTID_NEXT='UUID:GNO'. 11# 2) Execute a partially failed statement. 12# 3) Verify that the partially failed statement can consume 13# its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED 14# and mysql.gtid_executed table when binlog is disabled 15# if it did the same when binlog is enabled. 16# 4) Execute above three steps for all different types of statements 17# 18# ==== References ==== 19# 20# Bug#21686749 PARTIALLY FAILED DROP OR ACL STMT FAILS TO CONSUME GTID ON BINLOGLESS SLAVE 21# See mysql-test/suite/binlog/t/binlog_gtid_next_partially_failed_stmts.test 22# See mysql-test/suite/binlog/t/binlog_gtid_next_partially_failed_grant.test 23# See mysql-test/t/no_binlog_gtid_next_partially_failed_stmts_error.test 24# 25 26# Test is not required to run against embedded server 27--source include/not_embedded.inc 28# Should be tested against "binlog disabled" server 29--source include/not_log_bin.inc 30 31# Make sure the test is repeatable 32RESET MASTER; 33 34--let $master_uuid= `SELECT @@GLOBAL.SERVER_UUID` 35 36--replace_result $master_uuid MASTER_UUID 37--eval SET SESSION GTID_NEXT='$master_uuid:1' 38CREATE TABLE t1 (a int); 39 40# Check-1: DROP TABLE 41--replace_result $master_uuid MASTER_UUID 42--eval SET SESSION GTID_NEXT='$master_uuid:2' 43--echo # 44--echo # Execute a partially failed DROP TABLE statement. 45--echo # 46--error ER_BAD_TABLE_ERROR 47DROP TABLE t1, t2; 48 49--echo # 50--echo # The table t1 was dropped, which means DROP TABLE 51--echo # can be failed partially. 52--echo # 53--error ER_NO_SUCH_TABLE 54SHOW CREATE TABLE t1; 55 56--echo # 57--echo # Verify that the partially failed DROP TABLE statement can consume 58--echo # its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 59--echo # mysql.gtid_executed table when binlog is disabled as it did when 60--echo # binlog is enabled. 61--echo # 62--let $assert_text= Committed gtid MASTER_UUID:2 into @@GLOBAL.GTID_EXECUTED 63--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-2" 64--source include/assert.inc 65--let $assert_text= Saved gtid MASTER_UUID:2 into mysql.gtid_executed table 66--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 2]" = 1 67--source include/assert.inc 68 69 70--replace_result $master_uuid MASTER_UUID 71--eval SET SESSION GTID_NEXT='$master_uuid:3' 72CREATE TEMPORARY TABLE tmp1 (a int); 73 74# Check-2: DROP TEMPORARY TABLE 75--replace_result $master_uuid MASTER_UUID 76--eval SET SESSION GTID_NEXT='$master_uuid:4' 77--echo # 78--echo # Execute a partially failed DROP TEMPORARY TABLE statement. 79--echo # 80--error ER_BAD_TABLE_ERROR 81DROP TEMPORARY TABLE tmp1, t2; 82--echo # 83--echo # Verify that the partially failed DROP TEMPORARY TABLE statement can 84--echo # consume its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 85--echo # mysql.gtid_executed table when binlog is disabled as it did when 86--echo # binlog is enabled. 87--echo # 88--let $assert_text= Committed gtid MASTER_UUID:4 into @@GLOBAL.GTID_EXECUTED 89--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-4" 90--source include/assert.inc 91--let $assert_text= Saved gtid MASTER_UUID:4 into mysql.gtid_executed table 92--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 4]" = 1 93--source include/assert.inc 94 95--replace_result $master_uuid MASTER_UUID 96--eval SET SESSION GTID_NEXT='$master_uuid:5' 97CREATE TABLE t1(a INT, b INT); 98--replace_result $master_uuid MASTER_UUID 99--eval SET SESSION GTID_NEXT='$master_uuid:6' 100CREATE USER u1@h; 101--echo # 102--echo # Verify that u1 is added into mysql.user. 103--echo # 104SELECT user FROM mysql.user where user='u1'; 105 106# Check-3: GRANT a non-available privilege 107--replace_result $master_uuid MASTER_UUID 108--eval SET SESSION GTID_NEXT='$master_uuid:7' 109--error ER_BAD_FIELD_ERROR 110GRANT SELECT(a), SELECT(c) ON t1 TO u1@h; 111--echo # 112--echo # The SELECT privilege on column a is not granted to u1@h, 113--echo # which means that GRANT can not be failed partially when 114--echo # granting an available privilege and a non-available 115--echo # privilege to an existent user. 116--echo # 117SELECT user, column_name, column_priv FROM mysql.columns_priv; 118--echo # 119--echo # Verify that the failed GRANT statement does not save 120--echo # the specified gtid into @@GLOBAL.GTID_EXECUTED and 121--echo # mysql.gtid_executed table. 122--echo # 123--replace_result $master_uuid MASTER_UUID 124--let $assert_text= Did not commit gtid MASTER_UUID:7 into @@GLOBAL.GTID_EXECUTED 125--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6" 126--source include/assert.inc 127--let $assert_text= Did not save gtid MASTER_UUID:7 into mysql.gtid_executed table 128--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 7]" = 0 129--source include/assert.inc 130 131# Check-4: GRANT a privilege to a non-existent user 132--replace_result $master_uuid MASTER_UUID 133--eval SET SESSION GTID_NEXT='$master_uuid:7' 134--error ER_PASSWORD_NO_MATCH 135GRANT SELECT(a) ON t1 TO u1@h, u2@h; 136--echo # 137--echo # The SELECT privilege on column a is granted to u1@h, 138--echo # which means that GRANT can be failed partially when 139--echo # granting an available privilege to both an existent 140--echo # user and a non-existent user. 141--echo # 142SELECT user, column_name, column_priv FROM mysql.columns_priv; 143--echo # 144--echo # Verify that the partially failed GRANT statement does not 145--echo # save the specified gtid into @@GLOBAL.GTID_EXECUTED and 146--echo # mysql.gtid_executed table when binlog is disabled as it 147--echo # did when binlog is enabled. 148--echo # 149--replace_result $master_uuid MASTER_UUID 150--let $assert_text= Did not commit gtid MASTER_UUID:7 into @@GLOBAL.GTID_EXECUTED 151--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6" 152--source include/assert.inc 153--let $assert_text= Did not save gtid MASTER_UUID:7 into mysql.gtid_executed table 154--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 7]" = 0 155--source include/assert.inc 156 157# Check-5: REVOKE 158--replace_result $master_uuid MASTER_UUID 159--eval SET SESSION GTID_NEXT='$master_uuid:8' 160--error ER_NONEXISTING_TABLE_GRANT 161REVOKE SELECT(a), SELECT(b) ON t1 FROM u1@h; 162--echo # 163--echo # The SELECT privilege is removed on column a from user u1, 164--echo # which means that REVOKE statement can be failed partially 165--echo # when revoking multiple privileges from a user. 166--echo # 167SELECT user, column_name, column_priv FROM mysql.columns_priv; 168--echo # 169--echo # Verify that the partially failed REVOKE statement does not 170--echo # save the specified gtid into @@GLOBAL.GTID_EXECUTED and 171--echo # and mysql.gtid_executed table when binlog is disabled as it 172--echo # did when binlog is enabled. 173--echo # 174--replace_result $master_uuid MASTER_UUID 175--let $assert_text= Did not commit gtid MASTER_UUID:8 into @@GLOBAL.GTID_EXECUTED 176--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6" 177--source include/assert.inc 178--let $assert_text= Did not save gtid MASTER_UUID:8 into mysql.gtid_executed table 179--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 8]" = 0 180--source include/assert.inc 181 182# Check-6: DROP USER 183--replace_result $master_uuid MASTER_UUID 184--eval SET SESSION GTID_NEXT='$master_uuid:9' 185--error ER_CANNOT_USER 186DROP USER u1@h, u2@h; 187--echo # 188--echo # The u1 is removed from mysql.user which means that DROP USER 189--echo # can be failed partially. 190--echo # 191SELECT user FROM mysql.user where user='u1'; 192--echo # 193--echo # Verify that the partially failed DROP USER statement can consume 194--echo # its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 195--echo # mysql.gtid_executed table when binlog is disabled as it did when 196--echo # binlog is enabled. 197--echo # 198--replace_result $master_uuid MASTER_UUID 199--let $assert_text= Committed gtid MASTER_UUID:9 into @@GLOBAL.GTID_EXECUTED 200--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9" 201--source include/assert.inc 202--let $assert_text= Saved gtid MASTER_UUID:9 into mysql.gtid_executed table 203--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 9]" = 1 204--source include/assert.inc 205 206--replace_result $master_uuid MASTER_UUID 207--eval SET SESSION GTID_NEXT='$master_uuid:10' 208CREATE VIEW v1 as SELECT 1; 209 210# Check-7: DROP VIEW 211--replace_result $master_uuid MASTER_UUID 212--eval SET SESSION GTID_NEXT='$master_uuid:11' 213--error ER_BAD_TABLE_ERROR 214DROP VIEW v1, v2; 215--echo # 216--echo # The view v1 was dropped, which means DROP VIEW can be failed 217--echo # partially when dropping multiple views. 218--echo # 219--error ER_NO_SUCH_TABLE 220SHOW CREATE VIEW v1; 221--echo # 222--echo # Verify that the partially failed DROP VIEW statement can consume 223--echo # its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 224--echo # mysql.gtid_executed table when binlog is disabled as it did when 225--echo # binlog is enabled. 226--echo # 227--replace_result $master_uuid MASTER_UUID 228--let $assert_text= Committed gtid MASTER_UUID:11 into @@GLOBAL.GTID_EXECUTED 229--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-11" 230--source include/assert.inc 231--let $assert_text= Saved gtid MASTER_UUID:11 into mysql.gtid_executed table 232--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 11]" = 1 233--source include/assert.inc 234 235# Check-8: RENAME TABLE 236--replace_result $master_uuid MASTER_UUID 237--eval SET SESSION GTID_NEXT='$master_uuid:12' 238--error ER_FILE_NOT_FOUND 239RENAME TABLE t1 TO t2, t3 TO t4; 240--echo # 241--echo # The table t1 was not renamed, which means RENAME TABLE 242--echo # can not be failed partially. 243--echo # 244SHOW CREATE TABLE t1; 245--echo # 246--echo # Verify that the failed RENAME TABLE statement does not save 247--echo # the specified gtid into @@GLOBAL.GTID_EXECUTED and 248--echo # mysql.gtid_executed table when binlog is disabled as it did 249--echo # when binlog is enabled. 250--echo # 251--replace_result $master_uuid MASTER_UUID 252--let $assert_text= Did not commit gtid MASTER_UUID:12 into @@GLOBAL.GTID_EXECUTED 253--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-11" 254--source include/assert.inc 255--let $assert_text= Did not save gtid MASTER_UUID:12 into mysql.gtid_executed table 256--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 12]" = 0 257--source include/assert.inc 258 259# Check-9: OPTIMIZE TABLE 260--echo # 261--echo # The OPTIMIZE TABLE statement can be failed partially when optimizing 262--echo # multiple tables, which contain a non-existent table. 263--echo # 264--replace_result $master_uuid MASTER_UUID 265--eval SET SESSION GTID_NEXT='$master_uuid:12' 266OPTIMIZE TABLE t1, t_non_existent; 267--echo # 268--echo # When optimizing a non-existent table, verify that the OPTIMIZE TABLE 269--echo # statement saves its specified gtid into @@GLOBAL.GTID_EXECUTED and 270--echo # mysql.gtid_executed table when binlog is disabled as it did 271--echo # when binlog is enabled. 272--echo # 273--replace_result $master_uuid MASTER_UUID 274--let $assert_text= Committed gtid MASTER_UUID:12 into @@GLOBAL.GTID_EXECUTED 275--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-12" 276--source include/assert.inc 277--let $assert_text= Saved gtid MASTER_UUID:12 into mysql.gtid_executed table 278--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 12]" = 1 279--source include/assert.inc 280 281# Check-10: ANALYZE TABLE 282--echo # 283--echo # The ANALYZE TABLE statement can be failed partially when analyzing 284--echo # multiple tables, which contain a non-existent table. 285--echo # 286--replace_result $master_uuid MASTER_UUID 287--eval SET SESSION GTID_NEXT='$master_uuid:13' 288ANALYZE TABLE t1, t_non_existent; 289--echo # 290--echo # When analyzing a non-existent table, verify that the ANALYZE TABLE 291--echo # statement saves its specified gtid into @@GLOBAL.GTID_EXECUTED and 292--echo # mysql.gtid_executed table when binlog is disabled as it did 293--echo # when binlog is enabled. 294--echo # 295--replace_result $master_uuid MASTER_UUID 296--let $assert_text= Committed gtid MASTER_UUID:13 into @@GLOBAL.GTID_EXECUTED 297--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-13" 298--source include/assert.inc 299--let $assert_text= Saved gtid MASTER_UUID:13 into mysql.gtid_executed table 300--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 13]" = 1 301--source include/assert.inc 302 303# Check-11: REPAIR TABLE 304--echo # 305--echo # The REPAIR TABLE statement can be failed partially when repairing 306--echo # multiple tables, which contain a non-existent table. 307--echo # 308--replace_result $master_uuid MASTER_UUID 309--eval SET SESSION GTID_NEXT='$master_uuid:14' 310REPAIR TABLE t1, t_non_existent; 311--echo # 312--echo # When repairing a non-existent table, verify that the REPAIR TABLE 313--echo # statement saves its specified gtid into @@GLOBAL.GTID_EXECUTED and 314--echo # mysql.gtid_executed table when binlog is disabled as it did 315--echo # when binlog is enabled. 316--echo # 317--replace_result $master_uuid MASTER_UUID 318--let $assert_text= Committed gtid MASTER_UUID:14 into @@GLOBAL.GTID_EXECUTED 319--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-14" 320--source include/assert.inc 321--let $assert_text= Saved gtid MASTER_UUID:13 into mysql.gtid_executed table 322--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 14]" = 1 323--source include/assert.inc 324 325# Check-12: CHECKSUM TABLE 326--echo # 327--echo # The CHECKSUM TABLE statement can be failed partially when checksuming 328--echo # multiple tables, which contain a non-existent table. 329--echo # 330--replace_result $master_uuid MASTER_UUID 331--eval SET SESSION GTID_NEXT='$master_uuid:15' 332CHECKSUM TABLE t1, t_non_existent; 333--echo # 334--echo # When checksuming a non-existent table, verify that the CHECKSUM TABLE 335--echo # statement does not save its specified gtid into @@GLOBAL.GTID_EXECUTED 336--echo # and mysql.gtid_executed table when binlog is disabled as it did 337--echo # when binlog is enabled. 338--echo # 339--replace_result $master_uuid MASTER_UUID 340--let $assert_text= Did not commit gtid MASTER_UUID:15 into @@GLOBAL.GTID_EXECUTED 341--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-14" 342--source include/assert.inc 343--let $assert_text= Did not save gtid MASTER_UUID:15 into mysql.gtid_executed table 344--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 15]" = 0 345--source include/assert.inc 346 347# Check-13: CREATE USER 348--replace_result $master_uuid MASTER_UUID 349--eval SET SESSION GTID_NEXT='$master_uuid:16' 350CREATE USER u1@h; 351--replace_result $master_uuid MASTER_UUID 352--eval SET SESSION GTID_NEXT='$master_uuid:17' 353--error ER_CANNOT_USER 354CREATE USER u1@h, u2@h; 355--echo # 356--echo # The u2 is created into mysql.user which means that CREATE USER 357--echo # can be failed partially. 358--echo # 359SELECT user FROM mysql.user where user='u2'; 360--replace_result $master_uuid MASTER_UUID 361--eval SET SESSION GTID_NEXT='$master_uuid:18' 362--error ER_CANNOT_USER 363CREATE USER u1@h, u2@h; 364--echo # 365--echo # Verify that the partially failed CREATE USER statement can consume 366--echo # its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 367--echo # mysql.gtid_executed table when binlog is disabled as it did when 368--echo # binlog is enabled. 369--echo # 370--replace_result $master_uuid MASTER_UUID 371--let $assert_text= Committed gtid MASTER_UUID:17 into @@GLOBAL.GTID_EXECUTED 372--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-14:16-17" 373--source include/assert.inc 374--let $assert_text= Saved gtid MASTER_UUID:17 into mysql.gtid_executed table 375--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 17]" = 1 376--source include/assert.inc 377--let $assert_text= Did not save gtid MASTER_UUID:18 into mysql.gtid_executed table 378--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 18]" = 0 379--source include/assert.inc 380 381# Check-14: RENAME USER 382--replace_result $master_uuid MASTER_UUID 383--eval SET SESSION GTID_NEXT='$master_uuid:19' 384--error ER_CANNOT_USER 385RENAME USER u1@h TO u11@h, u3@h TO u33@h; 386--echo # 387--echo # The u1 is renamed on mysql.user which means that RENAME USER 388--echo # can be failed partially. 389--echo # 390SELECT user FROM mysql.user where user='u11'; 391--replace_result $master_uuid MASTER_UUID 392--eval SET SESSION GTID_NEXT='$master_uuid:20' 393--error ER_CANNOT_USER 394RENAME USER u1@h TO u11@h, u3@h TO u33@h; 395--echo # 396--echo # Verify that the partially failed RENAME USER statement can consume 397--echo # its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 398--echo # mysql.gtid_executed table when binlog is disabled as it did when 399--echo # binlog is enabled. 400--echo # 401--replace_result $master_uuid MASTER_UUID 402--let $assert_text= Committed gtid MASTER_UUID:19 into @@GLOBAL.GTID_EXECUTED 403--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-14:16-17:19" 404--source include/assert.inc 405--let $assert_text= Saved gtid MASTER_UUID:19 into mysql.gtid_executed table 406--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 19]" = 1 407--source include/assert.inc 408--let $assert_text= Did not save gtid MASTER_UUID:20 into mysql.gtid_executed table 409--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 20]" = 0 410--source include/assert.inc 411 412# Check-15: ALTER USER 413SELECT password_expired FROM mysql.user where user='u11'; 414--replace_result $master_uuid MASTER_UUID 415--eval SET SESSION GTID_NEXT='$master_uuid:21' 416--error ER_CANNOT_USER 417ALTER USER u11@h, u3@h PASSWORD EXPIRE; 418--echo # 419--echo # The u11 is altered on mysql.user which means that ALTER USER 420--echo # can be failed partially. 421--echo # 422SELECT password_expired FROM mysql.user where user='u11'; 423--replace_result $master_uuid MASTER_UUID 424--eval SET SESSION GTID_NEXT='$master_uuid:22' 425--error ER_CANNOT_USER 426ALTER USER u1@h, u3@h PASSWORD EXPIRE; 427--echo # 428--echo # Verify that the partially failed ALTER USER statement can consume 429--echo # its gtid and save the gtid into @@GLOBAL.GTID_EXECUTED and 430--echo # mysql.gtid_executed table when binlog is disabled as it did when 431--echo # binlog is enabled. 432--echo # 433--replace_result $master_uuid MASTER_UUID 434--let $assert_text= Committed gtid MASTER_UUID:21 into @@GLOBAL.GTID_EXECUTED 435--let $assert_cond= "[SELECT @@GLOBAL.GTID_EXECUTED]" = "$master_uuid:1-6:9-14:16-17:19:21" 436--source include/assert.inc 437--let $assert_text= Saved gtid MASTER_UUID:21 into mysql.gtid_executed table 438--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 21]" = 1 439--source include/assert.inc 440--let $assert_text= Did not save gtid MASTER_UUID:22 into mysql.gtid_executed table 441--let $assert_cond= "[SELECT COUNT(*) FROM mysql.gtid_executed WHERE interval_end = 22]" = 0 442--source include/assert.inc 443--replace_result $master_uuid MASTER_UUID 444--eval SET SESSION GTID_NEXT='$master_uuid:23' 445DROP USER u11@h, u2@h; 446 447# Clean up 448--replace_result $master_uuid MASTER_UUID 449--eval SET SESSION GTID_NEXT='$master_uuid:24' 450DROP TABLE t1; 451 452