Bug #71381 Problems with datatype datetime and timestamp after downgrade from 5.6 to 5.5
Submitted: 14 Jan 2014 20:04 Modified: 17 Feb 2014 18:33
Reporter: Martin Arrieta (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5.35 and 5.6.15 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: Downgrade datatype

[14 Jan 2014 20:04] Martin Arrieta
Description:
I was making a downgrade and I found the following issue.

If you create a table with datetime or timestamp on 5.6.15 and you want to downgrade to 5.5, you will get "ERROR 1033 (HY000): Incorrect information in file: './test/t1on56.frm'" over the tables with datetime and timestamp (at least those are the one that I tested) 

How to repeat:
I have tested this with mysql sandbox, here are the commands:

-- First I create a new instance on 5.5.35 --

[martin@testbox msb_5_5_35]$ ./clear
[martin@testbox msb_5_5_35]$ ./start
.. sandbox server started
[martin@testbox msb_5_5_35]$ ./use test -e "CREATE TABLE t1on55 (dtime1 datetime NOT NULL) ENGINE=InnoDB;"
[martin@testbox msb_5_5_35]$ ./use test -e "CREATE TABLE t2on55 (tstamp1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB;"
[martin@testbox msb_5_5_35]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1on55         |
| t2on55         |
+----------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1on55;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t2on55;
Empty set (0.00 sec)

-- All good so far, now let's move this dataset to 5.6.15 --

[martin@textbox msb_5_5_35]$ ./stop
[martin@testbox msb_5_5_35]$ cd ../msb_5_6_15/
[martin@testbox msb_5_6_15]$ cp ../msb_5_5_35/data/ data/ -r
[martin@testbox msb_5_6_15]$ ./start
[martin@testbox msb_5_6_15]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15-rel63.0 Percona Server with XtraDB (GPL), Release rel63.0, Revision 519

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > select * from t1on55;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t2on55;
Empty set (0.00 sec)

-- On 5.6 we can read the tables without problems. Let's try to create the same tables on 5.6 --

[martin@testbox msb_5_6_15]$ ./use test -e "CREATE TABLE t1on56 (dtime1 datetime NOT NULL) ENGINE=InnoDB;"
[martin@testbox msb_5_6_15]$ ./use test -e "CREATE TABLE t2on56 (tstamp1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB;"
[martin@testbox msb_5_6_15]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15-rel63.0 Percona Server with XtraDB (GPL), Release rel63.0, Revision 519

Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1on55         |
| t1on56         |
| t2on55         |
| t2on56         |
+----------------+
4 rows in set (0.00 sec)

-- Now we move the dataset back to 5.5 --

[martin@testbox msb_5_6_15]$ ./stop
[martin@testbox msb_5_6_15]$ cd ../msb_5_5_35/
[martin@testbox msb_5_5_35]$ rm data -rf
[martin@testbox msb_5_5_35]$ cp ../msb_5_6_15/data/ data/ -r
[martin@testbox msb_5_5_35]$ ./start
.. sandbox server started
[martin@testbox msb_5_5_35]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1on55         |
| t1on56         |
| t2on55         |
| t2on56         |
+----------------+
4 rows in set (0.00 sec)

-- We can see all tables, let's try to read them --

mysql [localhost] {msandbox} (test) > select * from t1on55;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1on56;
ERROR 1033 (HY000): Incorrect information in file: './test/t1on56.frm'
mysql [localhost] {msandbox} (test) > select * from t2on55;
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t2on56;
ERROR 1033 (HY000): Incorrect information in file: './test/t2on56.frm'

-- here is the error, and if I try to run the mysql_upgrade --

[martin@testbox msb_5_5_35]$ ~/mysql_versions/5.5.35/bin/mysql_upgrade --socket=/tmp/mysql_sandbox5535.sock --user=root --password=msandbox
Looking for 'mysql' as: /home/martin/mysql_versions/5.5.35/bin/mysql
Looking for 'mysqlcheck' as: /home/martin/mysql_versions/5.5.35/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql_sandbox5535.sock'
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql_sandbox5535.sock'
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log
Error    : Incorrect information in file: './mysql/general_log.frm'
error    : Corrupt
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats
Error    : Incorrect information in file: './mysql/innodb_index_stats.frm'
error    : Corrupt
mysql.innodb_table_stats
Error    : Incorrect information in file: './mysql/innodb_table_stats.frm'
error    : Corrupt
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv
Error    : Incorrect information in file: './mysql/procs_priv.frm'
error    : Corrupt
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info
Error    : Table 'mysql.slave_master_info' doesn't exist
status   : Operation failed
mysql.slave_relay_log_info
Error    : Table 'mysql.slave_relay_log_info' doesn't exist
status   : Operation failed
mysql.slave_worker_info
Error    : Table 'mysql.slave_worker_info' doesn't exist
status   : Operation failed
mysql.slow_log
Error    : Incorrect information in file: './mysql/slow_log.frm'
error    : Corrupt
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test.t1on55                                        OK
test.t1on56
Error    : Incorrect information in file: './test/t1on56.frm'
error    : Corrupt
test.t2on55                                        OK
test.t2on56
Error    : Incorrect information in file: './test/t2on56.frm'
error    : Corrupt

Repairing tables
mysql.general_log
Error    : Incorrect information in file: './mysql/general_log.frm'
error    : Corrupt
mysql.innodb_index_stats
Error    : Incorrect information in file: './mysql/innodb_index_stats.frm'
error    : Corrupt
mysql.innodb_table_stats
Error    : Incorrect information in file: './mysql/innodb_table_stats.frm'
error    : Corrupt
mysql.procs_priv
Error    : Incorrect information in file: './mysql/procs_priv.frm'
error    : Corrupt
mysql.slave_master_info
Error    : Table 'mysql.slave_master_info' doesn't exist
status   : Operation failed
mysql.slave_relay_log_info
Error    : Table 'mysql.slave_relay_log_info' doesn't exist
status   : Operation failed
mysql.slave_worker_info
Error    : Table 'mysql.slave_worker_info' doesn't exist
status   : Operation failed
mysql.slow_log
Error    : Incorrect information in file: './mysql/slow_log.frm'
error    : Corrupt
test.t1on56
Error    : Incorrect information in file: './test/t1on56.frm'
error    : Corrupt
test.t2on56
Error    : Incorrect information in file: './test/t2on56.frm'
error    : Corrupt
Running 'mysql_fix_privilege_tables'...
ERROR 1033 (HY000) at line 80: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 86: Incorrect information in file: './mysql/general_log.frm'
ERROR 1243 (HY000) at line 87: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 88: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1033 (HY000) at line 94: Incorrect information in file: './mysql/slow_log.frm'
ERROR 1243 (HY000) at line 95: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 96: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1033 (HY000) at line 719: Incorrect information in file: './mysql/general_log.frm'
ERROR 1033 (HY000) at line 730: Incorrect information in file: './mysql/slow_log.frm'
ERROR 1033 (HY000) at line 849: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 853: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 857: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 861: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 865: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 1125: Incorrect information in file: './mysql/procs_priv.frm'
ERROR 1033 (HY000) at line 1146: Incorrect information in file: './mysql/procs_priv.frm'
FATAL ERROR: Upgrade failed

-- check table shows  --

mysql [localhost] {msandbox} (test) > check table t1on56;
+-------------+-------+----------+----------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                           |
+-------------+-------+----------+----------------------------------------------------+
| test.t1on56 | check | Error    | Incorrect information in file: './test/t1on56.frm' |
| test.t1on56 | check | error    | Corrupt                                            |
+-------------+-------+----------+----------------------------------------------------+
2 rows in set (0.00 sec)

-- Also I tested with MyISAM tables, same result and there is no way to repair the table --

[martin@textbox msb_5_6_15]$ ./use test -e "CREATE TABLE t1on56myisam (dtime1 datetime NOT NULL)ENGINE=MyISAM;"
[martin@textbox msb_5_6_15]$ ./use test -e "CREATE TABLE t2on56myisam (tstamp1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )ENGINE=MyISAM;"

[martin@textbox msb_5_5_35]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1on55         |
| t1on56         |
| t1on56myisam   |
| t2on55         |
| t2on56         |
| t2on56myisam   |
+----------------+
6 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1on56myisam;
ERROR 1033 (HY000): Incorrect information in file: './test/t1on56myisam.frm'
mysql [localhost] {msandbox} (test) > select * from t2on56myisam;
ERROR 1033 (HY000): Incorrect information in file: './test/t2on56myisam.frm'
mysql [localhost] {msandbox} (test) > repair table t1on56myisam;
+-------------------+--------+----------+----------------------------------------------------------+
| Table             | Op     | Msg_type | Msg_text                                                 |
+-------------------+--------+----------+----------------------------------------------------------+
| test.t1on56myisam | repair | Error    | Incorrect information in file: './test/t1on56myisam.frm' |
| test.t1on56myisam | repair | error    | Corrupt                                                  |
+-------------------+--------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)

Suggested fix:
No idea :)
[14 Jan 2014 21:28] Ovais Tariq
This is already mentioned as an incompatible change here:
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html 

However these temporal data types that have changed should also be mentioned here as caveats to consider when downgrading:
http://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

The only option I see here is to do a dump on the 5.6 of the tables with these temporal columns on 5.6 and then importing them in 5.5
[16 Jan 2014 16:05] MySQL Verification Team
Hello Martin,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[17 Jan 2014 10:13] MySQL Verification Team
Documentation issue, changing category to documentation.
[17 Feb 2014 18:33] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added this note to the downgrading section:

For TIME, DATETIME, and TIMESTAMP columns, the storage required for
tables created before MySQL 5.6.4 differs from storage required for
tables created in 5.6.4 and later. This is due to a change in 5.6.4
that permits these temporal types to have a fractional part. To
downgrade to a version older than 5.6.4, dump affected tables with
mysqldump before downgrading, and reload the tables after
downgrading.