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
Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[28 Jan 2008 15:20] Michael Neubert
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.
[28 Jan 2008 15:21] Michael Neubert
large query testcase

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

[29 Jan 2008 16:19] Pekka Nousiainen
-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
[29 Jan 2008 22:14] Pekka Nousiainen
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
[29 Jan 2008 22:17] Pekka Nousiainen
that should be bug#29390
[30 Jan 2008 16:11] Michael Neubert
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
[31 Jan 2008 12:56] 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/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
[31 Jan 2008 12:58] 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/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
[31 Jan 2008 22:15] 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/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
[31 Jan 2008 23:07] 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/41533

ChangeSet@1.2671, 2008-02-01 00:07:15+01:00, pekka@sama.ndb.mysql.com +2 -0
  ndb - bug#34107 - post-merge
[12 Feb 2008 14:36] Jon Stephens
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.
[12 Feb 2008 16:07] Jon Stephens
Also documented for 5.1.23-ndb-6.2.12.
[20 Feb 2008 16:02] Bugs System
Pushed into 5.0.58
[20 Feb 2008 16:03] Bugs System
Pushed into 6.0.5-alpha
[20 Feb 2008 16:03] Bugs System
Pushed into 5.1.24-rc
[2 Mar 2008 9:15] Jon Stephens
Also documented for 5.0.58, 5.1.24, 6.0.5. Closed.