1

I upgraded from MySQL 5.1 to 5.6.17. Now in Wordpress plugins some of my timestamp columns have started behaving strangely.

MySQL documentation on this very critical feature enhancement (decimals in timestamp fields) is sloppy. SO one relies on blogs/websites.

Some websites such as this recommend ALTER TABLE...FORCE.

But this did nothing for the older data. Some tables which rely on the uniqueness of the timestamp field, now have ".0000" in the 4 digit suffix. Even the ALTER TABLE FORCE didn't change that data.

Any ideas on what we can do instead of manually tinkering with the timestamp fields? Thanks!

PKHunter
  • 113
  • 4

1 Answers1

2

There are two things here, updating the table structure to 5.6 format and handling the new features.

For the upgrade, run the mysql_upgrade script to check for format incompatibilities. For those tables that it tells you to REPAIR them, run REPAIR TABLE your_table if they are MyISAM and ALTER TABLE your_table ENGINE=InnoDB if they are InnoDB. That will reconstruct those tables (they will be locked while that happens) to avoid any incompatibility issues.

Once you have done that, in relation to temporal SQL changes, the idea is that a TIMESTAMP (and DATETIME, TIME) is now really a TIMESTAMP(0). You can also create TIMESTAMP(n), where n is the number of decimals. That shouldn't tamper any uniqueness of time fields, but you must understand that functions like seconds() can return now a decimal, not an integer.

The other change, making TIMESTAMPs not "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" are not enabled by default (they mimic the old behaviour). But it is deprecated and can be disabled with the variable explicit_defaults_for_timestamp.

Relaying on now() for creating unique keys is and was already a broken design, because you can insert two rows on the same second. You shouldn't do that.

jynus
  • 14,857
  • 1
  • 35
  • 43
  • Thank you. I guess the other issue is that in my PHP the microtime(true) function is not returning a large number, it's returning an exponential representation. When this is sent in PHP to a MySQL timestamp column it seldom creates a unique value, which is what we were hoping to create. Any PHP INI tweak that might help fix that side of affairs? – PKHunter Aug 03 '14 at 11:32
  • @PKHunter First, although unrelated, make sure to upgrade your connector and that it is 5.6-aware (you can run into some authentication issues). Second, if you have a timestamp(6), you insert either on string format 'XXXX-XX-XX XX:XX:XX.XXXXXX' or on decimal format with from_unixtime(XXXXXXXXXX.XXXXXX). – jynus Aug 03 '14 at 13:39