Intel, Salesforce, eBay, Sony, SAP, McKinsey & Company, Zalando and Cogitai are joining the Partnership on AI, a collection of companies and non-profits that have committed to sharing best practices and communicating openly about the benefits and risks of artificial intelligence research. The new members will be working alongside existing partners that include Facebook, Amazon, Google,… Read More
May
16
2017
16
2017
--
The Partnership on AI adds Intel, Salesforce and others as it formalizes Grand Challenges and work groups
Apr
27
2016
27
2016
--
Upgrading to MySQL 5.7, focusing on temporal types
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:
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
Jul
16
2013
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:
| 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.
Jul
12
2009
12
2009
PHP TimeDiff Tool
I needed to send an email with human readable string of the difference between two timestamps, so I made this little tool: