Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.33a, 5.5.37, 10.0.11
-
None
-
None
-
Ubuntu Precise Pangolin 12.04 LTS 64 bit on hardware (IBM Bladecenter with 48Gb RAM) or virtual machine (VmWare Fusion on MacOS X with 4Gb RAM).
Description
SIGSEGV in parsing a query that joins the same VIEW at least 8 times and the VIEW joins a number of tables.
I have reduced our much more complicated query and tables to the following script that reliably kills MariaDb 5.5.33a-MariaDB-1~precise-log on Ubuntu 12.04 Precise Pangolin 64 bit:
CREATE DATABASE IF NOT EXISTS test_14390; |
USE test_14390; |
|
|
DROP TABLE IF EXISTS a; |
DROP TABLE IF EXISTS b; |
DROP TABLE IF EXISTS c; |
DROP TABLE IF EXISTS d; |
DROP TABLE IF EXISTS e; |
DROP TABLE IF EXISTS f; |
|
|
CREATE TABLE a (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); |
CREATE TABLE b (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); |
CREATE TABLE c (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); |
CREATE TABLE d (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); |
CREATE TABLE e (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); |
CREATE TABLE f (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); |
|
CREATE OR REPLACE view v1 AS |
SELECT 1 |
FROM a a_alias_1 |
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 |
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 |
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 |
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 |
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 |
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 |
;
|
|
|
SELECT 1 |
FROM (v1 t1) |
LEFT OUTER JOIN (v1 t2) ON 1=1 |
LEFT OUTER JOIN (v1 t3) ON 1=1 |
LEFT OUTER JOIN (v1 t4) ON 1=1 |
LEFT OUTER JOIN (v1 t5) ON 1=1 |
LEFT OUTER JOIN (v1 t6) ON 1=1 |
LEFT OUTER JOIN (v1 t7) ON 1=1 |
LEFT OUTER JOIN (v1 t8) ON 1=1 |
;
|
See attached MariaDb_5_5_33a_log.txt for the resulting error log.
Similar failures have been seen on MariaDb 5.5.37 and 10.0.11 - see other attached log files. Note that these two have debug symbols which helps to show that the bug seems to be to do with materializing views in the query optimizer.
We wondered whether the "Optimizer switch" values might affect the behaviour, but we tried turning them all on and off with no effect on the crash.
The crashes have been observed on large BladeCenter machines with 48Gb of RAM, and reproduced on a VmWare Fusion virtual machine on Mac OS X with 4Gb of RAM. I doubt the hardware or architecture has anything to do with the problem.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Elena Stepanova [ elenst ] |
| Fix Version/s | 10.0.12 [ 15201 ] | |
| Fix Version/s | 5.5.38 [ 15400 ] | |
| Fix Version/s | 5.3.13 [ 12602 ] | |
| Affects Version/s | 5.3.12 [ 12000 ] | |
| Assignee | Elena Stepanova [ elenst ] | Oleksandr Byelkin [ sanja ] |
| Summary | SIGSEGV in query optimizer | SIGSEGV in query optimizer (in set_check_materialized with MERGE view) |
Hi Elena,
Thanks for the quick response. I have found documentation at http://dev.mysql.com/doc/refman/5.5/en/limits.html - but I don't fully understand it. It says "The maximum number of tables that can be referenced in a single join is 61" but the view only has 7 tables (but 8 references) and the main query has 8 joins. I guess it means that the total number of table references in the query and any merged views cannot exceed 61 (in our query it is 64).
However, we are currently trying to workaround the issue by expanding the views inline into the query, which seems to work, e.g.:
SELECT 1
|
FROM (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t1)
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t2) ON 1=1
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t3) ON 1=1
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t4) ON 1=1
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t5) ON 1=1
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t6) ON 1=1
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t7) ON 1=1
|
LEFT OUTER JOIN (( SELECT 1
|
FROM a a_alias_1
|
LEFT JOIN (b b_alias_1 JOIN a a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
|
LEFT JOIN c c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
|
LEFT JOIN d d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
|
LEFT JOIN c c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
|
LEFT JOIN e e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
|
LEFT JOIN f f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
|
) t8) ON 1=1
|
;
|
Why does it not also break the limit and crash the server?
Also, if we do this, is it a safe workaround or is there another limit we are about to hit if we add more subqueries? I have grown this up to 40 subqueries with no crash yet...
Thanks,
Jonathan.
I expect sanja will be able to give an expert answer to both questions; it's a bit hard to compare execution plans because EXPLAIN for the first (crashing) query also crashes, so it requires a deeper insight. I suppose that unlike for the 1st query, for the 2nd one the optimizer chooses not to merge all tables from subqueries into a single query, which is why it never reaches the same 64 tables/references.
As you say, the optimizer must be treating subqueries differently, and so I am concerned that inlining the views as subqueries may be much less efficient, e.g. will not take advantage of condition push-down.
I look forward to hearing from sanja very soon with advice on the relative benefits and disadvantages. We need to fix this issue urgently to avoid major outages, so a workaround that we can rely on would be very helpful.
Derived tables (AKA SELECT in the FROM clause) in MySQL materialized, so it is just many indepemdent SELECTS. IMHO workacound is to force materialization of view.
For sanja – EXPLAIN for the 2nd query (with subqueries):
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------------------+
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | <derived9> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (incremental, BNL join) |
|
| 9 | DERIVED | a_alias_1 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index |
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------------------------------------+
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select 1 AS `1` from `test_14390`.`a` `a_alias_1` left join (`test_14390`.`a` `a_alias_1`) on((1 = 1)) left join (`test_14390`.`a` `a_alias_1`) on((1 = 1)) left join (`test_14390`.`a` `a_alias_1`) on((1 = 1)) left join (`test_14390`.`a` `a_alias_1`) on((1 = 1)) left join (`test_14390`.`a` `a_alias_1`) on((1 = 1)) left join (`test_14390`.`a` `a_alias_1`) on((1 = 1)) left join ((select 1 AS `1` from `test_14390`.`a` `a_alias_1` where 1)) `t8` on((1 = 1)) where 1 |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Yes, as I suspected, the last table was not merged because there was not enough bits in the table map (the limitation), so I really remember correctly that there is such code.
And there is not such autodetection for view (jet?).
Ok, so inlining the VIEWs as subqueries is safe, but is it less performant?
| Status | Open [ 1 ] | In Progress [ 3 ] |
The problem is that a VIEW processed as DERIVED when it try to force materialization because run out of bits in the table mask.
CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE OR REPLACE view v2 AS
SELECT 1
FROM t1 a_alias_1
LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1
LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1
LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1
LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1
LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1
LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1
;
SELECT 1
FROM (v1 t1)
LEFT OUTER JOIN (v1 t2) ON 1=1
LEFT OUTER JOIN (v1 t3) ON 1=1
LEFT OUTER JOIN (v1 t4) ON 1=1
LEFT OUTER JOIN (v1 t5) ON 1=1
LEFT OUTER JOIN (v1 t6) ON 1=1
LEFT OUTER JOIN (v1 t7) ON 1=1
LEFT OUTER JOIN (v1 t8) ON 1=1
;
drop view v1;
drop table t1,t2,t3,t4,t5,t6;
| Rank | Ranked higher |
fix sent to review
(fix done for 5.5 because 5.3 do not compile on ubuntu 14.04, then it will be ported)
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | defaullt [ 40801 ] | MariaDB v2 [ 43545 ] |
| Workflow | MariaDB v2 [ 43545 ] | MariaDB v3 [ 62673 ] |
| Workflow | MariaDB v3 [ 62673 ] | MariaDB v4 [ 147897 ] |
Hi Jonathan,
Thank you for the report and the test case.
Reproducible on MariaDB 5.3, 5.5, 10.0.
The problem happens if the view is a MERGE view.
Assertion and stack trace (just to make it more searchable in JIRA):
mysqld: 5.5/sql/table.cc:4978: void TABLE_LIST::set_check_materialized(): Assertion `derived' failed.140520 0:35:16 [ERROR] mysqld got signal 6 ;/lib/x86_64-linux-gnu/libc.so.6(__assert_fail+0xf1)[0x7f33393c1621]sql/table.cc:4979(TABLE_LIST::set_check_materialized())[0x6fceac]sql/table.h:2102(TABLE_LIST::set_materialized_derived())[0x5e2f4e]sql/sql_derived.cc:405(mysql_derived_merge(THD*, LEX*, TABLE_LIST*))[0x60bdd9]sql/sql_derived.cc:192(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x60bae1]sql/table.cc:6561(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x700895]sql/sql_lex.cc:3553(st_select_lex::handle_derived(LEX*, unsigned int))[0x6278f6]sql/sql_select.cc:998(JOIN::optimize())[0x660fea]sql/sql_select.cc:3075(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x6684e7]sql/sql_select.cc:319(handle_select(THD*, LEX*, select_result*, unsigned long))[0x65f079]sql/sql_parse.cc:4688(execute_sqlcom_select)[0x638693]sql/sql_parse.cc:2233(mysql_execute_command(THD*))[0x631875]sql/sql_parse.cc:5799(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x63ada2]sql/sql_parse.cc:1081(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x62eded]sql/sql_parse.cc:793(do_command(THD*))[0x62df79]sql/sql_connect.cc:1266(do_handle_one_connection(THD*))[0x72f872]sql/sql_connect.cc:1182(handle_one_connection)[0x72f331]perfschema/pfs.cc:1017(pfs_spawn_thread)[0xb636b1]/lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7f333a921b50](the stack trace is from 5.5 tree, revno 4190).
MariaDB 5.2 and MySQL return the error instead:
1116: Too many tables; MySQL can only use 61 tables in a join