Apr
27
2016
--

Upgrading to MySQL 5.7, focusing on temporal types

temporal types

temporal typesIn 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:

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;

+--------+-------+
| FORMAT | TOTAL |
+--------+-------+
| NEW    | 1     |
| OLD    | 9     |
+--------+-------+

Or we can use show_old_temporals, which will highlight the old formats during a

show create table

.

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.

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

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

+--------+--------------+------------+
| 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:

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

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:

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.

 

Jul
16
2013
--

Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME

MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.

Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.

The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.

As of revision 79 the recovery toolkit supports the new format.

Let’s recover a sample table to see how it works. The table is

CREATE TABLE `t` (
  `id` int(11) AUTO_INCREMENT,
  `t1` time(3),
  `t2` timestamp(6),
  `t3` datetime(1),
  `t4` datetime,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> select * from t;
+----+--------------+----------------------------+-----------------------+---------------------+
| id | t1           | t2                         | t3                    | t4                  |
+----+--------------+----------------------------+-----------------------+---------------------+
|  1 | 05:05:10.000 | 2013-07-04 05:05:10.000000 | 2013-07-04 05:05:10.0 | 2013-07-04 05:05:10 |
|  2 | 05:14:24.414 | 2013-07-04 05:14:24.125000 | 2013-07-04 05:14:24.4 | 2013-07-04 05:14:25 |
|  3 | 05:14:32.566 | 2013-07-04 05:14:32.207031 | 2013-07-04 05:14:32.3 | 2013-07-04 05:14:32 |
|  4 | 05:14:34.344 | 2013-07-04 05:14:34.507813 | 2013-07-04 05:14:34.5 | 2013-07-04 05:14:35 |
|  5 | 05:14:45.348 | 2013-07-04 05:14:45.832031 | 2013-07-04 05:14:45.1 | 2013-07-04 05:14:45 |
+----+--------------+----------------------------+-----------------------+---------------------+

First, we should gerenrate a table definition file:

./create_defs.pl --db test --table t > include/table_defs.h

Then recompile the tool and split a table space that contains records of the table t:

./page_parser -f /var/lib/mysql/test/t.ibd

The next step is to get records from the InnoDB index:

$ ./constraints_parser -5f pages-1372929630/FIL_PAGE_INDEX/0-30/00000000-00000003.page  2> /dev/null
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
0000000007DB    A6000001A20110  t       1       "05:05:10.0"    "2013-07-04 05:05:10.0" "2013-07-04 05:05:10.0" "2013-07-04 05:05:10.0"
0000000007DE    A8000001530110  t       2       "05:14:24.4140" "2013-07-04 05:14:24.125000"    "2013-07-04 05:14:24.40"        "2013-07-04 05:14:25.0"
0000000007EC    B2000001A40110  t       3       "05:14:32.5660" "2013-07-04 05:14:32.207031"    "2013-07-04 05:14:32.30"        "2013-07-04 05:14:32.0"
0000000007ED    B3000001A50110  t       4       "05:14:34.3440" "2013-07-04 05:14:34.507813"    "2013-07-04 05:14:34.50"        "2013-07-04 05:14:35.0"
0000000007FB    BD000001670110  t       5       "05:14:45.3480" "2013-07-04 05:14:45.832031"    "2013-07-04 05:14:45.10"        "2013-07-04 05:14:45.0"
-- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES

To load this dump use the LOAD DATA INFILE command that’s generated by the contraints_parser, I then redirected it to /dev/null in the example above.

As you can see it’s pretty straightforward. There are two notes:

  • Because of format differences the tool can detect whether the field is in the new format or old
  • For the TIME field (w/o fractional part) in the new format you need to give a hint to contraints_parser. This is -6 option. Let me illustrate this

Before 5.6.4 TIME was packed in three bytes: DD×24×3600 + HH×3600 + MM×60 + SS. As of 5.6.4 it still uses three bytes, but format is different:

TIME new format 

bits description values
1 sign 1= non-negative, 0= negative
1 unused reserved for future extensions
10 hour 0-838
6 minute 0-59
6 second 0-59

If a field is created without a fractional part it’s impossible to determite the format from a field value. Let’s take a table:

CREATE TABLE `t` (
  `id` int(11) AUTO_INCREMENT,
  `t1` time ,
  `t2` time(3) ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> select * from t;
+----+----------+--------------+
| id | t1       | t2           |
+----+----------+--------------+
|  1 | 11:01:17 | 11:01:17.000 |
|  2 | 11:01:17 | 11:01:17.125 |
|  3 | 11:01:17 | 11:01:17.125 |
+----+----------+--------------+

If there is no fractional part constraints_parser assumes old format. So if we try to recover the the records from the table above the result will be wrong:

$ ./constraints_parser -5f pages-1373023772/FIL_PAGE_INDEX/0-31/00000000-00000003.page
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
000000000807    C5000001AC0110  t8      1       "12:32:17"      "11:01:17.0"
000000000807    C5000001AC011C  t8      2       "12:32:17"      "11:01:17.1250"
000000000807    C5000001AC0128  t8      3       "12:32:17"      "11:01:17.1250"
-- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES

Thus, we need to give a hint, then TIME values are correct:

./constraints_parser -5f pages-1373023772/FIL_PAGE_INDEX/0-31/00000000-00000003.page -6
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
000000000807    C5000001AC0110  t8      1       "11:01:17.0"    "11:01:17.0"
000000000807    C5000001AC011C  t8      2       "11:01:17.0"    "11:01:17.1250"
000000000807    C5000001AC0128  t8      3       "11:01:17.0"    "11:01:17.1250"
-- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES

The post Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME appeared first on MySQL Performance Blog.

Jun
20
2009
0

Update PHP Tools page

I updated my tools page.  The php timestamp converter tool has been updated to use timezone_identifiers_list() (i.e. ‘America/New_York’), now instead of hard-coded offsets.

Also moved all the scripts into one directory here.

When I get bored again I’ll add some more.  I actually use these from work so it’s really just a convenience for me.

Written by in: HTML,PHP,Tools,Web Development | Tags: , ,

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com