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

Create a way to reliably create a table for IMPORT

    XMLWordPrintable

    Details

      Description

      Right now the procedure for a partial backup, and restore on another server, using transportable tablespaces, is:

      • take backup with mariabackup
      • take schema-only backup with `mysqldump --no-data`
      • prepare backup with `mariabackup --prepare --export`
      • create tables on target system, using the schema-only dump, or parts of it
      • DISCARD newly created tables
      • transfer .ibd and .cfg files from backup to correct location
      • IMPORT transferred files

      This approach only works when the newly created table is exactly the same as on the original server. There are several cases though where that may not be the case:

      • table was created without explicit ROW_FORMAT, and the two servers having different innodb_default_row_format settings
      • different mysql56_temporal_types setting on both servers
      • table was originally created on a server version that did not have subsecond support, and contains a TIME, DATETIME or TIMESTAMP column

      In all such cases IMPORT will not work, and the list may not even be complete ...

      So my request is for a way to make sure a table can be created in exactly the same way as on the original server, without having to manually check for differences not covered by SHOW CREATE TABLE output.

      Maybe adding a new SHOW FULL CREATE TABLE command, or an option to mysqldump that makes it gather additional information from the INFORMATION_SCHEMA to rewrite SHOW CREATE output by adding further create options.

      Or have mariabackup itself create appropriate CREATE statement .sql files along with the .cfg files?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              ralf.gebhardt@mariadb.com Ralf Gebhardt
              Reporter:
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: