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) |
Attachments
Issue Links
blocks
MDEV-21786mysqldump will forget sequence definition details on --no-data dump
Closed
is blocked by
MDEV-23523CREATE SEQUENCE to accept RESTART WITH (like alter sequence)
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.
Michael Widenius
added a comment - 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.
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.
Michael Widenius
added a comment - 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.
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.
Daniel Black
added a comment - 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.
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.
Daniel Black
added a comment - 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.
Task
Major
MDEV-21786 mysqldump will forget sequence definition details on --no-data dump
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.