mysql:8980
From: KiyokoAsai <KiyokoAsai <kiyoasai@xxxxxxxxxx>>
Date: Sun, 14 Mar 2004 23:56:34 +0900
Subject: [mysql 08980] データベースの破損からの復旧方法について
はじめまして、浅井と申します。 mysqlについて質問できる場所を探していたらここに辿りつきました。 早速、識者の方々へどうしても教えていただきたいことがあり、投稿 させていただきました。宜しくお願いします。 レンタルしているサーバー上で会社の簡単なグループウェアを作成 しました。サーバーを移転するまでまったく問題なく動作していまし たがサーバーを移転後にmysqldumpで取り出したおいたデータを 同じくmysqldumpにてリストアいたしました。その後、サーバーを shutdownコマンドにてリスタートさせた後、apache,mysqldを起動 後、何故かデータベースとプログラムの接続がうまくいかず、 おかしいと思い、データベースのログを見たところ下記のような エラーが発生していました。 ※：サーバー環境、mysqlの情報は下の方に書いてあります。 ※：トランザクションを使っています(BDB) 040312 13:16:03 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 040312 13:16:08 bdb: Recovery function for LSN 11 7295565 failed 040312 13:16:08 Can't init databases 040312 13:16:08 Aborting 040312 13:16:08 /usr/local/mysql/libexec/mysqld: Shutdown Complete 040312 13:16:08 mysqld ended そこで、/db/mysql/data/内のlog.0000000091というログファイルを削除後 mysqldを起動したらうまく起動し安堵していた矢先、下記のエラーログ を時々吐きつづけるようになり、何故かグループウェア上でトランザクション を使い掲示板への投稿するなどすると時々、データベースエラーが発生して 投稿できなくなる現象が発生します。すべて投稿できなくなるわけでは なく時々です 040312 13:19:23 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 040312 13:19:24 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43902 InnoDB: Doing recovery: scanned up to log sequence number 0 43902 040312 13:19:24 InnoDB: Flushing modified pages from the buffer pool... 040312 13:19:24 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.17-max-log' socket: '/tmp/mysql.sock' port: 3306 040312 14:53:33 bdb: log_flush: LSN past current end-of-log ・ 省略 ・ 040312 15:22:40 read_const: Got error 22 when reading table./hogehoge/T_MAILBOX 040312 15:24:32 bdb: log_flush: LSN past current end-of-log ・ 省略 ・ 040314 22:53:36 bdb: Log sequence error: page LSN 1:45400; previous LSN 1 6381576 040314 22:53:36 bdb: Log sequence error: page LSN 1:64620; previous LSN 1 6380600 ・ 省略 ・ 040314 22:53:36 bdb: Log sequence error: page LSN 1:53577; previous LSN 5 2706548 エラーログから察するにデータベースが破損しているかと思い、OPTIMIZE TALBE を使い最適化をしようと実行したところ今度は下記のエラーが出て、失敗してし まったようなのですが、こういった場合、どのような処置をしたらいいのでしょ うか？ 040314 22:59:31 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 040314 22:59:32 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.17-max' socket: '/tmp/mysql.sock' port: 3306 040314 23:00:26 bdb: log_flush: LSN past current end-of-log ・ 省略 ・ Number of processes running now: 0 040314 23:00:26 mysqld restarted 040314 23:00:26 bdb: log_flush: LSN past current end-of-log 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=0x889bce0 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=0x41f9db74, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8102446 0x4003d8f8 (nil) 0x8202502 0x8202502 0x8202152 0x8166543 0x8174a0d 0x8171cae 0x811136e 0x8112f69 0x810df0d 0x810da88 0x810d401 0x40038484 0x420df147 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/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 0x88981d0 = OPTIMIZE TABLE T_MAILBOX thd->thread_id=18 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 040314 23:00:26 mysqld restarted Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line 040314 23:00:26 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.0.17-max' socket: '/tmp/mysql.sock' port: 3306 ■ＬＩＮＵＸサーバーの環境情報 [root@ns data]# uname -a Linux ns.xxxxxxxx.net 2.4.20-30.9 #1 Wed Feb 4 20:51:48 EST 2004 i686 i686 i386 GNU/Linux [root@ns data]# ls -la /lib/libc* -rwxr-xr-x 1 root root 1561228 11月 13 09:56 /lib/libc-2.3.2.so lrwxrwxrwx 1 root root 13 2月 1 14:33 /lib/libc.so.6 -> libc-2.3.2.so lrwxrwxrwx 1 root root 11 2月 1 22:05 /lib/libcap.so -> libcap.so.1 lrwxrwxrwx 1 root root 14 2月 1 22:03 /lib/libcap.so.1 -> libcap.so.1.10 -rwxr-xr-x 1 root root 10916 1月 25 2003 /lib/libcap.so.1.10 lrwxrwxrwx 1 root root 17 2月 1 21:59 /lib/libcom_err.so.2 -> libcom_err.so.2.0 -rwxr-xr-x 1 root root 5544 1月 25 2003 /lib/libcom_err.so.2.0 -rwxr-xr-x 1 root root 23688 11月 13 09:56 /lib/libcrypt-2.3.2.so lrwxrwxrwx 1 root root 17 2月 1 14:33 /lib/libcrypt.so.1 -> libcrypt-2.3.2.so -rwxr-xr-x 1 root root 971612 9月 25 21:32 /lib/libcrypto.so.0.9.7a lrwxrwxrwx 1 root root 19 2月 1 22:00 /lib/libcrypto.so.4 -> libcrypto.so.0.9.7a [root@ns data]# ls -la /lib/libpth* -rwxr-xr-x 1 root root 103388 11月 13 09:56 /lib/libpthread-0.10.so lrwxrwxrwx 1 root root 18 2月 1 14:33 /lib/libpthread.so.0 -> libpthread-0.10.so [root@ns data]# cat /etc/ld.so.conf /usr/kerberos/lib /usr/X11R6/lib /usr/local/BerkeleyDB.4.1/lib/ /usr/local/lib [root@ns data]# gcc --version gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Copyright (C) 2002 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. [root@ns data]# gcc -v /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs から spec を読み込み中 コンフィグオプション: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-ch ecking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux スレッドモデル: posix gcc バージョン 3.2.2 20030222 (Red Hat Linux 3.2.2-5) [root@ns data]# gcc -print-libgcc-file-name /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/libgcc.a [root@ns data]# gcc -print-search-dirs インストール: /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/ プログラム: =/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/:/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/:/usr/lib/gcc-lib/i386-redhat-linux/:/usr/lib/gcc/i386 -redhat-linux/3.2.2/:/usr/lib/gcc/i386-redhat-linux/:/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../../i386-redhat-linux/bin/i386-redhat-linux/3.2. 2/:/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../../i386-redhat-linux/bin/ ライブラリ: =/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/:/usr/lib/gcc/i386-redhat-linux/3.2.2/:/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../../i386 -redhat-linux/lib/i386-redhat-linux/3.2.2/:/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../../i386-redhat-linux/lib/:/usr/lib/gcc-lib/i386-redhat-li nux/3.2.2/../../../i386-redhat-linux/3.2.2/:/usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../:/lib/i386-redhat-linux/3.2.2/:/lib/:/usr/lib/i386-redha t-linux/3.2.2/:/usr/lib/ [mysql@ns mysql]$ mysqlbug Finding system information for a MySQL bug report test -x Could not find a text editor. (tried emacs) You can change editor by setting the environment variable VISUAL. If your shell is a bourne shell (sh) do VISUAL=your_editors_name; export VISUAL If your shell is a C shell (csh) do setenv VISUAL your_editors_name [mysql@ns mysql]$ ldd /usr/local/mysql/bin/mysql libncurses.so.5 => /usr/lib/libncurses.so.5 (0x4001e000) libmysqlclient.so.12 => /usr/local/mysql/lib/mysql/libmysqlclient.so.12 (0x4005d000) libz.so.1 => /usr/lib/libz.so.1 (0x40098000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x400a6000) libnsl.so.1 => /lib/libnsl.so.1 (0x400d3000) libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x400e8000) libm.so.6 => /lib/tls/libm.so.6 (0x4019c000) libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x401be000) libc.so.6 => /lib/tls/libc.so.6 (0x42000000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000) [mysql@ns mysql]$ ldd /usr/local/mysql/lib/mysql/libmysqlclient.so libz.so.1 => /usr/lib/libz.so.1 (0x40043000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x40051000) libnsl.so.1 => /lib/libnsl.so.1 (0x4007e000) libm.so.6 => /lib/tls/libm.so.6 (0x40093000) libc.so.6 => /lib/tls/libc.so.6 (0x42000000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x80000000) ■mysql情報 mysql> SHOW STATUS; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Bytes_received | 483188 | | Bytes_sent | 43903004 | | Com_admin_commands | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 22 | | Com_change_db | 414 | | Com_change_master | 0 | | Com_check | 0 | | Com_commit | 22 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_delete | 56 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 330 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 169 | | Com_optimize | 0 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 3364 | | Com_set_option | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 0 | | Com_show_databases | 0 | | Com_show_fields | 0 | | Com_show_grants | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_innodb_status | 0 | | Com_show_tables | 0 | | Com_show_variables | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 169 | | Com_update | 227 | | Connections | 416 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 0 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 14 | | Handler_read_first | 9 | | Handler_read_key | 1555 | | Handler_read_next | 125 | | Handler_read_prev | 0 | | Handler_read_rnd | 1601 | | Handler_read_rnd_next | 37763130 | | Handler_rollback | 0 | | Handler_update | 189 | | Handler_write | 330 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | | Max_used_connections | 3 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 25 | | Open_files | 0 | | Open_streams | 0 | | Opened_tables | 31 | | Questions | 5189 | | Qcache_queries_in_cache | 0 | | Qcache_inserts | 0 | | Qcache_hits | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_free_memory | 0 | | Qcache_free_blocks | 0 | | Qcache_total_blocks | 0 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 1999 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 1601 | | Sort_scan | 172 | | Table_locks_immediate | 3809 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 415 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 1288 | +--------------------------+----------+ 132 rows in set (0.00 sec) mysql> SHOW VARIABLES; +---------------------------------+------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------+ | back_log | 50 | | basedir | /usr/local/mysql/ | | bdb_cache_size | 4194304 | | bdb_log_buffer_size | 32768 | | bdb_home | /db/mysql/data/ | | bdb_max_lock | 10000 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /tmp/ | | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (December 14, 2003) | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set | ujis | | character_sets | ujis big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian k oi8_ru koi8_ukr latin1 latin1_de latin2 latin5 sjis swe7 tis620 usa7 win1250 win1251ukr win1251 | | concurrent_insert | ON | | connect_timeout | 5 | | convert_character_set | | | datadir | /db/mysql/data/ | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_min_word_len | 4 | | ft_max_word_len | 254 | | ft_max_word_len_for_sort | 20 | | ft_stopword_file | (built-in) | | have_bdb | YES | | have_crypt | YES | | have_innodb | YES | | have_isam | YES | | have_raid | NO | | have_symlink | YES | | have_openssl | NO | | have_query_cache | YES | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 1 | | innodb_fast_shutdown | ON | | innodb_flush_method | | | innodb_lock_wait_timeout | 50 | | innodb_log_arch_dir | ./ | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | innodb_max_dirty_pages_pct | 90 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 16777216 | | language | /usr/local/mysql/share/mysql/english/ | | large_files_support | ON | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_update | OFF | | log_bin | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | OFF | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_table_names | OFF | | max_allowed_packet | 1047552 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_user_connections | 0 | | max_tmp_tables | 32 | | max_write_lock_count | 4294967295 | | myisam_max_extra_sort_file_size | 268435456 | | myisam_max_sort_file_size | 2147483647 | | myisam_repair_threads | 1 | | myisam_recover_options | OFF | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 8192 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | open_files_limit | 1024 | | pid_file | /db/mysql/data/ns.hogehoge.net.pid | | log_error | | | port | 3306 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_size | 0 | | query_cache_type | ON | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | rpl_recovery_rank | 0 | | server_id | 1 | | slave_net_timeout | 3600 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slow_launch_time | 2 | | socket | /tmp/mysql.sock | | sort_buffer_size | 524280 | | sql_mode | 0 | | table_cache | 64 | | table_type | MYISAM | | thread_cache_size | 0 | | thread_stack | 196608 | | tx_isolation | REPEATABLE-READ | | timezone | JST | | tmp_table_size | 33554432 | | tmpdir | /tmp/ | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | version | 4.0.17-max | | version_comment | Source distribution | | wait_timeout | 28800 | +---------------------------------+------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------+ 135 rows in set (0.00 sec)