Debian Codegraph @

mysql-test/suite/rpl/r/rpl_partition_archive.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='Archive';
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='Archive'
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=ARCHIVE AUTO_INCREMENT=201 DEFAULT CHARSET=latin1
65 PARTITION BY RANGE (id)
66 (PARTITION pa100 VALUES LESS THAN (100) ENGINE = ARCHIVE,
67 PARTITION paMax VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
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=ARCHIVE AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
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 show create table test.byrange_tbl;
97 Table Create Table
98 byrange_tbl CREATE TABLE `byrange_tbl` (
99 `id` int(11) NOT NULL AUTO_INCREMENT,
100 `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
101 `user` char(255) DEFAULT NULL,
102 `uuidf` varbinary(255) DEFAULT NULL,
103 `fkid` int(11) DEFAULT NULL,
104 `filler` varchar(255) DEFAULT NULL,
105 PRIMARY KEY (`id`)
106 ) ENGINE=ARCHIVE AUTO_INCREMENT=201 DEFAULT CHARSET=latin1
107 PARTITION BY RANGE (id)
108 (PARTITION pa100 VALUES LESS THAN (100) ENGINE = ARCHIVE,
109 PARTITION paMax VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
110 show create table test.regular_tbl;
111 Table Create Table
112 regular_tbl CREATE TABLE `regular_tbl` (
113 `id` int(11) NOT NULL AUTO_INCREMENT,
114 `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
115 `user` char(255) DEFAULT NULL,
116 `uuidf` varbinary(255) DEFAULT NULL,
117 `fkid` int(11) DEFAULT NULL,
118 `filler` varchar(255) DEFAULT NULL,
119 PRIMARY KEY (`id`)
120 ) ENGINE=ARCHIVE AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
121 SELECT count(*) "Slave norm" FROM test.regular_tbl;
122 Slave norm
123 99
124 SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
125 Slave byrange
126 200
127 SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
128 id dt user uuidf fkid filler
129 99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
130 98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
131 SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
132 id dt user uuidf fkid filler
133 100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
134 101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
135 SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
136 id dt user uuidf fkid filler
137 99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
138 98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
139 SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
140 id dt user uuidf fkid filler
141 1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
142 2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
143 *** MDEV-5798: Wrong errorcode for missing partition after TRUNCATE PARTITION
144 connection master;
145 CREATE TABLE t1 (a INT)
146 ENGINE='Archive'
147 PARTITION BY LIST(a) (
148 PARTITION p0 VALUES IN (9, NULL),
149 PARTITION p1 VALUES IN (8, 2, 7),
150 PARTITION p2 VALUES IN (6, 4, 5),
151 PARTITION p3 VALUES IN (3, 1, 0)
152 );
153 ALTER TABLE t1 DROP PARTITION p0;
154 ALTER TABLE non_existent TRUNCATE PARTITION p1,p2;
155 ERROR 42S02: Table 'test.non_existent' doesn't exist
156 INSERT INTO t1 PARTITION (p1,p2,p3) VALUES (0),(9);
157 ERROR HY000: Table has no partition for value 9
158 connection slave;
159 connection master;
160 DROP PROCEDURE test.proc_norm;
161 DROP PROCEDURE test.proc_byrange;
162 DROP TABLE test.regular_tbl;
163 DROP TABLE test.byrange_tbl;
164 DROP TABLE test.t1;
165 include/rpl_end.inc