Bug #19201 TRUNCATE table with blobs fails with 1205
Submitted: 19 Apr 2006 17:08 Modified: 13 May 2006 14:46
Reporter: Nikolay Grishakin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:4.1 -> OS:Linux (Linux)
Assigned to: Pekka Nousiainen CPU Architecture:Any

[19 Apr 2006 17:08] Nikolay Grishakin
Description:
TRUNCATE table fails with 1205: "Lock wait timeout exceeded" error. This problem happens with DD tables too. The cause of the error is BLOB or TEXT field in the table. Removing BLOB field from the table fixes the problem.
Here is the test case to reproduce this problem.

 CREATE LOGFILE GROUP lg
 ADD UNDOFILE './lg_group/undofile.dat'
 INITIAL_SIZE 16M
 UNDO_BUFFER_SIZE = 1M
 ENGINE=NDB;
     
 CREATE TABLESPACE ts1
 ADD DATAFILE './table_space1/datafile.dat'
 USE LOGFILE GROUP lg
 INITIAL_SIZE 12M
 ENGINE NDB;
	  
 CREATE TABLE test.t1 (a1 int NOT NULL PRIMARY KEY,a2 BLOB) TABLESPACE ts1 STORAGE DISK  ENGINE=NDB;
 let $1=10;
 disable_query_log;
  while ($1)
 {
  eval insert into test.t1 values($1,"aaaaaaaaaaaaaaaa$1");
 dec $1;
 }
 enable_query_log;

SELECT * from test.t1;
		   
 TRUNCATE test.t1;
# query 'TRUNCATE test.t1' failed: 1205: Lock wait timeout exceeded; try #restarting transaction
		   
 SELECT * from test.t1;
 DROP TABLE test.t1;
		     
 ALTER TABLESPACE ts1
 DROP DATAFILE './table_space1/datafile.dat'
 ENGINE=NDB;
 DROP TABLESPACE ts1 ENGINE=NDB;
				 
 DROP LOGFILE GROUP lg
 ENGINE=NDB;
 

How to repeat:
see above
[21 Apr 2006 8:42] Jonas Oreland
Pekka, this needs same fix that you made for ndb_delete_all with blobs.
[26 Apr 2006 13:34] Pekka Nousiainen
i don't get the error, just endless number of blob operations.
[26 Apr 2006 20:02] Bugs System
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/5603
[2 May 2006 12:42] Bugs System
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/5799
[13 May 2006 14:46] Jon Stephens
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

Additional info:

Documented bugfix in 4.1.20/5.0.22/5.1.11 changelogs. Closed.