| Bug #17366 | crash on left join on union with literal, 5.0.18 | ||
|---|---|---|---|
| Submitted: | 14 Feb 2006 9:59 | Modified: | 14 Mar 2006 18:12 |
| Reporter: | Stijn Hoop | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.19-BK, 5.0.18 | OS: | Linux (Linux & FreeBSD) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[14 Feb 2006 10:05]
Stijn Hoop
Forgot to mention: MySQL 4.1 ran the original query where I discovered this bug just fine; I can't test this crasher anymore (no server on 4.1 anymore) but I think this is a regression.
[14 Feb 2006 12:37]
Stijn Hoop
A debug version of mysqld reported a backtrace in the error log. A resolve_stack_dump run did not produce very obvious results: thd=0xa4c1d18 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... Cannot determine thread, fp=0xaad1ec4c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x817b011 handle_segfault + 639 0xc0c420 (?) 0x819782e _Z16dispatch_command19enum_server_commandP3THDPcj + 1492 0x819782e _Z16dispatch_command19enum_server_commandP3THDPcj + 1492 0x8198c84 _Z10do_commandP3THD + 256 0x819970c handle_one_connection + 2224 0x766b80 (?) 0x6be9ce (?) New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xa5c98c8 = select uni.t as uni_type, uni.id as uni_id, crash1.alt as crash1_alt , crash2.alt as crash2_alt from (select 'crash1' as t, crash1.id as id from crash1 union select 'crash2' as t, crash2.id as id from crash2 ) as uni left join crash1 on uni.t = 'crash1' and uni.id = crash1.id left join crash2 on uni.t = 'crash2' and uni.id = crash2.id where uni.t = 'crash1' thd->thread_id=1
[19 Feb 2006 14:39]
Valeriy Kravchuk
Verified just as described with 5.0.19-BK (ChangeSet@1.2064, 2006-02-18 21:08:41+01:00) on Linux:
mysql> create table crash1 (id int primary key, alt int);
creaQuery OK, 0 rows affected (0.02 sec)
mysql> create table crash2 (id int primary key, alt int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into crash1 values (1, 1), (2, 2), (3, 3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into crash2 values (1, 4), (2, 5), (3, 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select
-> uni.t as uni_type,
-> uni.id as uni_id,
-> crash1.alt as crash1_alt
-> -- , crash2.alt as crash2_alt
-> from
-> (select 'crash1' as t,
-> crash1.id as id
-> from crash1
-> union
-> select 'crash2' as t,
-> crash2.id as id
-> from crash2
-> ) as uni
-> left join crash1 on
-> uni.t = 'crash1'
-> and uni.id = crash1.id
-> -- left join crash2 on
-> -- uni.t = 'crash2'
-> -- and uni.id = crash2.id
-> where
-> uni.t = 'crash1';
+----------+--------+------------+
| uni_type | uni_id | crash1_alt |
+----------+--------+------------+
| crash1 | 1 | 1 |
| crash1 | 2 | 2 |
| crash1 | 3 | 3 |
+----------+--------+------------+
3 rows in set (0.01 sec)
mysql> select
-> uni.t as uni_type,
-> uni.id as uni_id,
-> crash1.alt as crash1_alt
-> , crash2.alt as crash2_alt
-> from
-> (select 'crash1' as t,
-> crash1.id as id
-> from crash1
-> union
-> select 'crash2' as t,
-> crash2.id as id
-> from crash2
-> ) as uni
-> left join crash1 on
-> uni.t = 'crash1'
-> and uni.id = crash1.id
-> left join crash2 on
-> uni.t = 'crash2'
-> and uni.id = crash2.id
-> where
-> uni.t = 'crash1';
+----------+--------+------------+------------+
| uni_type | uni_id | crash1_alt | crash2_alt |
+----------+--------+------------+------------+
| crash1 | 1 | 1 | NULL |
| crash1 | 2 | 2 | NULL |
| crash1 | 3 | 3 | NULL |
+----------+--------+------------+------------+
3 rows in set (0.01 sec)
mysql>
Number of processes running now: 0
060212 00:05:08 mysqld restarted
mysql> exit
Bye
openxs@suse:~/dbs/5.0> tail -50 var/suse.err
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...
Cannot determine thread, fp=0x428d4a1c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817ba06
0xffffe420
0x16
0x819d860
0x819ea73
0x4004eaa7
0x40249c2e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8abfa90 = select
uni.t as uni_type,
uni.id as uni_id,
crash1.alt as crash1_alt
, crash2.alt as crash2_alt
from
(select 'crash1' as t,
crash1.id as id
from crash1
union
select 'crash2' as t,
crash2.id as id
from crash2
) as uni
left join crash1 on
uni.t = 'crash1'
and uni.id = crash1.id
left join crash2 on
uni.t = 'crash2'
and uni.id = crash2.id
where
uni.t = 'crash1'
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
060212 00:05:08 mysqld restarted
060212 0:05:08 InnoDB: Started; log sequence number 0 734296831
060212 0:05:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=suse-relay-bin' to avoid this problem.
060212 0:05:09 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.19' socket: '/tmp/mysql.sock' port: 3306 Source distribution
It is a serious bug, indeed.
[1 Mar 2006 13:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3321
[14 Mar 2006 9:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3811
[14 Mar 2006 15:12]
Evgeny Potemkin
When there is conjunction of conds, the substitute_for_best_equal_field() will call the eliminate_item_equal() function in loop to build final expression. But if eliminate_item_equal() finds that some cond will always evaluate to 0, then that cond will be substituted by Item_int with value == 0. In this case on the next iteration eliminate_item_equal() will get that Item_int and treat it as Item_cond. This is leads to memory corruption and server crash on cleanup phase. Fixed in 5.0.20, cset 1.2053.44.1
[14 Mar 2006 18:12]
Paul DuBois
Noted in 5.0.20 changelog. A <literal>LEFT JOIN</literal> with a <literal>UNION</literal> that selects literal values could crash the server. (Bug #17366)
Description: Running the following query, with everything uncommented, crashes MySQL 5.0.18 on FreeBSD and MySQL 5.0.15 on Linux. The crash is apparently *after* the query has completed as the mysql client reports a reconnect on the next statement sent. Running it as is does not cause a crash, hence the comments. %%% drop table if exists crash1, crash2; create table crash1 (id int primary key, alt int); create table crash2 (id int primary key, alt int); insert into crash1 values (1, 1), (2, 2), (3, 3); insert into crash2 values (1, 4), (2, 5), (3, 6); -- Uncomment the crash2 stuff in the query below to make MySQL crash select uni.t as uni_type, uni.id as uni_id, crash1.alt as crash1_alt -- , crash2.alt as crash2_alt from (select 'crash1' as t, crash1.id as id from crash1 union select 'crash2' as t, crash2.id as id from crash2 ) as uni left join crash1 on uni.t = 'crash1' and uni.id = crash1.id -- left join crash2 on -- uni.t = 'crash2' -- and uni.id = crash2.id where uni.t = 'crash1' %%% How to repeat: See above.