MySQL Forums
Forum List  »  InnoDB

How to cleanup transaction after crash
Posted by: Andre LAGADEC
Date: January 16, 2009 03:05PM

Hello,

After a file-system full on my mysql server, the server stop and can't restart.

I suppress file on that file-system to free more place, and I try to start MySQL server, but it don't want.

In hostname.err, I have these messages :
090116 10:21:16 mysqld started
090116 10:21:16 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...
090116 10:21:16 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 5 789480598.
InnoDB: Doing recovery: scanned up to log sequence number 5 789480633
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 23 row operations to undo
InnoDB: Trx id counter is 0 19873536
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 19873094, 23 rows to undoInnoDB: Error: trying to access page number 310308225 in space 0,
InnoDB: space name /opt/mysql/mysql/data/ibdata1,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10
090116 10:21:16InnoDB: Assertion failure in thread 1 in file fil0fil.c line 3922
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;
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=0
read_buffer_size=131072
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

090116 10:21:16 mysqld ended

After search on Web, I add this line in file my.cnf
innodb_force_recovery = 3
Now I can start my MySQL server and I can connect to it with MySQL client but in log, there is alway these messages :
090116 18:40:08 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 5 789480878.
InnoDB: Doing recovery: scanned up to log sequence number 5 789480934
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 23 row operations to undo
InnoDB: Trx id counter is 0 19873536

How can I cleanup or delete this transaction id counter 0 19873536 ?

If I execute this MySQL command "show innodb status;", I get these informations :
=====================================
090116 19:08:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 2, rounds 20, OS waits 1
RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 19873536
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 12
MySQL thread id 1, query id 4 localhost root
show innodb status
---TRANSACTION 0 19873094, ACTIVE 1680 sec, OS thread id 0
, undo log entries 23
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
64 OS file reads, 7 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 12, seg size 14, is empty
Ibuf for space 0: size 1, free list len 12, seg size 14,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 5 789481006
Log flushed up to 5 789481006
Last checkpoint at 5 789480934
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 36510808; in additional pool allocated 1741056
Buffer pool size 1024
Free buffers 983
Database pages 41
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 41, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 10, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Thanks for your help.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to cleanup transaction after crash
7033
January 16, 2009 03:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.