Bug #21059 Server crashes on join query with large dataset with NDB tables
Submitted: 14 Jul 2006 13:10 Modified: 17 Aug 2006 8:51
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:5.1.11, 5.0.19 OS:Linux (Linux)
Assigned to: Martin Skold
Tags: cluster

[14 Jul 2006 13:10] Giuseppe Maxia
Description:
In a NDB cluster, issuing a query involving two large tables and aggregate functions, the mysqld server crashes.
The tables are the following:

 CREATE TABLE `players` (
  `player_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `player_name` varchar(40) NOT NULL DEFAULT '',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`player_id`),
  KEY `player_name` (`player_name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

CREATE TABLE `results` (
  `player_id` int(10) unsigned NOT NULL DEFAULT '0',
  `game_id` int(10) unsigned NOT NULL DEFAULT '0',
  `color` enum('w','b') NOT NULL DEFAULT 'w',
  `result` decimal(3,1) NOT NULL DEFAULT '0.5',
  `elo` int(11) NOT NULL DEFAULT '0',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`player_id`,`game_id`),
  KEY `game_id` (`game_id`),
  KEY `result` (`result`),
  KEY `color` (`color`),
  KEY `elo` (`elo`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

show table status like "players"\G
*************************** 1. row ***************************
           Name: players
         Engine: NDBCLUSTER
        Version: 10
     Row_format: Dynamic
           Rows: 147664
 Avg_row_length: 16
    Data_length: 4849664
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 147666
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: number_of_replicas: 2

show table status like "results"\G
*************************** 1. row ***************************
           Name: results
         Engine: NDBCLUSTER
        Version: 10
     Row_format: Fixed
           Rows: 3400000
 Avg_row_length: 40
    Data_length: 137494528
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: number_of_replicas: 2

The query that causes the crash is this:

select 
    player_name, count(*) as how_many 
from 
    players 
    inner join results using (player_id) 
group by 
    player_name 
    having how_many > 50;

As you see, table "results" has 3,400,000 rows. I could not reproduce this bug with a lower number of records.

The stack trace resolved from the error log is:

0x81d02a8 handle_segfault + 356
0xd2e888 (?)
0x8311a09 hp_rec_key_cmp + 261
0x8419c12 _ZN3Ndb12getOperationEv + 38
0x842183f _ZN14NdbTransaction15getNdbOperationEPK12NdbTableImplP12NdbOperation + 71
0x8421902 _ZN14NdbTransaction15getNdbOperationEPKN13NdbDictionary5TableE + 38
0x8297f09 _ZN13ha_ndbcluster7pk_readEPKcjPcj + 93
0x829a249 _ZN13ha_ndbcluster23read_range_first_to_bufEPK12st_key_rangeS2_bbPc + 365
0x8299f93 _ZN13ha_ndbcluster10index_readEPcPKcj16ha_rkey_function + 75
0x822c076 _Z13join_read_keyP13st_join_table + 242
0x821e20b _Z10sub_selectP4JOINP13st_join_tableb + 259
0x8224425 _Z20evaluate_join_recordP4JOINP13st_join_tableiPc + 329
0x821e1ba _Z10sub_selectP4JOINP13st_join_tableb + 178
0x82240d8 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 268
0x821962d _ZN4JOIN4execEv + 1349
0x821ace1 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 305
0x8216c13 _Z13handle_selectP3THDP6st_lexP13select_resultm + 267
0x81e8443 _Z21mysql_execute_commandP3THD + 899
0x81ef817 _Z11mysql_parseP3THDPcj + 359
0x81e6ccf _Z16dispatch_command19enum_server_commandP3THDPcj + 1007
0x81e68a5 _Z10do_commandP3THD + 129
0x81e5d9d handle_one_connection + 621
0xd28371 (?)
0xbb29be (?)

How to repeat:
Download the data I used for this test (URL in the private section).
Use a 2-node cluster with the following parameters:

[NDBD DEFAULT]
NoOfReplicas=2   
DataMemory=1000M 
IndexMemory=1024M
MaxNoOfUniqueHashIndexes=100
MaxNoOfConcurrentOperations=250000

Create the offending tables, load the data, and issue the above mentioned query.
Notice that if you add a WHERE clause limiting the rows to 3,000,000, the bug does not strike.

select 
    player_name, count(*) as how_many 
from 
    players 
    inner join results using (player_id) 
WHERE 
    game_id < 1500000 # there are two rows for each game_id 
group by 
    player_name 
    having how_many > 50;
(it takes about 4 minutes to run)
[26 Jul 2006 8:30] Hartmut Holzgraefe
The query did not crash for me but after running it on the full table 
without the WHERE clause limitation mysqld had grown to a virtual 
size of 8GB, so consuming almost all available physical memory
on my 64bit test system:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
12501 hholzgra  16   0 8084m 7.5g 6292 S  0.0 98.9   8:32.82 mysqld

So i'm pretty sure you experienced a crash caused by an out-of-memory
situation.
[26 Jul 2006 8:46] Giuseppe Maxia
As I said in my original report, the query without the WHERE clause is "safe" (for the cluster), while the query without the WHERE clause is not.
Anyway, the problem could be an out-of-memory case, because my server has only 4 GB of RAM. The system should not crash, though. The same query in InnoDB or MyISAM works just fine, even with the full database, not only the reduced one, and much faster.

Giuseppe
[26 Jul 2006 9:44] Hartmut Holzgraefe
Even in its simplest form

  select player_name 
    from players 
   inner join results 
   using (player_id) 

this query leaks memory at about the same speed.

When converting the tables from NDB to MyISAM all is well.

EXPLAIN for NDB tables:

explain select      player_name  from      players      inner join results on players.player_id = results.player_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: results
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3400000
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.results.player_id
         rows: 1
        Extra:
2 rows in set (0.01 sec)

EXPLAIN for MyISAM tables:

explain select      player_name  from      players      inner join results on players.player_id = results.player_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: results
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 3400000
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.results.player_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)
[26 Jul 2006 11:13] Hartmut Holzgraefe
raised to P1 (Critical) due to crashing behavior and probably customers being affected
[7 Aug 2006 11:51] 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/10113

ChangeSet@1.2498, 2006-08-07 13:51:20+02:00, mskold@mysql.com +3 -0
  Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: Releasing operation for each intermediate batch, before next call to trans->execute(NoCommit);
[7 Aug 2006 12:49] 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/10118

ChangeSet@1.2200, 2006-08-07 14:48:54+02:00, mskold@mysql.com +2 -0
  Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: Releasing operation for each intermediate batch, before next call to trans->execute(NoCommit);
[7 Aug 2006 13: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/10119

ChangeSet@1.2201, 2006-08-07 15:58:43+02:00, mskold@mysql.com +1 -0
  Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: missing friend declaration in change set
[9 Aug 2006 12:33] 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/10198

ChangeSet@1.2203, 2006-08-09 14:32:56+02:00, mskold@mysql.com +2 -0
  Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: added more tests
[14 Aug 2006 7:43] 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/10339

ChangeSet@1.2204, 2006-08-14 09:42:51+02:00, mskold@mysql.com +2 -0
  Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: Post review fix, not releasing operation records if BLOB operations
[15 Aug 2006 7:38] 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/10424

ChangeSet@1.2537, 2006-08-15 09:38:21+02:00, mskold@mysql.com +5 -0
  ndb_lock.test, ndb_lock.result:
    bug #18184  SELECT ... FOR UPDATE does not work..: New test case
  ha_ndbcluster.h, ha_ndbcluster.cc, NdbConnection.hpp:
    Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: Releasing operation for each intermediate batch, before next call to trans->execute(NoCommit);
[15 Aug 2006 11:12] 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/10455

ChangeSet@1.2537, 2006-08-15 13:12:27+02:00, mskold@mysql.com +5 -0
  ndb_lock.test, ndb_lock.result:
    bug #18184  SELECT ... FOR UPDATE does not work..: New test case
  ha_ndbcluster.h, ha_ndbcluster.cc, NdbConnection.hpp:
    Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: Releasing operation for each intermediate batch, before next call to trans->execute(NoCommit);
[15 Aug 2006 12:31] 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/10468

ChangeSet@1.2254, 2006-08-15 14:31:21+02:00, mskold@mysql.com +2 -0
  Fix for bug #21059  Server crashes on join query with large dataset with NDB tables: do not release operation records for on-going read_multi_range
[16 Aug 2006 7:39] Martin Skold
Pushed to 4.1.22, 5.0.25, 5.1.12
[17 Aug 2006 8:51] 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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.22/5.0.25/5.1.12 changelogs.