MySQL Bugs: #38034: last_insert_id() value not shown in table with trigger

Bug #38034	last_insert_id() value not shown in table with trigger
Submitted:	11 Jul 2008 3:27	Modified:	14 Sep 2009 10:11
Reporter:	ws lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-6.2	OS:	Any (Solaris, Linux)
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	mysql-5.1-telco-6.*

Description:
In table with trigger, 
After insert query, last_insert_id() is now shown one time.
one more time,when query last_insert_id() shown last_insert_id()value at last.

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1 where c1=last_insert_id();
Empty set (0.00 sec)
mysql> select * from t1 where c1=last_insert_id();
+----+----+---------------------+---------------------+---------+
| c1 | c2 | c3                  | c4                  | c5      |
+----+----+---------------------+---------------------+---------+
|  8 |  5 | 2008-07-11 12:19:33 | 2008-07-11 12:19:33 | 1111111 | 
+----+----+---------------------+---------------------+---------+
1 row in set (0.00 sec)

How to repeat:
mysql> select version(); 
+---------------+
| version()     |
+---------------+
| 5.1.23-rc-log | 
+---------------+
1 row in set (0.00 sec)

mysql> show table status;
+------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine     | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| a1   | ndbcluster |      10 | Dynamic    |  100 |              0 |           0 |               0 |            0 |         0 |              1 | NULL        | NULL        | NULL       | utf8_general_ci |     NULL |                |         | 
| t1   | ndbcluster |      10 | Fixed      |  100 |              0 |           0 |               0 |            0 |         0 |              6 | NULL        | NULL        | NULL       | utf8_general_ci |     NULL |                |         | 
+------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.00 sec)

mysql> show triggers;
+---------+--------+-------+-----------------------------------+--------+---------+---------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement                         | Timing | Created | sql_mode            | Definer        | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+-----------------------------------+--------+---------+---------------------+----------------+----------------------+----------------------+--------------------+
| trg1    | INSERT | t1    | BEGIN 
   SET new.c3 = now();
End | BEFORE | NULL    | STRICT_TRANS_TABLES | root@localhost | utf8                 | utf8_general_ci      | utf8_general_ci    | 
+---------+--------+-------+-----------------------------------+--------+---------+---------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

mysql> desc a1;
+-------+---------------+------+-----+-------------------+----------------+
| Field | Type          | Null | Key | Default           | Extra          |
+-------+---------------+------+-----+-------------------+----------------+
| a1    | int(11)       | NO   | PRI | NULL              | auto_increment | 
| a2    | tinyint(4)    | YES  |     | NULL              |                | 
| a3    | varchar(280)  | YES  |     | NULL              |                | 
| a4    | timestamp     | NO   |     | CURRENT_TIMESTAMP |                | 
| c1    | int(11)       | YES  |     | NULL              |                | 
| c2    | int(11)       | YES  |     | NULL              |                | 
| c3    | timestamp     | YES  |     | NULL              |                | 
| c4    | timestamp     | YES  |     | NULL              |                | 
| c5    | decimal(38,0) | YES  |     | NULL              |                | 
+-------+---------------+------+-----+-------------------+----------------+
9 rows in set (0.00 sec)

mysql> desc t1;
+-------+---------------+------+-----+-------------------+----------------+
| Field | Type          | Null | Key | Default           | Extra          |
+-------+---------------+------+-----+-------------------+----------------+
| c1    | int(11)       | NO   | PRI | NULL              | auto_increment | 
| c2    | int(11)       | NO   |     | 0                 |                | 
| c3    | timestamp     | NO   |     | CURRENT_TIMESTAMP |                | 
| c4    | timestamp     | YES  |     | NULL              |                | 
| c5    | decimal(38,0) | NO   |     | NULL              |                | 
+-------+---------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        5 | 
+----------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1 where c1=last_insert_id();
Empty set (0.00 sec)

mysql> select * from t1 where c1=last_insert_id();
+----+----+---------------------+---------------------+---------+
| c1 | c2 | c3                  | c4                  | c5      |
+----+----+---------------------+---------------------+---------+
|  8 |  5 | 2008-07-11 12:19:33 | 2008-07-11 12:19:33 | 1111111 | 
+----+----+---------------------+---------------------+---------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Thank you for the report.

I can not repeat described behavior in test environment. Please provide your configuration and log files. Also please provide output of SELECT VERSION() query.

To. Sveta Smirnova

Thanks, your confirm.

>last_insert_id() value not shown in table with trigger
My bug report titie was ambigus.
last_insert_id() value is shown well.
but The inserted record of last_insert_id()'s value was not shown one time.
if must one more time run select query ,inerted recored is shown.

In this bug, mysql version is not likely important.
All mysql cluster version is may become same bug.
I have tested mysql5.1.23 and mysql5.1.24 and mysql5.1.23-ndb6.2.15 and mysql5.1.23-ndb6.3.9.
But, In upper version, all same bug.

Currently, I have testing in mysql 5.1.23-ndb6.3.9.

mysql> select version();
+----------------------------+
| version()                  |
+----------------------------+
| 5.1.23-ndb-6.3.9-telco-log | 
+----------------------------+
1 row in set (0.01 sec)

below is mysql cluster configureation.
more config.ini
----------------------------------------------
[NDBD DEFAULT]
NoOfReplicas=2

[NDBD]
hostname=192.0.0.1
datadir=/usr/local/mysql5.1.23-ndb6.3.9/data 
[NDBD]
hostname=192.0.0.2
datadir=/usr/local/mysql5.1.23-ndb6.3.9/data 

[MYSQLD]
hostname=192.0.0.1
[MYSQLD]
hostname=192.0.0.2
----------------------------------------------

Test you, according to below step 

# mysql -uroot -p

mysql> CREATE TABLE `t1` (
   `c1` int(11) NOT NULL AUTO_INCREMENT,
   `c2` int(11) NOT NULL DEFAULT '0',
   `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `c4` timestamp NULL DEFAULT NULL,
   `c5` decimal(38,0) NOT NULL,
   PRIMARY KEY (c1)
 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.54 sec)

mysql> CREATE TABLE `a1` (
   `a1` int(11) NOT NULL AUTO_INCREMENT,
   `a2` tinyint(4) DEFAULT NULL,
   `a3` varchar(280) DEFAULT NULL,
   `a4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `c1` int(11) DEFAULT NULL,
   `c2` int(11) DEFAULT NULL,
   `c3` timestamp NULL DEFAULT NULL,
   `c4` timestamp NULL DEFAULT NULL,
   `c5` decimal(38,0) DEFAULT NULL,
   PRIMARY KEY (`a1`)
 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.41 sec)

mysql> delimiter //
mysql> CREATE TRIGGER trg1 BEFORE INSERT ON t1
 FOR EACH ROW
 BEGIN 
    SET new.c3 = now();
 End//
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (2,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (3,now(),now(),123456);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (4,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (6,now(),now(),123456);
INSERT INTO t1(c2,c3,c4,c5) VALUES (7,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (7,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (8,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (9,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (10,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|       10 | 
+----------+
1 row in set (0.04 sec)

#######pay attention to from below line#######

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1(c2,c3,c4,c5) VALUES (11,now(),now(),123456);
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               11 | 
+------------------+
1 row in set (0.02 sec)

mysql> select * from t1 where c1=11;
Empty set (0.04 sec)

mysql> select * from t1 where c1=11;
+----+----+---------------------+---------------------+--------+
| c1 | c2 | c3                  | c4                  | c5     |
+----+----+---------------------+---------------------+--------+
| 11 | 11 | 2008-07-11 22:26:34 | 2008-07-11 22:26:34 | 123456 | 
+----+----+---------------------+---------------------+--------+
1 row in set (0.02 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Like the upper, result of "select * from t1 where c1=11" not shown one time.
one more time, if execute "select * from t1 where c1=11" query, in the seconed try is shown the record c1=11.

Can't reproduce, using the following statements and connected as root@localhost i get the same non-empty result on both SELECT queries:

  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1 (
    c1 INT PRIMARY KEY AUTO_INCREMENT,
    c2 INT,
    c3 TIMESTAMP,
    c4 TIMESTAMP,
    c5 DECIMAL(38,0)
  ) ENGINE=ndbcluster;

  DROP TRIGGER IF EXISTS trg1;
  DELIMITER //
  CREATE TRIGGER trg1 BEFORE INSERT ON t1
  FOR EACH ROW BEGIN 
     SET new.c3 = now();
  End
  //
  DELIMITER ;

  INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');

  SELECT * FROM t1 WHERE c1=LAST_INSERT_ID();

  SELECT * FROM t1 WHERE c1=LAST_INSERT_ID();

Can you provide your my.cnf and config.ini and check
whether the create statements match yours?

To. Hartmut Holzgraefe

thanks your reply.
rechecke , accorint to below, 
firstly, in t1 table , over 1 record, must insert temporary record.

mysql>INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');
mysql>INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');
mysql>INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');

then,you must execute transaction before insert query.
(of coure, this case t1 table have trigger)

mysql>start transaction;
mysql>INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');
mysql>SELECT * FROM t1 WHERE c1=LAST_INSERT_ID();
here, empty result.
mysql>SELECT * FROM t1 WHERE c1=LAST_INSERT_ID();
one more time,only here, not empty result.

if first select query is not empty result, this case,
to t1 table insert over 1 milian records.
then, repeat upper test.

Thank you for the feedback.

Verifying with following test case:

--source include/have_ndb.inc

create table t1(c1 int(11) not null auto_increment primary key, c2 int not null,
c3 timestamp not null, c4 timestamp, c5 decimal(38,0)) engine=ndb;

delimiter |;

create trigger bi_t1 before insert on t1
FOR EACH ROW BEGIN 
   SET new.c3 = now();
End
|

delimiter ;|

INSERT INTO t1(c2,c3,c4,c5) VALUES (1,now(),now(),'1111111');
INSERT INTO t1(c2,c3,c4,c5) VALUES (2,now(),now(),'1111111');
INSERT INTO t1(c2,c3,c4,c5) VALUES (2,now(),now(),'1111111');

start transaction;

INSERT INTO t1(c2,c3,c4,c5) VALUES (4,now(),now(),'1111111');

sleep 10;

INSERT INTO t1(c2,c3,c4,c5) VALUES (5,now(),now(),'1111111');

select * from t1 where c1=last_insert_id();
select * from t1 where c1=last_insert_id();

martin to estimate E/R

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81859

2967 Martin Skold	2009-08-28
      Bug#38034: last_insert_id() value not shown in table with trigger, Inserts were not flushed before scan, setting m_rows_to_insert=1 unless start_bulk_insert has been called.
      modified:
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82283

2977 Martin Skold	2009-09-03
      Bug#38034: last_insert_id() value not shown in table with trigger, added test case.
      modified:
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_trigger.test

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82295

3028 Martin Skold	2009-09-03 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82310

2982 Martin Skold	2009-09-03 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc

Pushed into 5.1.37-ndb-7.0.8 (revid:martin.skold@mysql.com-20090903095452-7ukx3yiaeltk2mgy) (version source revid:martin.skold@mysql.com-20090903095452-7ukx3yiaeltk2mgy) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:martin.skold@mysql.com-20090903115020-aqt0bnf1s1m458c3) (version source revid:martin.skold@mysql.com-20090903091350-l7if6pbajxsawq9z) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:martin.skold@mysql.com-20090903125016-bd6hb0ukbdasnj23) (version source revid:martin.skold@mysql.com-20090903075051-hr86da6r05olo6iy) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82339

2982 Martin Skold	2009-09-03 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc

Pushed into 5.1.35-ndb-7.1.0 (revid:martin.skold@mysql.com-20090903152059-p5762oa1wy9b6o6f) (version source revid:martin.skold@mysql.com-20090903152059-p5762oa1wy9b6o6f) (merge vers: 5.1.35-ndb-7.1.0) (pib:11)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82563

2973 Martin Skold	2009-09-07 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc
        storage/ndb/include/kernel/signaldata/CloseComReqConf.hpp
        storage/ndb/src/kernel/blocks/ERROR_codes.txt
        storage/ndb/src/kernel/blocks/cmvmi/Cmvmi.cpp
        storage/ndb/src/kernel/blocks/dbtc/Dbtc.hpp
        storage/ndb/src/kernel/blocks/dbtc/DbtcMain.cpp
        storage/ndb/src/kernel/blocks/dbtup/Dbtup.hpp
        storage/ndb/src/kernel/blocks/dbtup/DbtupDiskAlloc.cpp
        storage/ndb/src/kernel/blocks/dbtup/DbtupMeta.cpp
        storage/ndb/src/kernel/blocks/qmgr/Qmgr.hpp
        storage/ndb/src/kernel/blocks/qmgr/QmgrMain.cpp
        storage/ndb/test/ndbapi/testNdbApi.cpp
        storage/ndb/test/run-test/autotest-boot.sh
        storage/ndb/test/run-test/daily-basic-tests.txt

Documented bugfix in the NDB-6.2.19, 6.3.27, and 7.0.8 changelogs, as follows:

        An insert on an NDB table was not always flushed properly before
        performing a scan. One way in which this issue could manifest
        was that LAST_INSERT_ID() sometimes failed to return correct
        values when using a trigger on an NDB table.