MySQL Bugs: #29266: Mysqld restart repeatly

Bug #29266	Mysqld restart repeatly
Submitted:	21 Jun 2007 12:15	Modified:	18 Aug 2007 22:55
Reporter:	Ducheol Kim	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Linux (CentOS release 3.5)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	how can I make stack trace file?

Description:
Hi.

First time We used mysql4.x version.That time we have no problem.
But Feb in this year, we updated mysql 4.x to 5.0.27, because of some new feature like sub-query, and stored procedure, and so on . 
After that we have a problem that mysqld restart repeatly.
Sometimes it happen many times a day.

When that happened, some function of our system work correctly, but only one thing that reported in error log couldn't work well.

We use iBatis+jdbc+mysql for persistance.
( We use iBatis 2.1.0 and mysql-connector 3.1.7 ).
We tried to upgrade mysql-connector to 5.x, but this is impossible because we use iBatis.

Below is our error log.

Please give me a hint how can I solve it.

InnoDB: Error: trying to access page number 193803648 in space 0,
InnoDB: space name ./ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
070621  9:37:43InnoDB: Assertion failure in thread 2539662256 in file fil0fil.c line 3959
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/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
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=402653184
read_buffer_size=2093056
max_used_connections=10
max_connections=100
threads_connected=10
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 802415 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x98e8638
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=0x976009dc, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817adf8
0x832532d
0x8303b8b
0x830397d
0x82facd4
0x82e9624
0x82bf7e1
0x82b5bc5
0x8227824
0x81d0e1d
0x81c2b0f
0x81c8ff1
0x81c2b1a
0x81c8cb3
0x81bdf76
0x81bf5c1
0x81bb382
0x818fa2a
0x8196a02
0x818e2a6
0x818ddcd
0x818d4c4
0xf3ede8
0x1ed93a
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 0x9910e68 = SELECT  campaign.endDate , proposalDetail_id , advertiserBrandMember_id , updateTime , campaign.name , ioFlag , saleCode, overFlag ,                      campaign.rmkMember_no , campaign.memo , campaign.advertiserBrand_id , campaign.agency_id , campaign.agencyMember_id , campaign.registDate , campaign.campaign_id as campaignId ,                    updateRmkMember_no , campaign.startDate, advertiser.advertiser_id  as advertiserId,company.name as advertiserName,advertiserBrand.name as advertiserBrandName,                  agencyCompany.name as agencyName, campaign_insertionOrder.insertionOrder_id as insertionOrderId, approvalStatus.name as approvalStatusName,                         campaign.rmkTeam_id, rmkTeam.name AS rmkTeamName, RMKcontact.name AS rmkMemberName                  , campaign.campaign_origin_id as campaignOriginId, campaign.campaignCopyReasonCode, campaign.campaignCopyReason                     , agency.isContract as agencyIsContract, advertiserBrand.isContract as advertiserBrandIsContract FROM campaign                   LEFT JOIN campaign_insertionOrder                       USING ( campaign_id )                   LEFT JOIN insertionOr
thd->thread_id=1
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
070621 09:37:44  mysqld restarted
070621  9:37:44 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=adflow-bin' to avoid this problem.
070621  9:37:44  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...
070621  9:37:44  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 319565688.
InnoDB: Doing recovery: scanned up to log sequence number 0 319565688
InnoDB: Last MySQL binlog file position 0 646, file name ./adflow-bin.000226
070621  9:37:44  InnoDB: Started; log sequence number 0 319565688
070621  9:37:44 [Note] Recovering after a crash using adflow-bin
070621  9:37:44 [Note] Starting crash recovery...
070621  9:37:44 [Note] Crash recovery finished.
070621  9:37:44 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.27-standard-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)

How to repeat:
There is no rule.
Sometime error occured, but many times work normal.

Please run CHECK TABLE on campaign and campaign_insertionOrder.

One of tables may be corrupt.

Please resolve the stack trace.

Please post the ENTIRE .err log. The first abnormal prints in the .err log often give a clue.

This is error file on our server.(We split error file to three files, because of allowed size )

Attachment: adflow.247realmedia.co.kr.err1 (text/plain), 498.18 KiB.

This is error file on our server.(We split error file to three files, because of allowed: 2nd file)

Attachment: adflow.247realmedia.co.kr.err2 (text/plain), 361.89 KiB.

This is error file on our server.(We split error file to three files, because of allowed:3nd file)

Attachment: adflow.247realmedia.co.kr.err3 (text/plain), 410.12 KiB.

Thanks Heikki.

I upload entier error log.
And I executed CHECK TABLE, but there is no error message.

Below is message of CHECK TABLE.

mysql> CHECK TABLE campaign;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| adflow_v2.campaign | check | status   | OK       | 
+--------------------+-------+----------+----------+
1 row in set (0.46 sec)

mysql> CHECK TABLE campaign_insertionOrder;
+-----------------------------------+-------+----------+----------+
| Table                             | Op    | Msg_type | Msg_text |
+-----------------------------------+-------+----------+----------+
| adflow_v2.campaign_insertionOrder | check | status   | OK       | 
+-----------------------------------+-------+----------+----------+

Kim,

please resolve the stack trace in the crash.

--Heikki

May be a duplicate of http://bugs.mysql.com/bug.php?id=27803

Kim, have you had problems with hard disks like in http://bugs.mysql.com/bug.php?id=27803 ?

Assign to Inaam.

Ducheol,

Can you please post the resolved stack trace. Instructions on how to do this can be found here:

http://dev.mysql.com/doc/refman/5.0/en/resolve-stack-dump.html

Also, did you experience any disk issues or any other hardware problems?

Thanks Inaam.
I tried to make stack dump file.
But I couldn't.

When i strike below command.
receive "nm: 'mysqld': No such file".

So how can I make stack trace file?

shell>>nm --numeric-sort mysqld
nm: 'mysqld': No such file

Regards
Doochul

Kim,

You have to give full path to mysqld. Assuming mysqld is in your $PATH you can do:

nm --numeric-sort `which mysqld`

Inaam
I tried to "nm --numeric-sort `which mysqld`", and system generate 17480 line messsage.
After that I tried to "nm --numeric-sort `which mysqld` | grep mysql",
after that system generated message like below.

[root@adflow root]# nm --numeric-sort `which mysqld` | grep mysqld
0817cb84 t _Z20my_str_malloc_mysqldj
0817cb98 t _Z18my_str_free_mysqldPv
081e0d30 T _Z20mysqld_show_warningsP3THDm
0823ab20 T _Z27mysqld_show_storage_enginesP3THD
0823ae38 T _Z22mysqld_show_privilegesP3THD
0823b0f8 T _Z24mysqld_show_column_typesP3THD
0823bb28 T _Z18mysqld_show_createP3THDP13st_table_list
0823c11c T _Z21mysqld_show_create_dbP3THDPcP24st_ha_create_information
0823c684 T _Z16mysqld_show_logsP3THD
0823c8d8 T _Z18mysqld_list_fieldsP3THDP13st_table_listPKc
0823ca70 T _Z23mysqld_dump_create_infoP3THDP13st_table_listi
0823cf48 T _Z21mysqld_list_processesP3THDPKcb
0826b15c T _Z11mysqld_helpP3THDPKc
084b08b8 D mysqld_embedded
085bcc38 B mysqld_port
085bcc4c B mysqld_port_timeout
085bdcfc B mysqld_unix_port
085be838 b mysqld_user
085be83c b mysqld_chroot

What number in message could I used?
And What is  "symbols_file" in 'resolve_stack_dump' command

Kim,

cd into the directory where your .err file is located.
Assuming your .err file name (which contains entries of stack) is mysql.err, do the following:

inaam@inaam-laptop:~/bugs/27803$ grep ^0x mysql.err > numeric.dmp
inaam@inaam-laptop:~/bugs/27803$ nm --numeric-sort `which mysqld` > symbols.dmp
inaam@inaam-laptop:~/bugs/27803$ resolve_stack_dump -s symbols.dmp -n numeric.dmp > stack.trc

Then upload the stack.trc file.

Thanks,
inaam

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".