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
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 one2001...@yahoo.com To: Reindl Harald h.rei...@thelounge.net, 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
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
Re: help: innodb database cannot recover
2013/6/20 Peter one2001...@yahoo.com 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 15:18, schrieb Peter: 2013/6/20 Peter one2001...@yahoo.com 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 one2001...@yahoo.com 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
2013/6/20 Peter one2001...@yahoo.com 2013/6/20 Peter one2001...@yahoo.com 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
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
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
2013/6/20 Peter one2001...@yahoo.com 2013/6/20 Peter one2001...@yahoo.com 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
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
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
