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

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Won't Fix
Affects Version/s: 10.1.29
Fix Version/s: 11.2
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

Stalled

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

Closed

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...

(12 relates to, 18 mentioned in)

Activity

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:: 2023-08-03 13:03

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.