Update on a table changes a field in another table

I'm working on a web application that handles some orders.

I have two tables defined as

CREATE TABLE sent_orders (
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(45) NOT NULL,
  date_open TIMESTAMP NOT NULL,
  state VARCHAR(50) NOT NULL,
  direction VARCHAR(45) NOT NULL,
  PRIMARY KEY (id) )


CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT,
  state_id INT(11) NOT NULL,
  date_open TIMESTAMP NOT NULL,
  date_processed TIMESTAMP NULL,
  date_closed TIMESTAMP NULL,
  sent_order_id INT(11) NULL,
  PRIMARY KEY (id) ,
  INDEX fk_orders_3_idx (state_id ASC) ,
  INDEX fk_orders_5_idx (sent_order_id ASC) ,
  CONSTRAINT fk_orders_1
    FOREIGN KEY (state_id)
    REFERENCES order_states (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_orders_2
    FOREIGN KEY (sent_order_id)
    REFERENCES sent_orders (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

The order processing is made of two steps in different times:

an order is inserted with a specific date_open and null sent_order_id with
```
insert into orders (...,date_open,..) values(...,?,...)
```

a sent_order is inserted with a different date_open

insert into sent_orders (...,date_open,..) values(...,?,...)

an update is made on the orders table to add the foreign key on the sent_orders
```
update orders set sent_order_id = ? where id = ?
```

The problem is that when I run the update on the orders table the orders.date_open field gets updated with the current date.

Another strange thing is that to debug the problem I added a second date field 'date_open2' on the orders table and then I ran a massive update as:

alter table orders add date_open2 TIMESTAMP NOT NULL;
update orders set date_open2 = date_open;

And now when I run

select date_open,date_open2,id,sent_order_id from orders

what I have is something like:

date_open               date_open2              id          sent_order_id
2015-11-17 23:35:14.0   2015-11-17 23:34:27.0   8654        678

Where the date_open2 is the original date_open, but it should be equal to the date_open field! It means that the update query got the right date value, but when I select it, it is wrong.

Mysql instance:

bitnami wampstack-5.4.25-0 with MySQL Server 5.5

Solution

Your TIMESTAMP columns are being automatically updated as described in

11.3.5 Automatic Initialization and Updating for TIMESTAMP

Specifically

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.

It sounds like you'll either want to modify the auto-update behaviour of your TIMESTAMP columns or use DATETIME columns instead.