In this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.
MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?
Are they converted automatically to the new format?
If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we run mysql_upgrade, it does not warn us about the old format. If we check the MySQL error log, we cannot find anything regarding this. But the newly created tables are going to use the new format so that we will have two different types of temporal fields.
How can we find these tables?
The following query gives us a summary on the different table formats:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT CASE isc.mtype WHEN '6' THEN 'OLD' WHEN '3' THEN 'NEW' END FORMAT, count(*) TOTAL FROM information_schema.tables AS t INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema AND c.table_name = t.table_name LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name) LEFT OUTER JOIN information_schema.innodb_sys_columns AS 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') AND t.table_type = 'base table' AND (t.engine = 'innodb') GROUP BY isc.mtype; |
1 2 3 4 5 6 | +--------+-------+ | FORMAT | TOTAL | +--------+-------+ | NEW | 1 | | OLD | 9 | +--------+-------+ |
Or we can use show_old_temporals, which will highlight the old formats during a show create table.
1 2 3 4 5 | CREATE TABLE `mytbl` ( `ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP, `dt` datetime /* 5.5 binary format */ DEFAULT NULL, `t` time /* 5.5 binary format */ DEFAULT NULL ) DEFAULT CHARSET=latin1 |
MySQL can handle both types, but with the old format you cannot use microseconds, and the default DATETIME takes more space on disk.
Can I upgrade to MySQL 5.7?
Of course you can! But when mysql_upgrade is running it is going to convert the old fields into the new format by default. This basically means an alter table on every single table, which will contain one of the three types.
Depending on the number of tables, or the size of the tables, this could take hours – so you may need to do some planning.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | .... test.t1 error : Table rebuild required. Please do "ALTER TABLE `t1` FORCE" or dump/reload to fix it! test.t2 error : Table rebuild required. Please do "ALTER TABLE `t2` FORCE" or dump/reload to fix it! test.t3 error : Table rebuild required. Please do "ALTER TABLE `t3` FORCE" or dump/reload to fix it! Repairing tables mysql.proxies_priv OK `test`.`t1` Running : ALTER TABLE `test`.`t1` FORCE status : OK `test`.`t2` Running : ALTER TABLE `test`.`t2` FORCE status : OK `test`.`t3` Running : ALTER TABLE `test`.`t3` FORCE status : OK Upgrade process completed successfully. Checking if update is needed. |
Can we avoid this at upgrade?
We can run alter tables or use pt-online-schema-schange (to avoid locking) before an upgrade, but even without these preparations we can still avoid incompatibility issues.
My colleague Daniel Guzman Burgos pointed out that mysql_upgrade has an option called upgrade-system-tables. This will only upgrade the system tables, and nothing else.
Can we still write these fields?
The following query returns the schema and the table names that still use the old formats.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT CASE isc.mtype WHEN '6' THEN 'OLD' WHEN '3' THEN 'NEW' END FORMAT, t.schema_name, t.table_name FROM information_schema.tables AS t INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema AND c.table_name = t.table_name LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name) LEFT OUTER JOIN information_schema.innodb_sys_columns AS 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') AND t.table_type = 'base table' AND (t.engine = 'innodb'); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | +--------+--------------+------------+ | FORMAT | table_schema | table_name | +--------+--------------+------------+ | OLD | test | t | | OLD | test | t | | OLD | test | t | | NEW | sys | sys_config | +--------+--------------+------------+ 4 rows in set (0.03 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.11-4 | +-----------+ 1 row in set (0.00 sec) |
As we can see, we’re using 5.7 and table “test.t” still has the old format.
The schema:
1 2 3 4 5 6 7 | CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `t1` time DEFAULT NULL, `t2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `t3` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
1 2 3 4 5 6 7 8 9 10 | mysql> select * from t; +----+----------+---------------------+---------------------+ | id | t1 | t2 | t3 | +----+----------+---------------------+---------------------+ | 1 | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 | | 2 | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 | | 3 | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 | | 4 | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 | | 5 | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 | +----+----------+---------------------+---------------------+ |
Let’s try to insert a new row:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> insert into `t` (t1,t3) values ('20:28','2016:04:23 22:22:06'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +----+----------+---------------------+---------------------+ | id | t1 | t2 | t3 | +----+----------+---------------------+---------------------+ | 1 | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 | | 2 | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 | | 3 | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 | | 4 | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 | | 5 | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 | | 6 | 20:28:00 | 2016-04-09 01:56:38 | 2016-04-23 22:22:06 | +----+----------+---------------------+---------------------+ 6 rows in set (0.00 sec) |
It was inserted without a problem, and we can’t see any related info/warnings in the error log.
Does the Replication work?
In many scenarios, when you are upgrading a replicaset, the slaves are upgraded first. But will the replication work? The short answer is “yes.” I configured row-based replication between MySQL 5.6 and 5.7. The 5.6 was the master, and it had all the temporal types in the old format. On 5.7, I had new and old formats.
I replicated from old format to old format, and from old format to new format, and both are working.
Conclusion
Before upgrading to MySQL 5.7, tables should be altered to use the new format. If it isn’t done, however, the upgrade is still possible without altering all the tables – the drawbacks are you cannot use microseconds, and it takes more space on disk. If you had to upgrade to 5.7, however, you could change the format later using alter table or pt-online-schema-schange.
after upgrade from 5.6 to 5.7 i’ve got replication error,
Cannot get geometry object from data you send to the GEOMETRY field’ on query. Default database: ‘my_database’. Query: ‘INSERT INTO table (file_id, polygon, a_latitude, b_latitude, c_longitude, d_longitude) VALUES ( 93937, GeomFromText(“Polygon((36.91 -121.705,36.908….
Hi Mike,
Your question is not relevant to this blog post but maybe you have a similar issue like in this Bug report:
https://bugs.mysql.com/bug.php?id=79060
If you don’t use the latest 5.7 try that one.
Thanks,
Tibi
MySQL 5.6.24 added the show_old_temporals variable [1]. It’s dynamic and works with show create table.
MySQL’s docs also say [2] that tables with pre 5.6 date format columns will not be able to take full advantage of the online DDL improvements introduced in 5.6 (i.e., use algorithm=inplace with alter table).
[1]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_show_old_temporals
[2]: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html
Really useful blog post, thanks! Small type in your query: The first occurrence of t.schema_name should be t.table_schema.