Fix for lp:711565 "Index Condition Pushdown can make a thread hold MyISAM locks as well as be unKILLable for long time"

- In Maria/MyISAM: Release/re-acquire locks to give queries that wait on them a chance to make progress
- In Maria/MyISAM: Change from numeric constants to ICP_RES values.
- In Maria: Do check index condition in maria_rprev() (was lost in the merge/backport?)
- In Maria/MyISAM/XtraDB: Check if the query was killed, and return immediately if it was.

Added new storage engine error: HA_ERR_ABORTED_BY_USER, for handler to signal that it detected a kill of the query and aborted

Authors: Sergey Petrunia & Monty

include/my_base.h:
  Added HA_ERR_ABORTED_BY_USER, for handler to signal that it detected a kill of the query and aborted
include/my_handler.h:
  Added comment
mysql-test/r/myisam_icp.result:
  Updated test
mysql-test/t/myisam_icp.test:
  Drop used tables at start of test
  Added test case that can help with manual testing of killing index condition pushdown query.
mysys/my_handler_errors.h:
  Text for new storage engine error
sql/handler.cc:
  If engine got HA_ERR_ABORTED_BY_USER, send kill message.
sql/multi_range_read.cc:
  Return error code
storage/maria/ha_maria.cc:
  Added ma_killed_in_mariadb() to detect kill.
  Ensure that file->external_ref points to TABLE object.
storage/maria/ma_extra.c:
  Dummy test-if-killed for standalone
storage/maria/ma_key.c:
  If ma_check_index_cond() fails, set my_errno and info->cur_row.lastpos
storage/maria/ma_rkey.c:
  Release/re-acquire locks to give queries that wait on them a chance to make progress
  Check if the query was killed, and return immediately if it was
storage/maria/ma_rnext.c:
  Check if the query was killed, and return immediately if it was
  Added missing fast_ma_writeinfo(info)
storage/maria/ma_rnext_same.c:
  Check if the query was killed, and return immediately if it was
  Added missing fast_ma_writeinfo(info)
storage/maria/ma_rprev.c:
  Check if the query was killed, and return immediately if it was
  Added missing fast_ma_writeinfo(info) and ma_check_index_cond()
storage/maria/ma_search.c:
  Give error message if we find a wrong key
storage/maria/ma_static.c:
  Added pointer to test-if-killed function
storage/maria/maria_def.h:
  New prototypes
storage/myisam/ha_myisam.cc:
  Added mi_killed_in_mariadb()
  Ensure that file->external_ref points to TABLE object.
storage/myisam/mi_extra.c:
  Dummy test-if-killed for standalone
storage/myisam/mi_key.c:
  If ma_check_index_cond() fails, set my_errno and info->lastpos
storage/myisam/mi_rkey.c:
  Ensure that info->lastpos= HA_OFFSET_ERROR in case of error
  Release/re-acquire locks to give queries that wait on them a chance to make progress
  Check if the query was killed, and return immediately if it was
  Reorder code to do less things in case of error.
  Added missing fast_mi_writeinfo()
storage/myisam/mi_rnext.c:
  Check if the query was killed, and return immediately if it was
  Simplify old ICP code
  Added missing fast_ma_writeinfo(info)
storage/myisam/mi_rnext_same.c:
  Check if the query was killed, and return immediately if it was
  Added missing fast_mi_writeinfo(info)
storage/myisam/mi_rprev.c:
  Check if the query was killed, and return immediately if it was
  Simplify error handling of ICP
  Added missing fast_mi_writeinfo(info)
storage/myisam/mi_search.c:
  Give error message if we find a wrong key
storage/myisam/mi_static.c:
  Added pointer to test-if-killed function
storage/myisam/myisamdef.h:
  New prototypes
storage/xtradb/handler/ha_innodb.cc:
  Added DB_SEARCH_ABORTED_BY_USER and ha_innobase::is_thd_killed()
  Check if the query was killed, and return immediately if it was
storage/xtradb/handler/ha_innodb.h:
  Added prototype
storage/xtradb/include/db0err.h:
  Added DB_SEARCH_ABORTED_BY_USER
storage/xtradb/include/row0mysql.h:
  Added possible ICP errors
storage/xtradb/row/row0sel.c:
  Use ICP errors instead of constants.
  Detect if killed and return B_SEARCH_ABORTED_BY_USER

1 files changed, 188 insertions, 0 deletions

diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index 4c3db4d9d12..30a8b208230 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -4,3 +4,191 @@
 
 --source include/icp_tests.inc
 
+--disable_warnings
+drop table if exists t0, t1, t1i, t1m;
+--enable_warnings
+
+#
+# BUG#711565 Index Condition Pushdown can make a thread hold MyISAM locks as well as be unKILLable for long time
+#   This is not a ready mysqltest testcase but rather a set of queries that
+#   allow to check the bug[fix] manually. Making this testcase automatic is
+#   difficult because
+#    - big tables are involved
+#    - it's difficult to have automatic checks of whether the query could be
+#       KILLed that would reliably work on fast/slow buildslaves, etc.
+
+--disable_parsing
+ 
+  create table t0 (a int);
+  insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+  create table t1 (
+    kp1 int, kp2 int, 
+    filler char(100),
+    col int,
+    key(kp1, kp2)
+  );
+
+  set myisam_sort_buffer_size=32*1000*1000;
+  insert into t1 
+  select 
+    1000 + A.a + 10*B.a + 100*C.a + 1000*D.a + 10000 * F.a,
+    1,
+    'filler-data filler-data filler-data filler-data filler-data',
+    1
+  from 
+    t0 A, t0 B, t0 C, t0 D, t0 E, t0 F, t0 G
+  ;
+
+  insert into t1 values (990, 100, 'a record to test index_next checks',100); 
+
+  update t1 set kp2=10 where kp1 between 20000+100 and 28000;
+
+  update t1 set kp1=20000 where kp1 between 20000 and 28000;
+
+  insert into t1 values (20000, 100, 'last-20K-record',1);
+
+  create table t1i like t1;
+  alter table t1i engine=innodb;
+  insert into t1i select * from t1;
+
+  create table t1m like t1; 
+  alter table t1m engine=maria;
+  insert into t1m select * from t1;
+
+#
+# XtraDB has one check for all kinds of ranges.
+#
+  explain 
+  select * from t1i
+  where 
+    kp1 < 8000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 10;
+
+
+#
+# MyISAM, check range access + mi_rnext():
+#   (will return HA_ERR_END_OF_FILE)
+  explain 
+  select * from t1 
+  where 
+    kp1 < 10000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 10;
+
+
+#
+# MyISAM, check range access + mi_rkey():
+#   (will return HA_ERR_END_OF_FILE)
+  explain 
+  select * from t1 
+  where 
+    kp1 >= 999 and kp1 < 10000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 10;
+
+
+
+#
+# MyISAM, check mi_rnext_same:
+#
+
+  explain 
+  select * from t1 
+  where 
+    kp1 = 20000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 < 10;
+
+
+# 
+# MyISAM, check mi_rprev:
+#
+
+  explain 
+  select * from t1 
+  where 
+    kp1 = 20000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 20
+  order by kp1, kp2 desc;
+
+
+
+#
+# Maria, check range access + maria_rkey():
+#
+  explain 
+  select * from t1m 
+  where 
+    kp1 >= 999 and kp1 < 10000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 10;
+
+
+
+#
+# Maria, check range access + maria_rnext():
+#
+  explain 
+  select * from t1m
+  where 
+    kp1 < 10000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 10;
+
+
+#
+# Maria, check maria_rnext_same:
+#
+
+  explain 
+  select * from t1m
+  where 
+    kp1 = 20000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 100;
+
+# 
+# Maria, check maria_rprev:
+#
+
+  explain 
+  select * from t1m
+  where 
+    kp1 = 20000 and 
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    concat(repeat('foo-bar-', 100000), kp2) like '%bar-1%' and
+    kp2 +1 > 20
+  order by kp1, kp2 desc;
+
+drop table t0, t1, t1i, t1m;
+
+--enable_parsing