Re: BIG InnoDB problems!
Andy, - Alkuperäinen viesti - Lähettäjä: Andy Davidson [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lähetetty: Tuesday, January 04, 2005 5:02 PM Aihe: Re: BIG InnoDB problems! ...¨ On Mon, 3 Jan 2005, Heikki Tuuri wrote: the problem you had was serious corruption in the ibdata files. It can be caused by an InnoDB bug, an OS bug, faulty hardware, and also by an error of the database administrator. Linux kernels 2.4.18 seemed to have corruption issues. Hi, Heikki -- Is this something that you ONLY seem to see in 2.4.18, or is it all kernels up to 2.4.18 (excluding .15 of course), or all kernels since ? kernels 2.4.x, where x = 18. How have you reached a conclusion about this kernel version, please ? 2.4.18 is the stock kernel in recent Debians, for instance, and it seems that it's capable of stable MySQL/InnoDB environments. Though if I am wrong, I would be delighted to know why so that we can change things quickly. ;-) Red Hat kernels 2.4.18 are suspect. I have little feedback from Debian-2.4.18. -- Andy Davidson Systems Administrator Ebuyer UK Ltd., 201 Woodbourn Road, Sheffield, S9 3LR Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BIG InnoDB problems!
Joshua, the stack trace below shows that you are trying to drop a database? Why? 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. The .err file below starts from a situation where you have already set innodb_force_recovery to 5. 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. 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 0xe420 _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.47 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
Re: BIG InnoDB problems!
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 0xe420 _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.47 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
Re: BIG InnoDB problems!
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 [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com 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 [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 0xe420 _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.47 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
Re: BIG InnoDB problems!
well, since we were having so many problems with the InnoDB tables, we created a non-InnoDB version of the database in question... Basically we want to do whatever it will take to get InnoDB tables working in our environment. Any assistance you can offer towards this goal will be greatly appreciated! Cheers, J. On Mon, 3 Jan 2005 16:36:42 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com 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 [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 0xe420 _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.47
Re: BIG InnoDB problems!
Joshua, the problem you had was serious corruption in the ibdata files. It can be caused by an InnoDB bug, an OS bug, faulty hardware, and also by an error of the database administrator. Linux kernels 2.4.18 seemed to have corruption issues. There are no known corruption bugs of this type in InnoDB. If this happens again, please make a very detailed bug report, with the complete .err log, all the way from the birth of the installation up to the corruption. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: jsf [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Monday, January 03, 2005 4:56 PM Aihe: Re: BIG InnoDB problems! well, since we were having so many problems with the InnoDB tables, we created a non-InnoDB version of the database in question... Basically we want to do whatever it will take to get InnoDB tables working in our environment. Any assistance you can offer towards this goal will be greatly appreciated! Cheers, J. On Mon, 3 Jan 2005 16:36:42 +0200, Heikki Tuuri [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com 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 [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 0xe420 _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
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 0xe420 _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.47 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
Re: BIG InnoDB problems!
It looks like your tablespace is corrupt. Check this doc out: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html On Thu, 30 Dec 2004 17:21:40 -0500, jsf [EMAIL PROTECTED] wrote: 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.52 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 0xe420 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...
Re: BIG InnoDB problems!
Thanks Eric, We already tried levels 1, 3 and 5... no soap. I'm on the verge of thinking it's a bug. :-( J. On Fri, 31 Dec 2004 00:06:28 +, Eric Bergen [EMAIL PROTECTED] wrote: It looks like your tablespace is corrupt. Check this doc out: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html On Thu, 30 Dec 2004 17:21:40 -0500, jsf [EMAIL PROTECTED] wrote: 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.52 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 0xe420 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
Re: BIG InnoDB problems!
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.52 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 0xe420 0x82e71d5 0x82e71d5 0x82db68f 0x830479f 0x8304cc8 0x82be800 0x82d14a6 0x82ccafb 0x82cd865 0x826232b 0x827915a 0x81fe924
