Page MenuHomePhabricator

Rebuild old timestamp format tables
Closed, ResolvedPublic

Description

In order to get db1052 ready to be able to import S1 tablespaces to db1095 it would be easier and faster just to rebuild the following three tables which are using the old timestamp format:

MariaDB PRODUCTION s1 localhost enwiki > 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 |
+--------------+--------+-----------------+--------------+-------------+
| enwiki       | InnoDB | categorylinks   | cl_timestamp | timestamp   |
| enwiki       | InnoDB | objectcache     | exptime      | datetime    |
| enwiki       | InnoDB | __wmf_checksums | ts           | timestamp   |
+--------------+--------+-----------------+--------------+-------------+

Event Timeline

Restricted Application removed a project: Patch-For-Review. · View Herald TranscriptNov 25 2016, 7:27 AM

Mentioned in SAL (#wikimedia-operations) [2016-11-25T07:51:45Z] <marostegui> Stopping replication db1052 for maintenance - T151607

Looks like MariaDB implementation is different from MySQL's. I am not able to convert the old timestamp formats unless I upgrade to 10.1 and recreate the table (drop and create).
Version I am testing with: 10.1.19
Example:

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

MariaDB [test]> rename table objectcache to objectcache_bkup;
Query OK, 0 rows affected (0.01 sec)

Now, I use a mysqldump I took from that same table on that same server and load it:

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)

mysql_upgrade makes no difference at all.
This is a non blocker for the goal anyways, so I am not going to spend much time on this really. However, I am going to report this to MariaDB

jcrespo added a comment.EditedNov 25 2016, 10:02 AM

What if we install 10.0, import, then we upgrade? Would that solve the issue? And if we compress/convert to barracuda?

No, that doesn't solve the issue either, looks like it is an issue with 10.0 in general. I actually did that test too.

  • Install 10.0.28
  • Create the table
  • Rebuild it in all possible ways - no luck
  • mysqldump + import - no luck
Marostegui changed the task status from Open to Stalled.Nov 25 2016, 10:38 AM

MariaDB replied with a solution that works, however they admit that it is weird that an ALTER TABLE force doesn't work:

https://jira.mariadb.org/browse/MDEV-11351?focusedCommentId=88703&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-88703

This is what they suggest (modify the column to the same schema):

MariaDB [test]> show create table objectcache_bkup\G
*************************** 1. row ***************************
       Table: objectcache_bkup
Create 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
1 row in set (0.00 sec)

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.04 sec)

MariaDB [test]> alter table objectcache_bkup modify exptime datetime DEFAULT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Which seems to work:

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;
Empty set (0.02 sec)

The above comment obviously only works on 10.1

Marostegui removed Marostegui as the assignee of this task.Feb 16 2017, 7:32 AM

Unassigning as there is nothing we can really do now as we are not in 10.1 in general.

Marostegui lowered the priority of this task from Medium to Low.Mar 2 2017, 9:23 AM
Marostegui closed this task as Resolved.Jun 6 2017, 12:24 PM

Closing this as we are not moving tablespaces anymore.
We would need to make sure that this isn't an issue when upgrading to 10.1 + mysql_upgrade