Bug #21582 mysql server crashes in close_temporary_tables
Submitted: 11 Aug 2006 13:19 Modified: 28 Aug 2006 18:53
Reporter: Frank Maas (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.24 OS:Windows (windows, linux)
Assigned to: Andrei Elkin CPU Architecture:Any

[11 Aug 2006 13:19] Frank Maas
Description:
After upgrading from an (in this aspect) perfectly running 5.0.15, my current installation keeps stopping every few days. No error is recorded in the logs, only Windows makes not of an unexpected exit. The evenlog shows:

Event Type:	Error
Event Source:	Service Control Manager
Event Category:	None
Event ID:	7034
Description:
The MySQL service terminated unexpectedly.  It has done this 1 time(s).

and

Event Type:	Error
Event Source:	Application Error
Event Category:	(100)
Event ID:	1000
Date:		11-8-2006
Time:		2:28:31
Description:
Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x000bd838.

Data:
0000: 41 70 70 6c 69 63 61 74   Applicat
0008: 69 6f 6e 20 46 61 69 6c   ion Fail
0010: 75 72 65 20 20 6d 79 73   ure  mys
0018: 71 6c 64 2d 6e 74 2e 65   qld-nt.e
0020: 78 65 20 30 2e 30 2e 30   xe 0.0.0
0028: 2e 30 20 69 6e 20 6d 79   .0 in my
0030: 73 71 6c 64 2d 6e 74 2e   sqld-nt.
0038: 65 78 65 20 30 2e 30 2e   exe 0.0.
0040: 30 2e 30 20 61 74 20 6f   0.0 at o
0048: 66 66 73 65 74 20 30 30   ffset 00
0050: 30 62 64 38 33 38         0bd838  

How to repeat:
Well... I cannot really point my finger at something. Perhaps it has something to do with the non-releasing of handles (see bug #21487), but this is just a wild guess.

I have now started the daemon standalone with output to console to hopefully fetch more error info (tip found in #11117).
[11 Aug 2006 13:56] MySQL Verification Team
Thank you for the bug report. When you installed 5.0.15 it was an upgrade
install from earliest version? anyway can you create a dump of your databases
and then restore them? also run the script: mysql_fix_privilege_tables.sql.

Thanks in advance.
[11 Aug 2006 15:38] MySQL Verification Team
I think this crash is in the function:

close_temporary_tables(THD *thd)

Also, the .pdb files are now bundled with downloads now, so you can ask to upload a user.dmp by running "drwtsn32", setting the options, then running "drwtsn32 -i" :)
[12 Aug 2006 12:11] Frank Maas
The 5.0.15 install was a fresh install, 5.0.24 was installed as an upgrade. I have more installations running 5.0.24 now, one is currently running standalone via console (to catch output). I will try your suggestion with another installation. Feeback on both actions will be provided as it becomes available.
[14 Aug 2006 5:19] MySQL Verification Team
Frank,

Please upload a "user.dmp" file by running "drwtsn32", setting the options/path, then running "drwtsn32 -i".  Then, get mysqld-nt to crash and upload the .dmp file in the configured directory.  Even better is if you can use mysqld-debug.exe instead.  Thanks,
[14 Aug 2006 9:05] Frank Maas
Yes well... Based on the earlier remark by Miquel and a tip found in anothter bug report I have now restarted two of my installations to catch the bug. Unfortunately I cannot "trigger" it, so "make it crash" is not gonna happen. I'll try your suggestion if I get a crash and otherwise will start the debug version (will check if this works out on a production system first) or do the drwatson trick. You might want to set this back to 'Need feedback'.
[15 Aug 2006 15:06] MySQL Verification Team
verified with 5.0.24 on windows using the php testcase uploaded.
Stack traces differ slightly on many runs.
mysql-debug.exe crashes quicker than mysqld-nt.exe

mysqld_nt!close_temporary+0xe
WS2HELP!WahReferenceContextByHandle+0x32
msafd!WSPSetSockOpt+0x617
WS2_32!send+0x94
mysqld_nt!vio_write+0x18
mysqld_nt!net_real_write+0x13e
mysqld_nt!net_flush+0x1b
mysqld_nt!close_temporary_tables+0x48
mysqld_nt!THD::cleanup+0x9b
mysqld_nt!end_thread+0x9
mysqld_nt!handle_one_connection+0x396
mysqld_nt!pthread_start+0x3b
mysqld_nt!_threadstart(void * ptd = 0x0067f39c)+0x6c
WARNING: Frame IP not in any known module. Following frames may be wrong.
0x11ea070
[15 Aug 2006 15:08] MySQL Verification Team
> php bug21582.php

Attachment: bug21582.php (application/octet-stream, text), 456 bytes.

[15 Aug 2006 15:12] Frank Maas
Got a crash (finally, but Shane beat me to it :-( ). mysqld-nt does not show anything on console output. I've got the DrWatson log and user.dmp file (will attach them). This is on the machine where I simply upgraded, the system where I reinstalled from scratch and imported the database from sql is still running (but this system is less used).
mysqld-debug would not serve my application, will try it again later (when the platform is less busy).

Happy to give additional information.

Frank
[15 Aug 2006 15:15] MySQL Verification Team
I didn't repeat same crashes on linux yet, even with many threads at once.  I will try harder with that.

From mysqld-debug.exe:

mysqld_debug!close_temporary(struct st_table * table = 0xdddddddd, int delete_table = 1)+0x3b
mysqld_debug!close_temporary_tables(class THD * thd = 0x0440c228)+0x6f
mysqld_debug!THD::cleanup(void)+0xdb
mysqld_debug!end_thread(class THD * thd = 0x0440c228, int put_in_cache = 1)+0x35
mysqld_debug!handle_one_connection(void * arg = 0x0440c228)+0x3b3
mysqld_debug!pthread_start(void * param = 0x043890c8)+0x53
mysqld_debug!_threadstart(void * ptd = 0x0440afd8)+0xb7
KERNEL32!BaseThreadStart+0x52
[15 Aug 2006 15:19] Frank Maas
Shane: does the error also occur when you do not use TEMPORARY tables, but normal tables in stead? My app uses temporary tables as well, so this might be a connection?
[15 Aug 2006 15:24] MySQL Verification Team
Frank,
Thank you for the feedback. I Changed zip file to private.
[15 Aug 2006 15:35] MySQL Verification Team
Ensure 'DROP TEMPORARY TABLE IF EXISTS...'  is called before the connection closes if it does 'CREATE TEMPORARY TABLE.... '

It will help avoid some crashes.  Not all however, since if the connection is broken or times out, a crash can still occur (I just got such crash now).

Also, I'm trying next with tmp tables used to resolve GROUP BY and ORDER BY queries.
[15 Aug 2006 19:29] Frank Maas
OK. I had problems before dropping the temp table, but now got round to find out why. Our systems are more or less continuously connected and I did a 'CREATE TEMPORARY TABLE IF NOT EXISTS' pretty often. I have now changed this into a DROP TEMPORARY TABLE IF EXISTS and left out the IF in the CREATE on all systems. See if this changes things.

In the mean time I started up mysql-debug on this machine, still using the existing datafiles though (no re-install from scratch on this one).

You'll hear from me when there is something to report.
[16 Aug 2006 16:16] MySQL Verification Team
updated synopsis.
verified on mysql-standard-5.0.24-linux-i686-glibc23 too.
[16 Aug 2006 16:19] MySQL Verification Team
marked bug #21666 as a duplicate of this
marked bug #21609 as a duplicate of this
[16 Aug 2006 17:32] MySQL Verification Team
C testcase

Attachment: bug21582.c (text/x-csrc), 1.09 KiB.

[17 Aug 2006 7:41] Frank Maas
When I connected to the system running mysqld-debug this morning I found a lot of DBUG popups showning something with the word 'signal' and a button. I pressed that button on the top-popup and down went mysql ;-)
So I have another set of crashdump files, but I don't know if it is "the same crash". At any rate, in the drwatson file I see that "the symbols for mysqld-debug could not be loaded", so if this is of any use...?
Anyway, I saw a lot of useful information in the bug duplicates, so I hope the problem can be fixed. Good luck!
[17 Aug 2006 8:51] MySQL Verification Team
frank, latest crash is same:
00 mysqld_debug!close_temporary+0x3b
01 mysqld_debug!close_temporary_tables+0x6f
02 mysqld_debug!THD::cleanup+0xdb
03 mysqld_debug!end_thread+0x35
04 mysqld_debug!handle_one_connection+0x3b3
05 mysqld_debug!pthread_start+0x53
06 mysqld_debug!_threadstart+0xb7
07 kernel32!BaseThreadStart+0x34
[18 Aug 2006 20:59] Andrei Elkin
The whole bug family should be a dup of bug#20919 fixed in main bk-5.0 but not in 5.0.24.
[19 Aug 2006 18:01] MySQL Verification Team
Bug happens due to the fact that some of the functions called by close_temporary() has set thd->temporary_tables to NULL.
[21 Aug 2006 9:52] 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/10662

ChangeSet@1.2235, 2006-08-21 11:44:30+02:00, aelkin@dl145j.mysql.com +1 -0
  BUG#21582 mysql server crashes in close_temporary_tables
  
  the problem is described and resolved by 20919 which fix happened not to have got to
  the release tree. Applying the patch of the parent bug.
[21 Aug 2006 10:29] Andrei Elkin
Fixed in 5.0.24a.
[28 Aug 2006 18:53] Paul DuBois
Noted in 5.0.25a changelog.
[28 Aug 2006 19:07] Paul DuBois
Sorry, that last comment should say "5.0.24a changelog."
[31 Aug 2006 21:57] Steve Baroti
Hi,

System short description: 5.0.24-standard-log, upgraded from 5.0.21 - RHEL AS 4.

We closely monitor and graph (MRTG) the number of open tmp files kept on the disk by our mySQL server in its tmpdir [my.cnf: tmpdir=/var/tmp/mysql], and when we just had a warning about to many tmp files (>550), the server crashed and recovered with the error logged bellow. I believe that this is what's been fixed in 5.0.24a. Many thanks guys for your great job,

SteveZi

Version: '5.0.24-standard-log'  socket: '/data/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=131072
max_used_connections=76
max_connections=200
threads_connected=56
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 697342 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x9c8a8b88
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=0x9d4d8c8c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817b1b0
0xa6d898
0x8199e78
0x817ad50
0x8199e78
0xa67371
0x967ffe
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 (nil)  is invalid pointer
thd->thread_id=19530
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
060829 18:10:45  mysqld restarted
060829 18:10:45  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060829 18:10:46  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1330007868.
InnoDB: Doing recovery: scanned up to log sequence number 0 1330007868
InnoDB: Last MySQL binlog file position 0 0, file name
060829 18:10:46  InnoDB: Started; log sequence number 0 1330007868
060829 18:10:47 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.24-standard-log'  socket: '/data/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)