Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30155

SEQUENCEs dumped with mariadb-dump cannot be restored in different DB

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6
    • Fix Version/s: 10.6, 10.7, 10.8, 10.9, 10.10
    • Component/s: None
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              TheLinuxJedi Andrew Hutchings
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.