Import .ibd files with timestamp from MariaDB to Percona XtraDB Cluster

MySQL & MariaDB Other MySQL® Questions
,
1

I cannot import .ibd files with timestamp. I’m getting an error:

ERROR 1808 (HY000): Schema mismatch (Column col precise type mismatch.)

Import is from 10.1.48-MariaDB to Percona XtraDB Cluster (GPL), Release rel13, Revision a48e6d5, WSREP version 26.4.3.

Import of DATE and TIME type works correctly.

Steps on MariaDB:
SET GLOBAL mysql56_temporal_format = ‘OFF’;
CREATE TABLE t ( col timestamp ) ENGINE=InnoDB;
INSERT INTO t (col) VALUES (‘2020-01-01 10:10:10’), (‘2020-01-01 10:10:20’) , (‘2020-01-01 10:10:30’);
FLUSH TABLES t FOR EXPORT;

copy *.ibd and *.cfg files to PXC

Steps on PXC:
CREATE TABLE t ( col timestamp ) ENGINE=InnoDB;
alter table t row_format=COMPACT;
alter table t discard tablespace;

alter table t import tablespace;
ERROR 1808 (HY000): Schema mismatch (Column col precise type mismatch.)

1 Like
2

You may need to rebuild the table as mentioned here TIMESTAMP - MariaDB Knowledge Base before exporting. Since MariaDB is not MySQL, tables are not always 100% drop-in importable to Percona/Community MYSQL. Your best option is to dump the table and reload. Use mydumper for faster parallel dumping.

1 Like
3

Tables are big. It is not possible use dump and reload.

I tried my example once more with SET GLOBAL mysql56_temporal_format = ‘ON’; with the same result - ERROR 1808 (HY000): Schema mismatch. I think, that result it is equivalent as ALTER TABLE example_table MODIFY ts_col TIMESTAMP; …

If I imported ibd files wtihout cfg file I got error in both variant (mysql56_temporal_format ON and OFF)

ERROR 1808 (HY000): Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)

If I use different table with INT in first column, I can import ibd files without cfg files, regardless of the settings setting mysql56_temporal_format …
CREATE TABLE t (
id INT,
col timestamp
) ENGINE=InnoDB;

But I got error ERROR 1808 (HY000): Schema mismatch (Column col precise type mismatch.) during import with cfg files too.

It seems, that timestamp on first column behave differently.

There is next question: How is stored timestamp internally in PXC8?

1 Like
4

There is no difference in how PXC stores column data vs PS8 vs MySQL Community 8. We follow upstream. They are all the same. The only one with differences would be MariaDB.

I see you are on a pretty old version of MariaDB, 10.1 Have you tried first upgrading to a modern version of MariaDB before attempting all of this? I would upgrade MariaDB, ALTER TABLE ENGINE=INNODB to rebuild the table completely, then try this again. If it still doesn’t work, I would next file a bug with MariaDB and ask them what is happening.

1 Like
5

Thank you. Only for info - I tried import .cfg and .ibd files from newer version (10.3.27-MariaDB, for debian-linux-gnu (x86_64)) and PXC8 error is still ERROR 1808 (HY000): Schema mismatch (Column col precise type mismatch.)

1 Like
6

Have you tried MariaDB 10.3.27 → PXC 5.7?

1 Like
7

No. I am finding “one step” solution for upgrade to PCX8.

8

Due to the incompatibilities between MariaDB and everyone else, your “one step” is dump and import. As I said before, you might try first upgrading your MariaDB to the latest 5.7 equivalent then rebuild the table, then attempt the export. If that fails, then you’re stuck with dump/import. Check out mydumper for faster performance for logical imports.

1 Like