It's obvious why it happens, mysqldump currently treats sequences as tables, running SHOW CREATE TABLE and dumping the data, so with no-data all values which are stored in the table are lost. I guess the fix would be to determine inside mysqldump that a table is actually a sequence and handle it accordingly. I'll leave it to serg to decide in which version, if any, it can be done.

We already check in mysqldump if a table is of type sequence or not, which means that fixing this would be really trivial.

It's not clear what one should get in case of '--no-data' as the sequence information is kind of data. However, I assume that what a user would expect is to get a sequence that starts with it's minimal value.

Ok to add a fix for this in 10.3. However, please add a test case that ensures the sequence starts from the minium value!

"It's not clear what one should get in case of '--no-data' as the sequence information is kind of data."

well, that's how it's implemented, but it is actually schema metadata.

When I create a table with a certain auto_increment start value, that value is also preserved in a no-data dump.

So I expect everything given in a CREATE statement to be preserved in a schema only (--no-data) dump.

The actual counter state could be debatable, but the other values like MINVAL, MAXVAL, INCREMENT etc. should be preserved for sure. And even for the actual current counter value we also have sort of set precedence with the current counter value for AUTO_INCREMENT being preserved in schema only dumps so far. So for consistency sake, and by the principle of least surprise, AUTO_INCREMENT and SERIAL should behave the same in this respekt ...

" please add a test case that ensures the sequence starts from the minium value!"

Problem 1: the next minimum value isn't accessible, only the `next_not_cached_value`. While we could use NEXTVAL() to retreive this its disruptive for a mysqldump to trigger this. Hence two more MDEV are going to block this.

MariaDB [test]> CREATE SEQUENCE `s1` start with 50 minvalue 1 maxvalue 9223372036854775806

    -> increment by 1 cache 1000 nocycle ENGINE=Aria;

Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> show create sequence s1;

+-------+--------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                             |

+-------+--------------------------------------------------------------------------------------------------------------------------+

| s1    | CREATE SEQUENCE `s1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria |

+-------+--------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.001 sec)

MariaDB [test]> select nextval(s1);

+-------------+

| nextval(s1) |

+-------------+

|          50 |

+-------------+

1 row in set (0.002 sec)

MariaDB [test]> select nextval(s1);

+-------------+

| nextval(s1) |

+-------------+

|          51 |

+-------------+

1 row in set (0.000 sec)

MariaDB [test]> select nextval(s1);

+-------------+

| nextval(s1) |

+-------------+

|          52 |

+-------------+

1 row in set (0.000 sec)

MariaDB [test]> show create sequence s1;

+-------+--------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                             |

+-------+--------------------------------------------------------------------------------------------------------------------------+

| s1    | CREATE SEQUENCE `s1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria |

+-------+--------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

MariaDB [test]> select * from s1;

+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+

| next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |

+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+

|                  1050 |             1 | 9223372036854775806 |          50 |         1 |       1000 |            0 |           0 |

+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+

danblack please review the patch.

danblack please review new patch

key review points:

• mysqldump needs to contain `SELECT SETVAL(seq, number)` in its output otherwise sequence numbers are re-available after restore.
• because `nextval` can be used a a default value of a column, and tables can cross reference sequences in other databases, all sequence definitions need to be dumped before the tables that they refer to. As there's no way to easy way to do this, all sequence definations need to be dumped before tables.
• as during a mysqldump, a sequence number, may be used in a table that has yet to be dumped. So the next available sequence number (or higher), needs to be in `SELECT SETVAL()` at the end of the dump.

Other sequences mysqldump problems:

Tables that depend on sequences

CREATE DATABASE test2;

USE test1;

CREATE SEQUENCE seq_t_i INCREMENT 5 START WITH 1;

use test2;

CREATE TABLE t(

i integer DEFAULT nextval(test1.seq_t_i),

j integer

);

CREATE SEQUENCE seq_t_i INCREMENT 5 START WITH 1;

USE test1;

CREATE TABLE t(

i integer DEFAULT nextval(test2.seq_t_i),

j integer

);

The sequences need to be created before the table it generates.

portablility

using create sequence in the output and also other std sql (well `setval` is postgres).

the current value or higher of a sequence needs to be saved

This isn't exposed by SHOW CREATE SEQUENCE

Take for instance a table

CREATE SEQUENCE seq_t_i INCREMENT 5 START WITH 1;

CREATE TABLE t(

i integer UNSIGNED NOT NULL DEFAULT nextval(seq_t_i),

j integer,

PRIMARY KEY(i)

);

We can't have a restore that leave the seq_t_i at its initial value of 1 when it restores.

We also need to account for that even with --single-transaction sequences can be retrieve during the dump.

So we need to retreve a value at the end of the dump to ensure it won't conflict with any of the tables already dumped. The mapping between the sequence and its use in a table isn't always a table defination, user logic not embedded into the database can describe this.

what is the next value? - use LASTVAL(sequence_name) , and then output {{SELECT SETVAL(sequence_name,

, 1)}} to indicate its used - https://mariadb.com/kb/en/previous-value-for-sequence_name/ . So that way, a) its accurate, b) dumping doesn't change the value by using nextval, c) the value returned isn't in the existing dataset already dumped.

danblack
lastval will return NULL for the new session (mysqldump), so above algorithm we cannot use.
So I guess to restore the value we may, generate nextval - increment and use that value in setval (val), taking into the account combinations of increment and cycle.
If cycle , val = nextval - increment if increment > 0, val = nextval + increment if increment < 0.
If not cycle I'm not sure how to solve for the edge case when current_value is close to the MAXVALUE.
In that situation nextval will generate an error (MDEV-23831) and we can catch the error in mysqldump but the exact current_value may be not determined correctly, so val for setval may not be determined.
Looking forward to more inputs as:
1. based on above should we proceed with retrieving the values and if yes exactly how to get the current_value, taking into the account all above
2. the status of the last patch
Thanks.

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.

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:

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).

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:

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.

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.