mysqlpump_extended.test

-- source include/not_embedded.inc

--echo #
--echo # WL#7755  mysqlpump: Extend mysqldump functionalities
--echo #

--echo #
--echo # Test all newly added mysqlpump options
--echo #

CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

CREATE DATABASE db2_extended;
USE db2_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

--echo # test exclude-databases
--exec $MYSQL_PUMP --add-drop-table --exclude-databases=db1_extended,mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db2_extended;
DROP DATABASE db1_extended;
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW DATABASES;
DROP DATABASE db2_extended;

--echo # test exclude-events
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));
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;

--exec $MYSQL_PUMP --databases db1_extended --exclude-events=ev2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
       WHERE EVENT_SCHEMA='db1_extended' ORDER BY EVENT_NAME;

--echo # test exclude-routines
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

CREATE PROCEDURE p1 () SELECT * FROM t1;
CREATE PROCEDURE p2 () SELECT * FROM t2;
CREATE PROCEDURE p3 () SELECT * FROM t1,t2;

--exec $MYSQL_PUMP --databases db1_extended --exclude-routines=p1,p2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
       WHERE ROUTINE_SCHEMA='db1_extended' AND ROUTINE_TYPE= 'PROCEDURE'
       ORDER BY ROUTINE_NAME;

--echo # test exclude-tables
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));
CREATE TABLE t3 (a VARCHAR(10));

--exec $MYSQL_PUMP --databases db1_extended --exclude-tables=t1,t2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW TABLES FROM db1_extended;

--echo # test exclude-triggers
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

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;

--exec $MYSQL_PUMP --databases db1_extended --exclude-triggers=trig2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='db1_extended' ORDER BY TRIGGER_NAME;

--echo # test include-events
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));
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;

--exec $MYSQL_PUMP --databases db1_extended --include-events=ev2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT EVENT_NAME FROM INFORMATION_SCHEMA.EVENTS
       WHERE EVENT_SCHEMA='db1_extended' ORDER BY EVENT_NAME;

--echo # test include-routines
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

CREATE PROCEDURE p1 () SELECT * FROM t1;
CREATE PROCEDURE p2 () SELECT * FROM t2;
CREATE PROCEDURE p3 () SELECT * FROM t1,t2;

--exec $MYSQL_PUMP --databases db1_extended --include-routines=p1,p2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
       WHERE ROUTINE_SCHEMA='db1_extended' AND ROUTINE_TYPE= 'PROCEDURE'
       ORDER BY ROUTINE_NAME;

--echo # test include-tables
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));
CREATE TABLE t3 (a VARCHAR(10));

--exec $MYSQL_PUMP --databases db1_extended --include-tables=t1,t2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW TABLES FROM db1_extended;

--echo # test include-triggers
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

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;

--exec $MYSQL_PUMP --databases db1_extended --include-triggers=trig2 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
CREATE DATABASE db1_extended;
USE db1_extended;
--exec $MYSQL db1_extended < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='db1_extended' ORDER BY TRIGGER_NAME;
DROP DATABASE db1_extended;

--echo # test parallel-schemas

CREATE DATABASE db1_extended;
CREATE DATABASE db2_extended;
CREATE DATABASE db3_extended;
CREATE DATABASE db4_extended;

USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

USE db2_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

USE db3_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

USE db4_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

--exec $MYSQL_PUMP --parallel-schemas=db2_extended,db4_extended --exclude-databases=db1_extended,db3_extended,mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_extended.sql

DROP DATABASE db1_extended;
DROP DATABASE db2_extended;
DROP DATABASE db3_extended;
DROP DATABASE db4_extended;

--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW DATABASES;
DROP DATABASE db2_extended;
DROP DATABASE db4_extended;

--echo # test skip-dump-rows

CREATE DATABASE db1_extended;
USE db1_extended;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

INSERT INTO t1 VALUES (1),(2),(21),(12),(41),(22),(51),(24);
INSERT INTO t2 VALUES (1,'abc'),(2,'abc'),(21,'abc'),(12,'abc');

SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;

--exec $MYSQL_PUMP --databases db1_extended --skip-dump-rows > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP DATABASE db1_extended;
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

USE db1_extended;
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;
#cleanup
DROP DATABASE db1_extended;

--echo # test --users

CREATE USER u1@localhost IDENTIFIED BY 'abc';
CREATE USER u2;
--exec $MYSQL_PUMP --users --exclude-users=root,mysql.sys,mysql.session --exclude-databases=mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP USER u1@localhost,u2;
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SELECT user FROM mysql.user WHERE user like 'u%';
DROP USER u1@localhost,u2;

--echo # test --include-users

CREATE USER u1@localhost IDENTIFIED BY 'abc';
GRANT SELECT ON mysql.user to u1@localhost;
CREATE USER u1@120.0.0.1;
GRANT INSERT,UPDATE,DELETE ON mysql.* to u1@120.0.0.1;
CREATE USER u2;
CREATE USER u3@120.0.0.1;
GRANT SELECT ON mysql.user to u3@120.0.0.1;

--exec $MYSQL_PUMP --exclude-databases=mysql,mtr --include-users=u2,u1@120.0.0.1 > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP USER u1@localhost,u3@120.0.0.1,u1@120.0.0.1,u2;
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

# report u1,u2 user rows
SELECT user FROM mysql.user WHERE user like 'u%';
DROP USER u1@120.0.0.1,u2;

--echo # test --exclude-users

CREATE USER u1@localhost IDENTIFIED BY 'abc';
GRANT SELECT ON mysql.user to u1@localhost;
CREATE USER u1@120.0.0.1;
GRANT INSERT,UPDATE,DELETE ON mysql.* to u1@120.0.0.1;
CREATE USER u2;
CREATE USER u3@120.0.0.1;
GRANT SELECT ON mysql.user to u3@120.0.0.1;

--exec $MYSQL_PUMP --exclude-databases=mysql,mtr --exclude-users=u2,u1@120.0.0.1,root,mysql.sys,mysql.session > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP USER u1@localhost,u3@120.0.0.1,u1@120.0.0.1,u2;
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

# report u1,u3 user rows
SELECT user FROM mysql.user WHERE user like 'u%';
DROP USER u1@localhost,u3@120.0.0.1;

--echo # test add-drop-user

CREATE USER u1@localhost IDENTIFIED BY 'abc';
--exec $MYSQL_PUMP --exclude-databases=mysql,mtr --exclude-users=root,mysql.sys,mysql.session --add-drop-user > $MYSQLTEST_VARDIR/tmp/db1_extended.sql
DROP USER u1@localhost;
# if restore reports error it means db1_extended.sql has DROP USER stmt
--error 1
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

--echo # test skip-definer for views

CREATE DATABASE db1_extended;
USE db1_extended;
CREATE USER u1@localhost;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b VARCHAR(10), primary key(a));

INSERT INTO t1 VALUES (289), (298), (234), (456), (789);
INSERT INTO t2 VALUES (1, "on"), (2, "off"), (10, "pol"), (12, "meg");

CREATE DEFINER = u1@localhost VIEW v1 AS SELECT * FROM t2;
CREATE DEFINER = u1@localhost VIEW v2 AS SELECT t1.a as X, t2.* FROM t1,t2 ORDER BY X;
CREATE DEFINER = u1@localhost VIEW definer_name_view AS SELECT * FROM t1 union SELECT a FROM t2;

#test without skip-definer
--exec $MYSQL_PUMP --exclude-databases=mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_extended.sql

DROP DATABASE db1_extended;
# now restore and check for view definitions
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW CREATE VIEW db1_extended.v1;
SHOW CREATE VIEW db1_extended.v2;
SHOW CREATE VIEW db1_extended.definer_name_view;

#test with skip-definer
--exec $MYSQL_PUMP --exclude-databases=mysql,mtr --skip-definer > $MYSQLTEST_VARDIR/tmp/db1_extended.sql

DROP DATABASE db1_extended;
# now restore and check for view definitions
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW CREATE VIEW db1_extended.v1;
SHOW CREATE VIEW db1_extended.v2;
SHOW CREATE VIEW db1_extended.definer_name_view;

#cleanup
DROP DATABASE db1_extended;
DROP USER u1@localhost;

--echo # test skip-definer for stored routines (FUNCTIONS/PROCEDURES)

CREATE DATABASE db1_extended;
USE db1_extended;
CREATE USER u1@localhost;
CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (289), (298), (234), (456), (789);

DELIMITER |;
CREATE DEFINER = u1@localhost PROCEDURE p1()
BEGIN
  SET @A= 20;
  SELECT 1;
  SELECT 2, 3 UNION SELECT 4, 5;
  SELECT 6, 7, 8; 
END ;|

CREATE DEFINER = u1@localhost FUNCTION f1 () RETURNS int
BEGIN
SET NAMES 'big5';
RETURN (SELECT COUNT(*) FROM t1);
END ;|

CREATE DEFINER = u1@localhost FUNCTION f2() RETURNS int
BEGIN
  DECLARE n int;
  DECLARE m int;
  SET n:= (SELECT min(a) FROM t1);
  SET m:= (SELECT max(a) FROM t1);
  RETURN n < m;
END ;|

CREATE DEFINER = u1@localhost PROCEDURE p2(x int)
  INSERT INTO t1 VALUES (x) ;|

DELIMITER ;|

#test without skip-definer
--exec $MYSQL_PUMP --exclude-databases=mysql,mtr > $MYSQLTEST_VARDIR/tmp/db1_extended.sql

DROP DATABASE db1_extended;
# now restore and check for view definitions
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW CREATE FUNCTION db1_extended.f1;
SHOW CREATE FUNCTION db1_extended.f2;
SHOW CREATE PROCEDURE db1_extended.p1;
SHOW CREATE PROCEDURE db1_extended.p2;

#test with skip-definer
--exec $MYSQL_PUMP --exclude-databases=mysql,mtr --skip-definer > $MYSQLTEST_VARDIR/tmp/db1_extended.sql

DROP DATABASE db1_extended;
# now restore and check for view definitions
--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/db1_extended.sql
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.sql

SHOW CREATE FUNCTION db1_extended.f1;
SHOW CREATE FUNCTION db1_extended.f2;
SHOW CREATE PROCEDURE db1_extended.p1;
SHOW CREATE PROCEDURE db1_extended.p2;

#cleanup
DROP DATABASE db1_extended;
DROP USER u1@localhost;

--echo # test for compress-output
--exec $MYSQL_PUMP --all-databases --compress-output=zlib > $MYSQLTEST_VARDIR/tmp/db1_extended.zlib
--exec $MYSQL_PUMP --all-databases --compress-output=lz4 > $MYSQLTEST_VARDIR/tmp/db1_extended.lz4

--file_exists $MYSQLTEST_VARDIR/tmp/db1_extended.zlib
--file_exists $MYSQLTEST_VARDIR/tmp/db1_extended.lz4

#cleanup
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.zlib
--remove_file $MYSQLTEST_VARDIR/tmp/db1_extended.lz4