Details
Critical Description
mysqldump treads sequences the same as other tables, dumping all of them in alphabetical order. A sequence may provide the default value for a column on another table though. So if the table name alphabetically comes before the sequence name, the tables CREATE statement will be in the dump output before the CREATE for the sequence table, and so the table creation will fail as it refers to a sequence not created yet.
How to reproduce:
- create sequence and table:
CREATE SEQUENCE b;
|
CREATE TABLE a (id INT PRIMARY KEY DEFAULT (NEXT VALUE FOR b));
|
- dump database with mysqldump, then restore into an empty database
- restore will fail when trying to create table `a`:
ERROR 1146 (42S02) at line ...: Table 'test.b' doesn't exist
Attachments
Issue Links
- relates to
-
MDEV-33959 mysqldump of all databases cannot be loaded if a table uses a sequence from another schema
-
- Open
-
Activity
Jani Tolonen once made a patch that would fix this by in the beginning of the dump create an inital sequence table and than at the end of the dump add the value to it. This was to ensure that at the end of the dump we will have the latest values of the sequence.
That code was never added to 10.3, but it should have. I think we should add it now as it will make this patch easier.
In any case, to first find the sequences so that we can dump them first, we should change mysqldump to instead of using "mysql_list_tables() to find the tables, instead use performance schema and get both the table name and the engine used.
This would enable us to first dump the sequence definition, then the tables and last the sequence values.
Note that sequence tables should not be part of any LOCK table statement as it's ok that these changes during the dump!
"instead use performance schema and get both the table name and the engine used"
that would be INFORMATION_SCHEMA, not PERFORMANCE_SCHEMA, right? (we can't rely on P_S being enabled anyway)
monty hholzgra so we drop support for version earlier than 5.0.3 (first version with information schema). I hope it is OK?
I'd be ok with that, 4.x has loooong been EOL, and whoever still has it running also has old mysqldump binary of the same version ...
It appeared that we already use Information schema inside, so it is only 5.0.3 and up compatible, so checks of the first information schema version inside looks a bit useless. Maybe check it once and issue warning/error is better solution...
commit 3e01da668d14b29f8b9f3f8dacb16f685d8c825e (HEAD -> bb-10.3-MDEV-21785, origin/bb-10.3-MDEV-21785)
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Wed Jan 13 18:36:48 2021 +0100
|
|
|
MDEV-21785: sequences used as default by other table not dumped in right order by mysqldump
|
|
Dump sequences first.
|
|
This atch made to keep it small and
|
to keep number of queries to the server the same.
|
|
Order of tables in a dump can not be changed
|
(except sequences first) because mysql_list_tables
|
uses SHOW TABLES and I used SHOW FULL TABLES.
|
I can add to that. In such a case, SHOW CREATE TABLE a refers to b by fully qualified name, e.g.:
So, if the dump is restored into a different database, there will be other problems – either an error if the sequence in the initial schema no longer exists, or the table will end up referencing a wrong sequence.
Something needs to be done (at least about the initially reported problem), but I don't know what should be a fix for either of this – either we should have a variable which temporarily suppresses the reference check, like it happens with foreign keys, or the tricky logic with fake temporary tables, as it happens with views, or recognizing sequences (which might also be needed due to
MDEV-21786) and dumping them first, or something else.