Debian Codegraph @

mysql-test/suite/rpl/r/rpl_partition_innodb.result

raw
1 include/master-slave.inc
2 [connection master]
3 use test;
4 CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT,
5 dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
6 CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
7 fkid INT, filler VARCHAR(255),
8 PRIMARY KEY(id))
9 ENGINE='InnoDB';
10 CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT,
11 dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
12 CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
13 fkid INT, filler VARCHAR(255),
14 PRIMARY KEY(id))
15 ENGINE='InnoDB'
16 PARTITION BY RANGE(id)
17 (PARTITION pa100 values less than (100),
18 PARTITION paMax values less than MAXVALUE);
19 CREATE PROCEDURE test.proc_norm()
20 BEGIN
21 DECLARE ins_count INT DEFAULT 99;
22 DECLARE cur_user VARCHAR(255);
23 DECLARE local_uuid VARCHAR(255);
24 SET cur_user= "current_user@localhost";
25 SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
26 WHILE ins_count > 0 DO
27 # Must use local variables for statment based replication
28 INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
29 ins_count,'Non partitioned table! Going to test replication for MySQL');
30 SET ins_count = ins_count - 1;
31 END WHILE;
32 END|
33 CREATE PROCEDURE test.proc_byrange()
34 BEGIN
35 DECLARE ins_count INT DEFAULT 200;
36 DECLARE cur_user VARCHAR(255);
37 DECLARE local_uuid VARCHAR(255);
38 SET cur_user= "current_user@localhost";
39 SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
40 WHILE ins_count > 0 DO
41 INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
42 ins_count + 100,'Partitioned table! Going to test replication for MySQL');
43 SET ins_count = ins_count - 1;
44 END WHILE;
45 END|
46 CALL test.proc_norm();
47 SELECT count(*) as "Master regular" FROM test.regular_tbl;
48 Master regular
49 99
50 CALL test.proc_byrange();
51 SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
52 Master byrange
53 200
54 show create table test.byrange_tbl;
55 Table Create Table
56 byrange_tbl CREATE TABLE `byrange_tbl` (
57 `id` int(11) NOT NULL AUTO_INCREMENT,
58 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
59 `user` char(255) DEFAULT NULL,
60 `uuidf` varbinary(255) DEFAULT NULL,
61 `fkid` int(11) DEFAULT NULL,
62 `filler` varchar(255) DEFAULT NULL,
63 PRIMARY KEY (`id`)
64 ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
65 PARTITION BY RANGE (`id`)
66 (PARTITION `pa100` VALUES LESS THAN (100) ENGINE = InnoDB,
67 PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
68 show create table test.regular_tbl;
69 Table Create Table
70 regular_tbl CREATE TABLE `regular_tbl` (
71 `id` int(11) NOT NULL AUTO_INCREMENT,
72 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
73 `user` char(255) DEFAULT NULL,
74 `uuidf` varbinary(255) DEFAULT NULL,
75 `fkid` int(11) DEFAULT NULL,
76 `filler` varchar(255) DEFAULT NULL,
77 PRIMARY KEY (`id`)
78 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
79 ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl;
80 SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
81 id dt user uuidf fkid filler
82 99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
83 98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
84 SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
85 id dt user uuidf fkid filler
86 100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
87 101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
88 SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
89 id dt user uuidf fkid filler
90 99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
91 98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
92 SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
93 id dt user uuidf fkid filler
94 1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
95 2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
96 connection slave;
97 connection slave;
98 show create table test.byrange_tbl;
99 Table Create Table
100 byrange_tbl CREATE TABLE `byrange_tbl` (
101 `id` int(11) NOT NULL AUTO_INCREMENT,
102 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
103 `user` char(255) DEFAULT NULL,
104 `uuidf` varbinary(255) DEFAULT NULL,
105 `fkid` int(11) DEFAULT NULL,
106 `filler` varchar(255) DEFAULT NULL,
107 PRIMARY KEY (`id`)
108 ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
109 PARTITION BY RANGE (`id`)
110 (PARTITION `pa100` VALUES LESS THAN (100) ENGINE = InnoDB,
111 PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
112 show create table test.regular_tbl;
113 Table Create Table
114 regular_tbl CREATE TABLE `regular_tbl` (
115 `id` int(11) NOT NULL AUTO_INCREMENT,
116 `dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
117 `user` char(255) DEFAULT NULL,
118 `uuidf` varbinary(255) DEFAULT NULL,
119 `fkid` int(11) DEFAULT NULL,
120 `filler` varchar(255) DEFAULT NULL,
121 PRIMARY KEY (`id`)
122 ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
123 SELECT count(*) "Slave norm" FROM test.regular_tbl;
124 Slave norm
125 99
126 SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
127 Slave byrange
128 200
129 SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
130 id dt user uuidf fkid filler
131 99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
132 98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
133 SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
134 id dt user uuidf fkid filler
135 100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
136 101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
137 SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
138 id dt user uuidf fkid filler
139 99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
140 98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
141 SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
142 id dt user uuidf fkid filler
143 1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
144 2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
145 *** MDEV-5798: Wrong errorcode for missing partition after TRUNCATE PARTITION
146 connection master;
147 CREATE TABLE t1 (a INT)
148 ENGINE='InnoDB'
149 PARTITION BY LIST(a) (
150 PARTITION p0 VALUES IN (9, NULL),
151 PARTITION p1 VALUES IN (8, 2, 7),
152 PARTITION p2 VALUES IN (6, 4, 5),
153 PARTITION p3 VALUES IN (3, 1, 0)
154 );
155 ALTER TABLE t1 DROP PARTITION p0;
156 ALTER TABLE non_existent TRUNCATE PARTITION p1,p2;
157 ERROR 42S02: Table 'test.non_existent' doesn't exist
158 INSERT INTO t1 PARTITION (p1,p2,p3) VALUES (0),(9);
159 ERROR HY000: Table has no partition for value 9
160 connection slave;
161 connection master;
162 DROP PROCEDURE test.proc_norm;
163 DROP PROCEDURE test.proc_byrange;
164 DROP TABLE test.regular_tbl;
165 DROP TABLE test.byrange_tbl;
166 DROP TABLE test.t1;
167 include/rpl_end.inc