MySQL Bugs: #21817: mysql 5.0.24 is crashing under freebsd 5.4

Bug #21817	mysql 5.0.24 is crashing under freebsd 5.4
Submitted:	24 Aug 2006 19:28	Modified:	30 Sep 2006 19:35
Reporter:	Fernando Figaro	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.24	OS:	FreeBSD (freebsd)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Mysql 5.0.24 (via ports)is crashing under freebsd 5.4 - Even with the database server in another server (freebsd 5.4 too) the problem continues. I believe this may be related with some sql clauses that client send to server.

-------
Follows the logs:

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=33554432
read_buffer_size=1044480
max_used_connections=12
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 237167 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

060824 15:59:45  mysqld restarted
060824 15:59: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...
060824 15:59:45  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 175264808.
InnoDB: Doing recovery: scanned up to log sequence number 0 175265855
060824 15:59:45  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
060824 15:59:46  InnoDB: Started; log sequence number 0 175265855
060824 15:59:46 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.24-log'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port: mysql-server-5.0.24

How to repeat:
I believe (but not found) that some complex queries is crashing mysql server. This is not a hardware problem becaus this database was uploaded via mysqldump in another server.

I have already compiled mysql using WITH_LINUXTHREADS=yes WITH_PROC_SCOPE_PTH=yes BUILD_OPTIMIZED=yes BUILD_STATIC=yes

But the error persists.

Thank you for the report.

But we really need more information to repeat this bug.

> I believe (but not found) that some complex queries is crashing mysql server.

You can find such query: turn on general query log and than error happens again, simple found the query.

I don't want to believe this ;-) , but I was checking the queries and mysql log and for some circunstance this error occurs in some condition when php session expires, maybe when some transaction is running...

This is what my programmer said. I increased the expiration timeout, let's check if error persists.

OK. We will wait feedback from you.

Hi Fernando, 
Do you use temporary tables in the applications? (CREATE TEMPORARY TABLE ...) ?

Yes, we use many create temporary table.

I was searching for any other information about this problem and I'm unable to detect the exact query causing this error. But I know that is something related to innodb, inner join, transactions. The queries executed before errors use this functions.

I'm preparing some query logs to post here.

Txt with queries

Attachment: bug.txt.rtf (application/octet-stream, text), 17.02 KiB.

Hi,

I posted the log of the queries executed some minutes/seconds before the error.

In the end of this file you can see an "/usr/local/libexec/mysqld, Version: 
5.0.24-log. started with: Tcp port: 3306 Unix socket: (null)

This is the point where error ocurred.

This might be that temporary table crash, bug #21582.
Try adding DROP TEMPORARY TABLE .. before closing the connections and see if it helps.

2408 Connect 
2408 Init DB XXXXXX
2408 Query SELECT ...
2408 Query SELECT ...
2408 Query CREATE TEMPORARY TABLE ...
...
2408 Quit
<crash>

Thank you, Fernando, for the queries.

Could you please provide output of SHOW CREATE TABLE AgenciesUsers; and SHOW TABLE STATUS LIKE 'AgenciesUsers'; for AgenciesUsers, PackagesCountries and PackagesDepartureDates tables?

Also, please, try suggestion by Shane Bester.

Hi, here is the output of commands...

SHOW CREATE TABLE AgenciesUsers :**

CREATE TABLE `AgenciesUsers` (\n  `AgencyID` smallint(6) NOT NULL 
default '0',\n  `Sequencia` smallint(6) NOT NULL default '0',\n 
 `BuyNetFareID` char(9) collate latin1_general_ci NOT NULL default '',\n 
 `UserIDInclusion` smallint(5) unsigned NOT NULL default '0',\n 
 `InclusionDate` datetime NOT NULL default '0000-00-00 00:00:00',\n 
 `UserIDUpdate` smallint(5) unsigned default NULL,\n  `UpdateDate` 
datetime default NULL,\n  `UserName` char(100) collate latin1_general_ci 
default NULL,\n  `PositionID` tinyint(4) default NULL,\n  `Address` 
char(100) collate latin1_general_ci NOT NULL default '',\n  `CityID` 
int(10) unsigned NOT NULL default '0',\n  `ZipCode` char(20) collate 
latin1_general_ci NOT NULL default '',\n  `Telephone` char(20) collate 
latin1_general_ci NOT NULL default '',\n  `FaxNumber` char(20) collate 
latin1_general_ci NOT NULL default '',\n  `Email` char(100) collate 
latin1_general_ci NOT NULL default '',\n  `LoginName` char(100) collate 
latin1_general_ci NOT NULL default '',\n  `Senha` char(20) collate 
latin1_general_ci NOT NULL default '',\n  `LastLogin` datetime NOT NULL 
default '0000-00-00 00:00:00',\n  `Expired` char(1) collate 
latin1_general_ci NOT NULL default '',\n  `Online` datetime NOT NULL 
default '0000-00-00 00:00:00',\n  `Tentativas` tinyint(4) NOT NULL 
default '0',\n  `Inactive` char(1) collate latin1_general_ci NOT NULL 
default '1',\n  `BlastEmailEnviado` char(1) collate latin1_general_ci 
NOT NULL default '0',\n  PRIMARY KEY  (`AgencyID`,`Sequencia`)\n) 
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

*SHOW TABLE STATUS LIKE **'AgenciesUsers':*

Name Engine Version Row_format Rows Avg_row_length Data_length 
Max_data_length Index_length Data_free Auto_increment Create_time 
Update_time Check_time Collation Checksum Create_options Comment
AgenciesUsers InnoDB 10 Compact 3431 768 2637824 0 0 0 
/NULL/ 2006-08-30 16:52:01 /NULL/ /NULL/ latin1_general_ci 
/NULL/   InnoDB free: 24576 kB

*SHOW TABLE STATUS LIKE '*PackagesCountries***'*
Name Engine Version Row_format Rows Avg_row_length Data_length 
Max_data_length Index_length Data_free Auto_increment Create_time 
Update_time Check_time Collation Checksum Create_options Comment
PackagesCountries InnoDB 10 Compact 171 95 16384 0 0 0 
/NULL/ 2006-08-30 16:52:08 /NULL/ /NULL/ latin1_general_ci 
/NULL/   InnoDB free: 24576 kB

*SHOW** TABLE STATUS LIKE '***PackagesDepartureDates***':*
Name Engine Version Row_format Rows Avg_row_length Data_length 
Max_data_length Index_length Data_free Auto_increment Create_time 
Update_time Check_time Collation Checksum Create_options Comment
PackagesDepartureDates InnoDB 10 Compact 2447 53 131072 0 0 0 
/NULL/ 2006-08-30 16:52:08 /NULL/ /NULL/ latin1_general_ci 
/NULL/   InnoDB free: 24576 kB

Please, try suggestion by Shane Bester:

> Try adding DROP TEMPORARY TABLE .. before closing the connections and see if it
> helps.

> 2408 Connect 
> 2408 Init DB XXXXXX
> 2408 Query SELECT ...
> 2408 Query SELECT ...
> 2408 Query CREATE TEMPORARY TABLE ...
> ...
> 2408 Quit

and provide output of SHOW CREATE TABLE PackagesCountries; and SHOW CREATE TABLE PackagesDepartureDates; statements.

We add some Drop temporary table in our scripts...lets check if the problem persists.

I found in mysql data directory a file mysqld.core with almost 93M. It contains some data in begining and many pieces of my tables information. Which part would be better to upload here? 

Fernando

Fernando,

core file can be helpful only if you upload it whole (not only part) and binary mysqld produced it. Read instructions in "Files" field how to upload large file.

But Shane Bester can be right and this case can be same of bug #21582 already fixed BK sources. So lets wait results of "DROP TEMPORARY TABLE" test first.

MySQL 5.0.24a with fix of bug #21582 has relased today. Could you also try using this version?

You cab download MySQL 5.0.24a here: http://dev.mysql.com/downloads/mysql/5.0.html

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".