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:
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

[3 Jan 2007 11:55] Olivier Clavel
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
[3 Jan 2007 11:56] Olivier Clavel
Bug info

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

[3 Jan 2007 12:13] Valeriy Kravchuk
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.
[3 Jan 2007 14:30] Olivier Clavel
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).
[3 Jan 2007 14:31] Olivier Clavel
Smallest data chunk I could get that still crashes (full db dump)

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

[3 Jan 2007 14:31] Olivier Clavel
Evidence of crash in mysql command line after query

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

[3 Jan 2007 16:20] Valeriy Kravchuk
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,
    ->             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)

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

mysql> exit

Resolved stack trace is: 

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump  -s /tmp/mysqld50.sym -n 25383.st
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
[3 Jan 2007 22:39] Olivier Clavel
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.
[16 Feb 2007 10:03] Valeriy Kravchuk
Bug #26089 can be related/duplicate of this one.
[16 Feb 2007 19:27] Evgeny Potemkin
It seems that this bug is a duplicate of the bug#25172.
[16 Feb 2007 21:34] Evgeny Potemkin
Duplicate of the bug#25172.