Debian Codegraph @

mysql-test/suite/rpl/include/rpl_partition.inc

raw
1 ######## Create Table Section #########
2 use test;
3
4 #dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
5 eval CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT,
6 dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
7 CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
8 fkid INT, filler VARCHAR(255),
9 PRIMARY KEY(id))
10 ENGINE=$engine_type;
11
12 eval CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT,
13 dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
14 CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
15 fkid INT, filler VARCHAR(255),
16 PRIMARY KEY(id))
17 ENGINE=$engine_type
18 PARTITION BY RANGE(id)
19 (PARTITION pa100 values less than (100),
20 PARTITION paMax values less than MAXVALUE);
21
22 ######## Create SPs, Functions, Views and Triggers Section ##############
23
24 delimiter |;
25 CREATE PROCEDURE test.proc_norm()
26 BEGIN
27 DECLARE ins_count INT DEFAULT 99;
28 DECLARE cur_user VARCHAR(255);
29 DECLARE local_uuid VARCHAR(255);
30
31 SET cur_user= "current_user@localhost";
32 SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
33
34 WHILE ins_count > 0 DO
35 # Must use local variables for statment based replication
36 INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
37 ins_count,'Non partitioned table! Going to test replication for MySQL');
38 SET ins_count = ins_count - 1;
39 END WHILE;
40
41 END|
42
43 CREATE PROCEDURE test.proc_byrange()
44 BEGIN
45 DECLARE ins_count INT DEFAULT 200;
46 DECLARE cur_user VARCHAR(255);
47 DECLARE local_uuid VARCHAR(255);
48
49 SET cur_user= "current_user@localhost";
50 SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
51
52 WHILE ins_count > 0 DO
53 INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
54 ins_count + 100,'Partitioned table! Going to test replication for MySQL');
55 SET ins_count = ins_count - 1;
56 END WHILE;
57
58 END|
59
60 delimiter ;|
61
62 ############ Finish Setup Section ###################
63
64
65 ############ Test Section ###################
66
67 CALL test.proc_norm();
68 SELECT count(*) as "Master regular" FROM test.regular_tbl;
69 CALL test.proc_byrange();
70 SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
71 show create table test.byrange_tbl;
72 show create table test.regular_tbl;
73 ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl;
74 --replace_column 2 date-time 3 USER 4 UUID
75 SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
76 --replace_column 2 date-time 3 USER 4 UUID
77 SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
78 --replace_column 2 date-time 3 USER 4 UUID
79 SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
80 --replace_column 2 date-time 3 USER 4 UUID
81 SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
82
83 --source include/sync_slave_sql_with_master.inc
84 connection slave;
85 show create table test.byrange_tbl;
86 show create table test.regular_tbl;
87 SELECT count(*) "Slave norm" FROM test.regular_tbl;
88 SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
89 --replace_column 2 date-time 3 USER 4 UUID
90 SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
91 --replace_column 2 date-time 3 USER 4 UUID
92 SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
93 --replace_column 2 date-time 3 USER 4 UUID
94 SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
95 --replace_column 2 date-time 3 USER 4 UUID
96 SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
97
98 ###### CLEAN UP SECTION ##############
99
100 connection master;
101 DROP PROCEDURE test.proc_norm;
102 DROP PROCEDURE test.proc_byrange;
103 DROP TABLE test.regular_tbl;
104 DROP TABLE test.byrange_tbl;