Bug #58750 'out of connection objects' due to missing close() of prev. full_table_scan()
Submitted: 6 Dec 2010 9:59 Modified: 25 Jan 2011 10:42
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-5.1-telco-7.1 OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Tags: 5.1.51-ndb-7.1.9

[6 Dec 2010 9:59] Ole John Aske
Description:
When an 'unique index ... USING HASH' is used for a REF access, and the created REF key contains NULL values the handler has to do a ::full_table_scan() instead
of the REF access shown as part of explain output.

As a close_scan() to close any existing open scans was missing from this part of the code, we may run out of connection or operations objects before the operation is able to complete.

NOTE: This bug is similar, but not a duplicate, of bug#57481

How to repeat:
create table t1 (pk int primary key, a int) engine=ndb;
create table t2 (pk int primary key, uq int, a int, 
   unique key ix(uq,a) using hash) engine=ndb;

insert into t2 values
   (0,0,0), (1,1,0), (2,2,0), (3,3,0), (4,4,0),
   (5,5,1), (6,6,1), (7,7,1), (8,8,1), (9,9,1);

##
# 10^4 cross product on t2 creates 10.000 rows:
##
insert into t1
 select
   t1.pk + t2.pk*10 + t3.pk*100 + t4.pk*1000, t1.a
from
  t2 as t1, t2 as t2, t2 as t3, t2 as t4;

# Execute a 'scan(t1) join REF(t2) using ix'
#  - Where condition 't2.uq IS NULL' in combination with 
#    join cond. 't2.a=t1.a' will create the REF keys (NULL,t1.a).
#    This will initiate a ::full_table_scan() which fails to
#    close any open table scans.

SELECT STRAIGHT_JOIN count(*) FROM 
   t1 JOIN t2 ON t2.a=t1.a where t2.uq IS NULL;

Suggested fix:
Add a close_scan() to ha_ndbcluster::read_range_first_to_buf()
[6 Dec 2010 11:36] 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/126120

4015 Ole John Aske	2010-12-06
      Fix for bug#58750, for review.
      
      If ha_ndbcluster::read_range_first_to_buf() had to do a ::full_table_scan() 
      on a UNIQUE_INDEX, the resulting ScanOperation was not properly closed at end
      of its lifetime. If lots of such operations is executed within the 
      same transaction, we will eventually run out of operation or transaction objects.
      (Transaction objects is due to a dumnmy Hupp'ed transaction object is allocated as
      part of a ScanOperation)
      
      This fix ensures that ::close_scan() is called for any open cursors at start of
      ha_ndbcluster::read_range_first_to_buf().
[6 Dec 2010 11: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/126122

3388 Ole John Aske	2010-12-06
      SPJ-scan-scan: Cherry picked fix for bug#58750.
      
       If ha_ndbcluster::read_range_first_to_buf() had to do a ::full_table_scan()
       on a UNIQUE_INDEX, the resulting ScanOperation was not properly closed at end
       of its lifetime. If lots of such operations is executed within the
       same transaction, we will eventually run out of operation or transaction objects.
       (Transaction objects is due to a dumnmy Hupp'ed transaction object is allocated as
       part of a ScanOperation)
      
       This fix ensures that ::close_scan() is called for any open cursors at start of
       ha_ndbcluster::read_range_first_to_buf().
[6 Dec 2010 11:39] Ole John Aske
Reset status to 'Open' as bug still has to be verified
[17 Jan 2011 9:57] Ole John Aske
Set Status to 'Patch pending' as there is a patch awaiting review for this bug.
[17 Jan 2011 12:33] Ole John Aske
Patch pushed to mysql-5.1-telco-7.0 ++
[25 Jan 2011 8:30] Jonas Oreland
pushed to 7.0.21 and 7.1.10
[25 Jan 2011 10:42] Jon Stephens
Documented as follows in the NDB 7-0-21 and 7.1.10 changelogs:

        When executing a full table scan caused by a
        WHERE condition using unique_key IS NULL in combination with 
        a join, NDB failed to close the scan.

Closed.