rpl_stm_innodb.result 2.9 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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
msvensson@pilot.mysql.com's avatar
msvensson@pilot.mysql.com committed
15
INFILE '../../std_data/loaddata_pair.dat'  
16 17 18 19 20 21 22 23 24 25 26
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
msvensson@pilot.mysql.com's avatar
msvensson@pilot.mysql.com committed
27
INFILE '../../std_data/loaddata_pair.dat'  
28 29 30 31
REPLACE INTO TABLE t4
(name,number);
SELECT * FROM t4;
id	name	number
32 33
4	XXX	12345
5	XXY	12345
34 35
SELECT * FROM t4;
id	name	number
36 37
4	XXX	12345
5	XXY	12345
38 39 40 41 42 43 44 45
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 --------
46
ALTER TABLE mysqltest1.t1 ENGINE = MyISAM;
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
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
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
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
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
2
FLUSH LOGS;
-------- switch to master --------
FLUSH LOGS;
DROP DATABASE mysqltest1;
End of 5.1 tests
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
#
# 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