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

ON UPDATE CURRENT_TIMESTAMP doesn't always work

    XMLWordPrintable

    Details

      Description

      If one executes an UPDATE statement that doesn't really change any values and that has an explicit value for the field with ON UPDATE CURRENT_TIMESTAMP, then the next UPDATE statement (without an explicit value for the timestamp) won't automatically set current timestamp into that field. The problem can be easily revealed with the following addition to the existing test:

      --- a/mysql-test/include/function_defaults.inc
      +++ b/mysql-test/include/function_defaults.inc
      @@ -408,15 +408,28 @@ UPDATE t1 SET c = 2;
       SELECT * FROM t1;
       
       --echo #
      +--echo # Test that ON UPDATE CURRENT_TIMESTAMP works after non-changing UPDATE.
      +--echo #
      +
      +--echo # 2011-04-20 09:54:13 UTC
      +SET TIMESTAMP = 1303293253.794613;
      +
      +UPDATE t1 SET c = 2, b = '2011-04-20 09:53:41.794613';
      +SELECT * FROM t1;
      +
      +UPDATE t1 SET c = 3;
      +SELECT * FROM t1;
      +
      +--echo #
       --echo # Test of multiple-table UPDATE for ON UPDATE CURRENT_TIMESTAMP
       --echo #
       --echo # 2011-04-20 15:06:13 UTC
       SET TIMESTAMP = 1303311973.534231;
       
      -UPDATE t1 t11, t1 t12 SET t11.c = 2;
      +UPDATE t1 t11, t1 t12 SET t11.c = 3;
       SELECT * FROM t1;
       
      -UPDATE t1 t11, t1 t12 SET t11.c = 3;
      +UPDATE t1 t11, t1 t12 SET t11.c = 2;
       SELECT * FROM t1;
       
       DROP TABLE t1;

      In the test output you can see that after "UPDATE t1 SET c = 3" the field a is updated, but the field b is not.

      The problem is that HAS_EXPLICIT_VALUE is reset in TABLE::update_default_fields() which is called from mysql_update() only when compare_record(table) returns true. So when mysql_update() doesn't change the row the flag is not reset and gets spilled into the next row or even the next statement.

      I don't know yet what's the appropriate way to fix this problem (i.e. where the flag HAS_EXPLICIT_VALUE should be reset), so I'd really appreciate a speedy response.

      This problem is especially dangerous due to the fact that its manifestation depends on the order of statements executed on the same TABLE object which can be different between master and slaves.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              pivanof Pavel Ivanov
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: