Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5478

Improper maintenance of TIMESTAMP_AUTO_SET_ON_BOTH interferes with REPLACE optimization

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5.34
    • Fix Version/s: 5.5
    • Component/s: None
    • Labels:

      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

            There are no comments yet on this issue.

              People

              • Assignee:
                Unassigned
                Reporter:
                kolbe Kolbe Kegel
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: