Details
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions