MySQL Bugs: #72521: Upgrade of timestamp columns

Bug #72521	Upgrade of timestamp columns
Submitted:	3 May 2014 7:49	Modified:	13 Oct 8:17
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When upgrading from MySQL 5.5 to MySQL 5.6 the incompatible change in timestamps can break replication.

1. It is documented on http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

"Incompatible change: 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. After upgrading from MySQL 5.5 to MySQL 5.6.4 or later, it is recommended that you also upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types. ALTER TABLE currently allows the creation of tables containing temporal columns in both MySQL 5.5 and MySQL 5.6.4 (or later) binary format but this makes it more difficult to recreate tables in cases where .frm files are not available. Additionally, as of MySQL 5.6.4, the aforementioned temporal types are more space efficient. For more information about changes to temporal types in MySQL 5.6.4, see Storage Requirements for Date and Time Types."

This seems to indicate that it is recommended to upgrade, but not required.

2. It is not documented here:
https://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html

3. The mysql_upgade tool doesn't handle it.
It should give a warning about the possible incompatibility or upgrade to the new format.

4. As mentioned in the blog post from Ike Walker the upgrade procedure is not fully documented.

Related Bugs:
- Bug #70124
- Bug #71111

Related blog posts:
- http://mechanics.flite.com/blog/2014/05/01/upgrading-temporal-columns-from-mysql-5-dot-5-t...

How to repeat:
Read the docs, try mysql_upgrade, etc.

Suggested fix:
1. Get mysql_upgrade to upgrade the temporal columns. (or at least warn)
2. Check the documentation.

Thank you for the reasonable feature request. 
Form the documentation it's evident that the 5.5 tables will work too, but the new ones will be created in the new format. So mysql_upgrade should not IMHO upgrade these tables. 
But I totally agree that a mysql_upgrade warning is in order.

If I'm not mistaken: This is done in 5.7 w/ mysql_upgrade (unless avoid_temporal_upgrade is set to disable that behaviour)

Posted by developer:
 
Both versions now archived