[MDEV-15225] Can't import .ibd file with temporal type format differing from mysql56_temporal_format - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Won't Fix
Affects Version/s: 10.1.29
Fix Version/s: 11.2(EOL)
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Temporal Types
Labels:

Description

Let's say that we have two servers. One of the servers has a table with a temporal type that was created with MySQL 5.5 or earlier, and the other is a MariaDB 10.1 server that has mysql56_temporal_format=ON (the default). If we want to import a .ibd file for a table with temporal types from the first server into the second server, it seems difficult to do in a safe manner without setting mysql56_temporal_format=OFF and restarting mysqld.

We can simulate this problem by doing the following:

Set mysql56_temporal_format=OFF and restart mysqld.
Create a table with a temporal type:

USE db1;

CREATE TABLE dt_test (

   id int AUTO_INCREMENT PRIMARY KEY,

   dt datetime

);

INSERT INTO dt_test VALUES (1, NOW());

Set mysql56_temporal_format=ON (the default) and restart mysqld.
Create a second copy of the table:

USE db2;

CREATE TABLE dt_test (

   id int AUTO_INCREMENT PRIMARY KEY,

   dt datetime

);

Discard the tablespace of the new copy:

ALTER TABLE db2.dt_test DISCARD TABLESPACE;

Flush the tablespace of the old copy for export:

FLUSH TABLES db1.dt_test FOR EXPORT;

Copy the files to the new database:

sudo cp /var/lib/mysql/db1/dt_test.ibd /var/lib/mysql/db2/

sudo cp /var/lib/mysql/db1/dt_test.cfg /var/lib/mysql/db2/

sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.ibd

sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.cfg

Attempt to import the tablespace:

CREATE TABLE db2.dt_test …;

ALTER TABLE db2.dt_test DISCARD TABLESPACE;

ALTER TABLE db2.dt_test IMPORT TABLESPACE;

This will fail:

MariaDB [db2]> ALTER TABLE dt_test IMPORT TABLESPACE;

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

Update: Can we please support the following simplified workflow:

sudo cp /var/lib/mysql/db1/dt_test.frm /var/lib/mysql/db2/

sudo cp /var/lib/mysql/db1/dt_test.ibd /var/lib/mysql/db2/

sudo cp /var/lib/mysql/db1/dt_test.cfg /var/lib/mysql/db2/

sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.frm

sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.ibd

sudo chown mysql:mysql /var/lib/mysql/db2/dt_test.cfg

and simply

ALTER TABLE db2.dt_test IMPORT TABLESPACE;

to have InnoDB automatically create its table definition based on the .frm and optionally .cfg files, with no need to execute CREATE TABLE and ALTER TABLE…DISCARD TABLESPACE? That would create all temporal-type columns exactly as is.

Attachments

Issue Links

is blocked by

MDEV-26137 ALTER TABLE IMPORT enhancement

Closed

is duplicated by

MDEV-11351 Not able to rebuild tables with old timestamp format

Confirmed

MDEV-15544 IMPORT TABLESPACE silently corrupts BLOB data

Closed

is part of

MDEV-11658 Simpler, faster IMPORT of InnoDB tables

Open

MDEV-20974 Don't require .cfg files to import InnoDB tablespaces

Closed

relates to

MDEV-5528 Command line variable to choose MariaDB-5.3 vs MySQL-5.6 temporal data formats

Closed

MDEV-6389 DATETIME w/ transportable tablespaces from MySQL 5.6 to MariaDB 10.0 gives "precise type mismatch" error.

Open

MDEV-15228 Document how to upgrade old temporal columns

Closed

MDEV-18762 Support easy restore of partial backup

Closed

MDEV-19906 Show internal type for TIMESTAMP, DATETIME, and TIME columns

Closed

MDEV-20073 ALGORITHM=INSTANT fails because of surprise change of timestamp type

Confirmed

MDEV-26137 ALTER TABLE IMPORT enhancement

Closed

MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

Closed

MDEV-9967 Convert old temporal types on ALTER TABLE ... FORCE

Closed

MDEV-15049 Importing InnoDB table from 10.1 to 10.2 causes Schema mismatch (Expected FSP_SPACE_FLAGS=0x21, .ibd file contains 0x0

Closed

MDEV-16542 Fix ALTER TABLE FORCE to upgrade temporal types

Closed

MDEV-18827 Create utility to parse frm files and print their DDL (Full-time project - potential part-time)

Open

MDEV-21454 Show actual mismatching values in mismatch error messages from row_import::match_table_columns()

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(13 relates to, 18 mentioned in)

Activity

Ascending order - Click to sort in descending order

View 25 older comments

Hartmut Holzgraefe added a comment - 2021-07-14 11:51

The root problem though was that the error message gave no hints towards mysql56 temporal format being the cause of the mismatch?

How would you guess that being the root cause when just seeing

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

as error message?

If that message gave clear hints towards the workaround needed it would be much less of an issue.

Hartmut Holzgraefe added a comment - 2021-07-14 11:51 The root problem though was that the error message gave no hints towards mysql56 temporal format being the cause of the mismatch? How would you guess that being the root cause when just seeing ERROR 1808 (HY000): Schema mismatch (Column dt precise type mismatch.) as error message? If that message gave clear hints towards the workaround needed it would be much less of an issue.

Sergei Golubchik added a comment - 2021-07-14 12:16 - edited

It doesn't really matter. If you have a schema mismatch, you need to have ibd/cgf/frm files, and import using all the three. You cannot import only ibd/cfg if you have a schema mismatch. No matter what exactly mismatch it is.

~~MDEV-26137~~ is about making three-file import simpler.

Sergei Golubchik added a comment - 2021-07-14 12:16 - edited It doesn't really matter. If you have a schema mismatch, you need to have ibd/cgf/frm files, and import using all the three. You cannot import only ibd/cfg if you have a schema mismatch. No matter what exactly mismatch it is. MDEV-26137 is about making three-file import simpler.

Marko Mäkelä added a comment - 2021-07-16 12:36

serg, what you suggested 5 days ago is MDEV-11658. It is not as simple as you imply, because until we have removed the InnoDB data dictionary, the discovered table would have to be registered there. I would not want to allow arbitrary DML statements to modify the InnoDB data dictionary tables.

Marko Mäkelä added a comment - 2021-07-16 12:36 serg , what you suggested 5 days ago is MDEV-11658 . It is not as simple as you imply, because until we have removed the InnoDB data dictionary, the discovered table would have to be registered there. I would not want to allow arbitrary DML statements to modify the InnoDB data dictionary tables.

Sergei Golubchik added a comment - 2021-07-16 12:46

Okay. Then there's still a way to do it. I didn't mention it at first, hoping it won't be needed.

There's fallback-and-retry loop in the table open process. MyISAM uses it to auto-repair tables on open, discovery uses it to auto-magically create tables on open, system versioning uses it to add new partitions (~~MDEV-17554~~). It is the way to go if you need to modify metadata on open. I just hoped you won't need it.

Sergei Golubchik added a comment - 2021-07-16 12:46 Okay. Then there's still a way to do it. I didn't mention it at first, hoping it won't be needed. There's fallback-and-retry loop in the table open process. MyISAM uses it to auto-repair tables on open, discovery uses it to auto-magically create tables on open, system versioning uses it to add new partitions ( MDEV-17554 ). It is the way to go if you need to modify metadata on open. I just hoped you won't need it.

Marko Mäkelä added a comment - 2023-08-03 13:03

~~MDEV-26137~~ fixed this by allowing the original .frm file to be used. The files would be imported simply by executing

ALTER TABLE dt_test IMPORT TABLESPACE;

Marko Mäkelä added a comment - 2023-08-03 13:03 MDEV-26137 fixed this by allowing the original .frm file to be used. The files would be imported simply by executing ALTER TABLE dt_test IMPORT TABLESPACE;

People

Assignee:: Yuchen Pei

Reporter:: Geoff Montee (Inactive)

Votes:: 3 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2018-02-06 21:40

Updated:: 2024-09-16 22:20

Resolved:: 2021-07-13 21:02

Git Integration

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

MariaDB Server