[MDEV-8756] MariaDB 10.0.21 crashes during PREPARE Created: 2015-09-05  Updated: 2015-11-20  Resolved: 2015-10-28

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Data Manipulation - Update, Platform Debian, Prepared Statements
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 5.5.47, 10.0.22, 10.1.9

Type: Bug Priority: Major
Reporter: Tim Düsterhus Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 4
Labels: None
Environment:

MariaDB 10.0.21 Docker Container running on Ubuntu 15.04.

Attachments: Text File log.txt    
Sprint: 10.0.22

 Description   

MariaDB 10.0.21 crashes during preparation of an UPDATE statement with a SELECT subquery in combination with ONLY_FULL_GROUP_BY.

One can reproduce the issue using docker as follows:

First start the MariaDB database container:

docker run -it --rm --name crasher -e MYSQL_ROOT_PASSWORD=root mariadb:10.0.21

Afterwards connect with the MariaDB command line client:

docker run -ti --rm --link crasher:mariadb mariadb mysql --host=mariadb -proot

Inside the command line client perform the following querys:

-- create test database
 
CREATE DATABASE IF NOT EXISTS db; use db;
 
-- drop test tables
 
DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2;
 
-- create test tables
 
CREATE TABLE t1 ( id INT(10), value INT(10) ); CREATE TABLE t2 ( id INT(10) );
 
-- enable full group by
 
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
 
-- try to prepare query
 
PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)';

The last query will return:

ERROR 2013 (HY000): Lost connection to MySQL server during query

And the server crashes because of signal 11. The stack trace is a follows:

Thread pointer: 0x0x7fa1d3641008
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fa1f779ce70 thread_stack 0x48000
 
mysqld(my_print_stacktrace+0x3d)[0x7fa1f7195a2d]
 
mysqld(handle_fatal_signal+0x31a)[0x7fa1f6cd375a]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0xf8d0)[0x7fa1f633d8d0]
 
mysqld(_ZN10Item_field15fix_outer_fieldEP3THDPP5FieldPP4Item+0x14c)[0x7fa1f6cf8a1c]
 
mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x4f2)[0x7fa1f6cf9742]
 
mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x1b3)[0x7fa1f6d2f3a3]
 
mysqld(_Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item+0x1c3)[0x7fa1f6b09573]
 
mysqld(+0x42f111)[0x7fa1f6b9d111]
 
mysqld(_ZN30subselect_single_select_engine7prepareEv+0x688)[0x7fa1f6d62788]
 
mysqld(_ZN14Item_subselect10fix_fieldsEP3THDPP4Item+0xed)[0x7fa1f6d60aed]
 
mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x184)[0x7fa1f6b07594]
 
mysqld(+0x3f7f7a)[0x7fa1f6b65f7a]
 
mysqld(_ZN18Prepared_statement7prepareEPKcj+0x6dd)[0x7fa1f6b6771d]
 
mysqld(_Z22mysql_sql_stmt_prepareP3THD+0x39f)[0x7fa1f6b67caf]
 
mysqld(_Z21mysql_execute_commandP3THD+0x90e)[0x7fa1f6b4edfe]
 
mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e2)[0x7fa1f6b551d2]
 
mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1787)[0x7fa1f6b56f87]
 
mysqld(_Z24do_handle_one_connectionP3THD+0x28b)[0x7fa1f6c2da5b]
 
mysqld(handle_one_connection+0x40)[0x7fa1f6c2dac0]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7fa1f63360a4]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fa1f493e04d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fa1be042408): is an invalid pointer
 
Connection ID (thread ID): 2
 
Status: NOT_KILLED



 Comments   
Comment by Elena Stepanova [ 2015-09-06 ]

Thanks for the report and test case. 

DROP TABLE IF EXISTS t1, t2;
 
CREATE TABLE t1 ( id INT(10), value INT(10) ); 
CREATE TABLE t2 ( id INT(10) );
 
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
 
PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)';

Stack trace from 5.5 commit 102a85f9f30cdf8c3baa3893c68932617240bfa6

 
#3  <signal handler called>
 
#4  0x000000000059d3e4 in base_list::push_back (this=0x28, info=0x7f5d47d46e30) at 5.5/sql/sql_list.h:206
 
#5  0x00000000006a655b in List<Item_field>::push_back (this=0x28, a=0x7f5d47d46e30) at 5.5/sql/sql_list.h:512
 
#6  0x0000000000803e0a in Item_field::fix_outer_field (this=0x7f5d47d46e30, thd=0x7f5d48d50060, from_field=0x7f5d487b42f8, reference=0x7f5d47d46fd8) at 5.5/sql/item.cc:4891
 
#7  0x0000000000804d05 in Item_field::fix_fields (this=0x7f5d47d46e30, thd=0x7f5d48d50060, reference=0x7f5d47d46fd8) at 5.5/sql/item.cc:5213
 
#8  0x0000000000844090 in Item_func::fix_fields (this=0x7f5d47d46f38, thd=0x7f5d48d50060, ref=0x7f5d47e876c8) at 5.5/sql/item_func.cc:204
 
#9  0x00000000005e46c2 in setup_conds (thd=0x7f5d48d50060, tables=0x7f5d47d46758, leaves=..., conds=0x7f5d47e876c8) at 5.5/sql/sql_base.cc:8945
 
#10 0x00000000006a5944 in setup_without_group (thd=0x7f5d48d50060, ref_pointer_array=0x7f5d47d472e8, tables=0x7f5d47d46758, leaves=..., fields=..., all_fields=..., conds=0x7f5d47e876c8, order=0x0, group=0x0, hidden_group_fields=0x7f5d47e875b0) at 5.5/sql/sql_select.cc:577
 
#11 0x0000000000664625 in JOIN::prepare (this=0x7f5d47e872a0, rref_pointer_array=0x7f5d47d6ae38, tables_init=0x7f5d47d46758, wild_num=0, conds_init=0x7f5d47d46f38, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f5d47d6abc8, unit_arg=0x7f5d47d46078) at 5.5/sql/sql_select.cc:725
 
#12 0x000000000088582a in subselect_single_select_engine::prepare (this=0x7f5d47d471d0) at 5.5/sql/item_subselect.cc:3027
 
#13 0x000000000087e0a7 in Item_subselect::fix_fields (this=0x7f5d47d470b0, thd_param=0x7f5d48d50060, ref=0x7f5d47d47218) at 5.5/sql/item_subselect.cc:245
 
#14 0x00000000005e29df in setup_fields (thd=0x7f5d48d50060, ref_pointer_array=0x0, fields=..., mark_used_columns=MARK_COLUMNS_NONE, sum_func_list=0x0, allow_sum_func=false) at 5.5/sql/sql_base.cc:8219
 
#15 0x000000000065178a in mysql_test_update (stmt=0x7f5d47d36460, table_list=0x7f5d47d6a4d0) at 5.5/sql/sql_prepare.cc:1411
 
#16 0x0000000000652c75 in check_prepared_statement (stmt=0x7f5d47d36460) at 5.5/sql/sql_prepare.cc:2071
 
#17 0x0000000000655a7d in Prepared_statement::prepare (this=0x7f5d47d36460, packet=0x7f5d47e871d8 "UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)", packet_len=60) at 5.5/sql/sql_prepare.cc:3387
 
#18 0x00000000006536b3 in mysql_sql_stmt_prepare (thd=0x7f5d48d50060) at 5.5/sql/sql_prepare.cc:2457
 
#19 0x0000000000635cd0 in mysql_execute_command (thd=0x7f5d48d50060) at 5.5/sql/sql_parse.cc:2239
 
#20 0x000000000063f5c5 in mysql_parse (thd=0x7f5d48d50060, rawbuf=0x7f5d47e87078 "PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)'", length=80, parser_state=0x7f5d487b5620) at 5.5/sql/sql_parse.cc:5911
 
#21 0x00000000006331fd in dispatch_command (command=COM_QUERY, thd=0x7f5d48d50060, packet=0x7f5d48e07061 "PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)'", packet_length=80) at 5.5/sql/sql_parse.cc:1079
 
#22 0x0000000000632389 in do_command (thd=0x7f5d48d50060) at 5.5/sql/sql_parse.cc:793
 
#23 0x00000000007354ad in do_handle_one_connection (thd_arg=0x7f5d48d50060) at 5.5/sql/sql_connect.cc:1269
 
#24 0x0000000000735227 in handle_one_connection (arg=0x7f5d48d50060) at 5.5/sql/sql_connect.cc:1185
 
#25 0x0000000000b6fec5 in pfs_spawn_thread (arg=0x7f5d48d71c00) at 5.5/storage/perfschema/pfs.cc:1015
 
#26 0x00007f5d4f197b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#27 0x00007f5d4d44d95d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Comment by Tim Düsterhus [ 2015-09-22 ]

I just bisected MariaDB to find the commit that introduced the issue. These are my results:

2e941fe9fce7f1667993916ff3f238a283286d3f is the first bad commit
 
commit 2e941fe9fce7f1667993916ff3f238a283286d3f
 
Author: Monty <monty@mariadb.org>
 
Date:   Thu Jun 25 23:18:48 2015 +0300
 
 
 
    Fixed crashing bug when using ONLY_FULL_GROUP_BY in a stored procedure/trigger that is repeatedly executed.
 
    This is MDEV-7601, including it's sub tasks MDEV-7594, MDEV-7555, MDEV-7590, MDEV-7581, MDEV-7589
 
    
    The problem was that select_lex->non_agg_fields was not properly reset for re-execution and this caused an overwrite of a random memory position.
 
    The fix was move non_agg_fields from select_lext to JOIN, which is properly reset.
 
 
 
:040000 040000 85bfeaed2b4582ed814fd4cdacf5d6422671cfb2 537b2cb35eb0003c16da3cc1efce45c84825edc9 M	mysql-test
 
:040000 040000 40063c3973505e945f2f9d37bc02bff4c4a6b9fd ebc000e4cb0fc8da67e2ea712a9fb617ac98c4a0 M	sql

The full bisection log is as follows:

git bisect start
 
# bad: [0403790722e3941779ccea26e85fcd818e2320b5] increase the VERSION
 
git bisect bad 0403790722e3941779ccea26e85fcd818e2320b5
 
# good: [a6087e7dc1ef3561d8189c8db15e9591d0f9b520] MDEV-5309 - RENAME TABLE does not check for existence of the table's engine
 
git bisect good a6087e7dc1ef3561d8189c8db15e9591d0f9b520
 
# bad: [006ffca56e0638f14152f4ba97ecfc7bfe08d773] after-merge fixes
 
git bisect bad 006ffca56e0638f14152f4ba97ecfc7bfe08d773
 
# good: [00967e114cb4da4dca9bbc127e26facb08ede3ce] CONNECT: clean up a stray variable
 
git bisect good 00967e114cb4da4dca9bbc127e26facb08ede3ce
 
# bad: [fa765a45250176d1168ce5a61dee484c997604b6] MDEV-6697: Improve foreign keys warnings/errors
 
git bisect bad fa765a45250176d1168ce5a61dee484c997604b6
 
# bad: [a63d873861c2ed2e1155850ad0d4a48b7bf79a9c] Merge branch '5.5' of github.com:MariaDB/server into 5.5
 
git bisect bad a63d873861c2ed2e1155850ad0d4a48b7bf79a9c
 
# bad: [67c56ab1e4841058c40e6b61e1dcbb6e21d4ce52] Simple cleanups - Removing use of calls to current_thd - More DBUG_PRINT - Code style changes - Made some local functions static Ensure that calls to print_keyuse are locked with mutex to get all lines in same debug packet
 
git bisect bad 67c56ab1e4841058c40e6b61e1dcbb6e21d4ce52
 
# bad: [8c815751c92313dfa45ef0398b609c9988a0a451] Problem was that for cases like: SELECT ... WHERE XX IN (SELECT YY) this was transformed to something like: SELECT ... WHERE IF_EXISTS(SELECT ... HAVING XX=YY)
 
git bisect bad 8c815751c92313dfa45ef0398b609c9988a0a451
 
# bad: [2e941fe9fce7f1667993916ff3f238a283286d3f] Fixed crashing bug when using ONLY_FULL_GROUP_BY in a stored procedure/trigger that is repeatedly executed. This is MDEV-7601, including it's sub tasks MDEV-7594, MDEV-7555, MDEV-7590, MDEV-7581, MDEV-7589
 
git bisect bad 2e941fe9fce7f1667993916ff3f238a283286d3f
 
# first bad commit: [2e941fe9fce7f1667993916ff3f238a283286d3f] Fixed crashing bug when using ONLY_FULL_GROUP_BY in a stored procedure/trigger that is repeatedly executed. This is MDEV-7601, including it's sub tasks MDEV-7594, MDEV-7555, MDEV-7590, MDEV-7581, MDEV-7589

For extra confidence I also built the commit d199a0ffb0aac86881ea2db7dd78bc07b438dc67 which is the parent of the commit that git found to be guilty. It did not crash.

Comment by Sascha Greuel [ 2015-10-11 ]

Any ETA or any progress? Because more and more people are facing this problem and many of them are not able to perform a downgrade.

Comment by Oleksandr Byelkin [ 2015-10-22 ]

revision-id: 3c1512e2e130d1f07b64e4fd797e7e425f149443 (mariadb-10.0.21-44-g3c1512e)
parent(s): 18f7dfed179204dcfc02a27790e22bb9cc4e2e32
committer: Oleksandr Byelkin
timestamp: 2015-10-22 16:08:45 +0200
message:

MDEV-8756 MariaDB 10.0.21 crashes during PREPARE

Non-select-like queries has no correct JOIN structure connected to top-most SELECT_LEX (and should not).

—

Comment by Sergei Petrunia [ 2015-10-27 ]

Review comments provided over email

Comment by Sascha Greuel [ 2015-11-13 ]

This issue still exists in 10.1.

Comment by Elena Stepanova [ 2015-11-13 ]

SoftCreatR,

The fix has been pushed into 10.0 tree, it will be soon merged into 10.1 tree and released in 10.1.9.

Comment by Markus Lenz [ 2015-11-20 ]

Hello together,
will this issue also be solved within the 5.5 tree in any of the next releases?

Thanks and kind regards

Comment by Elena Stepanova [ 2015-11-20 ]

sanja, was there a reason why it was only fixed in 10.0, but not 5.5? Initially it was targeted for 5.5.

Comment by Oleksandr Byelkin [ 2015-11-20 ]

fixed in 5.5

Generated at Thu Feb 08 07:29:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.