Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.12
-
None
-
None
-
10.2.1-1, 10.2.1-2
Description
mysql 5.6.17 (root) [test] db1> CREATE TABLE `t1` (
|
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
-> `d` datetime DEFAULT NULL,
|
-> PRIMARY KEY (`id`)
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
Query OK, 0 rows affected (0.11 sec)
|
|
|
mysql 5.6.17 (root) [test] db1> flush table t1 for export;
|
Query OK, 0 rows affected (0.01 sec)
|
mysql 5.5.5-10.0.12-MariaDB (root) [test] db1> CREATE TABLE `t1` (
|
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
-> `d` datetime DEFAULT NULL,
|
-> PRIMARY KEY (`id`)
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
-> ;
|
Query OK, 0 rows affected (0.08 sec)
|
|
|
mysql 5.5.5-10.0.12-MariaDB (root) [test] db1> alter table t1 discard tablespace;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
mysql 5.5.5-10.0.12-MariaDB (root) [test] db1>
|
[2]+ Stopped mysql
|
[kolbe@centos6-build mariadb-10.0.12-linux-x86_64]$ cp ~/t1.{cfg,ibd} ./data/test/
|
[kolbe@centos6-build mariadb-10.0.12-linux-x86_64]$ fg
|
mysql
|
mysql 5.5.5-10.0.12-MariaDB (root) [test] db1>
|
mysql 5.5.5-10.0.12-MariaDB (root) [test] db1> alter table t1 import tablespace;
|
ERROR 1808 (HY000): Schema mismatch (Column d precise type mismatch.)
|
Attachments
Issue Links
- is duplicated by
-
-
- Confirmed
-
- is part of
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
Is there any update on this? This issue makes it impossible to import InnoDB tablespaces exported from MySQL 5.6.
Unfortunately, there isn't much we can do about it. MySQL-5.6 creates temporal fields that are binary incompatible with MariaDB-5.3 and later. MariaDB can read and write MySQL-5.6 temporal fields, but it cannot create them. But for import tablespace to work — see your example — one needs to create a table with the MySQL-5.6 temporal fields.
We plan to add this in 10.1.
I do not fully follow the last comment, is column 'd' on example a temporal field and why ?
I tested with MySQL 5.6.29:
jan@jan-lindstrom-asus-laptop:~$ /usr/local/mysql/bin/mysql -u root
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 1
|
Server version: 5.6.29-debug Source distribution
|
|
|
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
|
|
|
Oracle is a registered trademark of Oracle Corporation and/or its
|
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
mysql> use test;
|
Database changed
|
mysql> create table t1 ( id int not null auto_increment primary key, d datetime default null) engine=innodb;
|
Query OK, 0 rows affected (0,05 sec)
|
|
|
mysql> flush table t1 for export;
|
Query OK, 0 rows affected (0,00 sec)
|
and MariaDB 10.0.24 really fails (I also tested MariaDB 10.1.14 and it worked)
(gdb) p cfg_col
|
$1 = (const dict_col_t *) 0x7fffa801066c
|
(gdb) p *cfg_col
|
$2 = {prtype = 525324, mtype = 3, len = 5, mbminmaxlen = 6, ind = 1, ord_part = 0, max_prefix = 0}
|
(gdb) p *col
|
$3 = {prtype = 1036, mtype = 6, len = 8, mbminmaxlen = 0, ind = 1, ord_part = 0, max_prefix = 0}
|
Can't fix easily inside InnoDB as there is different main types used (binary format is not exactly the same). Problem is how DATETIME data type is stored. In MariaDB 10.0 we have (ha_innodb.cc):
case MYSQL_TYPE_DATETIME:
|
if (field->key_type() == HA_KEYTYPE_BINARY)
|
return(DATA_FIXBINARY);
|
else
|
return(DATA_INT);
|
And key_type() != HA_KEYTYPE_BINARY so DATA_INT is selected. In MySQL 5.6 there is:
case MYSQL_TYPE_TIME:
|
case MYSQL_TYPE_DATETIME:
|
case MYSQL_TYPE_TIMESTAMP:
|
switch (field->real_type()) {
|
case MYSQL_TYPE_TIME:
|
case MYSQL_TYPE_DATETIME:
|
case MYSQL_TYPE_TIMESTAMP:
|
return(DATA_INT);
|
default: /* Fall through */
|
DBUG_ASSERT((ulint)MYSQL_TYPE_DECIMAL < 256);
|
case MYSQL_TYPE_TIME2:
|
case MYSQL_TYPE_DATETIME2:
|
case MYSQL_TYPE_TIMESTAMP2:
|
return(DATA_FIXBINARY);
|
}
|
And it selects DATA_FIXBINARY. In row/row0import.cc we should somehow note that real type is e.g. DATETIME2 read it from page and somehow convert it to DATA_INT write back to page. Not sure if this is safe for 10.0.
I'm having a similar issue when exporting tables in MariaDB 10.1.22-1 (CentOS). And this is when trying to export data into the same server! Datetime field is the one that gave me the problem also. I'm just mentioning it since this JIRA refers to MySQL 5.6 -> MariaDB 10.0 and my issue is from MariaDB 10.1 to MariaDB 10.1. I can provide more details if needed.
Update: my mistake. this was happening if I copied the .frm and the .ibd files. If I only copy the .ibd file it works correctly
It looks related to what bar is working on in scope of
MDEV-5377.