CODEWITHSUNDEEP
mysqllinuxamazon-ec2timestamp
jhugo
jhugo

Reputation: 181

Timestamp in mysql column

I'm uploading a datababase to MySQL and get this problem:

ERROR 1293 (HY000) at line 31: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

The problem is that of two columns with "Timestamp" data type in that table, only one should have "Current Timestamp" value, while the other default value is "null". When I upload the database, both columns' value get changed to "Current Timestamp", which makes the process of exporting and importing very annoying, since I have to manually change it back to null before importing again.

Any idea why it automatically changes to "current timestamp"? This is an Amazon EC2 linux instance (see attached mysql version)

mysql version

Upvotes: 0

Views: 735

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179314

Before version 5.6, MySQL makes an assumption when you declare a timestamp column... specifically, it assumes the first timestamp column on a table will be the one that has the automatic update attributes.

If you don't explicitly disable the behavior on the first timestamp, it's implicitly enabled, which causes the explicit automatic value on a later timestamp to be rejected. It isn't enough not to ask.

It need not be the first TIMESTAMP column in a table that is automatically initialized or updated to the current timestamp. However, to specify automatic initialization or updating for a different TIMESTAMP column, you must suppress the automatic properties for the first one. Then, for the other TIMESTAMP column, the rules for the DEFAULT and ON UPDATE clauses are the same as for the first TIMESTAMP column, except that if you omit both clauses, no automatic initialization or updating occurs.

To suppress automatic properties for the first TIMESTAMP column, do either of the following:

Define the column with a DEFAULT clause that specifies a constant default value.

Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL.

— https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

So, for your first timestamp -- if it's not the one you want to be the automatic timestamp -- use either one of these column type declarations (they're identical):

TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
TIMESTAMP NOT NULL DEFAULT 0 -- automatically expanded to '0000-00-00 00:00:00'

This should allow you to copy this table definition between systems without issue.

This silliness was fixed in MySQL Server 5.6, where the system variable explicit_defaults_for_timestamp disables the implicit automatic behavior for the first timestamp in a table.

If you start a server running 5.6 without setting this option, a warning is written to the error log.

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please 
use --explicit_defaults_for_timestamp server option (see documentation 
for more details).

The warning is reminding you that you still have the legacy behavior, which is deprecated in 5.6.

Upvotes: 2

Related Questions