MySQL Bugs: #25383: Servers crashes on specific query

Bug #25383	Servers crashes on specific query
Submitted:	3 Jan 2007 11:55	Modified:	16 Feb 2007 21:34
Reporter:	Olivier Clavel	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.34-BK, 5.0.30	OS:	Linux (Linux, Debian Sarge i386)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
Same crash over and over again since I upgraded from 5.0.27. If the crash repeats too often, crash recovery fails leaving the server unreachable (killall + start repairs it though).

It took me a while to figure out (one of?) the exact query that cause the crash since the query is truncated in the crash dump in error log. After some test and try on the search page that causes it, I came out with query1. As I noticed in the explain an "Impossible where" comment on a subquery I tried to rewrite it as query2  but the result is exactly the same.

The 2 queries crash in the same maner on a slave server with same version of mysql and running debian sarge as well. The interesting thing to note is that, from the command line, the server crashes after giving the result in the terminal.

Both queries work on a second slave running mysql 5.0.27 on fedora core 5 x86_64

If I drop one the where clause (I tried dropping the exact name search, sex match and adhesion subquery), there is no crash.

Queries, queries exlain, error logs, stack trace resolution and table info in attachment.

How to repeat:
Send the queries, either on the command line or from a web page. All attachement follow

Bug info

Attachment: bugTrace.txt (text/plain), 28.26 KiB.

Thank you for a problem report. Please, send smallest set of data for that tables that allows you to repeat the crash. I tried both your queries on 5.0.34-BK with empty tables, and got no crashes.

I tried the query on empty tables as well and it does not crash the server either.

I took a copy of the database, removed uneeded tables for the test, set some fields to null for info that should not go out and arbitrarly removed records from the user table until the query would not crash anymore. 
I removed user with ids not between 1000 and 1078 from the user (utilisateur) table as well as from the corresponding linked tables (comedien, adhesion, comedien_has_language).

If I remove one more user (not between 1000 and 1077), it does not crash.

I attach below a full dump of the smallest data set that still crashes. I made the test on the slave server which currently has 2GB of RAM and a 2GB swap just in case you need that info.

Once again, please not that I see the server crash in the error log. There is no evidence of this crash in the command line client until you issue a new query. (see second attached file).

Smallest data chunk I could get that still crashes (full db dump)

Attachment: smallestDataChunk.sql (text/x-sql), 28.95 KiB.

Evidence of crash in mysql command line after query

Attachment: crashEvidenceAfterQuery.txt (text/plain), 1.82 KiB.

Verified just as described with your query 1 on data you uploaded. I used latest 5.0.34-BK-debug:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.03 sec)

mysql> SELECT
    ->     u.idutilisateur AS id,
    ->     IF( MAX( IF(a.dateDeDebut <= NOW() AND a.dateDeFin  >= NOW(), 1, 0))
 = 1, TRUE, FALSE) AS subscriber,
    ->     u.nom AS nom,
    ->     u.prenom AS prenom,
    ->     IF (u.sexe='M', 'Homme', 'Femme') AS sexe,
    ->     ch.libeleFR AS cheveux,
    ->     y.libeleFR AS yeux,
    ->     ta.libeleFR AS age,
    ->     p.nom AS pays,
    ->     GROUP_CONCAT( DISTINCT
    ->             l.nom_fr,
    ->             ': ',
    ->             ELT(cl.niveau, 'notion', 'courant', 'bilingue')
    ->             ORDER BY l.nom_fr
    ->             SEPARATOR ' - '
    ->     ) AS langues
    ->
    -> FROM
    ->     utilisateur u
    ->     JOIN comedien c ON u.idutilisateur=c.idutilisateur
    ->     LEFT JOIN adhesion a ON c.idutilisateur=a.idutilisateur
    ->     JOIN trancheAge ta ON c.idtrancheAge=ta.idtrancheAge
    ->     JOIN couleurCheveux ch ON ch.idcouleurCheveux=c.idcouleurCheveux
    ->     JOIN couleurYeux y ON y.idcouleurYeux = c.idcouleurYeux
    ->     JOIN pays p ON p.idpays = u.idpays
    ->     LEFT JOIN comedien_has_langue cl ON cl.idutilisateur=c.idutilisateur
    ->     LEFT JOIN langue l ON l.idlangue = cl.idlangue
W    -> WHERE a.dateDeDebut <= NOW()
    -> AND a.dateDeFin >= NOW()
    -> AND u.prenom='Olivier'
    -> AND u.sexe = 'M'
    -> AND 2 IN (SELECT l.idlangue FROM langue l JOIN comedien_has_langue cal U
SING(idlangue) WHERE cal.idutilisateur=u.idutilisateur)
    -> GROUP BY
    ->     id
    -> ORDER BY
    ->     subscriber DESC,
    ->     u.nom,
    ->     u.prenom,
    ->     u.idutilisateur
    -> LIMIT 0, 10;
Empty set (0.08 sec)

mysql>
Number of processes running now: 0
070103 14:08:09  mysqld restarted

mysql> exit
Bye

Resolved stack trace is: 

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump  -s /tmp/mysqld50.sym -n 25383.st
ack
0x81d9470 handle_segfault + 412
0x8f8f8f8f _end + -2028421105
0x81ca4f9 _ZN11Query_arena10free_itemsEv + 85
0x81c7192 _ZN3THD19cleanup_after_queryEv + 76
0x81f9375 _Z11mysql_parseP3THDPcj + 789
0x81efb99 _Z16dispatch_command19enum_server_commandP3THDPcj + 1951
0x81ef3ea _Z10do_commandP3THD + 526
0x81ee5d6 handle_one_connection + 982
0x40050aa7 _end + 932068135
0x40247c2e _end + 934128814

Just a confirmation: I just downgraded mysql to 5.0.27 on both master and slave debian sarge i386 servers and the queries do not crash anymore.

Bug #26089 can be related/duplicate of this one.

It seems that this bug is a duplicate of the bug#25172.

Duplicate of the bug#25172.