Bug #19414 Query causes MySQL server to crash

• Submitted: 27 Apr 2006 21:09
• Modified: 2 May 2006 19:24
• Reporter: Justin Plock
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.20a
• OS: Solaris (Solaris 10)
• CPU Architecture: Any

[27 Apr 2006 21:09] Justin Plock

Description:
When executing the following query:

SELECT b.date,b.timeid, ifnull(a.name,'AT'), IFNULL( a.counting, 0 )as count FROM (SELECT date,timeid FROM DateTable,timedimension WHERE timeid between 948 and 1008 and date='04/27/2006'  order by date, timeid) AS b LEFT JOIN (select date, timeid, sites.name, count( DISTINCT dts.agentid ) AS counting FROM dts use index (Date),sitemap, sites  WHERE dts.agentid=sitemap.agentid and sites.siteid = sitemap.siteid and sites.siteid='AT' AND timeid between 948 and 1008 and date='04/27/2006' group by date,timeid) as a ON(a.timeid =b.timeid and b.date=a.date)

MySQL 5.0.20a on Solaris 10 is crashing.  I am using the 5.0.20a-standard Solaris 10 pre-compiled binary downloaded from MySQL.com.  I'm sorry that I don't have an easier query to demonstrate the crash, but this query executes fine on a MySQL 5.0.17 server running on a Linux machine with the exact same table scheme.

How to repeat:
See Description

Suggested fix:
See Description

[27 Apr 2006 21:34] Justin Plock

The calling thread is holding the adaptive search, latch though calling innobase_query_caching_of_table_permitted.
TRANSACTION 0 38408, ACTIVE 0 sec, OS thread id 14
mysql tables in use 5, locked 0, holds adaptive hash latch

[27 Apr 2006 21:35] Justin Plock

060427 16:53:18InnoDB: Assertion failure in thread 14 in file ha_innodb.cc line 992
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;

[27 Apr 2006 21:35] Justin Plock

key_buffer_size=16777216
read_buffer_size=8200
max_used_connections=4
max_connections=100
threads_connected=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 68384 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

[28 Apr 2006 14:41] Valeriy Kravchuk

Thank you for a problem report. Please, send the SHOW CREATE TABLE and SHOW TABLE STATUS results for all the tables involved, or, even better, dump of  them. uname -a results may be also useful.

[28 Apr 2006 16:26] Justin Plock

Dump of all tables in the current database, SHOW TABLE STATUS output, and uname -a output

Attachment: MySQL Bug #19414.zip (application/zip, text), 3.49 KiB.

[1 May 2006 18:16] Justin Plock

I uploaded the necessary schema information. Let me know if you need anything else.  Thanks.

[1 May 2006 19:50] MySQL Verification Team

Thank you for the feedback. It is possible to provide a dump for to
insert data the enough which is possible to repeat the crash?
The according with the file size you can use the below ftp for
upload:

ftp:/ftp.mysql.com:/pub/mysql/upload

Thanks in advance.

[2 May 2006 19:24] Justin Plock

This was fixed in 5.0.21. Not sure exactly which bug its related to, but its working now. Thanks.