[MDEV-15544] IMPORT TABLESPACE silently corrupts BLOB data Created: 2018-03-12  Updated: 2018-11-02  Resolved: 2018-11-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.1.26
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Stefan Seidel Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback, upstream
Environment:

Debian 8, Debian 9

Issue Links:
Duplicate
duplicates MDEV-15225 Can't import .ibd file with temporal ... Closed
Relates
relates to MDEV-15228 Document how to upgrade old temporal ... Closed

 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.

 Comments   
Comment by Elena Stepanova [ 2018-03-16 ]

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

Comment by Stefan Seidel [ 2018-03-19 ] 

 
 
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

Comment by Elena Stepanova [ 2018-10-05 ]

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.

Generated at Thu Feb 08 08:22:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.