#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:jan.wedvik@stripped
3313 Ole John Aske 2010-10-15
Recommit after fixing diverged-branches:
spj-svs: MRR read may fail to close cursors for pending ScanOperations
causing 'out of transaction / operation / lock' errors depending on the database config.
This fix is cherry picked from proposed fix for bug 57481, with the addition of an
additional SPJ test for the same scenario.
modified:
mysql-test/suite/ndb/r/ndb_join_pushdown.result
mysql-test/suite/ndb/r/ndb_read_multi_range.result
mysql-test/suite/ndb/t/ndb_join_pushdown.test
mysql-test/suite/ndb/t/ndb_read_multi_range.test
sql/ha_ndbcluster.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-10-11 12:11:05 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-10-15 13:49:39 +0000
@@ -4014,6 +4014,35 @@ id select_type table type possible_keys
select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
pk a b pk a b
drop table t1;
+create table t (pk int primary key, a int) engine=ndb;
+insert into t values
+(1,1), (2,1),
+(4,3), (6,3),
+(7,4), (8,4);
+explain
+SELECT DISTINCT STRAIGHT_JOIN table1.pk FROM
+t AS table1 JOIN
+(t AS table2 JOIN
+(t AS table3 JOIN t AS table4 ON table3.pk = table4.a)
+ON table2.pk = table3.pk )
+ON table1.a = table4.pk
+WHERE table2.pk != 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE table1 ALL NULL NULL NULL NULL 6 Parent of 2 pushed join@1; Using temporary
+1 SIMPLE table2 range PRIMARY PRIMARY 4 NULL 2 Parent of 2 pushed join@2; Using where with pushed condition; Distinct
+1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 test.table2.pk 1 Child of pushed join@2; Distinct
+1 SIMPLE table4 eq_ref PRIMARY PRIMARY 4 test.table1.a 1 Child of pushed join@1; Using where; Distinct
+SELECT DISTINCT STRAIGHT_JOIN table1.pk FROM
+t AS table1 JOIN
+(t AS table2 JOIN
+(t AS table3 JOIN t AS table4 ON table3.pk = table4.a)
+ON table2.pk = table3.pk )
+ON table1.a = table4.pk
+WHERE table2.pk != 6;
+pk
+1
+2
+drop table t;
create temporary table spj_counts_at_end
select counter_name, sum(val) AS val
from ndbinfo.counters
@@ -4029,32 +4058,32 @@ and spj_counts_at_end.counter_name <> 'L
and spj_counts_at_end.counter_name <> 'SCAN_BATCHES_RETURNED';
counter_name spj_counts_at_end.val - spj_counts_at_startup.val
CONST_PRUNED_RANGE_SCANS_RECEIVED 6
-LOCAL_TABLE_SCANS_SENT 194
+LOCAL_TABLE_SCANS_SENT 196
PRUNED_RANGE_SCANS_RECEIVED 16
-RANGE_SCANS_RECEIVED 195
-READS_NOT_FOUND 403
+RANGE_SCANS_RECEIVED 203
+READS_NOT_FOUND 405
READS_RECEIVED 61
-SCAN_ROWS_RETURNED 63591
-TABLE_SCANS_RECEIVED 194
+SCAN_ROWS_RETURNED 63641
+TABLE_SCANS_RECEIVED 196
select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT'
from spj_counts_at_end, spj_counts_at_startup
where spj_counts_at_end.counter_name = spj_counts_at_startup.counter_name
and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT'
or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT');
LOCAL+REMOTE READS_SENT
-29120
+29146
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
scan_count
-1990
+2000
pruned_scan_count
7
sorted_scan_count
44
pushed_queries_defined
-335
+339
pushed_queries_dropped
11
pushed_queries_executed
-259
+264
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'mysql-test/suite/ndb/r/ndb_read_multi_range.result'
--- a/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2009-02-03 13:35:56 +0000
+++ b/mysql-test/suite/ndb/r/ndb_read_multi_range.result 2010-10-15 13:49:39 +0000
@@ -588,3 +588,24 @@ i i 9
m m 13
v v 22
drop table t1, t2;
+create table t1 (pk int primary key, a int) engine=ndb;
+create table t2 (pk int primary key, a int) engine=ndb;
+insert into t2 values
+(0,0), (1,1), (2,2), (3,3), (4,4),
+(5,5), (6,6), (7,7), (8,8), (9,9);
+insert into t1
+select
+t1.a + t2.a*10 + t3.a*100 + t4.a*1000,
+(t1.a + t2.a*10 + t3.a*100 + t4.a*1000) / 1000
+from
+t2 as t1, t2 as t2, t2 as t3, t2 as t4
+where (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) < 4000;
+explain
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
+t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4000 Using temporary
+1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Distinct
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
+t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+drop table t1, t2;
=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-10-11 12:11:05 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-10-15 13:49:39 +0000
@@ -2838,6 +2838,39 @@ select * from t1 as x1, t1 as x2 where x
drop table t1;
+########
+# Test correct cleanup of MRR accesses being executed multiple times.
+# This used to be bug#57481, and this is a SPJ specific testcase in addition to
+# the specific testcase commited together with patch for this bug.
+#######
+create table t (pk int primary key, a int) engine=ndb;
+insert into t values
+ (1,1), (2,1),
+ (4,3), (6,3),
+ (7,4), (8,4);
+
+explain
+SELECT DISTINCT STRAIGHT_JOIN table1.pk FROM
+ t AS table1 JOIN
+ (t AS table2 JOIN
+ (t AS table3 JOIN t AS table4 ON table3.pk = table4.a)
+ ON table2.pk = table3.pk )
+ ON table1.a = table4.pk
+ WHERE table2.pk != 6;
+
+--sorted_result
+SELECT DISTINCT STRAIGHT_JOIN table1.pk FROM
+ t AS table1 JOIN
+ (t AS table2 JOIN
+ (t AS table3 JOIN t AS table4 ON table3.pk = table4.a)
+ ON table2.pk = table3.pk )
+ ON table1.a = table4.pk
+ WHERE table2.pk != 6;
+
+drop table t;
+
+
+
########################################
# Verify DBSPJ counters for entire test:
=== modified file 'mysql-test/suite/ndb/t/ndb_read_multi_range.test'
--- a/mysql-test/suite/ndb/t/ndb_read_multi_range.test 2010-09-22 11:36:01 +0000
+++ b/mysql-test/suite/ndb/t/ndb_read_multi_range.test 2010-10-15 13:49:39 +0000
@@ -428,3 +428,53 @@ select * from t1
or (a = 'v')
order by a asc, b asc;
drop table t1, t2;
+
+########################
+# Check propper reinit of a mrr executed multiple time as part of a join.
+# The mrr access is driven by a scan and executed as for every tuple
+# in the scaned table.
+#
+# In certain queries the optimizer don't read the entire mrr result set
+# before it fetch the next tuple from the scanned table.
+# The next mrr operation would then still have an open scan which wasn't
+# cleaned up as expected. This may cause all available NdbOperation,
+# NdbTransaction or lock objects to be consumed before the operation
+# could finish.
+#####################
+
+create table t1 (pk int primary key, a int) engine=ndb;
+create table t2 (pk int primary key, a int) engine=ndb;
+
+insert into t2 values
+ (0,0), (1,1), (2,2), (3,3), (4,4),
+ (5,5), (6,6), (7,7), (8,8), (9,9);
+
+##
+# 10^4 cross product on t2 creates 10.000 rows:
+# Insert volume has been tunes to insert only 3.000
+# of these as this is sufficient to produce an 'out of connection objects'
+##
+insert into t1
+ select
+ t1.a + t2.a*10 + t3.a*100 + t4.a*1000,
+ (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) / 1000
+from
+ t2 as t1, t2 as t2, t2 as t3, t2 as t4
+where (t1.a + t2.a*10 + t3.a*100 + t4.a*1000) < 4000;
+
+
+# Execute a 'scan(t1) join mrr(t2)'
+# - 'DISTINCT t1.pk' will cause optimizer to stop fetching mrr(t2)
+# when the first matching 't2.a = t1.a' is found.
+# - 'LEFT JOIN' is to ensure that 'Using join buffer' is *not* used
+#
+explain
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
+ t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+
+--disable_result_log
+SELECT DISTINCT STRAIGHT_JOIN t1.pk FROM
+ t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.pk != 6;
+--enable_result_log
+
+drop table t1, t2;
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2010-10-11 13:18:51 +0000
+++ b/sql/ha_ndbcluster.cc 2010-10-15 13:49:39 +0000
@@ -13707,6 +13707,7 @@ ha_ndbcluster::read_multi_range_first(KE
ulong reclength= table_share->reclength;
Thd_ndb *thd_ndb= m_thd_ndb;
NdbTransaction *trans= m_thd_ndb->trans;
+ int error;
DBUG_ENTER("ha_ndbcluster::read_multi_range_first");
DBUG_PRINT("info", ("blob fields=%d read_set=0x%x", table_share->blob_fields, table->read_set->bitmap[0]));
@@ -13731,6 +13732,15 @@ ha_ndbcluster::read_multi_range_first(KE
sorted,
buffer));
}
+
+ /**
+ * There may still be an open m_multi_cursor from the previous
+ * mrr access on this handler.
+ * Close it now to free up resources for this NdbScanOperation.
+ */
+ if (unlikely(error= close_scan()))
+ DBUG_RETURN(error);
+
thd_ndb->query_state|= NDB_QUERY_MULTI_READ_RANGE;
m_disable_multi_read= FALSE;
@@ -13766,10 +13776,9 @@ ha_ndbcluster::read_multi_range_first(KE
*/
DBUG_ASSERT(cur_index_type != UNDEFINED_INDEX);
+ DBUG_ASSERT(m_multi_cursor == NULL);
+ DBUG_ASSERT(m_active_query == NULL);
- DBUG_ASSERT(m_active_query == NULL);;
- m_active_query= 0;
- m_multi_cursor= 0;
const NdbOperation* lastOp= trans ? trans->getLastDefinedOperation() : 0;
NdbOperation::LockMode lm=
(NdbOperation::LockMode)get_ndb_lock_type(m_lock.type, table->read_set);
@@ -13777,7 +13786,6 @@ ha_ndbcluster::read_multi_range_first(KE
const uchar *end_of_buffer= buffer->buffer_end;
uint num_scan_ranges= 0;
uint i;
- int error;
bool any_real_read= FALSE;
if (m_read_before_write_removal_possible)
Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20101015134939-gyyr3z1p31p8m25j.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3313) | Ole John Aske | 15 Oct |
