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

IMPORT TABLESPACE silently corrupts BLOB data

Details

    Description

      I have copied a .idb data file from an Debian 8 10.0.32 MariaDB to an Debian 9 10.1.26 MariaDB and imported the new tablespace with an identical `CREATE TABLE` statement.

      The IMPORT went without problems, the table was accessible, but the BLOB data column was corrupted. Each BLOB data on the new DB had 3 (seemingly) random bytes at the beginning of the data and was missing 3 bytes at the end.

      Importing the same .ibd file back to the original DB as well as to a different 10.0.32 MariaDB showed correct data.

      `OPTIMIZE TABLE` on the new, imported table didn't fix the problem and didn't show any errors.

      I can provide the `CREATE TABLE` statement and the DB file (~2GB, 240MB compressed) on request.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15225 Can't import .ibd file with temporal type format differing from mysql56_temporal_format

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-15228 Document how to upgrade old temporal columns

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Let's start with the CREATE statement, please do provide it. Thanks.

            elenst Elena Stepanova added a comment - Let's start with the CREATE statement, please do provide it. Thanks.
            sseidel Stefan Seidel added a comment - - edited 

             
            		 
            CREATE TABLE `coverproperties` (
            		 
              `upi` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
            		 
              `source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
            		 
              `timestamp` datetime DEFAULT NULL,
            		 
              `props` blob,
            		 
              PRIMARY KEY (`upi`)
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

            sseidel Stefan Seidel added a comment - - edited   CREATE TABLE `coverproperties` ( `upi` varchar (30) COLLATE utf8_unicode_ci NOT NULL , `source` varchar (255) COLLATE utf8_unicode_ci DEFAULT NULL , ` timestamp ` datetime DEFAULT NULL , `props` blob, PRIMARY KEY (`upi`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE =utf8_unicode_ci ROW_FORMAT=COMPRESSED
            elenst Elena Stepanova added a comment -

            Hi sseidel,
            Sorry, the report fell through the cracks.
            I can reproduce the corruption, on my side the culprit is not the blob, but the timestamp column. 10.1 by default has a different internal format than 10.0. So, if you import the tablespace with the cfg file, it will throw the error right away:

            > alter table coverproperties import tablespace;
            		 
            ERROR 1808 (HY000): Schema mismatch (Column timestamp precise type mismatch.)

            But if you do it without the cfg file, it skips metadata checks and imports the tablespace incorrectly.
            See more details in the documentation: https://mariadb.com/kb/en/library/datetime/#internal-format

            Assuming the reason is the same on your side, the workaround is setting set global mysql56_temporal_format = OFF. After that you should be able to import the tablespace.

            elenst Elena Stepanova added a comment - Hi sseidel , Sorry, the report fell through the cracks. I can reproduce the corruption, on my side the culprit is not the blob, but the timestamp column. 10.1 by default has a different internal format than 10.0. So, if you import the tablespace with the cfg file, it will throw the error right away: > alter table coverproperties import tablespace; ERROR 1808 (HY000): Schema mismatch (Column timestamp precise type mismatch.) But if you do it without the cfg file, it skips metadata checks and imports the tablespace incorrectly. See more details in the documentation: https://mariadb.com/kb/en/library/datetime/#internal-format Assuming the reason is the same on your side, the workaround is setting set global mysql56_temporal_format = OFF . After that you should be able to import the tablespace.

            People

              Unassigned Unassigned
              sseidel Stefan Seidel
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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