CODEWITHSUNDEEP
mysqltimetimestamp
RS7
RS7

Reputation: 2361

MySQL timestamp auto updating

I have a table which contains a couple timestamp fields and but I'm having some problems having some of the fields defaulting to NULL.

In my users table I have these timestamp tables:

(field),(NULL),(default val)

deleted_on YES NULL
last_change_attemp YES NULL
newpass_time YES NULL
last_login YES NULL
created NO 0000-00-00 00:00:00
updates YES NULL

When I go edit a row in phpMyAdmin, the row I'm editing already has the NULL checkbox checked for all NULL fields, except the first field: *deleted_on*. If I manually check the checkbox, it allows me to maintain that field as NULL.

When I update another field via a query, it automatically does a current_timestamp on that first field. Is this expected?

Upvotes: 1

Views: 3492

Answers (2)

RS7
RS7

Reputation: 2361

I've read through the docs again and noticed this piece of text

To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then, for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs.

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

So for the time being I have the fields default to 0000-00-00 00:00:00 to prevent auto updates.

Upvotes: 3

Ariel
Ariel

Reputation: 26753

phpMyAdmin does some weird things with timestamps and null, especially if the column is marked as CURRENT_TIMESTAMP

I don't know why.

Upvotes: 0

Related Questions