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
|
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
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
SELECT * FROM t4;
id name number
1 XXX 12345
2 XXY 12345
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
SELECT * FROM t4;
id name number
4 XXX 12345
5 XXY 12345
FLUSH LOGS;
FLUSH LOGS;
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;
-------- switch to 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=latin1
-------- switch to 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=MyISAM DEFAULT CHARSET=latin1
######### Must return no rows here #########
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
0
-------- switch to 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;
-------- switch to master --------
FLUSH LOGS;
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);
-------- switch to master1 --------
RENAME TABLE t1 TO t3, t2 TO t1;;
-------- switch to master --------
COMMIT;
-------- switch to master1 --------
-------- switch to master --------
SELECT * FROM t1;
id b
SELECT * FROM t3;
id b
1 1
2 2
3 3
4 4
-------- switch to slave --------
SELECT * FROM t1;
id b
SELECT * FROM t3;
id b
1 1
2 2
3 3
4 4
-------- switch to master --------
DROP TABLE t1;
DROP TABLE t3;
End of 6.0 tests
|
