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

AFTER UPDATE trigger and ON UPDATE now(): new value <> persisted value

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3.18, 10.3.20, 10.3.21, 10.4.10, 10.4.11, 10.5.5
    • Fix Version/s: 10.3, 10.4
    • Component/s: Triggers
    • Labels:
      None
    • Environment:
      Linux/Debian Buster 10.2

      Description

      [updated with a more concise example inspired from tests]

      Summary
      In an AFTER UPDATE trigger, the new value for a timestamp ON UPDATE NOW() column is set to NOW() even when the row is not updated.

      Steps to reproduce the issue

      create table t1 (t timestamp, i int);
      create trigger tr1 after update on t1 for each row set @new:=new.t;
      insert t1 (t,i) values (0, 1);
      update t1 set i = i;
      -- Query OK, 0 rows affected (0.00 sec)
      -- Rows matched: 1  Changed: 0  Warnings: 0
      select @new, t from t1;
      

      Expected result
      Value of the t column is equal to the value of the new.t column in the trigger

      +---------------------+---------------------+
      | @new                | t                   |
      +---------------------+---------------------+
      | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
      +---------------------+---------------------+
      1 row in set (0.00 sec)
      

      Actual result
      Value of the t column is NOT equal to the value of the new.t column in the trigger

      +---------------------+---------------------+
      | @new                | t                   |
      +---------------------+---------------------+
      | 2020-10-09 15:35:50 | 0000-00-00 00:00:00 |
      +---------------------+---------------------+
      1 row in set (0.00 sec)
      

      Context

      I've relied on this for years to distinguish updated from non updated rows, including in Mysql, but I skipped a lot of versions. The last working version for me was 10.3.17, and I found out about this when upgrading to 10.3.18 (the current stable Debian packaged version) so this is currently blocking for me.

      After a chat on irc #maria@freenode I understand it might not be clear what is the expected behavior since it doesn't seem to be documented, but I'll make the case that the NEW value in the trigger should reflect the value that's persisted in the database.

      Looking forward to reading some feedback on this!

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              PJ Pierre-Jean Clement
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.