Bug #4643 NDB doesn't seem to work with commit
Submitted: 20 Jul 2004 8:47 Modified: 20 Jul 2004 15:09
Reporter: Paul Weiss Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:4.1 bk src OS:Linux (RedHat 7.3)
Assigned to: Martin Skold CPU Architecture:Any

[20 Jul 2004 8:47] Paul Weiss
Description:
Inserting rows into NDB tables during a transaction is erratic.  See below for a transcript:

How to repeat:
mysql> create table abc (x integer primary key not null, y varchar(32)) engine ndb;
Query OK, 0 rows affected (1.27 sec)
 
mysql> select @@autocommit, @@tx_isolation, @@global.tx_isolation;
+--------------+-----------------+-----------------------+
| @@autocommit | @@tx_isolation  | @@global.tx_isolation |
+--------------+-----------------+-----------------------+
|            0 | REPEATABLE-READ | REPEATABLE-READ       |
+--------------+-----------------+-----------------------+
1 row in set (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into abc values (3 , 'three');
Query OK, 1 row affected (0.00 sec)
 ../bin/ndb_mgm

mysql> select * from abc;
Empty set (0.00 sec)
 
mysql> select * from abc;
Empty set (0.00 sec)

What happened to my row?
[20 Jul 2004 8:56] Paul Weiss
(ignore the line above with ndb_mgm...)
[20 Jul 2004 8:57] Paul Weiss
I've also seen the behavior where if I immediately do a select after the insert, I see the row.  Then a second later it is gone.
[20 Jul 2004 15:08] Martin Skold
Not reproducable in latest version (clone not yet pushed to mysql-4.1).
Probably an already solved bug.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.4-beta-debug
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> create table abc (x integer primary key not null, y varchar(32));
Query OK, 0 rows affected (1.18 sec)
 
mysql> insert into abc values (3 , 'three');
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from abc;
+---+-------+
| x | y     |
+---+-------+
| 3 | three |
+---+-------+
1 row in set (0.00 sec)
[20 Jul 2004 15:09] Martin Skold
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html
[20 Jul 2004 15:18] Martin Skold
After checking what have changed I assume that your
transaction must have timed-out (after .5 sec) and thus been aborted.
Will not be noticed until trying to commit.
We have changed the default to be looong for interactive tests,
can be changed through parameter "TransactionInactiveTimeout".
[20 Jul 2004 15:26] Martin Skold
The bug here is that the scan should return an
error instead of an empty answer.
[20 Jul 2004 18:10] Paul Weiss
I've upgraded to the latest bk version and set my TransactionInactivityTimeout high (60000 ms).  There is still some weirdness going on.

I've now set transaction isolation level to read committed in both clients.

Initial setup:

create table abc_n (x integer not null primary key, y varchar(32)) engine = ndb;
create table abc_i (x integer not null primary key, y varchar(32)) engine = innodb;

#client a:
insert into abc_n values (1,'one'), (2,'two');
insert into abc_i values (1,'one'), (2,'two');
commit;

#client a:
select * from abc_n; 
# returns two rows
select * from abc_i;
# returns two rows

#client b:
select * from abc_n; 
# returns two rows
select * from abc_i;
# returns two rows

# now try this:

#client a:
start transaction;
insert into abc_i values (3,'three');
insert into abc_n values (3,'three');
select * from abc_i; 
# returns three rows
select * from abc_n;
# returns three rows

# without committing go to
# client b:
select * from abc_i; 
# returns two rows
select * from abc_n;
# hangs for about a second, then
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I would have expected the select * from abc_n to return two rows (i.e. the committed rows) as in the innodb case.
[20 Jul 2004 22:02] Martin Skold
The problem you have is actually another known bug
where scans incorrectly wait on uncommitted inserts.
I will file another bug report on this.