MySQL 5.0.22 Crash on Ubuntu 6.06.2 LTS

[Shang Wu]

System info:
root@cmdb:~# dpkg -l mysql-server-5.0
ii mysql-server-5.0 5.0.22-0ubuntu6.06.11 mysql database server binaries
root@cmdb:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 6.06.2 LTS
Release: 6.06
Codename: dapper

Using the following query triger the crash on the dapper 6.06.2 machine:
Result from MySQL 5.0.22 (Ubuntu)
root@localhost:(none)> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.user) a;
ERROR 2013 (HY000): Lost connection to MySQL server during query
root@localhost:(none)> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu) a;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***
+------------------+
| User |
+------------------+
| debian-sys-maint |
| root |
+------------------+
5 rows in set (0.00 sec)

However, the issue does not exist in Debian:
Result from MySQL 5.0.81 (Debian)
root@localhost:(none)> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.user) a;
ERROR 1054 (42S22): Unknown column 'mu.user' in 'order clause'

More debug info:
Jun 23 16:09:57 cmdb mysqld[21409]: mysqld got signal 11;
Jun 23 16:09:57 cmdb mysqld[21409]: This could be because you hit a bug. It is also possible that this binary
Jun 23 16:09:57 cmdb mysqld[21409]: or one of the libraries it was linked against is corrupt, improperly built,
Jun 23 16:09:57 cmdb mysqld[21409]: or misconfigured. This error can also be caused by malfunctioning hardware.
Jun 23 16:09:57 cmdb mysqld[21409]: We will try our best to scrape up some info that will hopefully help diagnose
Jun 23 16:09:57 cmdb mysqld[21409]: the problem, but since we have already crashed, something is definitely wrong
Jun 23 16:09:57 cmdb mysqld[21409]: and this may fail.
Jun 23 16:09:57 cmdb mysqld[21409]:
Jun 23 16:09:57 cmdb mysqld[21409]: key_buffer_size=67108864
Jun 23 16:09:57 cmdb mysqld[21409]: read_buffer_size=131072
Jun 23 16:09:57 cmdb mysqld[21409]: max_used_connections=1
Jun 23 16:09:57 cmdb mysqld[21409]: max_connections=100
Jun 23 16:09:57 cmdb mysqld[21409]: threads_connected=1
Jun 23 16:09:57 cmdb mysqld[21409]: It is possible that mysqld could use up to
Jun 23 16:09:57 cmdb mysqld[21409]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 283135 K
Jun 23 16:09:57 cmdb mysqld[21409]: bytes of memory
Jun 23 16:09:57 cmdb mysqld[21409]: Hope that's ok; if not, decrease some variables in the equation.
Jun 23 16:09:57 cmdb mysqld[21409]:
Jun 23 16:09:57 cmdb mysqld[21409]: thd=0x8b0e230
Jun 23 16:09:57 cmdb mysqld[21409]: Attempting backtrace. You can use the following information to find out
Jun 23 16:09:57 cmdb mysqld[21409]: where mysqld died. If you see no messages after this, something went
Jun 23 16:09:57 cmdb mysqld[21409]: terribly wrong...
Jun 23 16:09:57 cmdb mysqld[21409]: Cannot determine thread, fp=0xb1037d48, backtrace may not be correct.
Jun 23 16:09:57 cmdb mysqld[21409]: Stack range sanity check OK, backtrace follows:
Jun 23 16:09:57 cmdb mysqld[21409]: 0x818a089
Jun 23 16:09:57 cmdb mysqld[21409]: 0xffffe420
Jun 23 16:09:57 cmdb mysqld[21409]: 0x8b9e4f0
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81d24e4
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81d2877
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81d40cd
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81e6609
Jun 23 16:09:57 cmdb mysqld[21409]: 0x8279856
Jun 23 16:09:57 cmdb mysqld[21409]: 0x827b172
Jun 23 16:09:57 cmdb mysqld[21409]: 0x827afae
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81c2c21
Jun 23 16:09:57 cmdb mysqld[21409]: 0x819d205
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81a2ce7
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81a32b1
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81a43ec
Jun 23 16:09:57 cmdb mysqld[21409]: 0x81a4d98
Jun 23 16:09:57 cmdb mysqld[21409]: 0xb7ed1341
Jun 23 16:09:57 cmdb mysqld[21409]: 0xb7d224ee
Jun 23 16:09:57 cmdb mysqld[21409]: New value of fp=(nil) failed sanity check, terminating stack trace!
Jun 23 16:09:57 cmdb mysqld[21409]: Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
Jun 23 16:09:57 cmdb mysqld[21409]: stack trace is much more helpful in diagnosing the problem, so please do
Jun 23 16:09:57 cmdb mysqld[21409]: resolve it
Jun 23 16:09:57 cmdb mysqld[21409]: Trying to get some variables.
Jun 23 16:09:57 cmdb mysqld[21409]: Some pointers may be invalid and cause the dump to abort...
Jun 23 16:09:57 cmdb mysqld[21409]: thd->query at 0x8b15f40 = SELECT * FROM (SELECT c.cid,sd.hostname,r.name AS rack,l.name AS location,r.rid,sd.height AS top_height,m.height AS height, IF(m.height<sd.height
,sd.height-m.height,'0') AS bottom_height,st.name AS status FROM chassis c LEFT JOIN serverdetail sd ON sd.sid=c.sid LEFT JOIN rack r ON c.rid=r.rid LEFT JOIN model m ON m.mid=c.mid LEFT JOIN status st ON c.
stid=st.stid LEFT JOIN location l ON c.lid=l.lid WHERE NOT st.name IN('Fased Out') UNION SELECT '0' AS cid,re.type AS hostname,r.name AS rack,l.name AS location,r.rid,re.start AS top_height,re.height AS heig
ht,IF(re.height<re.start,re.start-re.height,'0') AS bottom_height,'N/A' AS status FROM rackextras re LEFT JOIN rack r ON r.rid=re.rid LEFT JOIN location l ON l.lid=r.lid ORDER BY r.rid,re.bottom_height) a WH
ERE rid=66 ORDER BY bottom_height
Jun 23 16:09:57 cmdb mysqld[21409]: thd->thread_id=8
Jun 23 16:09:57 cmdb mysqld[21409]: The manual page at http://www.mysql.com/doc/en/Crashing.html contains
Jun 23 16:09:57 cmdb mysqld[21409]: information that should help you find out what is causing the crash.
Jun 23 16:09:57 cmdb mysqld_safe[21530]: Number of processes running now: 0
Jun 23 16:09:57 cmdb mysqld_safe[21532]: restarted
Jun 23 16:09:57 cmdb mysqld[21535]: 090623 16:09:57 InnoDB: Started; log sequence number 0 43655
Jun 23 16:09:57 cmdb mysqld[21535]: 090623 16:09:57 [Note] Recovering after a crash using /var/log/mysql/mysql-bin
Jun 23 16:09:57 cmdb mysqld[21535]: 090623 16:09:57 [Note] Starting crash recovery...
Jun 23 16:09:57 cmdb mysqld[21535]: 090623 16:09:57 [Note] Crash recovery finished.
Jun 23 16:09:57 cmdb mysqld[21535]: 090623 16:09:57 [Note] /usr/sbin/mysqld: ready for connections.
Jun 23 16:09:57 cmdb mysqld[21535]: Version: '5.0.22-Debian_0ubuntu6.06.11-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian Etch distribution

root@cmdb:~# zcat /usr/share/doc/mysql-server-5.0/mysqld.sym.gz > mysqld.sym
root@cmdb:~# resolve_stack_dump -s ./mysqld.sym mysql.stacktrace2
0x818a089 handle_segfault + 639
0xffffe420 _end + -140740944
0x8b9e4f0 _end + 5666688
0x81d24e4 _Z18find_order_in_listP3THDPP4ItemP13st_table_listP8st_orderR4ListIS1_ESA_b + 432
0x81d2877 _Z11setup_orderP3THDPP4ItemP13st_table_listR4ListIS1_ES8_P8st_order + 67
0x81d40cd _ZN4JOIN7prepareEPPP4ItemP13st_table_listjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit + 1817
0x81e6609 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 473
0x8279856 _ZN18st_select_lex_unit4execEv + 2128
0x827b172 _Z21mysql_derived_fillingP3THDP6st_lexP13st_table_list + 358
0x827afae _Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P13st_table_listE + 78
0x81c2c21 _Z20open_and_lock_tablesP3THDP13st_table_list + 201
0x819d205 _Z21mysql_execute_commandP3THD + 5833
0x81a2ce7 _Z11mysql_parseP3THDPcj + 337
0x81a32b1 _Z16dispatch_command19enum_server_commandP3THDPcj + 1259
0x81a43ec _Z10do_commandP3THD + 134
0x81a4d98 handle_one_connection + 2238
0xb7ed1341 _end + -1349933615
0xb7d224ee _end + -1351698562

[Mathias Gug (mathiaz)]

Hi,

On Thu, Jun 25, 2009 at 06:15:29PM -0000, Shang Wu wrote:
>
> Using the following query triger the crash on the dapper 6.06.2 machine:
> Result from MySQL 5.0.22 (Ubuntu)
> root@localhost:(none)> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.user) a;
> ERROR 2013 (HY000): Lost connection to MySQL server during query
> root@localhost:(none)> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu) a;
> ERROR 2006 (HY000): MySQL server has gone away
> No connection. Trying to reconnect...

> Jun 23 16:09:57 cmdb mysqld[21409]: thd->query at 0x8b15f40 = SELECT * FROM (SELECT c.cid,sd.hostname,r.name AS rack,l.name AS location,r.rid,sd.height AS top_height,m.height AS height, IF(m.height<sd.height
> ,sd.height-m.height,'0') AS bottom_height,st.name AS status FROM chassis c LEFT JOIN serverdetail sd ON sd.sid=c.sid LEFT JOIN rack r ON c.rid=r.rid LEFT JOIN model m ON m.mid=c.mid LEFT JOIN status st ON c.
> stid=st.stid LEFT JOIN location l ON c.lid=l.lid WHERE NOT st.name IN('Fased Out') UNION SELECT '0' AS cid,re.type AS hostname,r.name AS rack,l.name AS location,r.rid,re.start AS top_height,re.height AS heig
> ht,IF(re.height<re.start,re.start-re.height,'0') AS bottom_height,'N/A' AS status FROM rackextras re LEFT JOIN rack r ON r.rid=re.rid LEFT JOIN location l ON l.lid=r.lid ORDER BY r.rid,re.bottom_height) a WH
> ERE rid=66 ORDER BY bottom_height

These two queries don't look the same - or the log files don't
correspond. Could specify which query causes the bug? If the latter,
please attach a dump of the sql database to be able to reproduce it.

  status incomplete

--
Mathias Gug
Ubuntu Developer http://www.ubuntu.com

[Daniël van Eeden (dveeden)]

The latter query caused the initial crash, The first is a simplified query which is using the mysql user database and also crashes

So please use this query on a MySQL 5.0.22 Server on Ubuntu 6.06.2 LTS
SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.User) a;

The ORDER BY is causing the crash. I don't think the ORDER BY is syntactically allowed there.

[Mathias Gug (mathiaz)]

Both hardy (5.0.51a) and karmic (5.0.75) don't allow the use of such a query:

mysql> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.User) a;
ERROR 1054 (42S22): Unknown column 'mu.User' in 'order clause'

which seems to be right behavior.

Marking this bug Fixed Released in hardy and above.

[Mathias Gug (mathiaz)]

A backport of 5.0.38 doesn't produce a mysqld crash:

mathiaz@t-mysqld-d:~$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM (SELECT mu.User FROM mysql.user mu UNION SELECT mu.user FROM mysql.user mu ORDER BY mu.User) a;
ERROR 1054 (42S22): Unknown column 'mu.User' in 'order clause'

[Shang Wu (shangwu)]

Seems to be directly related to:
http://bugs.mysql.com/bug.php?id=21476

[JC Hulce (soaringsky)]

Thank you for taking the time to report this bug and helping to make Ubuntu better. The version of Ubuntu you're reporting this issue on is in End of Life status, and newer versions have fixed this issue. You can learn more about this at https://wiki.ubuntu.com/Releases