Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21786

mysqldump will forget sequence definition details on --no-data dump

Details

    Description

      How to reproduce:

      • create a sequence that uses many optional settings, like:

      CREATE SEQUENCE s START WITH 100 INCREMENT BY 10 MINVALUE=100 MAXVALUE=1000 CYCLE;
      

      Dump and restore it with regular mysqldump, and do SHOW CREATE SEQUENCE s:

      MariaDB [test]> show create sequence s;
      +-------+--------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                 |
      +-------+--------------------------------------------------------------------------------------------------------------+
      | s     | CREATE SEQUENCE `s` start with 100 minvalue 100 maxvalue 1000 increment by 10 cache 1000 cycle ENGINE=InnoDB |
      +-------+--------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      

      So the sequence was restored perfectly. Now lets try the same with a --no-data dump:

      MariaDB [test]> show create sequence s;
      +-------+--------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                             |
      +-------+--------------------------------------------------------------------------------------------------------------------------+
      | s     | CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB |
      +-------+--------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      

      Now the sequence was recreated completely differently, only its name was preserved, and the CACHE value matches by chance as the original create didn't specify that one, so that it was using the default.

      Attachments

        Issue Links

          Activity

            danblack based on above (not taking into account restoring the values from sequence until right algorithm is determined) and addressing the review here is the new patch.

            anel Anel Husakovic added a comment - danblack based on above (not taking into account restoring the values from sequence until right algorithm is determined) and addressing the review here is the new patch .
            danblack Daniel Black added a comment - - edited

            restore values

            parsing cycle, increment, next value is unnecessary, if you have a value, `SETVAL(sequence_name, next_value, is_used)` is_used can be set a value of 0 on the restore to ensure the next value used is that value

            e.g. fiddle:

            <!-- -->
            >     select * from bob
            > 
            > <pre>
            > next_not_cached_value | minimum_value |       maximum_value | start_value | increment | cache_size | cycle_option | cycle_count
            > --------------------: | ------------: | ------------------: | ----------: | --------: | ---------: | -----------: | ----------:
            >                  1001 |             1 | 9223372036854775806 |           1 |         1 |       1000 |            0 |           0
            > </pre>
             
            <!-- -->
            >     select setval(bob, 7, 0)
            > 
            > <pre>
            > | setval(bob, 7, 0) |
            > | ----------------: |
            > |                 7 |
            > </pre>
             
            <!-- -->
            >     select nextval(bob)
            > 
            > <pre>
            > | nextval(bob) |
            > | -----------: |
            > |            7 |
            > </pre>
            

            next value which?

            As you correctly pointed out lastval is unusable.

            Two options exist:

            Call nextval(sequence_name)

            pro: on restoration will contain an accurate number
            con: will consume a sequence number away from the application at dump time

            select next_not_cached_value from sequencename

            pro: will not consume a sequence number of the live system
            con: will leave a cached gap in sequences on restore.

            At the moment I think this is the preferable option. A SQL output file commenting that there may be lost values.

            conclusion

            So in conclusion:

            At the end of the dump, there will be for all sequences in the dump, output like for a single database dump:

            set values for sequences (single database dump)

            SELECT  SETVAL(sequence_name, {next_not_cached_value}, 0)
            

            If there was a single database, sequence_name will contain just the sequence name (for importing to a different database).

            set values for sequences (multiple database dump)

            SELECT  SETVAL(db1.sequence_name, {next_not_cached_value}, 0);
            SELECT  SETVAL(db1.sequence_name2, {next_not_cached_value}, 0);
             
            SELECT  SETVAL(db2.sequence_name, {next_not_cached_value}, 0);
            SELECT  SETVAL(db2.sequence_name2, {next_not_cached_value}, 0);
            

            or alternately separated:

            set values for sequences (multiple database dump)

            use db1;
            SELECT  SETVAL(sequence_name, {next_not_cached_value}, 0);
            SELECT  SETVAL(sequence_name2, {next_not_cached_value}, 0);
             
            use db2;
            SELECT  SETVAL(sequence_name, {next_not_cached_value}, 0);
            SELECT  SETVAL(sequence_name2, {next_not_cached_value}, 0);
            

            danblack Daniel Black added a comment - - edited restore values parsing cycle, increment, next value is unnecessary, if you have a value, `SETVAL(sequence_name, next_value, is_used )` is_used can be set a value of 0 on the restore to ensure the next value used is that value e.g. fiddle : <!-- --> > select * from bob > > <pre> > next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count > --------------------: | ------------: | ------------------: | ----------: | --------: | ---------: | -----------: | ----------: > 1001 | 1 | 9223372036854775806 | 1 | 1 | 1000 | 0 | 0 > </pre>   <!-- --> > select setval(bob, 7, 0) > > <pre> > | setval(bob, 7, 0) | > | ----------------: | > | 7 | > </pre>   <!-- --> > select nextval(bob) > > <pre> > | nextval(bob) | > | -----------: | > | 7 | > </pre> next value which? As you correctly pointed out lastval is unusable. Two options exist: Call nextval(sequence_name) pro: on restoration will contain an accurate number con: will consume a sequence number away from the application at dump time select next_not_cached_value from sequencename pro: will not consume a sequence number of the live system con: will leave a cached gap in sequences on restore. At the moment I think this is the preferable option. A SQL output file commenting that there may be lost values. conclusion So in conclusion: At the end of the dump, there will be for all sequences in the dump, output like for a single database dump: set values for sequences (single database dump) SELECT SETVAL(sequence_name, {next_not_cached_value}, 0) If there was a single database, sequence_name will contain just the sequence name (for importing to a different database). set values for sequences (multiple database dump) SELECT SETVAL(db1.sequence_name, {next_not_cached_value}, 0); SELECT SETVAL(db1.sequence_name2, {next_not_cached_value}, 0);   SELECT SETVAL(db2.sequence_name, {next_not_cached_value}, 0); SELECT SETVAL(db2.sequence_name2, {next_not_cached_value}, 0); or alternately separated: set values for sequences (multiple database dump) use db1; SELECT SETVAL(sequence_name, {next_not_cached_value}, 0); SELECT SETVAL(sequence_name2, {next_not_cached_value}, 0);   use db2; SELECT SETVAL(sequence_name, {next_not_cached_value}, 0); SELECT SETVAL(sequence_name2, {next_not_cached_value}, 0);

            Pushed to 10.3 with commit 81870e499ff14af.

            anel Anel Husakovic added a comment - Pushed to 10.3 with commit 81870e499ff14af .
            jpp JP Pozzi added a comment -

            Hello,

            When I dump a table with a column linked to a sequence mysqldump (--no-create-table) only write "lock tables table_name write;".
            When you want to restore the dump you get a nasty message :
            ERROR 1100 (HY000) at line 23 in file: 'ALERT_V2.dump_original': Table 'sequence_name' was not locked with LOCK TABLES
            "sequence_name" is the name of the sequence used by the table "table_name".
            When I "correct" the dump file with the two names "lock tables table_name sequence_name write;" the
            restore is working as it does.

            Is there a solution ?

            Regards

            JP P

            jpp JP Pozzi added a comment - Hello, When I dump a table with a column linked to a sequence mysqldump (--no-create-table) only write "lock tables table_name write;". When you want to restore the dump you get a nasty message : ERROR 1100 (HY000) at line 23 in file: 'ALERT_V2.dump_original': Table 'sequence_name' was not locked with LOCK TABLES "sequence_name" is the name of the sequence used by the table "table_name". When I "correct" the dump file with the two names "lock tables table_name sequence_name write;" the restore is working as it does. Is there a solution ? Regards JP P

            Hi jpp, yes there is MDEV-20070 related to that, it is still not fixed.

            anel Anel Husakovic added a comment - Hi jpp , yes there is MDEV-20070 related to that, it is still not fixed.

            People

              anel Anel Husakovic
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.