[MDEV-30155] SEQUENCEs dumped with mariadb-dump cannot be restored in different DB Created: 2022-12-05  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Andrew Hutchings Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-20070 cannot insert into write locked table... Open
relates to MDEV-30280 Create a server option for a backup mode Open
relates to MDBF-513 Mydumper: Add SEQUENCEs support Closed

 Description   

If you create the following table (assuming you already created the sequence):

create table t1 (a int primary key default (next value for s), b int);

The SHOW CREATE TABLE output (and therefore mariadb-dump output) gives:

CREATE TABLE `t1` (
  `a` int(11) NOT NULL DEFAULT nextval(`test`.`s`),
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

This turns the implicit schema into an explicit one. This table can only be restored into the schema "test" due to this.

A common pattern is to dump from one schema and restore into another. This cannot be done due to the above.

Whilst we can change the SHOW CREATE TABLE behaviour, this won't retrospectively fix things. We might need some kind of schema regex filter in mariadb-dump for this to check for things like nextval() against the current schema and remove the schema part.



 Comments   
Comment by Andrew Hutchings [ 2022-12-05 ]

For crediting purposes, this was found by David Ducos working on mydumper: https://github.com/mydumper/mydumper/discussions/931

Generated at Thu Feb 08 10:14:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.