List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:January 3 2005 2:36pm
Subject:Re: BIG InnoDB problems!
View as plain text  
Joshua,

about dumping tables from a corrupt database, see:
http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html

This describes how to remove the whole InnoDB database:
http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html

Be very careful. You do not want to lose your valuable data.

Regards,

Heikki

----- Alkuperäinen viesti ----- 
Lähettäjä: "jsf" <jfreeman@stripped>
Vastaanottaja: "Heikki Tuuri" <Heikki.Tuuri@stripped>
Kopio: <mysql@stripped>
Lähetetty: Monday, January 03, 2005 4:31 PM
Aihe: Re: BIG InnoDB problems!


Hi Heikki,

Please see below...


On Mon, 3 Jan 2005 15:14:12 +0200, Heikki Tuuri <Heikki.Tuuri@stripped> 
wrote:
> Joshua,
>
> the stack trace below shows that you are trying to drop a database? Why?

At that point, I'd heard from our developer of so many problems I
figured what I would do is test things out.  I created a database.  I
created a table in the database as an InnoDB .  I tried to insert data
into it and was unsuccessful.. I tried a few more things.. all
unsuccessful, so I figured I'd just try to drop the database. But I
couldn't do that either.

>
> If you can, you should use SELECT ... INTO OUTFILE to save of your tables
> what you can save, then rebuild the whole InnoDB tablespace, and import 
> the
> tables back to MySQL.

I'm going to have to 'go to school' on InnoDB tablespace.  I have only
the most rudimentary understanding of what you've written here.


>
> The .err file below starts from a situation where you have already set
> innodb_force_recovery to 5.
>
> "

Is that bad?

> 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the
> database: InnoDB will treat even incomplete transactions as committed.
> "
>
> Was the original problem the same as what we see below? The history list 
> of
> InnoDB in the ibdata files seems to be corrupt. The only way to fix that
> kind of corruption is to rebuild the whole tablespace.

Is there a tutorial on rebuilding the tablespace?  or deleting the
table space and starting over?

>
> Best regards,
>
> Heikki
> Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
> MyISAM
> tables
> http://www.innodb.com/order.php
>
> Order MySQL support from http://www.mysql.com/support/index.html
>
> ----- Alkuperäinen viesti -----
> Lähettäjä: "jsf" <jfreeman@stripped>
> Vastaanottaja: "Heikki Tuuri" <Heikki.Tuuri@stripped>
> Kopio: <mysql@stripped>
> Lähetetty: Friday, December 31, 2004 5:21 PM
> Aihe: Re: BIG InnoDB problems!
>
>
> > Greetings Heikki and Happy New Year!
> >
> > Here's what I got.  I hope it's useful.
> >
> > beech:/home/jfreeman # resolve_stack_dump -s /tmp/mysqld.sym -n
> > mysqld.stack
> > 0x815f0cf handle_segfault + 575
> > 0xffffe420 _end + -138916432
> > 0x82e71d5 buf_read_page + 165
> > 0x82e71d5 buf_read_page + 165
> > 0x82db68f buf_page_get_gen + 175
> > 0x830479f flst_insert_before + 239
> > 0x8304cc8 flst_add_first + 152
> > 0x82be800 trx_purge_add_update_undo_to_history + 624
> > 0x82d14a6 trx_undo_update_cleanup + 38
> > 0x82ccafb trx_commit_off_kernel + 363
> > 0x82cd865 trx_sig_start_handle + 1109
> > 0x826232b que_run_threads + 2299
> > 0x827915a row_drop_table_for_mysql + 2314
> > 0x81fe924 _ZN11ha_innobase12delete_tableEPKc + 404
> > 0x81ef33c _Z15ha_delete_table7db_typePKc + 60
> > 0x820aead _Z20mysql_rm_table_part2P3THDP13st_table_listbbb + 989
> > 0x820b19d _Z30mysql_rm_table_part2_with_lockP3THDP13st_table_listbbb + 
> > 93
> > 0x8201554 _Z20mysql_rm_known_filesP3THDP9st_my_dirPKcS4_j + 1428
> > 0x8202739 _Z11mysql_rm_dbP3THDPcbb + 345
> > 0x81796cb _Z21mysql_execute_commandP3THD + 19339
> > 0x817c1b4 _Z11mysql_parseP3THDPcj + 484
> > 0x817de5d _Z16dispatch_command19enum_server_commandP3THDPcj + 2685
> > 0x817f137 handle_one_connection + 2391
> > 0x401619ed _end + 936280957
> > 0x403519ca _end + 938312538
> >
> >
> > p.s.  the whole error file is only 301 lines long.  If you wish I
> > could send it to you...
> >
> > Here's a segment from lines 1 - 41:
> >
> > 041230 11:12:10  mysqld started
> > 041230 11:12:10  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...
> > 041230 11:12:10  InnoDB: Starting log scan based on checkpoint at
> > InnoDB: log sequence number 0 241342003.
> > InnoDB: Doing recovery: scanned up to log sequence number 0 241342036
> > InnoDB: Last MySQL binlog file position 0 79, file name 
> > ./beech-bin.000047
> > 041230 11:12:10  InnoDB: Flushing modified pages from the buffer pool...
> > 041230 11:12:10  InnoDB: Started; log sequence number 0 241342036
> > InnoDB: !!! innodb_force_recovery is set to 5 !!!
> > 041230 11:12:10 [Warning] mysql.user table is not updated to new
> > password format; Disabling new password usage until
> > mysql_fix_privilege_tables is run
> > 041230 11:12:10 [Warning] Can't open and lock time zone table: Table
> > 'mysql.time_zone_leap_second' doesn't exist trying to live without
> > them
> > /usr/local/libexec/mysqld: ready for connections.
> > Version: '4.1.8a-log'  socket: '/tmp/mysql.sock'  port: 3306  Source
> > distribution
> > InnoDB: A new raw disk partition was initialized or
> > InnoDB: innodb_force_recovery is on: we do not allow
> > InnoDB: database modifications by the user. Shut down
> > InnoDB: mysqld and edit my.cnf so that newraw is replaced
> > InnoDB: with raw, and innodb_force_... is removed.
> > InnoDB: Error: trying to access page number 940269659 in space 0,
> > InnoDB: space name ./ibdata1,
> > InnoDB: which is outside the tablespace bounds.
> > InnoDB: Byte offset 0, len 16384, i/o type 10
> > 041230 16:42:57InnoDB: Assertion failure in thread 1124068272 in file
> > fil0fil.c line 3729
> > 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.
> >
> > On Fri, 31 Dec 2004 07:46:01 +0200, Heikki Tuuri
> > <Heikki.Tuuri@stripped> wrote:
> >> Joshua,
> >>
> >> please show what the FIRST InnoDB error in the .err log was. The first
> >> error
> >> is always the important thing to report.
> >>
> >> Please follow these instructions:
> >>
> >> > 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
> >>
> >> Best regards,
> >>
> >> Heikki Tuuri
> >> Innobase Oy
> >> Foreign keys, transactions, and row level locking for MySQL
> >> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
> >> MyISAM
> >> tables
> >> http://www.innodb.com/order.php
> >>
> >> Order MySQL technical support from https://order.mysql.com/
> >>
> >>
> >> ----- Original Message -----
> >> From: "jsf" <jfreeman@stripped>
> >> Newsgroups: mailing.database.myodbc
> >> Sent: Friday, December 31, 2004 12:26 AM
> >> Subject: BIG InnoDB problems!
> >>
> >> > I've been struggling with this problem for the last few days.  I've
> >> > enlisted the help of some colleagues on the NYLUG (NY Linux User's
> >> > Group) list but finally we figured this is the best place to look for
> >> > some help.
> >> >
> >> > We have a server running SLES 9.0 (SuSE Linux Enterprise Server 9.0)
> >> > and:
> >> >
> >> > mysqladmin  Ver 8.41 Distrib 4.1.8a, for pc-linux-gnu on i686
> >> >
> >> > There are 5 MySQL databases on the server.  The smallest has 5 
> >> > tables,
> >> > the largest 14 tables.  All the tables in all the databases are 
> >> > myISAM
> >> > tables.
> >> >
> >> > There is ONE database on the server that we are trying to create/work
> >> > with that is all InnoDB tables.
> >> >
> >> > We are having serious problems with these tables.
> >> >
> >> > There are indications in the error logfile regarding what to do to 
> >> > try
> >> > and discover the root of these problems and fix them.  I will begin
> >> > pursuing those options shortly after posting this but as:
> >> >
> >> > 1) We're under a deadline with the application in question that
> >> > requires the InnoDB tables and
> >> >
> >> > 2) Although I'm the most qualified person, from a technical
> >> > standpoint, at my institution to try and get this fixed, that's not
> >> > saying much as I'm not THAT deeply technical.
> >> >
> >> > I thought I'd risk posting some of the logfile here to see what the
> >> > experts have to say.  Please accept my apologies for just coming here
> >> > and dumping this on the list's lap.
> >> >
> >> > I will try to figure it out myself but if anyone can help guide me
> >> > towards a solution in the meantime I'd be much obliged.
> >> >
> >> > Many thanks in advance.
> >> >
> >> > Joshua
> >> >
> >> > Here is the output of 'tail -100' on the error logfile:
> >> >
> >> > ------snip------
> >> >
> >> > InnoDB: log sequence number 0 241346488.
> >> > InnoDB: Doing recovery: scanned up to log sequence number 0 241346521
> >> > InnoDB: Last MySQL binlog file position 0 79, file name
> >> > ./beech-bin.000052
> >> > 041230 16:43:20  InnoDB: Flushing modified pages from the buffer
> >> > pool...
> >> > 041230 16:43:20  InnoDB: Started; log sequence number 0 241346521
> >> > InnoDB: !!! innodb_force_recovery is set to 5 !!!
> >> > 041230 16:43:20 [Warning] mysql.user table is not updated to new
> >> > password format; Disabling new password usage until
> >> > mysql_fix_privilege_tables is run
> >> > 041230 16:43:20 [Warning] Can't open and lock time zone table: Table
> >> > 'mysql.time_zone_leap_second' doesn't exist trying to live without
> >> > them
> >> > /usr/local/libexec/mysqld: ready for connections.
> >> > Version: '4.1.8a-log'  socket: '/tmp/mysql.sock'  port: 3306  Source
> >> > distribution
> >> > InnoDB: Error: trying to access page number 940269659 in space 0,
> >> > InnoDB: space name ./ibdata1,
> >> > InnoDB: which is outside the tablespace bounds.
> >> > InnoDB: Byte offset 0, len 16384, i/o type 10
> >> > 041230 16:46:01InnoDB: Assertion failure in thread 1123867568 in file
> >> > fil0fil.c line 3729
> >> > 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=16777216
> >> > read_buffer_size=131072
> >> > max_used_connections=2
> >> > max_connections=100
> >> > threads_connected=1
> >> > It is possible that mysqld could use up to
> >> > key_buffer_size + (read_buffer_size +
> >> > sort_buffer_size)*max_connections = 80383 K
> >> > bytes of memory
> >> > Hope that's ok; if not, decrease some variables in the equation.
> >> >
> >> > thd=0x89441a8
> >> > 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=0x42fcb1ac, backtrace may not be correct.
> >> > Stack range sanity check OK, backtrace follows:
> >> > 0x815f0cf
> >> > 0xffffe420
> >> > 0x82e71d5
> >> > 0x82e71d5
> >> > 0x82db68f
> >> > 0x830479f
> >> > 0x8304cc8
> >> > 0x82be800
> >> > 0x82d14a6
> >> > 0x82ccafb
> >> > 0x82cd865
> >> > 0x826232b
> >> > 0x827915a
> >> > 0x81fe924
> >> > 0x81ef33c
> >> > 0x820aead
> >> > 0x820b19d
> >> > 0x8201554
> >> > 0x8202739
> >> > 0x81796cb
> >> > 0x817c1b4
> >> > 0x817de5d
> >> > 0x817f137
> >> > 0x401619ed
> >> > 0x403519ca
> >> > 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 0x8951778 = DROP DATABASE `josh_Test`
> >> > thd->thread_id=5
> >> > 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
> >> > 041230 16:46:01  mysqld restarted
> >> > 041230 16:46:01  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...
> >> > 041230 16:46:01  InnoDB: Starting log scan based on checkpoint at
> >> > InnoDB: log sequence number 0 241346521.
> >> > InnoDB: Doing recovery: scanned up to log sequence number 0 241346554
> >> > InnoDB: Last MySQL binlog file position 0 79, file name
> >> > ./beech-bin.000053
> >> > 041230 16:46:01  InnoDB: Flushing modified pages from the buffer
> >> > pool...
> >> > 041230 16:46:01  InnoDB: Started; log sequence number 0 241346554
> >> > InnoDB: !!! innodb_force_recovery is set to 5 !!!
> >> > 041230 16:46:01 [Warning] mysql.user table is not updated to new
> >> > password format; Disabling new password usage until
> >> > mysql_fix_privilege_tables is run
> >> > 041230 16:46:01 [Warning] Can't open and lock time zone table: Table
> >> > 'mysql.time_zone_leap_second' doesn't exist trying to live without
> >> > them
> >> > /usr/local/libexec/mysqld: ready for connections.
> >> > Version: '4.1.8a-log'  socket: '/tmp/mysql.sock'  port: 3306  Source
> >> > distribution
> >> > -----snip----
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> >> > http://lists.mysql.com/mysql?unsub=1
> >> >
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: 
> >> http://lists.mysql.com/mysql?unsub=1
> >>
> >>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
> 

Thread
BIG InnoDB problems!jsf30 Dec
  • Re: BIG InnoDB problems!Eric Bergen31 Dec
    • Re: BIG InnoDB problems!jsf31 Dec
Re: BIG InnoDB problems!Heikki Tuuri31 Dec
  • Re: BIG InnoDB problems!jsf31 Dec
Re: BIG InnoDB problems!Heikki Tuuri3 Jan
  • Re: BIG InnoDB problems!jsf3 Jan
Re: BIG InnoDB problems!Heikki Tuuri3 Jan
  • Re: BIG InnoDB problems!jsf3 Jan
Re: BIG InnoDB problems!Heikki Tuuri3 Jan
Re: BIG InnoDB problems!Heikki Tuuri11 Jan