MySQL Bugs: #20423: Unhandled resource shortage in unique index code

Bug #20423	Unhandled resource shortage in unique index code
Submitted:	13 Jun 2006 10:10	Modified:	4 Aug 2006 9:01
Reporter:	David Abbott	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.0.22	OS:	Linux (Red Hat Linux ES4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If a script generated by mysqldump is used to drop and re-create 16 tables of type ndbcluster in a database and run twice (into one or more databases) then a subsequent query joining two of the tables run on a separate sql node shortly after can cause ndbd to crash. Ultimately the entire cluster needs re-starting.

This has been replicated across 2 different configurations a) 4 ndbd, 8 mysql nodes b) 2 ndbd, 2 sql nodes.

I will attach log files, my.cnf and config.ini for the 2-node config I used.

How to repeat:
Not very easily unfortunately. I have tried using a subset of tables, and also to update some of the data to clear down customer information in order to send a repro but then the repro never works, making me wonder if it is data/index related.

The crash does happen across versions 4.1.20 and 5.0.22 and several earlier versions of 4 and 5.

There are 2 tables and one query that seem to have most success in causing the ndbd crash -

CREATE TABLE `Company` (
  `CompanyID` int(4) NOT NULL auto_increment,
  `Year` int(2) NOT NULL default '0',
  `SalesSectorID` int(2) NOT NULL default '0',
  `CompanyName` varchar(255) NOT NULL default '',
  `AM` int(2) NOT NULL default '0',
  `TSC` int(2) NOT NULL default '0',
  `TAM` int(2) NOT NULL default '0',
  `Grade` char(1) NOT NULL default '',
  `Trial` int(1) NOT NULL default '0',
  `Enabled` int(1) NOT NULL default '0',
  PRIMARY KEY  (`CompanyID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

CREATE TABLE `Passport` (
  `PassportID` int(4) NOT NULL auto_increment,
  `PassportSecID` varchar(255) NOT NULL default '',
  `CompanyID` int(4) NOT NULL default '0',
  `FName` varchar(255) NOT NULL default '',
  `LName` varchar(255) NOT NULL default '',
  `JobRole` varchar(255) NOT NULL default '',
  `Department` varchar(255) NOT NULL default '',
  `Email` varchar(255) character set latin1 collate latin1_bin NOT NULL default
'',
  `ContactNo` varchar(255) NOT NULL default '',
  `Password` varchar(255) NOT NULL default '',
  `AdminUser` int(1) NOT NULL default '0',
  `Generic` int(1) NOT NULL default '0',
  `Enabled` int(1) NOT NULL default '0',
  PRIMARY KEY  (`PassportID`),
  UNIQUE KEY `Email` USING HASH (`Email`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

SELECT PassportID, PassportSecID, P.CompanyID, C.CompanyName, C.AM, C.TSC, C.TAM
, C.Trial, FName, LName, JobRole, Department, ContactNo, Password, AdminUser, Ge
neric, P.Enabled, C.Enabled AS CompanyEnabled FROM Passport P, Company C WHERE E
mail = 'david_sitecon@hotmail.com' AND P.CompanyID = C.CompanyID ;

ndbd log and trace files

Attachment: ndblogs.tgz (application/x-compressed, text), 46.85 KiB.

my.cnf for an sql node

Attachment: my.cnf (application/octet-stream, text), 1.98 KiB.

cluster config.ini for basic dev cluster used to repro ndbd crash

Attachment: config.ini (application/octet-stream, text), 787 bytes.

Changed category to a more appropriate one.

Hi,

This is an error when running out of resources for unique index access.

The work-around is to increase TransactionBufferMemory in your config.ini
  (default is 1M)

Please verify that increasing this helps.

/Jonas

This should be fixed by fix for http://bugs.mysql.com/bug.php?id=21384