Hi, I have the same issue - in my case this was affecting MediaWiki installation (I have done mysqldump, removed the datafiles, reinstalled MariaDB server and imported the mysqldump, and this didn't help; MariaDB 10.1.14 is working properly).
If there is a possibility that this will affect other applications as well may be it is worth considering to withdraw release 10.1.15? At least I received the update Friday evening, so I assume many enterprises will do the update during the business hours next week. 
DavisNT, yes, we have had exactly the same thought. 10.1.15 is now withdrawn and we've shifted the release schedule to have 10.1.16 out sooner.
Sorry for the trouble.
MediaWiki-based test case:
CREATE TABLE `job` ( |
`job_id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`job_cmd` varbinary(60) NOT NULL DEFAULT '', |
`job_namespace` int(11) NOT NULL, |
`job_title` varbinary(255) NOT NULL, |
`job_params` blob NOT NULL, |
`job_timestamp` varbinary(14) DEFAULT NULL, |
`job_random` int(10) unsigned NOT NULL DEFAULT '0', |
`job_token` varbinary(32) NOT NULL DEFAULT '', |
`job_token_timestamp` varbinary(14) DEFAULT NULL, |
`job_sha1` varbinary(32) NOT NULL DEFAULT '', |
`job_attempts` int(10) unsigned NOT NULL DEFAULT '0', |
PRIMARY KEY (`job_id`), |
KEY `job_cmd` (`job_cmd`,`job_namespace`,`job_title`,`job_params`(128)), |
KEY `job_timestamp` (`job_timestamp`), |
KEY `job_sha1` (`job_sha1`), |
KEY `job_cmd_token` (`job_cmd`,`job_token`,`job_random`), |
KEY `job_cmd_token_id` (`job_cmd`,`job_token`,`job_id`) |
);
|
|
|
INSERT INTO `job` VALUES |
(NULL, 'foo', 1, 'foo', 'foo', 'foo', 1, 'foo', 'foo', 'foo', 1), |
(NULL, 'bar', 2, 'bar', 'bar', 'bar', 2, 'bar', 'bar', 'bar', 2); |
|
|
|
SELECT DISTINCT job_cmd FROM `job` WHERE job_cmd IN ('foobar','null'); |
|
Investigation:
The following happens:
- range optimizer is invoked for the first time (from get_quick_record_count)
with keys_to_use=3.
- range optimizer is invoked for the second time here:
#0 SQL_SELECT::test_quick_select (this=0x7fff9c009718, thd=0x555557a60070, keys_to_use=..., prev_tables=4611686018427387904, limit=1, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=false) at /home/psergey/dev-git/10.1-dbg6/sql/opt_range.cc:2434
|
#1 0x0000555555aa6fbd in make_join_select (join=0x7fff9c0073b8, select=0x7fff9c009540, cond=0x7fff9c006880) at /home/psergey/dev-git/10.1-dbg6/sql/sql_select.cc:9917
|
#2 0x0000555555a8fe98 in JOIN::optimize_inner (this=0x7fff9c0073b8) at /home/psergey/dev-git/10.1-dbg6/sql/sql_select.cc:1572
|
#3 0x0000555555a8e068 in JOIN::optimize (this=0x7fff9c0073b8) at /home/psergey/dev-git/10.1-dbg6/sql/sql_select.cc:1036
|
This is the "We plan to scan all rows. Check again if we should use an index."
call.
This time, keys_to_use.map=1
However, group-min-max optimizer ignores that and tries to consider a plan with
key_no=1. This causes assertion failure, because Mem_root_array checks against
attempts to access data beyond the end of the array.
The example doesn't crash MySQL 5.7 for multiple reasons:
- 5.7 doesn't invoke the range optimizer for the second time. They have the
call there, but the logic to invoke it is changed (it's complex), and the
condition for invoking the range optimizer is not met
- 5.7 also has tab->keys()=3, tab->keys=3. So, even if I use gdb to force the
optimizer to be invoked, it will operate on the full set of keys, not on the
subset.
- but forcing tab->keys=
{1}
doesn't cause them to crash, either...
Found it:
there is this change:
- for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
|
- cur_index_info++, cur_index++)
|
+ // We go through allowed indexes
|
+ for (uint cur_param_idx= 0; cur_param_idx < param->keys ; ++cur_param_idx)
|
made by this patch: https://github.com/mysql/mysql-server/commit/96fcfcbd7b5120e8f64fd45985001eca8d36fbfb
This is what I've missed when backporting the patch mentioned in the bug report.
The mentioned patch against 5.7 causes a different query plan to be chosen for this query:
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY, ab) GROUP BY a;
|
THe change is:
-1 SIMPLE t1 NULL range PRIMARY,ab ab 4 NULL 13 100.00 Using index for group-by
|
+1 SIMPLE t1 NULL range PRIMARY,ab ab 4 NULL 13 100.00 Using index for group-by; Using temporary; Using filesort
|
One can argue that the previous plan was incorrect: if the query says "IGNORE INDEX FOR GROUP BY", why does the plan have "Using index for group-by" ?
However, the new query plan doesn't make any sense. It has:
Using index for group-by; Using temporary; Using filesort
|
If we do "Using index for group-by", it does grouping, and provides result in GROUP BY order.
What is the purpose of doing "Using temporary" or "Using filesort", then?
But this is what current mysql-5.7 has. I'm looking at tip cset:
commit e0e0ae2ea27c9bb76577664845507ef224d362e4
|
Author: Lars Tangvald <lars.tangvald@oracle.com>
|
Date: Tue May 24 09:22:25 2016 +0200
|
Running ANALYZE and debugging shows that the execution follows the EXPLAIN. It uses both QUICK_GROUP_MIN_MAX_SELECT, then writes rows into a temp. table and then invokes filesort to read the result.
We arrive at the wrong query plan through the following steps:
- range optimizer is invoked by get_quick_record_count(). It produces a QUICK_GROUP_MIN_MAX_SELECT. Note that this ignores the "IGNORE INDEX FOR GROUP BY" hint
- We reach test_if_skip_sort_order. It invokes test_quick_select (... keys_to_use=
{1}
...), which fails to produce a quick select that produces the required ordering. test_quick_select executes "goto use_filesort" and returns false/
- The code in sql_select.cc sees that, and sets:
need_tmp=1; simple_order=simple_group=0; // Force tmp table without sort - This way, we get both a GROUP_MIN_MAX_SELECT and a temp. table with filesort.
A way to fix the above without rewriting a lot of code: make get_best_group_min_max() take into account IGNORE INDEX FOR GROUP BY, and not produce "Using index for group-by" query plans when they are not allowed.