1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
include/master-slave.inc
[connection master]
connection master;
CREATE TABLE t4 (
id INT(5) unsigned NOT NULL auto_increment,
name varchar(15) NOT NULL default '',
number varchar(35) NOT NULL default 'default',
PRIMARY KEY (id),
UNIQUE KEY unique_rec (name,number)
) ENGINE=InnoDB;
LOAD DATA
INFILE '../../std_data/loaddata_pair.dat'
REPLACE INTO TABLE t4
(name,number);
SELECT * FROM t4;
id name number
1 XXX 12345
2 XXY 12345
connection slave;
SELECT * FROM t4;
id name number
1 XXX 12345
2 XXY 12345
connection master;
LOAD DATA
INFILE '../../std_data/loaddata_pair.dat'
REPLACE INTO TABLE t4
(name,number);
SELECT * FROM t4;
id name number
4 XXX 12345
5 XXY 12345
connection slave;
SELECT * FROM t4;
id name number
4 XXX 12345
5 XXY 12345
connection master;
connection slave;
connection master;
FLUSH LOGS;
connection slave;
FLUSH LOGS;
connection master;
DROP DATABASE IF EXISTS mysqltest1;
CREATE DATABASE mysqltest1;
CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT);
CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB";
SET AUTOCOMMIT = 0;
connection slave;
connection slave;
ALTER TABLE mysqltest1.t1 ENGINE = MyISAM;
SHOW CREATE TABLE mysqltest1.t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f1` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
connection master;
INSERT INTO mysqltest1.t1 SET f1= 1;
DROP TEMPORARY TABLE mysqltest1.tmp;
ROLLBACK;
SHOW CREATE TABLE mysqltest1.tmp;
ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
######### Must return no rows here #########
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
0
INSERT INTO mysqltest1.t1 SET f1= 2;
CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT);
ROLLBACK;
SHOW CREATE TABLE mysqltest1.tmp2;
Table Create Table
tmp2 CREATE TEMPORARY TABLE `tmp2` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
######### Must return no rows here #########
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
0
connection slave;
connection slave;
SHOW CREATE TABLE mysqltest1.tmp;
ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
SHOW CREATE TABLE mysqltest1.tmp2;
ERROR 42S02: Table 'mysqltest1.tmp2' doesn't exist
######### t1 has two rows here: the transaction not rolled back since t1 uses MyISAM #########
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
2
FLUSH LOGS;
connection master;
FLUSH LOGS;
DROP TEMPORARY TABLE IF EXISTS mysqltest1.tmp2;
DROP DATABASE mysqltest1;
End of 5.1 tests
#
# Bug#39675 rename tables on innodb tables with pending
# transactions causes slave data issue.
#
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (
id INT PRIMARY KEY auto_increment,
b INT DEFAULT NULL
) ENGINE=InnoDB;
CREATE TABLE t2 (
id INT PRIMARY KEY auto_increment,
b INT DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO t1 (b) VALUES (1),(2),(3);
BEGIN;
INSERT INTO t1(b) VALUES (4);
connection master1;
RENAME TABLE t1 TO t3, t2 TO t1;;
connection master;
COMMIT;
connection master1;
connection master;
SELECT * FROM t1;
id b
SELECT * FROM t3;
id b
1 1
2 2
3 3
4 4
connection slave;
connection slave;
SELECT * FROM t1;
id b
SELECT * FROM t3;
id b
1 1
2 2
3 3
4 4
connection master;
DROP TABLE t1;
DROP TABLE t3;
End of 6.0 tests
include/rpl_end.inc
|
