Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Duplicate
-
Affects Version/s: 5.5.20
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
SQL to reproduce:
drop table if exists test.t1;
create table test.t1 (c1 int primary key, c2 char(5));
set optimizer_switch="derived_with_keys=on";
SELECT COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, COLUMNS.COLUMN_NAME, UNIQUES.INDEX_NAME, UNIQUES.COLUMN_NAMES, UNIQUES.COUNT_COLUMN_IN_INDEX, COLUMNS.DATA_TYPE, COLUMNS.CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COUNT( * ) AS COUNT_COLUMN_IN_INDEX, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES, SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE NON_UNIQUE=0 GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS UNIQUES ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA AND COLUMNS.TABLE_NAME = UNIQUES.TABLE_NAME AND COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME ) WHERE COLUMNS.TABLE_SCHEMA = 'test' AND COLUMNS.TABLE_NAME = 't1' ORDER BY COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, CASE UNIQUES.INDEX_NAME WHEN 'PRIMARY' THEN 0 ELSE 1 END, CASE IFNULL(CHARACTER_SET_NAME, '') WHEN '' THEN 0 ELSE 1 END, CASE DATA_TYPE WHEN 'tinyint' THEN 0 WHEN 'smallint' THEN 1 WHEN 'int' THEN 2 WHEN 'bigint' THEN 3 ELSE 100 END, COUNT_COLUMN_IN_INDEX;
If we set optimizer_switch="derived_with_keys=off" then we get no crash.
Reported by a user via IRC. The query on I_S is run by the openark online alter table tool.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Reported by Dev0n on #maria.
Reduced test case:
drop table if exists test.t1;
create table test.t1 (c1 int primary key, c2 char(5));
set optimizer_switch="derived_with_keys=on";
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN ( SELECT TABLE_SCHEMA,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES
FROM INFORMATION_SCHEMA.STATISTICS
GROUP BY TABLE_SCHEMA) AS UNIQUES
ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
drop table t1;
Stack trace:
#0 0x00007f1e65fdd4ec in __pthread_kill (threadid=<optimized out>, signo=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:63
#1 0x0000000000cd6c06 in my_write_core (sig=11) at /home/knielsen/my/5.5/mariadb-5.5/mysys/stacktrace.c:457
#2 0x00000000007d5301 in handle_fatal_signal (sig=11) at /home/knielsen/my/5.5/mariadb-5.5/sql/signal_handler.cc:274
#3 <signal handler called>
#4 0x0000000000b41c6d in ha_maria::scan_time (this=0x2906b18) at /home/knielsen/my/5.5/mariadb-5.5/storage/maria/ha_maria.cc:1010
#5 0x00000000008d34b5 in SQL_SELECT::test_quick_select (this=0x2908aa0, thd=0x2779240, keys_to_use=..., prev_tables=4611686018427387905, limit=18446744073709551615, force_quick_range=false,
ordered_output=false) at /home/knielsen/my/5.5/mariadb-5.5/sql/opt_range.cc:2890
#6 0x000000000066d223 in make_join_select (join=0x28f6838, select=0x2908748, cond=0x2908930) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:8429
#7 0x0000000000659dfe in JOIN::optimize (this=0x28f6838) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:1337
#8 0x000000000065f83d in mysql_select (thd=0x2779240, rref_pointer_array=0x277beb8, tables=0x2887c00, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2684619520, result=0x28a2718, unit=0x277b5b0, select_lex=0x277bc68) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:2951
#9 0x0000000000656a5c in handle_select (thd=0x2779240, lex=0x277b500, result=0x28a2718, setup_tables_done_option=0) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:309
#10 0x0000000000630710 in execute_sqlcom_select (thd=0x2779240, all_tables=0x2887c00) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:4616
#11 0x00000000006299d0 in mysql_execute_command (thd=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:2185
#12 0x0000000000632e9f in mysql_parse (thd=0x2779240,
rawbuf=0x2887888 "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES FROM INFORMATION_SCHEMA.STATISTICS GROUP BY TABLE_SCHEMA)"..., length=261, parser_state=0x7f1e5a57d630) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:5730
#13 0x00000000006270aa in dispatch_command (command=COM_QUERY, thd=0x2779240,
packet=0x2871011 "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES FROM INFORMATION_SCHEMA.STATISTICS GROUP BY TABLE_SCHEMA)"..., packet_length=261) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:1056
#14 0x000000000062639a in do_command (thd=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:795
#15 0x0000000000721422 in do_handle_one_connection (thd_arg=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_connect.cc:1253
#16 0x0000000000720e0d in handle_one_connection (arg=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_connect.cc:1168
#17 0x0000000000c812c3 in pfs_spawn_thread (arg=0x28f1170) at /home/knielsen/my/5.5/mariadb-5.5/storage/perfschema/pfs.cc:1015
#18 0x00007f1e65fd7b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#19 0x00007f1e64d2c90d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
#20 0x0000000000000000 in ?? ()
I did a rough trace of how the crash is reached by examing the mysqld.1.trace
file from mysql-test-run --debug on the reduced test case:
mysql_select
JOIN::prepare
st_select_lex::handle_derived
TABLE_LIST::handle_derived
mysql_handle_single_derived
- Note, it doesn't call mysql_derived_init() here
mysql_derived_prepare
create_result_table
create_tmp_table
get_new_handler - This is where we obtain the handler object for the
- tmp table. However, it is never opened.
setup_tables_and_check_access
JOIN::optimize
JOIN::optimize
JOIN::transform_max_min_subquery
convert_join_subqueries_to_semijoins
simplify_joins
build_bitmap_for_nested_joins
optimize_cond
get_sort_by_table
make_join_statistics
get_best_combination
make_select
reset_nj_counters
make_outerjoin_info
make_join_select
add_not_null_conds
SQL_SELECT::test_quick_select
ha_maria::scan_time()
-> CRASH due to not opened handler
So inside mysql_select(), we first call JOIN::prepare, which ends up in
mysql_handle_single_derived(). It calls mysql_derived_prepare() which
allocates the new handler object for the Aria tmp table. Note that this
handler object is never open()'ed.
After, mysql_select() calls JOIN::optimize(). This goes through a number of
calls until it does make_join_select(). And inside make_join_select() we end
up in ha_maria::scan_time() on a not opened handler object, which crashes with
NULL pointer access.
I will have to leave to the optimiser people to investigate further why the
handler is accessed without being opened first and what the fix may be.
Moved to Launchpad: https://bugs.launchpad.net/maria/+bug/944782
this affects MariaDB-5.5.20-alpha