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

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.18, 10.3.20, 10.3.21, 10.4.10, 10.4.11, 10.5.5
    • 10.4
    • Triggers
    • None
    • 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

              sanja Oleksandr Byelkin
              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.