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

SEQUENCEs/VIEWS/TRIGGERS/PROCEDURES dumped with mariadb-dump cannot be restored in different DB

Details

    • New Feature
    • Status: Confirmed (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.6
    • None
    • 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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20070 cannot insert into write locked tables with sequences

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Task - A task that needs to be done. MDEV-30280 Create a server option for a backup mode

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDBF-513 Mydumper: Add SEQUENCEs support

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            TheLinuxJedi Andrew Hutchings (Inactive) created issue -
            TheLinuxJedi Andrew Hutchings (Inactive) made changes -
            Field Original Value New Value
            anel Anel Husakovic made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            serg Sergei Golubchik made changes -
            Assignee Oleksandr Byelkin [ sanja ]
            TheLinuxJedi Andrew Hutchings (Inactive) made changes -
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 105495
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            julien.fritsch Julien Fritsch made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            sanja Oleksandr Byelkin made changes -
            Summary SEQUENCEs dumped with mariadb-dump cannot be restored in different DB SEQUENCEs/VIEWS/TRIGGERS/PROCEDURES dumped with mariadb-dump cannot be restored in different DB
            sanja Oleksandr Byelkin made changes -
            Affects Version/s 10.5 [ 23123 ]
            sanja Oleksandr Byelkin made changes -
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Issue Type Task [ 3 ] New Feature [ 2 ]

            People

              sanja Oleksandr Byelkin
              TheLinuxJedi Andrew Hutchings (Inactive)
              Votes:
              8 Vote for this issue
              Watchers:
              8 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.