Description:
The import tablespace schema check does not check for decimal columns' precision and scale matches, that results in tablespace may successfully imported but data is not correctly interpreted.
How to repeat:
connect (con1,localhost,root,,);
--disable_warnings
drop table if exists t1;
--enable_warnings
let MYSQLD_DATADIR=`select @@datadir`;
create table t1 (
pk int,
a decimal(10, 4),
PRIMARY KEY (pk)
);
show create table t1;
insert into t1 values(1, 3.4545);
insert into t1 values(2, 3.4546);
insert into t1 values(3, 3.4547);
insert into t1 values(4, 3.4548);
insert into t1 values(5, 3.4549);
select * from t1;
create database test2;
use test2;
# Create a table with same schema but different precision for decimal column a
create table t2 (
pk int,
a decimal(9, 3),
PRIMARY KEY (pk)
);
SHOW CREATE TABLE t2;
ALTER TABLE t2 DISCARD TABLESPACE;
USE test;
FLUSH TABLES t1 FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test2/t2.ibd
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test2/t2.cfg
USE test2;
LOCK TABLES t2 WRITE;
ALTER TABLE t2 IMPORT TABLESPACE;
UNLOCK TABLES;
select * from t2;
drop table t2;
use test;
drop database test2;
drop table t1;
The data after imported in t2 shown as
select * from t2;
pk a
1 3.250
2 3.251
3 3.252
4 3.253
5 3.254
Suggested fix:
There is no easy fix for this issue, it may require
1. getting the precision and scale for the given column
2. save both values into cfg file in export
3. compare both values in case of cfg file presents.
However, in case of cfg file missing scenario, we have to accept potential data incorrectness in case of decimal value.
A fully fix may require to save precision and scale in ibd file somewhere
