You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
640 lines
20 KiB
640 lines
20 KiB
-- source include/no_valgrind_without_big.inc
|
|
|
|
--echo #
|
|
--echo # WL#7755 mysqlpump: Extend mysqldump functionalities
|
|
--echo #
|
|
|
|
--echo # Test for help
|
|
--exec $MYSQL_PUMP --help > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
|
|
--echo #
|
|
--echo # Test all existing options
|
|
--echo #
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));
|
|
CREATE TABLE t3 (`a"b"` char(2));
|
|
CREATE TABLE t4 (
|
|
name VARCHAR(64) NOT NULL,
|
|
value FLOAT DEFAULT NULL,
|
|
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
comment VARCHAR(1024) DEFAULT NULL,
|
|
PRIMARY KEY (name)
|
|
);
|
|
|
|
CREATE TABLE t5 (
|
|
id int(11) NOT NULL,
|
|
id2 tinyint(3) NOT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY index2 (id2)
|
|
);
|
|
|
|
CREATE TABLE t6 (`x"z"` INT, xyz VARCHAR(20), notes TEXT);
|
|
ALTER TABLE t6 ADD INDEX t6_index (`x"z"`, xyz, notes(3));
|
|
|
|
CREATE TABLE t7 (
|
|
PS_PARTKEY int(11) NOT NULL,
|
|
PS_SUPPKEY int(11) NOT NULL,
|
|
PS_AVAILQTY int(11) NOT NULL,
|
|
PS_SUPPLYCOST float NOT NULL,
|
|
PS_COMMENT varchar(199) NOT NULL
|
|
);
|
|
|
|
ALTER TABLE t7 ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
|
|
|
|
CREATE TABLE t8 (
|
|
c_id INT(11) NOT NULL AUTO_INCREMENT,
|
|
c_name VARCHAR(255) NOT NULL,
|
|
c_description text,
|
|
PRIMARY KEY (c_id)
|
|
);
|
|
|
|
CREATE TABLE t9 (
|
|
v_id INT(11) NOT NULL AUTO_INCREMENT,
|
|
v_name VARCHAR(255) DEFAULT NULL,
|
|
PRIMARY KEY (v_id)
|
|
);
|
|
|
|
CREATE TABLE t10 (
|
|
p_id INT(11) NOT NULL AUTO_INCREMENT,
|
|
p_name VARCHAR(355) NOT NULL,
|
|
p_price decimal(10,0) DEFAULT NULL,
|
|
c_id INT(11) NOT NULL,
|
|
PRIMARY KEY (p_id),
|
|
KEY fk_t8 (c_id),
|
|
CONSTRAINT t10_ibfk_1 FOREIGN KEY (c_id) REFERENCES t8 (c_id) ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE t11 (
|
|
num int PRIMARY KEY,
|
|
FOREIGN KEY (num) REFERENCES t9 (v_id)
|
|
);
|
|
|
|
ALTER TABLE t10 ADD COLUMN v_id INT NOT NULL AFTER c_id;
|
|
ALTER TABLE t10 ADD FOREIGN KEY fk_t9(v_id) REFERENCES
|
|
t9(v_id) ON DELETE NO ACTION ON UPDATE CASCADE;
|
|
|
|
CREATE TABLE t12 (
|
|
ID bigint NOT NULL DEFAULT '0',
|
|
v bigint NOT NULL,
|
|
PRIMARY KEY (ID,v)
|
|
);
|
|
|
|
CREATE TABLE t13 (
|
|
ID bigint NOT NULL DEFAULT '0',
|
|
k varchar(30) NOT NULL DEFAULT '',
|
|
v bigint NOT NULL,
|
|
PRIMARY KEY (ID, v, k),
|
|
CONSTRAINT relation_tags_ibfk_1 FOREIGN KEY (ID, v) REFERENCES t12 (ID,v)
|
|
);
|
|
|
|
INSERT INTO t4 (name) VALUES ('disk_temptable_create_cost');
|
|
INSERT INTO t4 (name) VALUES ('disk_temptable_row_cost');
|
|
INSERT INTO t4 (name) VALUES ('key_compare_cost');
|
|
INSERT INTO t4 (name) VALUES ('memory_temptable_create_cost');
|
|
INSERT INTO t4 (name) VALUES ('memory_temptable_row_cost');
|
|
INSERT INTO t4 (name) VALUES ('row_evaluate_cost');
|
|
|
|
# store table value before backup
|
|
SELECT last_update INTO @val1 FROM t4 WHERE name= 'disk_temptable_create_cost';
|
|
SELECT last_update INTO @val2 FROM t4 WHERE name= 'disk_temptable_row_cost';
|
|
SELECT last_update INTO @val3 FROM t4 WHERE name= 'key_compare_cost';
|
|
SELECT last_update INTO @val4 FROM t4 WHERE name= 'memory_temptable_create_cost';
|
|
SELECT last_update INTO @val5 FROM t4 WHERE name= 'memory_temptable_row_cost';
|
|
SELECT last_update INTO @val6 FROM t4 WHERE name= 'row_evaluate_cost';
|
|
|
|
INSERT INTO t1 VALUES (289), (298), (234), (456), (789);
|
|
INSERT INTO t2 VALUES (1, "on"), (2, "off"), (10, "pol"), (12, "meg");
|
|
INSERT INTO t3 VALUES ("1\""), ("\"2");
|
|
|
|
CREATE VIEW v1 AS SELECT * FROM t2;
|
|
CREATE VIEW v2 AS SELECT * FROM t1;
|
|
CREATE VIEW v3 AS SELECT v1.*,v2.a as X FROM v1,v2;
|
|
CREATE VIEW v11 AS SELECT * FROM v3;
|
|
|
|
CREATE TEMPORARY TABLE t14 (id INT);
|
|
|
|
CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = 1;
|
|
CREATE TRIGGER trig2 BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a = 999;
|
|
|
|
CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SET @a=5;
|
|
CREATE EVENT ev2 ON SCHEDULE EVERY 5 HOUR DO SELECT 1;
|
|
|
|
DELIMITER |;
|
|
CREATE PROCEDURE t_cache()
|
|
BEGIN
|
|
SET @A= 20;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1;
|
|
PREPARE x FROM 'SELECT 1';
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1;
|
|
END ;|
|
|
DELIMITER ;|
|
|
|
|
DELIMITER |;
|
|
CREATE FUNCTION f1 () RETURNS int
|
|
BEGIN
|
|
SET NAMES 'big5';
|
|
RETURN (SELECT COUNT(*) FROM t1);
|
|
END ;|
|
|
DELIMITER ;|
|
|
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'BASE TABLE'
|
|
ORDER BY TABLE_NAME;
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'VIEW'
|
|
ORDER BY TABLE_NAME;
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'PROCEDURE'
|
|
ORDER BY ROUTINE_NAME;
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'FUNCTION'
|
|
ORDER BY ROUTINE_NAME;
|
|
SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
|
|
WHERE EVENT_SCHEMA='db1_basic' ORDER BY EVENT_NAME;
|
|
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
|
|
WHERE TRIGGER_SCHEMA='db1_basic' ORDER BY TRIGGER_NAME;
|
|
|
|
--echo # Check if all objects are dumped
|
|
--exec $MYSQL_PUMP --databases db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'BASE TABLE'
|
|
ORDER BY TABLE_NAME;
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'VIEW'
|
|
ORDER BY TABLE_NAME;
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'PROCEDURE'
|
|
ORDER BY ROUTINE_NAME;
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'FUNCTION'
|
|
ORDER BY ROUTINE_NAME;
|
|
SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
|
|
WHERE EVENT_SCHEMA='db1_basic' ORDER BY EVENT_NAME;
|
|
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
|
|
WHERE TRIGGER_SCHEMA='db1_basic' ORDER BY TRIGGER_NAME;
|
|
|
|
# restore and check the values
|
|
SELECT 1 FROM db1_basic.t4 WHERE name= 'disk_temptable_create_cost' AND last_update = @val1;
|
|
SELECT 2 FROM db1_basic.t4 WHERE name= 'disk_temptable_row_cost' AND last_update = @val2;
|
|
SELECT 3 FROM db1_basic.t4 WHERE name= 'key_compare_cost' AND last_update = @val3;
|
|
SELECT 4 FROM db1_basic.t4 WHERE name= 'memory_temptable_create_cost' AND last_update = @val4;
|
|
SELECT 5 FROM db1_basic.t4 WHERE name= 'memory_temptable_row_cost' AND last_update = @val5;
|
|
SELECT 6 FROM db1_basic.t4 WHERE name= 'row_evaluate_cost' AND last_update = @val6;
|
|
|
|
--echo # test for skip-triggers
|
|
--exec $MYSQL_PUMP --databases db1_basic --skip-triggers > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
|
|
#report 0 rows
|
|
SHOW TRIGGERS FROM db1_basic;
|
|
|
|
--echo # test for skip-events
|
|
--exec $MYSQL_PUMP --databases db1_basic --skip-events > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
|
|
#report 0 rows
|
|
SHOW EVENTS FROM db1_basic;
|
|
DROP DATABASE db1_basic;
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
CREATE TABLE t0 (`b` blob);
|
|
INSERT INTO `t0` VALUES (0x602010000280100005E71A);
|
|
--exec $MYSQL_PUMP --skip-extended-insert --hex-blob --databases db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
DROP TABLE IF EXISTS `t0`;
|
|
CREATE TABLE `t0` (a INT);
|
|
INSERT INTO `t0` VALUES (1),(2),(3);
|
|
INSERT INTO `t0` VALUES (4),(5),(6);
|
|
--exec $MYSQL_PUMP --insert-ignore --databases db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
DROP TABLE IF EXISTS `t0`;
|
|
CREATE TABLE `t0` (a int);
|
|
INSERT INTO `t0` VALUES (1),(2),(3);
|
|
--exec $MYSQL_PUMP --add-drop-database --databases db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
|
|
--echo # test for complete-insert
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
CREATE TABLE t0 ( a INT, b VARCHAR(10), c TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
|
|
INSERT INTO t0(a,b) VALUES (1, 'temptable'), (2, 'temptable');
|
|
INSERT INTO t0(a,b) VALUES (3, ''), (4, NULL);
|
|
--exec $MYSQL_PUMP --complete-insert --extended-insert=0 --databases db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
CREATE TABLE t0 ( a INT );
|
|
CREATE TABLE t2 ( a INT );
|
|
INSERT INTO t0 VALUES (1), (2);
|
|
INSERT INTO t2 VALUES (1), (2);
|
|
|
|
SELECT * FROM t0 ORDER BY a;
|
|
SELECT * FROM t2 ORDER BY a;
|
|
--exec $MYSQL_PUMP --skip-dump-rows db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
USE db1_basic;
|
|
SELECT * FROM t0 ORDER BY a;
|
|
SELECT * FROM t2 ORDER BY a;
|
|
DROP DATABASE db1_basic;
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
CREATE TABLE t0 (c1 INT);
|
|
CREATE TABLE t2 (c1 INT);
|
|
INSERT INTO t0 VALUES (1), (2), (3);
|
|
INSERT INTO t2 VALUES (1), (2), (3);
|
|
--exec $MYSQL_PUMP --single-transaction --databases db1_basic > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
USE db1_basic;
|
|
SELECT * FROM t0;
|
|
SELECT * FROM t2;
|
|
DROP DATABASE db1_basic;
|
|
|
|
--echo #
|
|
--echo # Test view dependencies
|
|
--echo #
|
|
|
|
CREATE DATABASE db1_basic;
|
|
USE db1_basic;
|
|
CREATE TABLE t1 (a1 INT);
|
|
CREATE TABLE t2 (a1 INT, b1 VARCHAR(10), primary key(a1));
|
|
|
|
CREATE DATABASE db3_basic;
|
|
USE db3_basic;
|
|
CREATE TABLE t1 (a3 INT);
|
|
CREATE TABLE t2 (a3 INT, b3 VARCHAR(10), primary key(a3));
|
|
CREATE VIEW v1 AS SELECT * FROM db1_basic.t2;
|
|
|
|
CREATE DATABASE db2_basic;
|
|
USE db2_basic;
|
|
CREATE TABLE t1 (a2 INT);
|
|
CREATE TABLE t2 (a2 INT, b2 VARCHAR(10), primary key(a2));
|
|
CREATE VIEW v1 AS SELECT * FROM db1_basic.t2;
|
|
CREATE VIEW v2 AS SELECT * FROM db3_basic.t1;
|
|
|
|
CREATE DATABASE db4_basic;
|
|
USE db4_basic;
|
|
CREATE TABLE t1 (a4 INT);
|
|
CREATE TABLE t2 (a4 INT, b4 VARCHAR(10), primary key(a4));
|
|
CREATE VIEW v1 AS SELECT * FROM db1_basic.t2,db3_basic.t1;
|
|
CREATE VIEW v2 AS SELECT * FROM db2_basic.t1,db1_basic.t2;
|
|
|
|
SHOW CREATE VIEW db3_basic.v1;
|
|
SHOW CREATE VIEW db2_basic.v1;
|
|
SHOW CREATE VIEW db2_basic.v2;
|
|
SHOW CREATE VIEW db4_basic.v1;
|
|
SHOW CREATE VIEW db4_basic.v2;
|
|
|
|
#dump all dbs
|
|
--exec $MYSQL_PUMP --exclude-databases=mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
DROP DATABASE db2_basic;
|
|
DROP DATABASE db3_basic;
|
|
DROP DATABASE db4_basic;
|
|
#restore all dbs
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
|
|
SHOW CREATE VIEW db3_basic.v1;
|
|
SHOW CREATE VIEW db2_basic.v1;
|
|
SHOW CREATE VIEW db2_basic.v2;
|
|
SHOW CREATE VIEW db4_basic.v1;
|
|
SHOW CREATE VIEW db4_basic.v2;
|
|
|
|
# test with default-parallelism=0
|
|
--exec $MYSQL_PUMP --default-parallelism=0 --exclude-databases=mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
DROP DATABASE db1_basic;
|
|
DROP DATABASE db2_basic;
|
|
DROP DATABASE db3_basic;
|
|
DROP DATABASE db4_basic;
|
|
#restore all dbs
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/db1_basic.sql
|
|
|
|
SHOW CREATE VIEW db3_basic.v1;
|
|
SHOW CREATE VIEW db2_basic.v1;
|
|
SHOW CREATE VIEW db2_basic.v2;
|
|
SHOW CREATE VIEW db4_basic.v1;
|
|
SHOW CREATE VIEW db4_basic.v2;
|
|
|
|
DROP DATABASE db1_basic;
|
|
DROP DATABASE db2_basic;
|
|
DROP DATABASE db3_basic;
|
|
DROP DATABASE db4_basic;
|
|
|
|
--echo #
|
|
--echo # Bug #21650559 MYSQLPUMP MAKES CORRUPTED DUMP WHEN TABLE HAS GENERATED COLUMN
|
|
--echo #
|
|
|
|
CREATE DATABASE bug21650559;
|
|
USE bug21650559;
|
|
CREATE TABLE t1 (pk INTEGER, a INTEGER, b INTEGER, c VARCHAR(16),
|
|
sum INTEGER GENERATED ALWAYS AS (a+b),
|
|
sub VARCHAR(4) GENERATED ALWAYS AS (SUBSTRING(c, 1, 4)),
|
|
key k1(sum),
|
|
key k2(sub)
|
|
);
|
|
CREATE TABLE t2 (pk INTEGER, a INTEGER, b INTEGER,
|
|
sum INTEGER GENERATED ALWAYS AS (a+b),
|
|
c VARCHAR(16),
|
|
key k1(sum)
|
|
);
|
|
CREATE TABLE t3 (sum INTEGER GENERATED ALWAYS AS (a+(EXTRACT(YEAR from pk))),
|
|
pk TIMESTAMP, a INTEGER,
|
|
c VARCHAR(16)
|
|
);
|
|
|
|
INSERT INTO t1(pk, a, b, c) VALUES (1, 11, 12, 'oneone'), (2, 21, 22, 'twotwo');
|
|
SELECT * FROM t1;
|
|
INSERT INTO t2(pk, a, b, c) VALUES (1, 11, 12, 'oneone'), (2, 21, 22, 'twotwo');
|
|
SELECT * FROM t2;
|
|
INSERT INTO t3(pk, a, c) VALUES ('2003-01-02 10:30:00.000123', 11, 12), ('2015-11-22 00:30:00', 21, 22);
|
|
SELECT * FROM t3;
|
|
|
|
--exec $MYSQL_PUMP --databases bug21650559 > $MYSQLTEST_VARDIR/tmp/bug21650559.sql
|
|
DROP DATABASE bug21650559;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21650559.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug21650559.sql
|
|
USE bug21650559;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
SELECT * FROM t3;
|
|
DROP DATABASE bug21650559;
|
|
|
|
#echo
|
|
#echo Bug #22505474 MYSQLPUMP FAILS WHEN IS DUMPING ALL DATABASES
|
|
#echo
|
|
|
|
CREATE DATABASE bug22505474;
|
|
USE bug22505474;
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
CREATE VIEW `v 1` AS SELECT * FROM t1;
|
|
SELECT * FROM `v 1`;
|
|
--exec $MYSQL_PUMP --databases bug22505474 > $MYSQLTEST_VARDIR/tmp/bug22505474.sql
|
|
DROP DATABASE bug22505474;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug22505474.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug22505474.sql
|
|
USE bug22505474;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM `v 1`;
|
|
DROP DATABASE bug22505474;
|
|
|
|
#echo
|
|
#echo Bug #22726732 MYSQLPUMP OUTPUT FAILS TO LOAD BECAUSE IT MISSES A KEY STATEMENT
|
|
#echo
|
|
|
|
CREATE DATABASE bug22726732;
|
|
USE bug22726732;
|
|
CREATE TABLE `part1_hash` (
|
|
`id` int(11) NOT NULL,
|
|
`fname` varchar(30) DEFAULT NULL,
|
|
`lname` varchar(30) DEFAULT NULL,
|
|
`hired` date NOT NULL DEFAULT '1970-01-01',
|
|
`store_id` int(11) DEFAULT NULL,
|
|
KEY `id` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
PARTITION BY HASH (MONTH(hired)) PARTITIONS 2;
|
|
|
|
CREATE TABLE `part2_hash` (
|
|
`firstname` VARCHAR(25) NOT NULL,
|
|
`lastname` VARCHAR(25) NOT NULL,
|
|
`username` VARCHAR(16) NOT NULL,
|
|
`email` VARCHAR(35),
|
|
`joined` DATE NOT NULL
|
|
)
|
|
PARTITION BY RANGE( YEAR(joined) ) (
|
|
PARTITION p0 VALUES LESS THAN (1960),
|
|
PARTITION p1 VALUES LESS THAN (1970),
|
|
PARTITION p2 VALUES LESS THAN (1980),
|
|
PARTITION p3 VALUES LESS THAN (1990),
|
|
PARTITION p4 VALUES LESS THAN MAXVALUE
|
|
);
|
|
|
|
CREATE TABLE `part3_hash` (
|
|
`id` INT NOT NULL,
|
|
`fname` VARCHAR(30),
|
|
`lname` VARCHAR(30),
|
|
`hired` DATE NOT NULL DEFAULT '1970-01-01',
|
|
`separated` DATE NOT NULL DEFAULT '9999-12-31',
|
|
`job_code` INT,
|
|
`store_id` INT
|
|
)
|
|
PARTITION BY LIST(store_id) (
|
|
PARTITION pNorth VALUES IN (3,5,6,9,17),
|
|
PARTITION pEast VALUES IN (1,2,10,11,19,20),
|
|
PARTITION pWest VALUES IN (4,12,13,14,18),
|
|
PARTITION pCentral VALUES IN (7,8,15,16)
|
|
);
|
|
SHOW TABLES;
|
|
--exec $MYSQL_PUMP --databases bug22726732 > $MYSQLTEST_VARDIR/tmp/bug22726732.sql
|
|
DROP DATABASE bug22726732;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug22726732.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug22726732.sql
|
|
USE bug22726732;
|
|
SHOW TABLES;
|
|
DROP DATABASE bug22726732;
|
|
|
|
#echo
|
|
#echo Bug #23072245 MYSQLPUMP OUTPUT FAILS TO LOAD WHEN IT INCLUDES TRIGGERS
|
|
#echo
|
|
|
|
CREATE DATABASE bug23072245;
|
|
USE bug23072245;
|
|
CREATE TABLE test1(a1 INT);
|
|
CREATE TABLE test2(a2 INT);
|
|
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
|
|
CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0);
|
|
DELIMITER |;
|
|
CREATE TRIGGER testref BEFORE INSERT ON test1
|
|
FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO test2 SET a2 = NEW.a1;
|
|
DELETE FROM test3 WHERE a3 = NEW.a1;
|
|
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
|
|
END ;|
|
|
DELIMITER ;|
|
|
--replace_column 6 '0000-00-00 00:00:00'
|
|
SHOW TRIGGERS;
|
|
|
|
--exec $MYSQL_PUMP --databases bug23072245 > $MYSQLTEST_VARDIR/tmp/bug23072245.sql
|
|
DROP DATABASE bug23072245;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug23072245.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug23072245.sql
|
|
USE bug23072245;
|
|
--replace_column 6 '0000-00-00 00:00:00'
|
|
SHOW TRIGGERS;
|
|
DROP DATABASE bug23072245;
|
|
|
|
#
|
|
# Bug #23721446 MYSQLPUMP DOES NOT DUMPS ALL THE GRANT STATEMENTS OF USERS
|
|
#
|
|
|
|
CREATE USER bug23721446_u1,bug23721446_u2;
|
|
CREATE DATABASE db23721446;
|
|
CREATE TABLE db23721446.t(i int);
|
|
DELIMITER |;
|
|
CREATE PROCEDURE db23721446.sp(out p1 int)
|
|
BEGIN
|
|
select count(*) from db23721446.t;
|
|
END ;|
|
|
DELIMITER ;|
|
|
|
|
GRANT ALL PRIVILEGES ON db23721446.* TO 'bug23721446_u1'@'%' ;
|
|
GRANT SELECT, SELECT (i) ON db23721446.t TO 'bug23721446_u1'@'%';
|
|
GRANT EXECUTE ON PROCEDURE db23721446.sp TO 'bug23721446_u1'@'%';
|
|
SHOW GRANTS FOR bug23721446_u1@'%';
|
|
|
|
GRANT UPDATE (i) ON db23721446.t TO 'bug23721446_u2'@'%';
|
|
GRANT DELETE ON db23721446.t TO 'bug23721446_u2'@'%';
|
|
SHOW GRANTS FOR bug23721446_u2@'%';
|
|
|
|
--exec $MYSQL_PUMP --include-users=bug23721446_u% --include-databases=db23721446 > $MYSQLTEST_VARDIR/tmp/bug23721446.sql
|
|
# drop the user, database and restore from backup
|
|
DROP USER bug23721446_u1,bug23721446_u2;
|
|
DROP DATABASE db23721446;
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug23721446.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug23721446.sql
|
|
|
|
#check for user privileges
|
|
SHOW GRANTS FOR bug23721446_u1@'%';
|
|
SHOW GRANTS FOR bug23721446_u2@'%';
|
|
#cleanup
|
|
DROP USER bug23721446_u1,bug23721446_u2;
|
|
DROP DATABASE db23721446;
|
|
|
|
-- echo #
|
|
-- echo # Bug#22919028 - MYSQLPUMP DOES NOT HANDLE RE-IMPORT FROM UPGRADE
|
|
-- echo #
|
|
|
|
CREATE DATABASE bug22919028;
|
|
USE bug22919028;
|
|
CREATE TABLE t (i DATE);
|
|
SET SQL_MODE = ALLOW_INVALID_DATES;
|
|
INSERT INTO t VALUES('2016-02-31');
|
|
--exec $MYSQL_PUMP --databases bug22919028 > $MYSQLTEST_VARDIR/tmp/bug22919028.sql
|
|
DROP DATABASE bug22919028;
|
|
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug22919028.sql
|
|
SELECT * FROM bug22919028.t;
|
|
|
|
# Cleanup
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug22919028.sql
|
|
DROP DATABASE bug22919028;
|
|
|
|
-- echo #
|
|
-- echo # Bug#26694675: MYSQLPUMP SPAMS WITH SQL_NO_CACHE DEPRECATION WARNINGS
|
|
-- echo #
|
|
|
|
CREATE DATABASE bug26694675;
|
|
USE bug26694675;
|
|
CREATE TABLE t (i INT);
|
|
INSERT INTO t VALUES (9), (0);
|
|
--exec $MYSQL_PUMP --all-databases --log-error-file=$MYSQLTEST_VARDIR/log/err.log > $MYSQLTEST_VARDIR/tmp/bug26694675.sql
|
|
DROP DATABASE bug26694675;
|
|
|
|
--echo check for warnings
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/err.log;
|
|
let SEARCH_PATTERN= mysqlpump: \[WARNING\];
|
|
--source include/search_pattern.inc
|
|
|
|
# cleanup
|
|
--remove_file $MYSQLTEST_VARDIR/log/err.log
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug26694675.sql
|
|
|
|
-- echo #
|
|
-- echo # Bug#26116415 TABLESPACE CLAUSE CONFUSES MYSQLPUMP
|
|
-- echo #
|
|
|
|
CREATE TABLESPACE `mytbsp` ADD DATAFILE 'mytbsp.ibd' ENGINE INNODB;
|
|
|
|
CREATE TABLE `test`.`t1` (a integer) TABLESPACE `mytbsp`;
|
|
|
|
SHOW CREATE TABLE test.t1;
|
|
|
|
--let $program= $MYSQL_PUMP --include-databases=test --include-tables=t1
|
|
|
|
--exec $PROGRAM >> $MYSQL_TMP_DIR/mysqlpump.sql
|
|
|
|
DROP TABLE `test`.`t1`;
|
|
|
|
--exec $MYSQL < $MYSQL_TMP_DIR/mysqlpump.sql
|
|
|
|
SHOW CREATE TABLE test.t1;
|
|
|
|
DROP TABLE `test`.`t1`;
|
|
DROP TABLESPACE `mytbsp`;
|
|
--remove_file $MYSQL_TMP_DIR/mysqlpump.sql
|
|
|
|
-- echo #
|
|
-- echo # Bug#29023216: MYSQLPUMP SHOULD WRITE USERS AND GRANTS BEFORE VIEWS AND STORED PROGRAMS
|
|
-- echo #
|
|
|
|
CREATE DATABASE DB29023216;
|
|
CREATE USER 'VIEWUSER'@'LOCALHOST';
|
|
GRANT ALL ON *.* TO 'VIEWUSER'@'LOCALHOST';
|
|
|
|
USE DB29023216;
|
|
#case1: view defined on another view with definer set to non existing user
|
|
CREATE TABLE T1 (ID INT UNSIGNED NOT NULL PRIMARY KEY, VAL VARCHAR(10));
|
|
CREATE DEFINER=VIEWUSER@LOCALHOST SQL SECURITY INVOKER VIEW V_T1_A AS SELECT * FROM T1;
|
|
CREATE DEFINER=VIEWUSER@LOCALHOST SQL SECURITY DEFINER VIEW V_T1_B AS SELECT * FROM T1;
|
|
CREATE SQL SECURITY DEFINER VIEW V_T1_A2 AS SELECT * FROM V_T1_A;
|
|
CREATE SQL SECURITY DEFINER VIEW V_T1_B2 AS SELECT * FROM V_T1_B;
|
|
|
|
--exec $MYSQL_PUMP --exclude-databases=mtr --include-users=VIEWUSER@localhost --all-databases --add-drop-table --users > $MYSQLTEST_VARDIR/tmp/bug29023216.sql
|
|
DROP DATABASE DB29023216;
|
|
DROP USER 'VIEWUSER'@'LOCALHOST';
|
|
# restore should succeed
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug29023216.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug29023216.sql
|
|
|
|
USE DB29023216;
|
|
SELECT * FROM V_T1_A;
|
|
SELECT * FROM V_T1_B;
|
|
SELECT * FROM V_T1_A2;
|
|
SELECT * FROM V_T1_B2;
|
|
|
|
#case2: view defined on function with definer set to non existing user
|
|
CREATE DEFINER=VIEWUSER@LOCALHOST FUNCTION TESTFUNC() RETURNS BOOL RETURN TRUE;
|
|
CREATE VIEW V_T1_C AS SELECT DB29023216.TESTFUNC();
|
|
|
|
--exec $MYSQL_PUMP --exclude-databases=mtr --include-users=VIEWUSER@localhost --all-databases --add-drop-table --users > $MYSQLTEST_VARDIR/tmp/bug29023216.sql
|
|
DROP DATABASE DB29023216;
|
|
DROP USER 'VIEWUSER'@'LOCALHOST';
|
|
# restore should succeed
|
|
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug29023216.sql
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/bug29023216.sql
|
|
|
|
USE DB29023216;
|
|
SELECT * FROM V_T1_C;
|
|
|
|
#cleanup
|
|
DROP DATABASE DB29023216;
|
|
DROP USER 'VIEWUSER'@'LOCALHOST';
|
|
|