[MDEV-23524] SHOW CREATE SEQUENCE to output restart value Created: 2020-08-20  Updated: 2020-09-11  Resolved: 2020-09-11

Status: Closed
Project: MariaDB Server
Component/s: Sequences
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Daniel Black Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-21786 mysqldump will forget sequence defini... Closed
is blocked by MDEV-23523 CREATE SEQUENCE to accept RESTART WIT... Closed

 Description   

When a user is restoring from a mysqldump, or looking at a galera node previously ssted from a mysqldump sst, we really don't want them panicing that they are missing data that is derived from sequences.

anel's current work has placed the `CREATE SEQUENCES` at the end of the mysqldump, so regardless if its dumped with `--single-transaction` or not, the `show create sequence` at the time its executated will contain a usable sequnce number.

Rather than the original plan of extending `create sequence`, `show create sequence` can return a `setval(seq, nextnum)` as follows:

proposed SHOW CREATE SEQUENCE results

 
| s     | CREATE SEQUENCE `s` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 10 cache 1000 nocycle ENGINE=InnoDB |
 
| s     | SELECT SETVAL(`s`, 1024)                                                                                                  |



 Comments   
Comment by Michael Widenius [ 2020-08-21 ]

Why is the exact value needed?
A sequence does not guarantee that there are no gaps. On a server restart (or a backup) gaps are expected.
There is no way a backup can know what is the next-to-be-used value as this can be cached by the server internals and not visible outside. There is also no need for the backup program to know this.
The simple reason is that sequences are not transactional and during the backup the sequence can be used many times.

The proper way to do a backup is to first backup all tables and then backup the sequence tables. This will ensure that the sequence numbers are higher than any numbers used by the server.

Comment by Michael Widenius [ 2020-08-21 ]

There is no reason to have a restart value in 'show create sequence' for the purpose of backup. The backup.

Note that 'select * from sequence' gives you all information about the sequence values.

Comment by Daniel Black [ 2020-08-21 ]

So the request is that show create sequence shows the next value in the same way as SHOW CREATE TABLE will show the next auto_increment value.

Good point about transaction effects.

Comment by Daniel Black [ 2020-09-03 ]

Reopened, with description change to include justification.

While we don't promise any continuity of numbers, a user restoring from a backup or viewing a galera node after a sst with mysqldump, we'd rather not have them seeing large gaps and spending far too cursing mariadb's dump for a long time before they discover their data is all there.

Comment by Daniel Black [ 2020-09-11 ]

For the moment val=LASTVAL(seqname) and SETVAL(seqname, val, 1) is sufficient.

Generated at Thu Feb 08 09:23:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.