Debian Codegraph @

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