MySQL Bugs: #34107: Large query ends in wrong results or even segmentation faults

Bug #34107	Large query ends in wrong results or even segmentation faults
Submitted:	28 Jan 2008 15:20	Modified:	2 Mar 2008 9:15
Reporter:	Michael Neubert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	5.1.22	OS:	Linux (Debian)
Assigned to:	Pekka Nousiainen	CPU Architecture:	Any
Tags:	large query, NDB Cluster, segmentation fault

Description:
Hello,

by using a really large Query on a NDB table, the result set is wrong (empty). I tried the testcase on different architectures (i386 and amd64) and hardware systems. Sometimes I had even segmentation faults, so that some NDB nodes crashed (amd64).

By using a MyISAM table, the large query was executed as expected and the result set was o.k. So it seems to be a NDB problem only.

Best wishes
Michael

How to repeat:
Testcase:

-> setup a standard cluster with 2 NDB Nodes (NoOfReplicas = 2), by using packages from http://dev.mysql.com/downloads/mysql/5.1.html (MySQL 5.1.22) or from source development tree

CREATE DATABASE test_database;

USE test_database;

CREATE TABLE `test_table_ndb` (
  `id` int unsigned NOT NULL auto_increment,
  PRIMARY KEY (`id`)
) ENGINE NDB;

CREATE TABLE `test_table_myisam` (
  `id` int unsigned NOT NULL auto_increment,
  PRIMARY KEY (`id`)
) ENGINE MyISAM;

INSERT INTO test_table_ndb (id) VALUES (1);
INSERT INTO test_table_myisam (id) VALUES (1);

Start the attached PHP Script or copy the included large SQL statements into the mysql client. You will get a not expected empty result set or even a segmentation fault when executing the query on the ndb table.

The ouput of the script should look like this:

"
NDB table - mysql_num_rows (expected 1): 0
NDB table - result set (expected 1):
MyISAM table - mysql_num_rows (expected 1): 1
MyISAM table - result set (expected 1): 1,

done
"

Suggested fix:
At the moment I try to avoid such large queries.

Interestingly the segmentation fault on amd64 architectures can be avoided by setting "engine_condition_pushdown = 0". But this is no solution to the problem at all. But it shows, that the problem is related to ndb storage engine.

large query testcase

Attachment: large_query.rar (application/octet-stream, text), 16.75 KiB.

-verified in latest 5.1 linux 32-bit (query=>no result)
-not seen in 5.0
-filter in the example is 28217 words (below max 50000)
 so filter is sent to kernel

Correction, also happens in 5.0 if table has more rows.
Optimizer difference.

The bug is in some buffer sizes and missing error message.
In any case there will be a limit and if mysqld encounters
it, the pushdown is skipped silently. See bug#35527

that should be bug#29390

Hello,

yes, the problem seems to be the same as mentionned in Bug#29390.
According to the bugreport the problem should be fixed in 5.1.23-rc.

I just testet 5.1.24 from source development tree with the same negative result. The bug seems still to exist (wrong result set and no warning).

Best wishes
Michael

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

ChangeSet@1.2590, 2008-01-31 13:56:12+01:00, pekka@sama.ndb.mysql.com +4 -0
  ndb - bug#34107 patch 1, kernel

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

ChangeSet@1.2591, 2008-01-31 13:58:29+01:00, pekka@sama.ndb.mysql.com +3 -0
  ndb - bug#34107 patch 2, ndb api

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

ChangeSet@1.2593, 2008-01-31 23:15:07+01:00, pekka@sama.ndb.mysql.com +2 -0
  ndb - bug#34107 - ndb api test case

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

ChangeSet@1.2671, 2008-02-01 00:07:15+01:00, pekka@sama.ndb.mysql.com +2 -0
  ndb - bug#34107 - post-merge

Documented bugfix in 5.1.23-ndb-6.3.9 as follows:

        Extremely long queries (where the text of the query was in
        excess of 50000 characters) on NDB tables returned empty
        results.

Left PQ status pending further merges.

Also documented for 5.1.23-ndb-6.2.12.

Pushed into 5.0.58

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Also documented for 5.0.58, 5.1.24, 6.0.5. Closed.