Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.28, 10.1.19
-
None
-
debian
Description
While doing a tablespace import I noticed the following error when imported a few tables:
This is an example with a given table:
Schema mismatch (Column exptime precise type mismatch.)
|
This is the table
CREATE TABLE `objectcache_bkup` (
|
`keyname` varbinary(255) NOT NULL DEFAULT '',
|
`value` mediumblob,
|
`exptime` datetime DEFAULT NULL,
|
`test` int(11) DEFAULT NULL,
|
UNIQUE KEY `keyname` (`keyname`),
|
KEY `exptime` (`exptime`)
|
) ENGINE=InnoDB DEFAULT CHARSET=binary
|
The table was of course the same on the source and the target.
I noticed that the table was using the old timestamp format http://mechanics.flite.com/blog/2014/05/01/upgrading-temporal-columns-from-mysql-5-dot-5-to-mysql-5-dot-6-format/:
select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type from information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name) left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime') and t.table_schema not in ('mysql','information_schema','performance_schema', 'ops', 'sys') and t.table_type = 'base table' and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6)) order by t.table_schema,t.table_name,c.column_name;
|
+--------------+--------+-------------------+-------------+-------------+
|
| table_schema | engine | table_name | column_name | column_type |
|
+--------------+--------+-------------------+-------------+-------------+
|
| test | InnoDB | objectcache | exptime | datetime |
|
+--------------+--------+-------------------+-------------+-------------+
|
1 rows in set (0.01 sec)
|
I assumed rebuilding the table would work as it does on MySQL (I have faced this before with MySQL but not with MariaDB), but it looks like it doesn't work.
MariaDB [test]> alter table objectcache engine = innodb, force;
|
Query OK, 0 rows affected (0.01 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type from information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name) left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime') and t.table_schema not in ('mysql','information_schema','performance_schema', 'ops', 'sys') and t.table_type = 'base table' and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6)) order by t.table_schema,t.table_name,c.column_name;
|
+--------------+--------+-------------+-------------+-------------+
|
| table_schema | engine | table_name | column_name | column_type |
|
+--------------+--------+-------------+-------------+-------------+
|
| test | InnoDB | objectcache | exptime | datetime |
|
+--------------+--------+-------------+-------------+-------------+
|
1 row in set (0.02 sec)
|
|
|
MariaDB [test]> alter table objectcache algorithm=copy;
|
Query OK, 0 rows affected (0.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type from information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name) left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime') and t.table_schema not in ('mysql','information_schema','performance_schema', 'ops', 'sys') and t.table_type = 'base table' and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6)) order by t.table_schema,t.table_name,c.column_name;
|
+--------------+--------+-------------+-------------+-------------+
|
| table_schema | engine | table_name | column_name | column_type |
|
+--------------+--------+-------------+-------------+-------------+
|
| test | InnoDB | objectcache | exptime | datetime |
|
+--------------+--------+-------------+-------------+-------------+
|
1 row in set (0.02 sec)
|
|
|
|
|
MariaDB [test]> alter table objectcache add column test int;
|
Query OK, 0 rows affected (0.02 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type from information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name) left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime') and t.table_schema not in ('mysql','information_schema','performance_schema', 'ops', 'sys') and t.table_type = 'base table' and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6)) order by t.table_schema,t.table_name,c.column_name;
|
+--------------+--------+-------------+-------------+-------------+
|
| table_schema | engine | table_name | column_name | column_type |
|
+--------------+--------+-------------+-------------+-------------+
|
| test | InnoDB | objectcache | exptime | datetime |
|
+--------------+--------+-------------+-------------+-------------+
|
1 row in set (0.02 sec)
|
On 10.0.28 I decided to mysqldump the table, drop it and created it again. This didn't work.
On 10.1.19 That does work. Take a mysqldump and reimport the table works.
MariaDB [test]> rename table objectcache to objectcache_bkup;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [test]> source objectcache.sql;
|
<snip>
|
|
|
MariaDB [test]> select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type from information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name) left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime') and t.table_schema not in ('mysql','information_schema','performance_schema', 'ops', 'sys') and t.table_type = 'base table' and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6)) order by t.table_schema,t.table_name,c.column_name;
|
+--------------+--------+------------------+-------------+-------------+
|
| table_schema | engine | table_name | column_name | column_type |
|
+--------------+--------+------------------+-------------+-------------+
|
| test | InnoDB | objectcache_bkup | exptime | datetime |
|
+--------------+--------+------------------+-------------+-------------+
|
1 row in set (0.02 sec)
|
Obviously taking a mysqldump is a big blocker if we are talking about a replication environment and big tables.
Attachments
Issue Links
- duplicates
-
-
- Open
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-15228 Document how to upgrade old temporal columns
-
- Closed
-
-
-
- Closed
-
10.0 does not support mysql56 temporal format, so naturally it does not work there.
For 10.1, the workaround is a degenerate MODIFY:
| 20 | exptime | 2 | 6 | 1036 | 8 || 23 | exptime | 2 | 3 | 525324 | 5 |However, I agree it looks strange that ALTER .. FORCE does not do it.