From 81870e499ff14afacc7e9cabde65a626165f209f Mon Sep 17 00:00:00 2001 From: Anel Husakovic Date: Mon, 31 Aug 2020 16:36:32 +0200 Subject: MDEV-21786 mysqldump will forget sequence definition details on --no-data dump - Original patch was contributed by Jani Tolonen https://github.com/an3l/server/commits/bb-10.3-anel-MDEV-21786-dump-sequence which distinguishes data structure (linked list) of sequences from tables. - Added standard sql output to prevent future changes of sequences and disabled locks for sequences. - Added test case for `MDEV-20070: mysqldump won't work correct on sequences` where table column depends on sequence value. - Restore sequence last value in the following way: - Find `next_not_cached_value` and use it to `setval()` - We just need for logical restore, so don't execute `setval()` - `setval()` should be showed also in case of `--no-data` option. Reviewed-by: daniel@mariadb.org --- mysql-test/main/mysqldump.result | 118 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/mysqldump.test | 83 +++++++++++++++++++++++++++ 2 files changed, 201 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index 5f3f0d89593..622ef745859 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -5904,4 +5904,122 @@ invisible int(11) YES NULL a b c & $!@#$%^&*( ) int(11) YES 4 INVISIBLE ds=~!@ \# $% ^ & * ( ) _ - = + int(11) YES 5 INVISIBLE drop database d; +# +# MDEV-21786: +# mysqldump will forget sequence definition details on --no-data dump +# +create database d; +CREATE SEQUENCE d.s1 START WITH 100 INCREMENT BY 10 MINVALUE=100 MAXVALUE=1100 CYCLE; +CREATE SEQUENCE d.s2 START WITH 200 INCREMENT BY 20 MINVALUE=200 MAXVALUE=1200 CYCLE; +CREATE SEQUENCE d.s3 START WITH 300 INCREMENT BY 30 MINVALUE=300 MAXVALUE=1300 CYCLE; +CREATE SEQUENCE d.s4 START WITH 400 INCREMENT BY 40 MINVALUE=400 MAXVALUE=1400 CYCLE; +SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); +NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) +100 200 300 400 +# Show create before dump +show create sequence d.s1; +Table Create Table +s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s2; +Table Create Table +s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s3; +Table Create Table +s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s4; +Table Create Table +s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM +# Dump sequence without `--no-data` +# Restore from mysqldump +SETVAL(`s1`, 1101, 0) +1101 +SETVAL(`s2`, 1201, 0) +1201 +SETVAL(`s3`, 1301, 0) +1301 +SETVAL(`s4`, 1401, 0) +1401 +# Show create after restore +show create sequence d.s1; +Table Create Table +s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s2; +Table Create Table +s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s3; +Table Create Table +s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s4; +Table Create Table +s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM +SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); +NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) +100 200 300 400 +# Dump sequence with `--no-data` +# Restore from mysqldump +SETVAL(`s1`, 1101, 0) +1101 +SETVAL(`s2`, 1201, 0) +1201 +SETVAL(`s3`, 1301, 0) +1301 +SETVAL(`s4`, 1401, 0) +1401 +# Show create after restore `--no-data` +show create sequence d.s1; +Table Create Table +s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s2; +Table Create Table +s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s3; +Table Create Table +s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM +show create sequence d.s4; +Table Create Table +s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM +SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); +NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) +100 200 300 400 +# Restore to different database than original +create database d2; +SETVAL(`s1`, 1101, 0) +1101 +SETVAL(`s2`, 1201, 0) +1201 +SETVAL(`s3`, 1301, 0) +1301 +SETVAL(`s4`, 1401, 0) +1401 +show create sequence d2.s1; +Table Create Table +s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +drop sequence d.s1, d.s2, d.s3, d.s4; +drop database d; +drop database d2; +# +# MDEV-20070 +# mysqldump won't work correct on sequences +# +DROP DATABASE IF EXISTS test1; +Warnings: +Note 1008 Can't drop database 'test1'; database doesn't exist +DROP DATABASE IF EXISTS test2; +Warnings: +Note 1008 Can't drop database 'test2'; database doesn't exist +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +CREATE SEQUENCE seq_t_i INCREMENT 5 START WITH 1; +CREATE TABLE t( +i integer DEFAULT nextval(seq_t_i), +j integer +); +INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4); +# Dump database 1 +# Restore from database 1 to database 2 +SETVAL(`seq_t_i`, 1, 0) +1 +DROP DATABASE IF EXISTS test1; +DROP DATABASE IF EXISTS test2; # End of 10.3 tests diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index 10dc9bf6d96..662ff077d91 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -2793,4 +2793,87 @@ select * from t3; desc t3; drop database d; +--echo # +--echo # MDEV-21786: +--echo # mysqldump will forget sequence definition details on --no-data dump +--echo # +create database d; + +CREATE SEQUENCE d.s1 START WITH 100 INCREMENT BY 10 MINVALUE=100 MAXVALUE=1100 CYCLE; +CREATE SEQUENCE d.s2 START WITH 200 INCREMENT BY 20 MINVALUE=200 MAXVALUE=1200 CYCLE; +CREATE SEQUENCE d.s3 START WITH 300 INCREMENT BY 30 MINVALUE=300 MAXVALUE=1300 CYCLE; +CREATE SEQUENCE d.s4 START WITH 400 INCREMENT BY 40 MINVALUE=400 MAXVALUE=1400 CYCLE; +SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); + +--echo # Show create before dump +show create sequence d.s1; +show create sequence d.s2; +show create sequence d.s3; +show create sequence d.s4; + +--echo # Dump sequence without `--no-data` +--exec $MYSQL_DUMP --databases d > $MYSQLTEST_VARDIR/tmp/dump1.sql +--echo # Restore from mysqldump +--exec $MYSQL -Dd < $MYSQLTEST_VARDIR/tmp/dump1.sql +--remove_file $MYSQLTEST_VARDIR/tmp/dump1.sql + +--echo # Show create after restore +show create sequence d.s1; +show create sequence d.s2; +show create sequence d.s3; +show create sequence d.s4; +SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); + +--echo # Dump sequence with `--no-data` +--exec $MYSQL_DUMP --databases d --no-data > $MYSQLTEST_VARDIR/tmp/dump-no-data.sql +--echo # Restore from mysqldump +--exec $MYSQL -Dd < $MYSQLTEST_VARDIR/tmp/dump-no-data.sql +--remove_file $MYSQLTEST_VARDIR/tmp/dump-no-data.sql + +--echo # Show create after restore `--no-data` +show create sequence d.s1; +show create sequence d.s2; +show create sequence d.s3; +show create sequence d.s4; +SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); + +--echo # Restore to different database than original +--exec $MYSQL_DUMP d > $MYSQLTEST_VARDIR/tmp/dumpd.sql +create database d2; +--exec $MYSQL d2 < $MYSQLTEST_VARDIR/tmp/dumpd.sql +--remove_file $MYSQLTEST_VARDIR/tmp/dumpd.sql +show create sequence d2.s1; + +drop sequence d.s1, d.s2, d.s3, d.s4; +drop database d; +drop database d2; + +--echo # +--echo # MDEV-20070 +--echo # mysqldump won't work correct on sequences +--echo # + +DROP DATABASE IF EXISTS test1; +DROP DATABASE IF EXISTS test2; +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +CREATE SEQUENCE seq_t_i INCREMENT 5 START WITH 1; +CREATE TABLE t( +i integer DEFAULT nextval(seq_t_i), +j integer +); +INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4); + +--echo # Dump database 1 +--exec $MYSQL_DUMP test1 > $MYSQLTEST_VARDIR/tmp/dumptest1.sql +--echo # Restore from database 1 to database 2 + +--error 1 +--exec $MYSQL test2 < $MYSQLTEST_VARDIR/tmp/dumptest1.sql + +--remove_file $MYSQLTEST_VARDIR/tmp/dumptest1.sql +DROP DATABASE IF EXISTS test1; +DROP DATABASE IF EXISTS test2; + --echo # End of 10.3 tests -- cgit v1.2.1