diff options
author | Anel Husakovic <anel@mariadb.org> | 2020-08-31 16:36:32 +0200 |
---|---|---|
committer | Anel Husakovic <anel@mariadb.org> | 2020-10-23 09:06:56 +0200 |
commit | 81870e499ff14afacc7e9cabde65a626165f209f (patch) | |
tree | d53adb2ea3c39f42629dac36eb8bbb0ae1fd0c28 /mysql-test/main/mysqldump.result | |
parent | e3d692aa092a76415ce1ce0e9662338873d84abb (diff) | |
download | mariadb-git-81870e499ff14afacc7e9cabde65a626165f209f.tar.gz |
MDEV-21786 mysqldump will forget sequence definition details on --no-data dump
- Original patch was contributed by Jani Tolonen <jani.k.tolonen@gmail.com>
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
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 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 |