#
# WL#7755  mysqlpump: Extend mysqldump functionalities
#
# Test for help
#
# Test all existing options
#
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)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t9 (
v_id INT(11) NOT NULL AUTO_INCREMENT,
v_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (v_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
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;
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 ;|
CREATE FUNCTION f1 () RETURNS int
BEGIN
SET NAMES 'big5';
RETURN (SELECT COUNT(*) FROM t1);
END ;|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'BASE TABLE'
       ORDER BY TABLE_NAME;
TABLE_NAME
t1
t10
t11
t12
t13
t2
t3
t4
t5
t6
t7
t8
t9
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'VIEW'
       ORDER BY TABLE_NAME;
TABLE_NAME
v1
v11
v2
v3
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'PROCEDURE'
       ORDER BY ROUTINE_NAME;
ROUTINE_NAME
t_cache
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'FUNCTION'
       ORDER BY ROUTINE_NAME;
ROUTINE_NAME
f1
SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA='db1_basic' ORDER BY EVENT_NAME;
EVENT_NAME
ev1
ev2
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='db1_basic' ORDER BY TRIGGER_NAME;
TRIGGER_NAME
trig1
trig2
# Check if all objects are dumped
DROP DATABASE db1_basic;
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'BASE TABLE'
       ORDER BY TABLE_NAME;
TABLE_NAME
t1
t10
t11
t12
t13
t2
t3
t4
t5
t6
t7
t8
t9
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db1_basic' AND TABLE_TYPE= 'VIEW'
       ORDER BY TABLE_NAME;
TABLE_NAME
v1
v11
v2
v3
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'PROCEDURE'
       ORDER BY ROUTINE_NAME;
ROUTINE_NAME
t_cache
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='db1_basic' AND ROUTINE_TYPE= 'FUNCTION'
       ORDER BY ROUTINE_NAME;
ROUTINE_NAME
f1
SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA='db1_basic' ORDER BY EVENT_NAME;
EVENT_NAME
ev1
ev2
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='db1_basic' ORDER BY TRIGGER_NAME;
TRIGGER_NAME
trig1
trig2
SELECT 1 FROM db1_basic.t4 WHERE name= 'disk_temptable_create_cost' AND last_update = @val1;
1
1
SELECT 2 FROM db1_basic.t4 WHERE name= 'disk_temptable_row_cost' AND last_update = @val2;
2
2
SELECT 3 FROM db1_basic.t4 WHERE name= 'key_compare_cost' AND last_update = @val3;
3
3
SELECT 4 FROM db1_basic.t4 WHERE name= 'memory_temptable_create_cost' AND last_update = @val4;
4
4
SELECT 5 FROM db1_basic.t4 WHERE name= 'memory_temptable_row_cost' AND last_update = @val5;
5
5
SELECT 6 FROM db1_basic.t4 WHERE name= 'row_evaluate_cost' AND last_update = @val6;
6
6
# test for skip-triggers
DROP DATABASE db1_basic;
SHOW TRIGGERS FROM db1_basic;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
# test for skip-events
DROP DATABASE db1_basic;
SHOW EVENTS FROM db1_basic;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
DROP DATABASE db1_basic;
CREATE DATABASE db1_basic;
USE db1_basic;
CREATE TABLE t0 (`b` blob);
INSERT INTO `t0` VALUES (0x602010000280100005E71A);
DROP DATABASE db1_basic;
CREATE DATABASE db1_basic;
USE db1_basic;
DROP TABLE IF EXISTS `t0`;
Warnings:
Note	1051	Unknown table 'db1_basic.t0'
CREATE TABLE `t0` (a INT);
INSERT INTO `t0` VALUES (1),(2),(3);
INSERT INTO `t0` VALUES (4),(5),(6);
DROP DATABASE db1_basic;
CREATE DATABASE db1_basic;
USE db1_basic;
DROP TABLE IF EXISTS `t0`;
Warnings:
Note	1051	Unknown table 'db1_basic.t0'
CREATE TABLE `t0` (a int);
INSERT INTO `t0` VALUES (1),(2),(3);
DROP DATABASE db1_basic;
# 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);
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;
a
1
2
SELECT * FROM t2 ORDER BY a;
a
1
2
DROP DATABASE db1_basic;
USE db1_basic;
SELECT * FROM t0 ORDER BY a;
a
SELECT * FROM t2 ORDER BY a;
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);
DROP DATABASE db1_basic;
USE db1_basic;
SELECT * FROM t0;
c1
1
2
3
SELECT * FROM t2;
c1
1
2
3
DROP DATABASE db1_basic;
#
# Test view dependencies
#
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;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db3_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from `db1_basic`.`t2`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db2_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from `db1_basic`.`t2`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db2_basic.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_basic`.`v2` AS select `db3_basic`.`t1`.`a3` AS `a3` from `db3_basic`.`t1`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db4_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1`,`db3_basic`.`t1`.`a3` AS `a3` from (`db1_basic`.`t2` join `db3_basic`.`t1`)	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db4_basic.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `db2_basic`.`t1`.`a2` AS `a2`,`db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from (`db2_basic`.`t1` join `db1_basic`.`t2`)	utf8mb4	utf8mb4_0900_ai_ci
DROP DATABASE db1_basic;
DROP DATABASE db2_basic;
DROP DATABASE db3_basic;
DROP DATABASE db4_basic;
SHOW CREATE VIEW db3_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db3_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from `db1_basic`.`t2`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db2_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from `db1_basic`.`t2`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db2_basic.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_basic`.`v2` AS select `db3_basic`.`t1`.`a3` AS `a3` from `db3_basic`.`t1`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db4_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db4_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1`,`db3_basic`.`t1`.`a3` AS `a3` from (`db1_basic`.`t2` join `db3_basic`.`t1`)	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db4_basic.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db4_basic`.`v2` AS select `db2_basic`.`t1`.`a2` AS `a2`,`db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from (`db2_basic`.`t1` join `db1_basic`.`t2`)	utf8mb4	utf8mb4_0900_ai_ci
DROP DATABASE db1_basic;
DROP DATABASE db2_basic;
DROP DATABASE db3_basic;
DROP DATABASE db4_basic;
SHOW CREATE VIEW db3_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db3_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from `db1_basic`.`t2`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db2_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from `db1_basic`.`t2`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db2_basic.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2_basic`.`v2` AS select `db3_basic`.`t1`.`a3` AS `a3` from `db3_basic`.`t1`	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db4_basic.v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db4_basic`.`v1` AS select `db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1`,`db3_basic`.`t1`.`a3` AS `a3` from (`db1_basic`.`t2` join `db3_basic`.`t1`)	utf8mb4	utf8mb4_0900_ai_ci
SHOW CREATE VIEW db4_basic.v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db4_basic`.`v2` AS select `db2_basic`.`t1`.`a2` AS `a2`,`db1_basic`.`t2`.`a1` AS `a1`,`db1_basic`.`t2`.`b1` AS `b1` from (`db2_basic`.`t1` join `db1_basic`.`t2`)	utf8mb4	utf8mb4_0900_ai_ci
DROP DATABASE db1_basic;
DROP DATABASE db2_basic;
DROP DATABASE db3_basic;
DROP DATABASE db4_basic;
#
# Bug #21650559 MYSQLPUMP MAKES CORRUPTED DUMP WHEN TABLE HAS GENERATED COLUMN
#
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;
pk	a	b	c	sum	sub
1	11	12	oneone	23	oneo
2	21	22	twotwo	43	twot
INSERT INTO t2(pk, a, b, c) VALUES (1, 11, 12, 'oneone'), (2, 21, 22, 'twotwo');
SELECT * FROM t2;
pk	a	b	sum	c
1	11	12	23	oneone
2	21	22	43	twotwo
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;
sum	pk	a	c
2014	2003-01-02 10:30:00	11	12
2036	2015-11-22 00:30:00	21	22
DROP DATABASE bug21650559;
USE bug21650559;
SELECT * FROM t1;
pk	a	b	c	sum	sub
1	11	12	oneone	23	oneo
2	21	22	twotwo	43	twot
SELECT * FROM t2;
pk	a	b	sum	c
1	11	12	23	oneone
2	21	22	43	twotwo
SELECT * FROM t3;
sum	pk	a	c
2014	2003-01-02 10:30:00	11	12
2036	2015-11-22 00:30:00	21	22
DROP DATABASE bug21650559;
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`;
a
1
2
3
DROP DATABASE bug22505474;
USE bug22505474;
SELECT * FROM t1;
a
1
2
3
SELECT * FROM `v 1`;
a
1
2
3
DROP DATABASE bug22505474;
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;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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;
Tables_in_bug22726732
part1_hash
part2_hash
part3_hash
DROP DATABASE bug22726732;
USE bug22726732;
SHOW TABLES;
Tables_in_bug22726732
part1_hash
part2_hash
part3_hash
DROP DATABASE bug22726732;
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);
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 ;|
SHOW TRIGGERS;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
testref	INSERT	test1	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	BEFORE	0000-00-00 00:00:00	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
DROP DATABASE bug23072245;
USE bug23072245;
SHOW TRIGGERS;
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
testref	INSERT	test1	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	BEFORE	0000-00-00 00:00:00	NO_AUTO_VALUE_ON_ZERO	root@localhost	utf8mb4	utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci
DROP DATABASE bug23072245;
CREATE USER bug23721446_u1,bug23721446_u2;
CREATE DATABASE db23721446;
CREATE TABLE db23721446.t(i int);
CREATE PROCEDURE db23721446.sp(out p1 int)
BEGIN
select count(*) from db23721446.t;
END ;|
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@'%';
Grants for bug23721446_u1@%
GRANT USAGE ON *.* TO `bug23721446_u1`@`%`
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`@`%`
GRANT UPDATE (i) ON db23721446.t TO 'bug23721446_u2'@'%';
GRANT DELETE ON db23721446.t TO 'bug23721446_u2'@'%';
SHOW GRANTS FOR bug23721446_u2@'%';
Grants for bug23721446_u2@%
GRANT USAGE ON *.* TO `bug23721446_u2`@`%`
GRANT UPDATE (`i`), DELETE ON `db23721446`.`t` TO `bug23721446_u2`@`%`
DROP USER bug23721446_u1,bug23721446_u2;
DROP DATABASE db23721446;
SHOW GRANTS FOR bug23721446_u1@'%';
Grants for bug23721446_u1@%
GRANT USAGE ON *.* TO `bug23721446_u1`@`%`
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_u2@'%';
Grants for bug23721446_u2@%
GRANT USAGE ON *.* TO `bug23721446_u2`@`%`
GRANT UPDATE (`i`), DELETE ON `db23721446`.`t` TO `bug23721446_u2`@`%`
DROP USER bug23721446_u1,bug23721446_u2;
DROP DATABASE db23721446;
#
# Bug#22919028 - MYSQLPUMP DOES NOT HANDLE RE-IMPORT FROM UPGRADE
#
CREATE DATABASE bug22919028;
USE bug22919028;
CREATE TABLE t (i DATE);
SET SQL_MODE = ALLOW_INVALID_DATES;
INSERT INTO t VALUES('2016-02-31');
DROP DATABASE bug22919028;
SELECT * FROM bug22919028.t;
i
0000-00-00
DROP DATABASE bug22919028;
#
# Bug#26694675: MYSQLPUMP SPAMS WITH SQL_NO_CACHE DEPRECATION WARNINGS
#
CREATE DATABASE bug26694675;
USE bug26694675;
CREATE TABLE t (i INT);
INSERT INTO t VALUES (9), (0);
DROP DATABASE bug26694675;
check for warnings
Pattern "mysqlpump: \[WARNING\]" not found
#
# Bug#26116415 TABLESPACE CLAUSE CONFUSES MYSQLPUMP
#
CREATE TABLESPACE `mytbsp` ADD DATAFILE 'mytbsp.ibd' ENGINE INNODB;
CREATE TABLE `test`.`t1` (a integer) TABLESPACE `mytbsp`;
SHOW CREATE TABLE test.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL
) /*!50100 TABLESPACE `mytbsp` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE `test`.`t1`;
SHOW CREATE TABLE test.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int DEFAULT NULL
) /*!50100 TABLESPACE `mytbsp` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE `test`.`t1`;
DROP TABLESPACE `mytbsp`;
#
# Bug#29023216: MYSQLPUMP SHOULD WRITE USERS AND GRANTS BEFORE VIEWS AND STORED PROGRAMS
#
CREATE DATABASE DB29023216;
CREATE USER 'VIEWUSER'@'LOCALHOST';
GRANT ALL ON *.* TO 'VIEWUSER'@'LOCALHOST';
USE DB29023216;
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;
DROP DATABASE DB29023216;
DROP USER 'VIEWUSER'@'LOCALHOST';
USE DB29023216;
SELECT * FROM V_T1_A;
ID	VAL
SELECT * FROM V_T1_B;
ID	VAL
SELECT * FROM V_T1_A2;
ID	VAL
SELECT * FROM V_T1_B2;
ID	VAL
CREATE DEFINER=VIEWUSER@LOCALHOST FUNCTION TESTFUNC() RETURNS BOOL RETURN TRUE;
CREATE VIEW V_T1_C AS SELECT DB29023216.TESTFUNC();
DROP DATABASE DB29023216;
DROP USER 'VIEWUSER'@'LOCALHOST';
USE DB29023216;
SELECT * FROM V_T1_C;
DB29023216.TESTFUNC()
1
DROP DATABASE DB29023216;
DROP USER 'VIEWUSER'@'LOCALHOST';
USE test;
#
# WL10905 - Support for INVISIBLE columns.
#           Test case to verify Logical Dump using mysqlpump tool
#
CREATE TABLE t1(f1 INT INVISIBLE, f2 INT,
f3 INT AS (f1 + 10), f4 INT AS (f2 + 10) INVISIBLE);
CREATE TABLE t2(f1 INT, f2 INT);
INSERT INTO t1(f1, f2) VALUES (10, 20), (20, 30);
INSERT INTO t2(f1, f2) VALUES (10, 20), (20, 30);
DROP TABLE t1, t2;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
  `f2` int DEFAULT NULL,
  `f3` int GENERATED ALWAYS AS ((`f1` + 10)) VIRTUAL,
  `f4` int GENERATED ALWAYS AS ((`f2` + 10)) VIRTUAL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t1 ORDER BY f2;
f2	f3
20	20
30	30
SELECT f1, f2, f3, f4 FROM t1 ORDER BY f2;
f1	f2	f3	f4
10	20	20	30
20	30	30	40
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `f1` int DEFAULT NULL,
  `f2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t2 ORDER BY f2;
f1	f2
10	20
20	30
DROP TABLE t1, t2;
#
# WL13784 - Create auto_increment PK on table creation when no PK is specified 
#
# Test case to verify Logical Dump using mysqlpump tool
#
SET @saved_global_sql_generate_invisible_primary_key =
@@global.sql_generate_invisible_primary_key;
SET @saved_session_sql_generate_invisible_primary_key =
@@session.sql_generate_invisible_primary_key;
CREATE TABLE t1 (my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE, f INT,
PRIMARY KEY(my_row_id));
INSERT INTO t1 VALUES (1), (3), (7), (8), (4);
SET SESSION sql_generate_invisible_primary_key=ON;
CREATE TABLE t2(f1 INT INVISIBLE, f2 INT);
INSERT INTO t2(f1, f2) VALUES (10, 20), (20, 30);
# Test case to verify table dump with generated primary key.
# Dump complete test database with generated invisible primary key
# information using mysqlpump tool.
DROP TABLE t1, t2;
# Load from the complete test database dump created using mysqlpump tool."
# Table t1 should list generated primary key information.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `f` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t1;
f
1
3
7
8
4
SELECT my_row_id, f FROM t1;
my_row_id	f
1	1
2	3
3	7
4	8
5	4
# Table t2 should list generated primary key information.
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
  `f2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t2;
f2
20
30
SELECT my_row_id, f1, f2 FROM t2;
my_row_id	f1	f2
1	10	20
2	20	30
# Test case to verify table dump without generated primary key.
# Dump complete "test" database *without* generated invisible primary
# key information using mysqlpump tool.
DROP TABLE t1, t2;
# Load from the test.t3 dump created using mysqlpump tool."
# Table t1 should *not* list generated primary key information.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t1;
f
1
3
7
8
4
SELECT f FROM t1;
f
1
3
7
8
4
# Table t1 should *not* list generated primary key information.
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
  `f2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t2;
f2
20
30
SELECT f1, f2 FROM t2;
f1	f2
10	20
20	30
DROP TABLE t1, t2;
# Test case to verify primary key generation while loading table dump
# obtained *without* generated primary key on a MySQL server with
# @@global.sql_generate_invisible_primary_key = ON.
SET GLOBAL sql_generate_invisible_primary_key = ON;
# Load from dump created without generated primary key information.
# Primary key is generated for the table t1 while loading.
# Table t1 should list generated primary key information.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `f` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t1;
f
1
3
7
8
4
SELECT my_row_id, f FROM t1;
my_row_id	f
1	1
2	3
3	7
4	8
5	4
# Primary key is generated for the table t2 while loading.
# Table t2 should list generated primary key information.
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `f1` int DEFAULT NULL /*!80023 INVISIBLE */,
  `f2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * FROM t2;
f2
20
30
SELECT my_row_id, f1, f2 FROM t2;
my_row_id	f1	f2
1	10	20
2	20	30
# Cleanup
DROP TABLE t1, t2;
SET GLOBAL sql_generate_invisible_primary_key =
@saved_global_sql_generate_invisible_primary_key;
SET SESSION sql_generate_invisible_primary_key =
@saved_session_sql_generate_invisible_primary_key;