Debian Codegraph @

mysql-test/suite/rpl_nogtid/r/rpl_mix_innodb.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 CREATE TABLE t4 (
7 id INT(5) unsigned NOT NULL auto_increment,
8 name varchar(15) NOT NULL default '',
9 number varchar(35) NOT NULL default 'default',
10 PRIMARY KEY (id),
11 UNIQUE KEY unique_rec (name,number)
12 ) ENGINE=InnoDB;
13 Warnings:
14 Warning 1681 Integer display width is deprecated and will be removed in a future release.
15 LOAD DATA
16 INFILE '../../std_data/loaddata_pair.dat'
17 REPLACE INTO TABLE t4
18 (name,number);
19 SELECT * FROM t4;
20 id name number
21 1 XXX 12345
22 2 XXY 12345
23 SELECT * FROM t4;
24 id name number
25 1 XXX 12345
26 2 XXY 12345
27 LOAD DATA
28 INFILE '../../std_data/loaddata_pair.dat'
29 REPLACE INTO TABLE t4
30 (name,number);
31 SELECT * FROM t4;
32 id name number
33 4 XXX 12345
34 5 XXY 12345
35 SELECT * FROM t4;
36 id name number
37 4 XXX 12345
38 5 XXY 12345
39 FLUSH LOGS;
40 FLUSH LOGS;
41 DROP DATABASE IF EXISTS mysqltest1;
42 CREATE DATABASE mysqltest1;
43 CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT);
44 CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB";
45 SET AUTOCOMMIT = 0;
46 -------- switch to slave --------
47 ALTER TABLE mysqltest1.t1 ENGINE = MyISAM;
48 SHOW CREATE TABLE mysqltest1.t1;
49 Table Create Table
50 t1 CREATE TABLE `t1` (
51 `f1` bigint DEFAULT NULL
52 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
53 -------- switch to master --------
54 INSERT INTO mysqltest1.t1 SET f1= 1;
55 DROP TEMPORARY TABLE mysqltest1.tmp;
56 ROLLBACK;
57 Warnings:
58 Warning # Some temporary tables were dropped, but these operations could not be rolled back.
59 SHOW CREATE TABLE mysqltest1.tmp;
60 ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
61 ######### Must return no rows here #########
62 SELECT COUNT(*) FROM mysqltest1.t1;
63 COUNT(*)
64 0
65 INSERT INTO mysqltest1.t1 SET f1= 2;
66 CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT);
67 ROLLBACK;
68 Warnings:
69 Warning # The creation of some temporary tables could not be rolled back.
70 SHOW CREATE TABLE mysqltest1.tmp2;
71 Table Create Table
72 tmp2 CREATE TEMPORARY TABLE `tmp2` (
73 `a` int DEFAULT NULL
74 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
75 ######### Must return no rows here #########
76 SELECT COUNT(*) FROM mysqltest1.t1;
77 COUNT(*)
78 0
79 -------- switch to slave --------
80 SHOW CREATE TABLE mysqltest1.tmp;
81 ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
82 SHOW CREATE TABLE mysqltest1.tmp2;
83 ERROR 42S02: Table 'mysqltest1.tmp2' doesn't exist
84 ######### for SBR, t1 has two rows here: the transaction not rolled back since t1 uses MyISAM #########
85 ######### for MBR, t1 has zero rows here, as CREATE/DROP TEMPORARY TABLE were not binlogged #########
86 SELECT COUNT(*) FROM mysqltest1.t1;
87 COUNT(*)
88 0
89 FLUSH LOGS;
90 -------- switch to master --------
91 FLUSH LOGS;
92 DROP TEMPORARY TABLE IF EXISTS mysqltest1.tmp2;
93 DROP DATABASE mysqltest1;
94 End of 5.1 tests
95 #
96 # Bug#39675 rename tables on innodb tables with pending
97 # transactions causes slave data issue.
98 #
99 DROP TABLE IF EXISTS t1;
100 DROP TABLE IF EXISTS t2;
101 DROP TABLE IF EXISTS t3;
102 CREATE TABLE t1 (
103 id INT PRIMARY KEY auto_increment,
104 b INT DEFAULT NULL
105 ) ENGINE=InnoDB;
106 CREATE TABLE t2 (
107 id INT PRIMARY KEY auto_increment,
108 b INT DEFAULT NULL
109 ) ENGINE=InnoDB;
110 INSERT INTO t1 (b) VALUES (1),(2),(3);
111 BEGIN;
112 INSERT INTO t1(b) VALUES (4);
113 -------- switch to master1 --------
114 RENAME TABLE t1 TO t3, t2 TO t1;;
115 -------- switch to master --------
116 COMMIT;
117 -------- switch to master1 --------
118 -------- switch to master --------
119 SELECT * FROM t1;
120 id b
121 SELECT * FROM t3;
122 id b
123 1 1
124 2 2
125 3 3
126 4 4
127 -------- switch to slave --------
128 SELECT * FROM t1;
129 id b
130 SELECT * FROM t3;
131 id b
132 1 1
133 2 2
134 3 3
135 4 4
136 -------- switch to master --------
137 DROP TABLE t1;
138 DROP TABLE t3;
139 End of 6.0 tests
140 include/rpl_end.inc