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.
665 lines
23 KiB
665 lines
23 KiB
5 months ago
|
#
|
||
|
|
# 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\]" 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(11) 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(11) 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';
|