Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11351

Not able to rebuild tables with old timestamp format

    XMLWordPrintable

Details

    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

          Activity

            People

              Unassigned Unassigned
              marostegui Manuel Arostegui
              Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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