Hi Heikki, Please see below...
On Mon, 3 Jan 2005 15:14:12 +0200, Heikki Tuuri <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> > Kopio: <mysql@lists.mysql.com> > 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 > > <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > >> 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/[EMAIL PROTECTED] > >> > > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >> > >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
