Bug #69695 NOW() is not constant in a trigger (without functions)

• Submitted: 9 Jul 2013 5:12
• Modified: 30 Nov 2013 9:22
• Reporter: Alfie John
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.70
• OS: Linux (Wheezy)
• CPU Architecture: Any
• Tags: NOW(), triggers

[9 Jul 2013 5:12] Alfie John

Description:
There seems to be a regression between 5.1.65 and 5.1.70.

Originally we thought this was a manifest of #12480, but after running the test case I can confirm that it's a different bug. Below is a script which tries to tickle the bug.

No discrepancies were found after running multiple times against 5.1.65:

$ perl trigger_bug.pl
$

However there are many inconsistent rows after running *each* time against 5.1.70 e.g:

$ perl trigger_bug.pl                        
id: 3, 2013-07-08 23:25:08 vs 2013-07-08 23:25:07 
id: 6, 2013-07-08 23:25:11 vs 2013-07-08 23:25:10 
id: 9, 2013-07-08 23:25:14 vs 2013-07-08 23:25:13 
id: 19, 2013-07-08 23:25:24 vs 2013-07-08 23:25:23
id: 24, 2013-07-08 23:25:29 vs 2013-07-08 23:25:28
id: 30, 2013-07-08 23:25:35 vs 2013-07-08 23:25:34
id: 38, 2013-07-08 23:25:43 vs 2013-07-08 23:25:42
id: 45, 2013-07-08 23:25:50 vs 2013-07-08 23:25:49
id: 49, 2013-07-08 23:25:54 vs 2013-07-08 23:25:53
id: 50, 2013-07-08 23:25:55 vs 2013-07-08 23:25:54
id: 57, 2013-07-08 23:26:02 vs 2013-07-08 23:26:01
id: 62, 2013-07-08 23:26:07 vs 2013-07-08 23:26:06
id: 66, 2013-07-08 23:26:11 vs 2013-07-08 23:26:10
id: 69, 2013-07-08 23:26:14 vs 2013-07-08 23:26:13
id: 77, 2013-07-08 23:26:22 vs 2013-07-08 23:26:21
id: 78, 2013-07-08 23:26:23 vs 2013-07-08 23:26:22
id: 96, 2013-07-08 23:26:41 vs 2013-07-08 23:26:40
$

How to repeat:
#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Time::HiRes qw(gettimeofday usleep);

# init db {{{

my $Dbh = DBI->connect('dbi:mysql:test','', '', {PrintError => 0, RaiseError => 1});

$Dbh->do(q{DROP TABLE IF EXISTS trigger_bug});
$Dbh->do(q{
  CREATE TABLE trigger_bug(
    id INT(11)   NOT NULL,
    at TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
  ) ENGINE=InnoDB
});

$Dbh->do(q{DROP TABLE IF EXISTS trigger_bug_child});
$Dbh->do(q{
  CREATE TABLE trigger_bug_child(
    id INT(11)   NOT NULL PRIMARY KEY,
    at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
});

$Dbh->do(q{DROP TRIGGER IF EXISTS trigger_bug_trigger});
$Dbh->do(q{
  CREATE TRIGGER  trigger_bug_trigger
  AFTER INSERT ON trigger_bug_child
  FOR EACH ROW
  UPDATE trigger_bug
  SET    at = NOW()
  WHERE  id = NEW.id
});

# }}}

# try to trigger the bug {{{

for (1..100) {
  my ($s, $ms);

  do {
    usleep(1_000_000 - $ms - 900) if $ms;
    ($s, $ms) = gettimeofday();
  } while ($ms < 999_000);

  $Dbh->do('INSERT INTO trigger_bug(id)       VALUES (?)', undef, $_);
  $Dbh->do('INSERT INTO trigger_bug_child(id) VALUES (?)', undef, $_);
}

# }}}

# check if bug was triggered {{{

my $BadParent = $Dbh->selectall_arrayref(q{
  SELECT    tb.id AS tb_id, tb.at AS tb_at, tbc.at AS tbc_at
  FROM      trigger_bug       tb
  LEFT JOIN trigger_bug_child tbc USING (id)
  WHERE     tb.at != tbc.at
}, {Slice => {}});

foreach my $Row (@{$BadParent}) {
  print "id: $Row->{tb_id}, $Row->{tb_at} vs $Row->{tbc_at}\n";
}

# }}}

[30 Nov 2013 9:22] MySQL Verification Team

Hello Alfie,

Thank you for the bug report and test case.
I tried the provided test case on 5.1.72/70/69, 5.5.34,5.6.15, 5.7.3-m13 but couldn't reproduce.

Is this issue still repeatable at your end? Please try it on latest GA version and let us know if it is still reproducible at your end. 

Thanks,
Umesh