MySQL Bugs: #18856: Table remains locked after SELECT * INTO OUTFILE statment.

Bug #18856	Table remains locked after SELECT * INTO OUTFILE statment.
Submitted:	6 Apr 2006 17:05	Modified:	21 Aug 2007 19:50
Reporter:	Nikolay Grishakin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.1	OS:	Linux (Linux)
Assigned to:	Jonathan Miller	CPU Architecture:	Any

Description:
Table remains locked after dumping records to ASCI file using 
SELECT * INTO OUTFILE statment. To reproduce use the following test case:

 CREATE LOGFILE GROUP lg
 ADD UNDOFILE './undofile.dat'
 INITIAL_SIZE 16M
 UNDO_BUFFER_SIZE = 1M
 ENGINE=NDB;

 CREATE TABLESPACE ts
 ADD DATAFILE './datafile.dat'
 USE LOGFILE GROUP lg
 INITIAL_SIZE 12M
 ENGINE NDB;

CREATE DATABASE test;

CREATE TABLE test.t (
  a smallint NOT NULL,
  b int NOT NULL,
  c bigint NOT NULL,
  d char(10),
  e TEXT,
  f VARCHAR(255),
  PRIMARY KEY(a)
) TABLESPACE ts STORAGE DISK ENGINE=NDB;

 ALTER TABLE test.t ADD INDEX (d), ADD INDEX (f);
 SHOW CREATE TABLE test.t;

# insert records into tables

 let $1=100;
 disable_query_log;
 while ($1)
 {
  eval insert into test.t values($1, $1+1, $1+2, "aaa$1", "bbb$1", "ccccc$1");
  dec $1;
 }
 enable_query_log;

SELECT * INTO OUTFILE 't_backup' FROM test.t;
TRUNCATE test.t;

#'TRUNCATE test.t' failed: 1205: Lock wait timeout exceeded; try restarting #transaction

How to repeat:
see above

1) Will you get the same behaviour with DD?
2) Are you running with/without autocommit?
3) Do you get same behaviour wo/ blobs (text) ?

Jonas, here what I found:

1) Will you get the same behaviour with DD?

* If I change in CREATE TABLE statment "TABLESPACE ts STORAGE DISK  ENGINE=NDB;" to just "ENGINE=NDB;" I'm getting the same error

* If I don't specify engine type at all it works fine. In that case mySQL uses the default engine. Right?  

2) Are you running with/without autocommit?
I did not specify it so I was running whichever is default setting.

3) Do you get same behaviour wo/ blobs (text)

If I remove TEXT and BLOB fields from the table it works fine no error.

I guess the problem with NDB engine and BLOB fields.

Thanks,
Nikolay.

I updated Synopsis based on your replies. I.e it has nothing in particular to do with DD.

Should not be assigned to Nikolay Grishakin. In addition since ndb_dd_advance.test where this test case resides is being changed for alters only, this test case will be moved to ndb_dd_disk2memory.test.

I will try to reproduce this and narrow down the scope a bit.

Unable to reproduce.

+ CREATE LOGFILE GROUP lg
+ ADD UNDOFILE './undofile.dat'
+  INITIAL_SIZE 16M
+ UNDO_BUFFER_SIZE = 1M
+ ENGINE=NDB;
+ CREATE TABLESPACE ts
+ ADD DATAFILE './datafile.dat'
+  USE LOGFILE GROUP lg
+ INITIAL_SIZE 12M
+ ENGINE NDB;
+ CREATE TABLE test.t (
+ a smallint NOT NULL,
+ b int NOT NULL,
+ c bigint NOT NULL,
+ d char(10),
+ e TEXT,
+ f VARCHAR(255),
+ PRIMARY KEY(a)
+ ) TABLESPACE ts STORAGE DISK ENGINE=NDB;
+ ALTER TABLE test.t ADD INDEX (d), ADD INDEX (f);
+ SHOW CREATE TABLE test.t;
+ Table Create Table
+ t     CREATE TABLE `t` (
+   `a` smallint(6) NOT NULL,
+   `b` int(11) NOT NULL,
+   `c` bigint(20) NOT NULL,
+   `d` char(10) DEFAULT NULL,
+   `e` text,
+   `f` varchar(255) DEFAULT NULL,
+   PRIMARY KEY (`a`),
+   KEY `d` (`d`),
+   KEY `f` (`f`)
+ ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+ SELECT * INTO OUTFILE 't_backup' FROM test.t;
+ TRUNCATE test.t;

Workrd fine for me

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/30079

ChangeSet@1.2548, 2007-07-02 15:29:27+02:00, jmiller@mysql.com +2 -0
  Clean up of test case for Bug#18856

pushed into:

bk-internal.mysql.com:/home/bk/mysql-5.1-target-5.1.22
http://lists.mysql.com/commits/32835