CODEWITHSUNDEEP
mysqlinsert-update
Alon Kogan
Alon Kogan

Reputation: 3418

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

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)
);

Upvotes: 4

Views: 6028

Answers (3)

Richard Harrison
Richard Harrison

Reputation: 19393

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

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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 ;

Upvotes: 4

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: 0

Related Questions