This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ‘0000-00-00 00:00:00’, like so:
1 2 3 4 5 | CREATE TABLE mysql56 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', ); |
However, under Amazon RDS, the same table looked like this:
1 2 3 4 5 | CREATE TABLE rds56 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT NULL, ); |
They mentioned that their schema contains TIMESTAMP column definitions without any modifiers for nullability or default values. In other words, they were doing something like this:
1 2 3 4 5 | CREATE TABLE foo56 ( id NOT NULL AUTO_INCREMENT PRIMARY KEY, ts1 TIMESTAMP, ts2 TIMESTAMP ); |
It’s a known issue (or change, or difference, whatever we choose to call it) that MySQL is deprecating defaults for TIMESTAMP columns that don’t have any nullability or default-value specifiers; this is covered in the 5.6 documentation. However, the docs also mention that the default value for this setting is OFF – i.e., if you create a table with TIMESTAMP columns without any defaults, it will fill them in for you, similarly to what I’ve described above.
As it turns out, the RDS default for this setting is ON, hence the “NULL DEFAULT NULL” modifiers when creating the table under RDS. We changed the parameter group, restarted the instance (note that this variable is NOT dynamic), and their schema-creation script created the tables in the proper way.
So, what have we learned here?
- Migrating from standalone MySQL to Amazon RDS sometimes has hidden pitfalls that aren’t always readily apparent. Many times it will “just work” – but sometimes it doesn’t. Percona is, of course, happy to help review your configurations and assist with any Amazon RDS implementation plans you might have.
- When in doubt, fully-specify your TIMESTAMP columns. If you want them NOT NULL, say so. If you want a default value or an on-updated value, set it. Even the configuration variable explicit_defaults_for_timestamp is deprecated and slated for removal in a future version, so eventually it won’t be possible to get the old pre-5.6 behavior at all.
“When in doubt, fully-specify your TIMESTAMP columns.”
Agree. I find being explicit in any schema definition is the best course, as it leads less to interpretation by MySQL and is more portable.
Same is true for MySQL configuration settings, actually. Who knows when the default will change on you!
It’s not that great when MySQL decides to deprecate settings that people already are using. I remember one instance where they disallowed the following query (notice the absence of quotes):
SELECT * FROM table WHERE field=fieldvalue;
That happened back in 2007 (or maybe 2008, can’t remember exactly), but it did cause a lot of issues. I understand that the above query is wrong – but why did they allow it in the first place?