[MDEV-27779] Cannot import into 10.6.5 a table exported from 10.3.14 due to precise type mismatch on varchar Created: 2022-02-08  Updated: 2023-09-15

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.3.14, 10.6.5
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Daniel Ragle Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: import, tablespace
Environment:

Linux


 Description   

In brief, trying to import a simple InnoDB test table created on a 10.3.14 server into a 10.6.5 server fails with:

ERROR 1808 (HY000): Schema mismatch (Column the_data precise type mismatch, it's 0X2D000F in the table and 0X8000F in the tablespace meta file)

I followed the instructions here:

https://mariadb.com/kb/en/innodb-file-per-table-tablespaces/#copying-transportable-tablespaces-for-non-partitioned-tables

Specific statements follow. On the 10.3.14 server:

SELECT @@GLOBAL.innodb_default_row_format; /* dynamic */
 
DROP DATABASE IF EXISTS MyDB;
 
CREATE DATABASE MyDB;
 
CREATE TABLE MyDB.t1 ( the_key INT PRIMARY KEY AUTO_INCREMENT, the_data VARCHAR(10) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
 
INSERT INTO MyDB.t1 (the_data) VALUES ('here'),('there'),('everywhere');
 
FLUSH TABLES MyDB.t1 FOR EXPORT;
 
system cp -p /<datadir>/MyDB/t1.cfg /tests/innodb_import/.
 
system cp -p /<datadir>/MyDB/t1.ibd /tests/innodb_import/.
 
UNLOCK TABLES;

And then, after copying the t1.cfg and t1.ibd files over to the target 10.6.5 server (also into the /tests/innodb_import folder, with appropriate ownership and permissions):

SELECT @@GLOBAL.innodb_default_row_format; /* dynamic */
 
DROP DATABASE IF EXISTS MyDB;
 
CREATE DATABASE MyDB;
 
CREATE TABLE MyDB.t1 ( the_key INT PRIMARY KEY AUTO_INCREMENT, the_data VARCHAR(10) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
 
ALTER TABLE MyDB.t1 DISCARD TABLESPACE;
 
system cp -p /tests/innodb_import/t1.ibd /<datadir>/MyDB/t1.ibd
 
system cp -p /tests/innodb_import/t1.cfg /<datadir>/MyDB/t1.cfg
 
ALTER TABLE MyDB.t1 IMPORT TABLESPACE;
 
ERROR 1808 (HY000): Schema mismatch (Column the_data precise type mismatch, it's 0X2D000F in the table and 0X8000F in the tablespace meta file)

The error comes when trying to import the table. I also tried it without the .cfg (only copying the .ibd to the target directory and not the .cfg) and with that I get a different error:

ERROR 1808 (HY000): Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x21.)

And I also tried the whole thing shutting down the source server (with innodb_fast_shutdown = 0) before copying, with the same results (in that case I only copied the .ibd file).

I read with interest MDEV-15225, however it seems mostly focused on temporal types which I'm not using here. It does suggest, however, that there may be data types that cause problems between versions other than just temporal, and mentions a three-file (with the .frm) import as a possible general solution to the issue (MDEV-26137) and the ongoing work to streamline the import process (MDEV-11658).

But in all that, I don't see a work-around for my specific issue. Is there one? Or am I going about it wrong?

 Comments   
Comment by Marko Mäkelä [ 2022-02-08 ]

What does SHOW CREATE TABLE report for the table, in each server? The InnoDB "precise type" depends on the character set and collation.

Comment by Daniel Ragle [ 2022-02-08 ]

Character set fixed the issue with the "precise type" mismatch; when I add the same DEFAULT CHARSET to both sides I am able to import when I use the .cfg file, thanks.

However, the .ibd only import (without the .cfg) is still problematic. It still gives 

ERROR 1808 (HY000): Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x21.)

I thought that explicitly adding the ROW_FORMAT=DYNAMIC on both sides would fix that, but still getting it.

The create table now looks like this (it's identical on both sides, now that I am specifying DEFAULT CHARSET):

CREATE TABLE `t1` (
 
  `the_key` int(11) NOT NULL AUTO_INCREMENT,
 
  `the_data` varchar(10) DEFAULT NULL,
 
  PRIMARY KEY (`the_key`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

Comment by Marko Mäkelä [ 2023-09-15 ]

dragle, sorry, I missed your update. The tablespace flags 0x15 are what innodb_checksum_algorithm=full_crc32 (the default since MDEV-19534) uses for a table with innodb_page_size=16k. The flags 0x21 should correspond to a ROW_FORMAT=DYNAMIC table in innodb_page_size=16k using innodb_checksum_algorithm=crc32 or older. You could try creating the table explicitly in the old-checksum format, before importing:

SET GLOBAL innodb_checksum_algorithm=crc32;
 
CREATE TABLE t1 …;
 
SET GLOBAL innodb_checksum_algorithm=full_crc32;
 
ALTER TABLE t1 DISCARD TABLESPACE;
 
-- copy the .ibd file to the data directory
 
ALTER TABLE t1 IMPORT TABLESPACE;

In MDEV-26137 the workflow was simplified, so that this form of incompatibility should not be possible.

Generated at Thu Feb 08 09:55:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.