Bug #14108 Trigger work improperly with NDB tables

Submitted: 18 Oct 2005 2:31 Modified: 23 Feb 2006 13:19
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.0.13-RC OS:Solaris (solaris 9)
Assigned to: Martin Skold CPU Architecture:Any

[18 Oct 2005 2:31] [ name withheld ]
Description:
A cluster with two ndb nodes.

Create two tables:
CREATE TABLE `t` (
  `a` tinyint(3) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`a`)
)

CREATE TABLE `test` (
  `a` varchar(21) NOT NULL default '1',
  `b` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `a` (`a`,`b`)
) ENGINE=ndbcluster

And create a trigger on table t:
CREATE TRIGGER trigtest AFTER INSERT ON t
FOR EACH ROW BEGIN
	declare rc int;
	set rc=(select count(*) from test);
	if rc=0 then insert into test values('1111',New.a);
	else update test set b=b+New.a;
	end if;
END

insert into t values(default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---+
| a    | b |
+------+---+
| 1111 | 1 |
+------+---+
1 row in set (0.00 sec)

mysql> alter table t engine=ndb;
mysql> insert into t values(default);
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> show warnings;           
+---------+------+----------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                        |
+---------+------+----------------------------------------------------------------------------------------------------------------+
| Error   | 1296 | Got error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from NDB |
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1                                                            |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1                                                            |
+---------+------+----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from test;
+------+-----+
| a    | b   |
+------+-----+
| 1111 | 255 |
+------+-----+
1 row in set (0.01 sec)

mysql> insert into t values(default);
ERROR 1062 (23000): Duplicate entry '255' for key 1

if drop the 255 from t and test, alter table t engine=myisam, the trigger can work again.

How to repeat:
Do as what i descript

Suggested fix:
No
[6 Nov 2005 0:57] Hartmut Holzgraefe
verified with current bk sources
[23 Feb 2006 13:17] Martin Skold
Test file

Attachment: ndb_bug#14108.test (application/octet-stream, text), 783 bytes.

[23 Feb 2006 13:17] Martin Skold
Result file

Attachment: ndb_bug#14108.result (application/octet-stream, text), 665 bytes.

[23 Feb 2006 13:19] Martin Skold
I cannot reproduce this one on the latest 5.0 source.
Please verify again with the attached test file.

-- Martin

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.