| 1 |
################################################################# |
| 2 |
# Bug#18258933 CONCATENATION OF MYSQLBINLOG OUTPUT DOES NOT |
| 3 |
# WORK WITH GTID |
| 4 |
|
| 5 |
# Problem: When mysqlbinlog processes and concatenates |
| 6 |
# multiple binary log files into one output file, this |
| 7 |
# output file is not in useful state. When it is later |
| 8 |
# used for point-in-time recovery, it is producing |
| 9 |
# ER_GTID_NEXT_TYPE_UNDEFINED_GTID "ERROR 1837: When |
| 10 |
# @@SESSION.GTID_NEXT is set to a GTID, you must explicitly |
| 11 |
# set it to a different value after a COMMIT or ROLLBACK. |
| 12 |
# Please check GTID_NEXT variable manual page for detailed |
| 13 |
# explanation. Current @@SESSION.GTID_NEXT is 'xyz'" |
| 14 |
|
| 15 |
# Solution: When mysqlbinlog processes second binary log's |
| 16 |
# start_log_event, it can output |
| 17 |
# "SET GTID_NEXT=AUTOMATIC" if required i.e., if |
| 18 |
# previous binary log is leaving the GTID_NEXT state |
| 19 |
# in 'undefined' state. |
| 20 |
|
| 21 |
# Steps to verify the case: |
| 22 |
# 1) On a Gtid enabled server, generate some dummy statements |
| 23 |
# in binary log ( to generate GTID_NEXT events in binary log 1) |
| 24 |
# 2) Restart server with gtid-mode OFF |
| 25 |
# 3) On a Gtid disabled sever, generate some dummy statements |
| 26 |
# in binary log ( to generate anonymous events in binary log 2) |
| 27 |
# 4) Restart server again with gtid-mode ON. |
| 28 |
# 5) On a Gtid enabled server, generate some dummy statements |
| 29 |
# in binary log ( to generate GTID_NEXT events in binary log 3) |
| 30 |
# 6) Concat all three binary logs 1,2 and 3 and apply the generated |
| 31 |
# sql against mysql command line tool and it should not cause |
| 32 |
# any error (i.e., no ER_GTID_NEXT_TYPE_UNDEFINED_GTID error) |
| 33 |
################################################################# |
| 34 |
--source include/have_binlog_format_statement.inc |
| 35 |
|
| 36 |
# Use this so that we can use rpl_restart_server.inc later. |
| 37 |
--let $rpl_server_count= 1 |
| 38 |
--let $rpl_topology= none |
| 39 |
--source include/rpl_init.inc |
| 40 |
--source include/rpl_default_connections.inc |
| 41 |
|
| 42 |
|
| 43 |
#Initial setup |
| 44 |
--let $datadir= `SELECT @@datadir` |
| 45 |
--let $binlog_file1= query_get_value(SHOW MASTER STATUS, File, 1) |
| 46 |
|
| 47 |
# Step 1 : Dummy statements to generate gtid transactions (gtid-mode is ON) |
| 48 |
# in binlog_file1 |
| 49 |
CREATE TABLE t1(i INT); |
| 50 |
INSERT INTO t1 values (1); |
| 51 |
|
| 52 |
# Step 2: Restart server with gtid-mode off (binlog will be rotated) |
| 53 |
--let $rpl_server_number= 1 |
| 54 |
--let $rpl_server_parameters= --gtid-mode=off |
| 55 |
--source include/rpl_restart_server.inc |
| 56 |
--let $binlog_file2= query_get_value(SHOW MASTER STATUS, File, 1) |
| 57 |
|
| 58 |
# Step 3: Dummy statements to generate anonymous transactions |
| 59 |
# (gtid-mode is OFF) |
| 60 |
CREATE TABLE t2(i INT); |
| 61 |
INSERT INTO t2 values (2); |
| 62 |
|
| 63 |
# Step 4: Restart server with gtid-mode ON (binlog will be rotated) |
| 64 |
--let $rpl_server_number= 1 |
| 65 |
--let $rpl_server_parameters= --gtid-mode=on |
| 66 |
--source include/rpl_restart_server.inc |
| 67 |
--let $binlog_file3= query_get_value(SHOW MASTER STATUS, File, 1) |
| 68 |
|
| 69 |
# Step 5 : Dummy statements to generate gtid transactions (gtid-mode is ON) |
| 70 |
# in binlog_file3 |
| 71 |
CREATE TABLE t3(i INT); |
| 72 |
INSERT INTO t3 values (3); |
| 73 |
|
| 74 |
# Clean up command (let DROP TABLE be in binary log 4, when we apply |
| 75 |
# all three binary logs, we can select the data and verify that the |
| 76 |
# data is there. |
| 77 |
FLUSH LOGS; |
| 78 |
DROP TABLE t1,t2,t3; |
| 79 |
|
| 80 |
# Step 6: Combine binlog_file1, binlog_file2, binlog_file3 and generate |
| 81 |
# a .sql file. |
| 82 |
--exec $MYSQL_BINLOG --force-if-open $datadir/$binlog_file1 > $MYSQLTEST_VARDIR/tmp/concat_three_files.sql |
| 83 |
--exec $MYSQL_BINLOG --force-if-open --skip-gtids $datadir/$binlog_file2 >> $MYSQLTEST_VARDIR/tmp/concat_three_files.sql |
| 84 |
--exec $MYSQL_BINLOG --force-if-open $datadir/$binlog_file3 >> $MYSQLTEST_VARDIR/tmp/concat_three_files.sql |
| 85 |
|
| 86 |
# Execute reset master, so that when we are applying gtid transactions, |
| 87 |
# they should not skipped. |
| 88 |
RESET MASTER; |
| 89 |
|
| 90 |
# Step 7: Apply the generated .sql through mysql command line tool |
| 91 |
# and it should not cause any issues |
| 92 |
--exec $MYSQL --user=root --host=127.0.0.1 --port=$MASTER_MYPORT < $MYSQLTEST_VARDIR/tmp/concat_three_files.sql |
| 93 |
|
| 94 |
# Step 8: Verify the data in t1 and t2 |
| 95 |
# [NOTE] there will not be table t3 created because CREATE TABLE t2, INSERT INTO t2 |
| 96 |
# (originally anonymous transactions) now occupies gtid numbers 3 and 4, |
| 97 |
# hence CREATE TABLE t3, INSERT INTO t3(which has originally gtid numbers 3 |
| 98 |
# and 4) will be skipped when we are reapplying them. |
| 99 |
SELECT * FROM t1; |
| 100 |
SELECT * FROM t2; |
| 101 |
|
| 102 |
# Clean up |
| 103 |
DROP TABLE t1,t2; |
| 104 |
--remove_file $MYSQLTEST_VARDIR/tmp/concat_three_files.sql |