[#MDEV-5478] Improper maintenance of TIMESTAMP_AUTO_SET_ON_BOTH interferes with REPLACE optimization

According to the comments and code in sql_insert.cc:1764(5.5.34), a table with both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP should be able to use the "update" optimization for REPLACE (instead of the strict delete/insert behavior).

          The manual defines the REPLACE semantics that it is either

          an INSERT or DELETE(s) + INSERT; FOREIGN KEY checks in

          InnoDB do not function in the defined way if we allow MySQL

          to convert the latter operation internally to an UPDATE.

          We also should not perform this conversion if we have

          timestamp field with ON UPDATE which is different from DEFAULT.

          Another case when conversion should not be performed is when

          we have ON DELETE trigger on table so user may notice that

          we cheat here. Note that it is ok to do such conversion for

          tables which have ON UPDATE but have no ON DELETE triggers,

          we just should not expose this fact to users by invoking

          ON UPDATE triggers.

*/

        if (last_uniq_key(table,key_nr) &&

            !table->file->referenced_by_foreign_key() &&

            (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||

             table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH) &&

            (!table->triggers || !table->triggers->has_delete_triggers()))

However, this "doesn't work". It appears that table->timestamp_field_type is never TIMESTAMP_AUTO_SET_ON_BOTH for some reason at this point, even if the table has DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP and timestamp_field_type is correctly set when the table is opened.

Running mysqld under gdb confirms that at this point in execution, timestamp_field_type is set to TIMESTAMP_AUTO_SET_ON_UPDATE when it seems that it should be TIMESTAMP_AUTO_SET_ON_BOTH.

(gdb) frame

#0  write_record (thd=0x7f474afbd000, table=0x7f475d071800, info=0x7f474aad3e78) at /home/buildbot/buildbot/build/sql/sql_insert.cc:1777

1777    in /home/buildbot/buildbot/build/sql/sql_insert.cc

(gdb) print table->timestamp_field_type

$1 = TIMESTAMP_AUTO_SET_ON_UPDATE

mysql 5.5.34-MariaDB-log (root) [test] db1> show create table t1\G

*************************** 1. row ***************************

       Table: t1

Create Table: CREATE TABLE `t1` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `c` int(11) DEFAULT NULL,

  `time_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=148440 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

This matches upstream behavior, but does not match the intended behavior according to the comments or intent of the code in the 5.5 server.

MariaDB 10.0 and MySQL 5.6 do not have this behavior, as they've simply removed the timestamp_field_type check from this if statement... I'm not sure if they check for it in some other way or whether they're just doing something bizarre in this case.

[MDEV-5478] Improper maintenance of TIMESTAMP_AUTO_SET_ON_BOTH interferes with REPLACE optimization Created: 2013-12-20 Updated: 2014-06-06
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.34
Fix Version/s:	5.5

[MDEV-5478] Improper maintenance of TIMESTAMP_AUTO_SET_ON_BOTH interferes with REPLACE optimization Created: 2013-12-20 Updated: 2014-06-06