Mysql - when trying to UPDATE a timestamp column. It fails to update

Question

I think I may have encountered a bug in mysql, or is it just me doing it wrong.

I've been using the same specific queries for the last four months and just today it stopped working somehow. I can't see the problem.

I'm executing these queries in the mysql console it works great, and the field is being updated. but when these queries are being executed by PHP it fails.

After insertion of a record into a table(with two timestamp fields), I'm trying to update a specific timestamp column.

But unfortunately it fails to update the column.

The query goes well(no errors), but still the value in the timestamp column stays the same. That's weird, cause when I'm leaving the initial column value as NULL, the update query succeed.

Columns :

START_DATETIME, END_DATETIME - are "timestamp" type.

Insert:

INSERT INTO TABLE1(START_DATETIME, END_DATETIME, RESPONSE) 
VALUES(NOW(), NOW(), 'STARTED')

Insert is done successfully. id is 123

The update query is normal like any other query:

UPDATE TABLE1 
SET END_DATETIME = NOW(), RESPONSE='ENDED'
WHERE ID = 123

Update fails, END_DATETIME doesn't get the NOW() value.

Can be reproduced with this:

CREATE TABLE TABLE1 
(
    id int auto_increment, 
    start_datetime timestamp, 
    end_datetime timestamp, 
    response varchar(100), 

    primary key(id)
);

In fact the END_DATETIME gets the NOW() value. Unfortunately the START_DATETIME gets the same. So far I have no explanation for this, though. — fancyPants
– fancyPants, Commented Jul 2, 2012 at 8:49
Hi guys, i think you missed my point. The real problem isn't in the default values of START_DATETIME (it has a DEFAULT CURRENT_TIMESTAMP) The problem is in the END_DATETIME column that's being initialized in the INSERT query, Couldn't be updated to a different value -later-. — Alon Kogan
– Alon Kogan, Commented Jul 2, 2012 at 10:41

ypercubeᵀᴹ · Accepted Answer · 2012-07-02 09:07:46Z

4

You probably have defined the first timestamp column (the START_DATETIME one) to be auto-inserted and auto-updated with the CURRENT_TIMESTAMP value (which is the same as NOW().

Notice that if you don't explicitedly state anything about the TIMESTAMP columns in the CREATE TABLE script, the first one of them gets by default this behaviour/attributes. Read the MySQL documentation about this Automatic Initialization and Updating for TIMESTAMP, where it states:

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

So, if you do a SHOW CREATE TABLE tableName, you'll have something like this:

CREATE TABLE table1
( ...
, START_DATETIME TIMESTAMP NOT NULL 
                   DEFAULT CURRENT_TIMESTAMP
                   ON UPDATE CURRENT_TIMESTAMP
, ...
) ;

You should alter the column definition to not be auto_updated, if you don't want this behaviour:

ALTER TABLE table1
  MODIFY COLUMN  
    START_DATETIME TIMESTAMP NOT NULL
                     DEFAULT CURRENT_TIMESTAMP ;

edited Jul 2, 2012 at 9:07

answered Jul 2, 2012 at 8:55

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

fancyPants Over a year ago

Edited the question to include a CREATE TABLE with which it can be reproduced. No ON UPDATE or whatever.

ypercubeᵀᴹ Over a year ago

See my edit. Try the SHOW CREATE TABLE tableName ; statement.

Alon Kogan Over a year ago

Hi guys, i think you missed my point. The real problem isn't in the default values of START_DATETIME (it has a DEFAULT CURRENT_TIMESTAMP) The problem is in the END_DATETIME column that's being initialized in the INSERT query, Couldn't be updated to a different value -later-.

ypercubeᵀᴹ Over a year ago

Please run: SELECT * FROM table1 WHERE ID = 123;, then UPDATE table1 SET END_DATETIME = NOW(), RESPONSE='ENDED' WHERE ID = 123;, then again SELECT * FROM table1 WHERE ID = 123; and shows us the outputs you get.

Alon Kogan Over a year ago

:) it's the same initial date. that's the problem. Looks like the update query skips that column. That is why I assumed there is a bug in mysql. When i run that query from console it works fine!.

|

Richard Harrison · Accepted Answer · 2012-07-02 09:12:34Z

2

After looking into this more this is what I'd expect if the first table has ON UPDATE CURRENT_TIMESTAMP as it is being automatically set by the update to the second record.

I don't think that this can have been working previously;

to fix it:

ALTER TABLE TABLE1
    CHANGE COLUMN start_datetime start_datetime TIMESTAMP NULL DEFAULT NULL AFTER id,
    CHANGE COLUMN end_datetime end_datetime TIMESTAMP NULL DEFAULT NULL AFTER start_datetime;

If you want date time values the TIMESTAMP isn't much good for this as it is useful for auto-updating values as the TIMESTAMP data type offers automatic initialization and updating to the current date and time. For more information, see Automatic Initialization and Updating for TIMESTAMP

If you need a field you can manage yourself then one of the other types

DATE
DATETIME

may be more appropriate, see 11.3.1. The DATE, DATETIME, and TIMESTAMP Types

edited Jul 2, 2012 at 9:12

answered Jul 2, 2012 at 8:30

Richard Harrison

19.5k4 gold badges43 silver badges67 bronze badges

9 Comments

eggyal Over a year ago

Er, as explained in the manual page to which you link, automatic intialisation/update of TIMESTAMP columns is optional. And in any event, can be overridden in the INSERT/UPDATE command.

Alon Kogan Over a year ago

Hi Richard, thnx for your fast reply. I've been using the same specific queries for the last four months and just today it stoped working somehow. I don't see the problem. If i'm executing these qureies in the mysql console it works great, and the field is being updated. but when these queries are being executed by php it fails. is there any problem inserting NOW() value into a timestamp? I could use CURRENT_TIMESTAMP....

Alon Kogan Over a year ago

There is no error during the execute. I wish there was. so i could point the problem

Richard Harrison Over a year ago

revised again after doing more tests it is clear that the first field TIMESTAMP is being set as a result of ON UPDATE CURRENT_TIMESTAMP.

ypercubeᵀᴹ Over a year ago

@Richard: How did you "revise"? It was not your question!

|

Sashi Kant · Accepted Answer · 2012-07-02 08:57:17Z

0

This is a problem with timestamp type, If you change both the column to datetime, you will get what you are expecting

answered Jul 2, 2012 at 8:57

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

Mysql - when trying to UPDATE a timestamp column. It fails to update

3 Answers 3

8 Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related