diff options
Diffstat (limited to 'mysql-test/main/mysqldump.result')
| -rw-r--r-- | mysql-test/main/mysqldump.result | 118 |
1 files changed, 118 insertions, 0 deletions
diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index 18f9a924fea..b8dad5daae3 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -5906,4 +5906,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 |