Debian Codegraph @

mysql-test/suite/rpl/include/rpl_innodb.test

raw
1 #
2 # Bug#11401: Load data infile 'REPLACE INTO' fails on slave.
3 #
4 connection master;
5 CREATE TABLE t4 (
6 id INT(5) unsigned NOT NULL auto_increment,
7 name varchar(15) NOT NULL default '',
8 number varchar(35) NOT NULL default 'default',
9 PRIMARY KEY (id),
10 UNIQUE KEY unique_rec (name,number)
11 ) ENGINE=InnoDB;
12
13 --disable_warnings
14 LOAD DATA
15 INFILE '../../std_data/loaddata_pair.dat'
16 REPLACE INTO TABLE t4
17 (name,number);
18 --enable_warnings
19 SELECT * FROM t4;
20
21 sync_slave_with_master;
22 SELECT * FROM t4;
23
24 connection master;
25 --disable_warnings
26 LOAD DATA
27 INFILE '../../std_data/loaddata_pair.dat'
28 REPLACE INTO TABLE t4
29 (name,number);
30 --enable_warnings
31 SELECT * FROM t4;
32
33 sync_slave_with_master;
34 SELECT * FROM t4;
35
36 connection master;
37 --disable_query_log
38 DROP TABLE t4;
39 --enable_query_log
40 sync_slave_with_master;
41 connection master;
42
43 # End of 4.1 tests
44
45 #
46 # Bug #26418: Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK
47 # on master
48 #
49 #Note Matthias: to be merged to rpl_ddl.test
50
51 FLUSH LOGS;
52 sync_slave_with_master;
53 FLUSH LOGS;
54 connection master;
55 let $engine_type= "InnoDB";
56
57 --disable_warnings
58 DROP DATABASE IF EXISTS mysqltest1;
59 --enable_warnings
60
61 CREATE DATABASE mysqltest1;
62 CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT);
63 eval CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE=$engine_type;
64 SET AUTOCOMMIT = 0;
65
66 sync_slave_with_master;
67 connection slave;
68
69 # We want to verify that the following transactions are written to the
70 # binlog, despite the transaction is rolled back. (They should be
71 # written to the binlog since they contain non-transactional DROP
72 # TEMPORARY TABLE). To see that, we use the auxiliary table t1, which
73 # is transactional (InnoDB) on master and MyISAM on slave. t1 should
74 # be transactional on master so that the insert into t1 does not cause
75 # the transaction to be logged. Since t1 is non-transactional on
76 # slave, the change will not be rolled back, so the inserted rows will
77 # stay in t1 and we can verify that the transaction was replicated.
78 ALTER TABLE mysqltest1.t1 ENGINE = MyISAM;
79 SHOW CREATE TABLE mysqltest1.t1;
80
81 connection master;
82 INSERT INTO mysqltest1.t1 SET f1= 1;
83 DROP TEMPORARY TABLE mysqltest1.tmp;
84 ROLLBACK;
85 --error ER_NO_SUCH_TABLE
86 SHOW CREATE TABLE mysqltest1.tmp;
87 --echo ######### Must return no rows here #########
88 SELECT COUNT(*) FROM mysqltest1.t1;
89
90 INSERT INTO mysqltest1.t1 SET f1= 2;
91 CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT);
92 ROLLBACK;
93 --replace_result MyISAM InnoDB
94 SHOW CREATE TABLE mysqltest1.tmp2;
95 --echo ######### Must return no rows here #########
96 SELECT COUNT(*) FROM mysqltest1.t1;
97
98 sync_slave_with_master;
99 connection slave;
100 --error ER_NO_SUCH_TABLE
101 SHOW CREATE TABLE mysqltest1.tmp;
102 --error ER_NO_SUCH_TABLE
103 SHOW CREATE TABLE mysqltest1.tmp2;
104 --echo ######### t1 has two rows here: the transaction not rolled back since t1 uses MyISAM #########
105 SELECT COUNT(*) FROM mysqltest1.t1;
106 FLUSH LOGS;
107
108 connection master;
109 FLUSH LOGS;
110 DROP TEMPORARY TABLE IF EXISTS mysqltest1.tmp2;
111 DROP DATABASE mysqltest1;
112
113 --echo End of 5.1 tests
114
115 --echo #
116 --echo # Bug#39675 rename tables on innodb tables with pending
117 --echo # transactions causes slave data issue.
118 --echo #
119
120 --disable_warnings
121 DROP TABLE IF EXISTS t1;
122 DROP TABLE IF EXISTS t2;
123 DROP TABLE IF EXISTS t3;
124 --enable_warnings
125
126 CREATE TABLE t1 (
127 id INT PRIMARY KEY auto_increment,
128 b INT DEFAULT NULL
129 ) ENGINE=InnoDB;
130
131 CREATE TABLE t2 (
132 id INT PRIMARY KEY auto_increment,
133 b INT DEFAULT NULL
134 ) ENGINE=InnoDB;
135
136 INSERT INTO t1 (b) VALUES (1),(2),(3);
137
138 BEGIN;
139 INSERT INTO t1(b) VALUES (4);
140
141 connection master1;
142 --send RENAME TABLE t1 TO t3, t2 TO t1;
143
144 connection master;
145 # Need to wait until RENAME is received
146 let $wait_condition=
147 SELECT COUNT(*) = 1 FROM information_schema.processlist
148 WHERE info = "RENAME TABLE t1 TO t3, t2 TO t1" and
149 state = "Waiting for table metadata lock";
150 --source include/wait_condition.inc
151
152 COMMIT;
153
154 connection master1;
155 --reap
156
157 connection master;
158 SELECT * FROM t1;
159 SELECT * FROM t3;
160
161 sync_slave_with_master;
162
163 connection slave;
164 SELECT * FROM t1;
165 SELECT * FROM t3;
166
167 connection master;
168 DROP TABLE t1;
169 DROP TABLE t3;
170
171 --echo End of 6.0 tests