Re: help: innodb database cannot recover
As a matter of dumb questions, what versions are the old and new mysqld; and are they running on the same platform (OS, 32/64 bit, ...) ? - Original Message - > From: "Peter" > To: "Reindl Harald" , mysql@lists.mysql.com > Sent: Friday, 21 June, 2013 10:04:27 AM > Subject: Re: help: innodb database cannot recover > > I removed "ib_logfile0" and "ib_logfile1" and restarted mysql with > innodb_force_recovery=1, > mysql keeps crashing and restart: > > > thd: 0x0 > 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... > stack_bottom = (nil) thread_stack 0x3 > /usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84bbbae] > /usr/libexec/mysqld(handle_segfault+0x4bc) [0x81eca1c] > [0xf57fe400] > [0xf57fe416] > /lib/libc.so.6(gsignal+0x51) [0x45a7bb71] > /lib/libc.so.6(abort+0x17a) [0x45a7d44a] > /usr/libexec/mysqld(fil_io+0x377) [0x83ba177] > /usr/libexec/mysqld() [0x83a257b] > /usr/libexec/mysqld(buf_read_page+0x282) [0x83a3132] > /usr/libexec/mysqld(buf_page_get_gen+0x351) [0x839c111] > /usr/libexec/mysqld(btr_cur_search_to_nth_level+0x3c1) [0x838ca31] > /usr/libexec/mysqld(row_search_index_entry+0x79) [0x840d3c9] > /usr/libexec/mysqld() [0x840bf97] > /usr/libexec/mysqld(row_purge_step+0x574) [0x840d1e4] > /usr/libexec/mysqld(que_run_threads+0x535) [0x83fa815] > /usr/libexec/mysqld(trx_purge+0x365) [0x8427e25] > /usr/libexec/mysqld(srv_master_thread+0x75b) [0x842009b] > /lib/libpthread.so.0() [0x45bf09e9] > /lib/libc.so.6(clone+0x5e) [0x45b2dc2e] > The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html > contains > information that should help you find out what is causing the crash. > 130620 00:47:21 mysqld_safe Number of processes running now: 0 > 130620 00:47:21 mysqld_safe mysqld restarted > InnoDB: Error: tablespace size stored in header is 456832 pages, but > InnoDB: the sum of data file sizes is only 262080 pages > InnoDB: Cannot start InnoDB. The tail of the system tablespace is > InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in > an > InnoDB: inappropriate way, removing ibdata files from there? > InnoDB: You can set innodb_force_recovery=1 in my.cnf to force > InnoDB: a startup if you are trying to recover a badly corrupt > database. > 130620 0:47:22 [ERROR] Plugin 'InnoDB' init function returned error. > 130620 0:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE > ENGINE failed. > > > if I set innodb_force_recovery=4 to restart mysql and then run > mysqldump, i got the following error: > mysqldump: Got error: 2013: Lost connection to MySQL server during > query when using LOCK TABLES > > it looks that all data from innodb is messed up and gone forever even > though *.frm is still there. > > Peter > -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help: innodb database cannot recover
>boah you *must not* remove ibdata1 >it contains the global tablespace even with file_per_table >"ib_logfile0" and "ib_logfile1" may be removed, but make sure you have >a as cinsistent as possible backup of the whole datadir I removed "ib_logfile0" and "ib_logfile1" and restarted mysql with innodb_force_recovery=1, mysql keeps crashing and restart: thd: 0x0 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... stack_bottom = (nil) thread_stack 0x3 /usr/libexec/mysqld(my_print_stacktrace+0x2e) [0x84bbbae] /usr/libexec/mysqld(handle_segfault+0x4bc) [0x81eca1c] [0xf57fe400] [0xf57fe416] /lib/libc.so.6(gsignal+0x51) [0x45a7bb71] /lib/libc.so.6(abort+0x17a) [0x45a7d44a] /usr/libexec/mysqld(fil_io+0x377) [0x83ba177] /usr/libexec/mysqld() [0x83a257b] /usr/libexec/mysqld(buf_read_page+0x282) [0x83a3132] /usr/libexec/mysqld(buf_page_get_gen+0x351) [0x839c111] /usr/libexec/mysqld(btr_cur_search_to_nth_level+0x3c1) [0x838ca31] /usr/libexec/mysqld(row_search_index_entry+0x79) [0x840d3c9] /usr/libexec/mysqld() [0x840bf97] /usr/libexec/mysqld(row_purge_step+0x574) [0x840d1e4] /usr/libexec/mysqld(que_run_threads+0x535) [0x83fa815] /usr/libexec/mysqld(trx_purge+0x365) [0x8427e25] /usr/libexec/mysqld(srv_master_thread+0x75b) [0x842009b] /lib/libpthread.so.0() [0x45bf09e9] /lib/libc.so.6(clone+0x5e) [0x45b2dc2e] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 130620 00:47:21 mysqld_safe Number of processes running now: 0 130620 00:47:21 mysqld_safe mysqld restarted InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:22 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. if I set innodb_force_recovery=4 to restart mysql and then run mysqldump, i got the following error: mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES it looks that all data from innodb is messed up and gone forever even though *.frm is still there. Peter
Re: help: innodb database cannot recover
Am 20.06.2013 23:47, schrieb Peter: >> Hello, >>> >>> I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >>> database directory) from one crashed machine to another. >>> I find that I cannot start database to get the database data any more. >> >> >>> How did you copy the database? >>> Manuel >> >> I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in >> linux and the whole database directory my_database_name into >> /var/lib/mysql/my_database_name, the same location as previous machine >> /var/lib/mysql/my_database_name >> >> peter > > If I remove ib_logfile0 ib_logfile1 ibdata1, then restart mysql. > still I am unable to open the .frm file though it exists. Here is the error I > got boah you *must not* remove ibdata1 it contains the global tablespace even with file_per_table "ib_logfile0" and "ib_logfile1" may be removed, but make sure you have a as cinsistent as possible backup of the whole datadir signature.asc Description: OpenPGP digital signature
Re: help: innodb database cannot recover
>Hello, >> >>I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >>database directory) from one crashed machine to another. >>I find that I cannot start database to get the database data any more. > > >>How did you copy the database? >>Manuel > >I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in >linux and the whole database directory my_database_name into >/var/lib/mysql/my_database_name, the same location as previous machine >/var/lib/mysql/my_database_name > > peter If I remove ib_logfile0 ib_logfile1 ibdata1, then restart mysql. still I am unable to open the .frm file though it exists. Here is the error I got. 130620 14:28:18 [ERROR] Cannot find or open table my_database_name/my_database_table from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html how you can resolve the problem. .frm files are all there and just don't know how to open them. Peter
Re: help: innodb database cannot recover
2013/6/20 Peter > >2013/6/20 Peter > >Hello, >> >>I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >>database directory) from one crashed machine to another. >>I find that I cannot start database to get the database data any more. > > >>How did you copy the database? >>Manuel > >I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in >linux and the whole database directory my_database_name into >/var/lib/mysql/my_database_name, the same location as previous machine >/var/lib/mysql/my_database_name > > > > >Was MySQL daemon stopped in both machines? It must be stopped in both of them. >If you cannot afford stopping your mysql, you might want to take a look at >xtrabackup: http://www.percona.com/doc/percona-xtrabackup/2.1/ yes, the mysql daemon was stopped during the copy. actually, the old machine cannot run anymore, and I copy it over from another machine by moving the disk to another machine. Peter
Re: help: innodb database cannot recover
Am 20.06.2013 15:18, schrieb Peter: >> >>> I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >>> database directory) from one crashed machine to another. >>> I find that I cannot start database to get the database data any more. > >>> How did you copy the database? >>> Manuel > >> I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in >> linux and the whole database directory my_database_name into >> /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name >and your "my.cnf"? >you hardly can move innodb around without the same settings sorry for the previous post missing the file. the my.cnf file is same in both machines.as follows: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Peter
Re: help: innodb database cannot recover
Am 20.06.2013 15:18, schrieb Peter: >> >>> I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >>> database directory) from one crashed machine to another. >>> I find that I cannot start database to get the database data any more. > >>> How did you copy the database? >>> Manuel > >> I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in >> linux and the whole database directory my_database_name into >> /var/lib/mysql/my_database_name, the same location as previous machine >> /var/lib/mysql/my_database_name >and your "my.cnf"? >you hardly can move innodb around without the same settings the my.cnf file is same as follows:
Re: help: innodb database cannot recover
2013/6/20 Peter > > 2013/6/20 Peter > > Hello, > > I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole > database directory) from one crashed machine to another. > I find that I cannot start database to get the database data any more. > > > >How did you copy the database? > >Manuel > > I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in > linux and the whole database directory my_database_name into > /var/lib/mysql/my_database_name, the same location as previous machine > /var/lib/mysql/my_database_name > > > Was MySQL daemon stopped in both machines? It must be stopped in both of them. If you cannot afford stopping your mysql, you might want to take a look at xtrabackup: http://www.percona.com/doc/percona-xtrabackup/2.1/ Manuel.
Re: help: innodb database cannot recover
2013/6/20 Peter Hello, > >I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >database directory) from one crashed machine to another. >I find that I cannot start database to get the database data any more. >How did you copy the database? >Manuel I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in linux and the whole database directory my_database_name into /var/lib/mysql/my_database_name, the same location as previous machine /var/lib/mysql/my_database_name Thanks. Peter
Re: help: innodb database cannot recover
Am 20.06.2013 15:18, schrieb Peter: > 2013/6/20 Peter > > Hello, >> >> I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole >> database directory) from one crashed machine to another. >> I find that I cannot start database to get the database data any more. > >> How did you copy the database? >> Manuel > > I copy the files ib_logfile0 ib_logfile1 ibdata1 into /var/lib/mysql in > linux and the whole database directory my_database_name into > /var/lib/mysql/my_database_name, the same location as previous machine > /var/lib/mysql/my_database_name and your "my.cnf"? you hardly can move innodb around without the same settings signature.asc Description: OpenPGP digital signature
Re: help: innodb database cannot recover
2013/6/20 Peter > Hello, > > I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole > database directory) from one crashed machine to another. > I find that I cannot start database to get the database data any more. How did you copy the database? Manuel
Re: help: innodb database cannot recover
Am 20.06.2013 10:11, schrieb Peter: > 130620 00:47:08 mysqld_safe Starting mysqld daemon with databases from > /var/lib/mysql > InnoDB: Error: tablespace size stored in header is 456832 pages, but > InnoDB: the sum of data file sizes is only 262080 pages > InnoDB: Cannot start InnoDB. The tail of the system tablespace is > InnoDB: missing > > is there a way to start the database again? > Thanks for your help in advance restore your backups that is one reason why replication exists to have a slave which a) does not die with the master at a complete crash and b) have a machine where offline backups could be pulled from without any downtime signature.asc Description: OpenPGP digital signature
help: innodb database cannot recover
Hello, I copied innodb database (ib_logfile0 ib_logfile1 ibdata1 and the whole database directory) from one crashed machine to another. I find that I cannot start database to get the database data any more. I tried innodb_force_recovery=1 or innodb_force_recovery=4, it doesn't help. Here is the error message I got: 130620 00:47:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql InnoDB: Error: tablespace size stored in header is 456832 pages, but InnoDB: the sum of data file sizes is only 262080 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. 130620 0:47:08 [ERROR] Plugin 'InnoDB' init function returned error. 130620 0:47:08 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 130620 0:47:09 [Note] Event Scheduler: Loaded 0 events 130620 0:47:09 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.52' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution InnoDB: Error: trying to access page number 10929 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. 130620 0:47:09 InnoDB: Assertion failure in thread 2985618288 in file fil/fil0fil.c line 4123 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.1/en/forcing-recovery.html InnoDB: about forcing recovery. 130620 0:47:09 - mysqld got signal 6 ; 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. is there a way to start the database again? Thanks for your help in advance. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
