mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
167 lines
6.8 KiB
Text
167 lines
6.8 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
use test;
|
|
CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT,
|
|
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
|
|
CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
|
|
fkid INT, filler VARCHAR(255),
|
|
PRIMARY KEY(id))
|
|
ENGINE='MyISAM';
|
|
CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT,
|
|
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
|
|
CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
|
|
fkid INT, filler VARCHAR(255),
|
|
PRIMARY KEY(id))
|
|
ENGINE='MyISAM'
|
|
PARTITION BY RANGE(id)
|
|
(PARTITION pa100 values less than (100),
|
|
PARTITION paMax values less than MAXVALUE);
|
|
CREATE PROCEDURE test.proc_norm()
|
|
BEGIN
|
|
DECLARE ins_count INT DEFAULT 99;
|
|
DECLARE cur_user VARCHAR(255);
|
|
DECLARE local_uuid VARCHAR(255);
|
|
SET cur_user= "current_user@localhost";
|
|
SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
|
|
WHILE ins_count > 0 DO
|
|
# Must use local variables for statement based replication
|
|
INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
|
|
ins_count,'Non partitioned table! Going to test replication for MySQL');
|
|
SET ins_count = ins_count - 1;
|
|
END WHILE;
|
|
END|
|
|
CREATE PROCEDURE test.proc_byrange()
|
|
BEGIN
|
|
DECLARE ins_count INT DEFAULT 200;
|
|
DECLARE cur_user VARCHAR(255);
|
|
DECLARE local_uuid VARCHAR(255);
|
|
SET cur_user= "current_user@localhost";
|
|
SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
|
|
WHILE ins_count > 0 DO
|
|
INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
|
|
ins_count + 100,'Partitioned table! Going to test replication for MySQL');
|
|
SET ins_count = ins_count - 1;
|
|
END WHILE;
|
|
END|
|
|
CALL test.proc_norm();
|
|
SELECT count(*) as "Master regular" FROM test.regular_tbl;
|
|
Master regular
|
|
99
|
|
CALL test.proc_byrange();
|
|
SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
|
|
Master byrange
|
|
200
|
|
show create table test.byrange_tbl;
|
|
Table Create Table
|
|
byrange_tbl CREATE TABLE `byrange_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`user` char(255) DEFAULT NULL,
|
|
`uuidf` varbinary(255) DEFAULT NULL,
|
|
`fkid` int(11) DEFAULT NULL,
|
|
`filler` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`id`)
|
|
(PARTITION `pa100` VALUES LESS THAN (100) ENGINE = MyISAM,
|
|
PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
|
|
show create table test.regular_tbl;
|
|
Table Create Table
|
|
regular_tbl CREATE TABLE `regular_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`user` char(255) DEFAULT NULL,
|
|
`uuidf` varbinary(255) DEFAULT NULL,
|
|
`fkid` int(11) DEFAULT NULL,
|
|
`filler` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl;
|
|
SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
|
|
98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
|
|
SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
|
|
101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
|
|
SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
|
|
98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
|
|
SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
|
|
2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
|
|
connection slave;
|
|
connection slave;
|
|
show create table test.byrange_tbl;
|
|
Table Create Table
|
|
byrange_tbl CREATE TABLE `byrange_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`user` char(255) DEFAULT NULL,
|
|
`uuidf` varbinary(255) DEFAULT NULL,
|
|
`fkid` int(11) DEFAULT NULL,
|
|
`filler` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`id`)
|
|
(PARTITION `pa100` VALUES LESS THAN (100) ENGINE = MyISAM,
|
|
PARTITION `paMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
|
|
show create table test.regular_tbl;
|
|
Table Create Table
|
|
regular_tbl CREATE TABLE `regular_tbl` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
`user` char(255) DEFAULT NULL,
|
|
`uuidf` varbinary(255) DEFAULT NULL,
|
|
`fkid` int(11) DEFAULT NULL,
|
|
`filler` varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT count(*) "Slave norm" FROM test.regular_tbl;
|
|
Slave norm
|
|
99
|
|
SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
|
|
Slave byrange
|
|
200
|
|
SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
|
|
98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
|
|
SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
|
|
101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
|
|
SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
|
|
98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
|
|
SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
|
|
id dt user uuidf fkid filler
|
|
1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
|
|
2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
|
|
*** MDEV-5798: Wrong errorcode for missing partition after TRUNCATE PARTITION
|
|
connection master;
|
|
CREATE TABLE t1 (a INT)
|
|
ENGINE='MyISAM'
|
|
PARTITION BY LIST(a) (
|
|
PARTITION p0 VALUES IN (9, NULL),
|
|
PARTITION p1 VALUES IN (8, 2, 7),
|
|
PARTITION p2 VALUES IN (6, 4, 5),
|
|
PARTITION p3 VALUES IN (3, 1, 0)
|
|
);
|
|
ALTER TABLE t1 DROP PARTITION p0;
|
|
ALTER TABLE non_existent TRUNCATE PARTITION p1,p2;
|
|
ERROR 42S02: Table 'test.non_existent' doesn't exist
|
|
INSERT INTO t1 PARTITION (p1,p2,p3) VALUES (0),(9);
|
|
ERROR HY000: Table has no partition for value 9
|
|
connection slave;
|
|
connection master;
|
|
DROP PROCEDURE test.proc_norm;
|
|
DROP PROCEDURE test.proc_byrange;
|
|
DROP TABLE test.regular_tbl;
|
|
DROP TABLE test.byrange_tbl;
|
|
DROP TABLE test.t1;
|
|
include/rpl_end.inc
|