MySQL Bugs: #55911: Error updating primary key on more than one entry

Bug #55911	Error updating primary key on more than one entry
Submitted:	11 Aug 2010 13:01	Modified:	12 Aug 2010 18:14
Reporter:	Azeddine ESSAI	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-7.0	OS:	Linux (Red Hat 3.4.6-8)
Assigned to:		CPU Architecture:	Any
Tags:	cluster, mysql-5.1.39 ndb-7.0.9, ndbcluster, primary key

Description:
Hi.
We use MySQL Cluster.
We have the following error :
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.
When we run an update query, involving more than one row, attempting to change a primary key field. After the query just one row is changed.
The problem occurs on NDBD tables but doesn't on MyIsam tables.

How to repeat:
CREATE TABLE IF NOT EXISTS `test` (
  `a` varchar(8) COLLATE latin1_general_ci NOT NULL,
  `b` varchar(8) COLLATE latin1_general_ci NOT NULL,
  `c` varchar(16) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `test` (`a`, `b`, `c`) VALUES
('TP02', 'sepia2', 'machin'),
('TP01', 'sepia2', 'truc');

The query :
update `test` set b='sepia3' where b='sepia2'

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

NDB is transactional storage engine while MyISAM is not. This explain why you don't get this error using MyISAM. Please read about transactions at http://dev.mysql.com/doc/refman/5.1/en/index.html

Hi,
Can you explain me plase why it's not a bug?
especialy, we don't use transactions :
ndb_use_transactions=off

Regards.

Thank you for the feedback.

This option changes case. Verified as described. Maybe duplicate of bug #55888