Форум пользователей MySQL

Всех приветствую.
Вчера вдруг упал mysql и больше не грузится из-за базы одной
mysql не стартует, а при старте сыпятся ошибки типа:

Код:
Jan 22 11:00:11 lt mysqld_safe[12478]: started
Jan 22 11:00:11 lt mysqld[12482]: 110122 11:00:11  InnoDB: Database was not shut down normally!
Jan 22 11:00:11 lt mysqld[12482]: InnoDB: Starting crash recovery.
Jan 22 11:00:11 lt mysqld[12482]: InnoDB: Reading tablespace information from the .ibd files...
Jan 22 11:00:11 lt mysqld[12482]: InnoDB: Restoring possible half-written data pages from the doublewrite
Jan 22 11:00:11 lt mysqld[12482]: InnoDB: buffer...
Jan 22 11:00:11 lt mysqld[12482]: 110122 11:00:11  InnoDB: Starting log scan based on checkpoint at
Jan 22 11:00:11 lt mysqld[12482]: InnoDB: log sequence number 254 2518577586.
Jan 22 11:00:12 lt named[4507]: client 188.128.1.98#43285: query (cache) '90056-b7208518/A/IN' denied
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: Doing recovery: scanned up to log sequence number 254 2523587322
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: 1 transaction(s) which must be rolled back or cleaned up
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: in total 4 row operations to undo
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: Trx id counter is 0 979812096
Jan 22 11:00:12 lt mysqld[12482]: 110122 11:00:12  InnoDB: Starting an apply batch of log records to the database...
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: Progress in percents: 0 1 2 3 4 5 InnoDB: ERROR: Submit the output to http://bugs.mysql.com
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: ibuf cursor restoration fails!
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: ibuf record inserted to page 10303017
Jan 22 11:00:12 lt mysqld[12482]: PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
Jan 22 11:00:12 lt mysqld[12482]:  0: len 4; hex 00000000; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 009d3629; asc   6);; 3: len 13; hex 00860300040000860300048000; asc              ;; 4: len 4; hex 80000e72; asc    r;; 5: len 4; hex 9c764a58; asc  vJX;;
Jan 22 11:00:12 lt mysqld[12482]: PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
Jan 22 11:00:12 lt mysqld[12482]:  0: len 4; hex 00000000; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 009d3629; asc   6);; 3: len 13; hex 00860300040000860300048000; asc              ;; 4: len 4; hex 80000e72; asc    r;; 5: len 4; hex 9c764a58; asc  vJX;;
Jan 22 11:00:12 lt mysqld[12482]: DATA TUPLE: 3 fields;
Jan 22 11:00:12 lt mysqld[12482]:  0: len 4; hex 00000000; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 009d3629; asc   6);;
Jan 22 11:00:12 lt mysqld[12482]: PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
Jan 22 11:00:12 lt mysqld[12482]:  0: len 4; hex 00000000; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 009d3629; asc   6);; 3: len 13; hex 00860300040000860300048000; asc              ;; 4: len 4; hex 80000e72; asc    r;; 5: len 4; hex 9c777dcd; asc  w} ;;
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: Validating insert buffer tree:
Jan 22 11:00:12 lt mysqld[12482]: 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 InnoDB: Records in wrong order on page 322007index CLUST_IND of table SYS_IBUF_TABLE_0
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: previous record PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
Jan 22 11:00:12 lt mysqld[12482]:  0: len 4; hex 00000000; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 009d362b; asc   6+;; 3: len 13; hex 00860300040000860300048000; asc              ;; 4: len 4; hex 80000a77; asc    w;; 5: len 4; hex 9c720c5e; asc  r ^;;
Jan 22 11:00:12 lt mysqld[12482]:
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: record PHYSICAL RECORD: n_fields 6; 1-byte offsets; info bits 0
Jan 22 11:00:12 lt mysqld[12482]:  0: len 4; hex 00000000; asc     ;; 1: len 1; hex 00; asc  ;; 2: len 4; hex 009d3629; asc   6);; 3: len 13; hex 00860300040000860300048000; asc              ;; 4: len 4; hex 80000e72; asc    r;; 5: len 4; hex 9c714571; asc  qEq;;
Jan 22 11:00:12 lt mysqld[12482]:
Jan 22 11:00:12 lt mysqld[12482]: InnoDB: Apparent corruption in page 322007 in index CLUST_IND of table SYS_IBUF_TABLE_0
Jan 22 11:00:12 lt mysqld[12482]: 110122 11:00:12  InnoDB: Page dump in ascii and hex (16384 bytes):


далее куча всякой фигни еще
и в конце:

Код:
Jan 22 11:00:13 lt mysqld[12482]: 110122 11:00:13  InnoDB: Page checksum 2439518664, prior-to-4.0.14-form checksum 3748383345
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: stored checksum 2439518664, prior-to-4.0.14-form stored checksum 3748383345
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: Page lsn 254 2522608615, low 4 bytes of lsn at page end 2522608615
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: Page number (if stored to page already) 1355194,
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: Page may be an index page where index id is 4294967295 0
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: (index CLUST_IND of table SYS_IBUF_TABLE_0)
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: Error in page 1355194 of index CLUST_IND of table SYS_IBUF_TABLE_0
Jan 22 11:00:13 lt mysqld[12482]: 110122 11:00:13InnoDB: Assertion failure in thread 2716576656 in file ibuf0ibuf.c line 2967
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: We intentionally generate a memory trap.
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: If you get repeated assertion failures or crashes, even
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: immediately after the mysqld startup, there may be
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: corruption in the InnoDB tablespace. Please refer to
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: http://dev.mysql.com/doc/refman/5.0/en/ … overy.html
Jan 22 11:00:13 lt mysqld[12482]: InnoDB: about forcing recovery.
Jan 22 11:00:13 lt mysqld[12482]: 110122 11:00:13 - mysqld got signal 11;
Jan 22 11:00:13 lt mysqld[12482]: This could be because you hit a bug. It is also possible that this binary
Jan 22 11:00:13 lt mysqld[12482]: or one of the libraries it was linked against is corrupt, improperly built,
Jan 22 11:00:13 lt mysqld[12482]: or misconfigured. This error can also be caused by malfunctioning hardware.
Jan 22 11:00:13 lt mysqld[12482]: We will try our best to scrape up some info that will hopefully help diagnose
Jan 22 11:00:13 lt mysqld[12482]: the problem, but since we have already crashed, something is definitely wrong
Jan 22 11:00:13 lt mysqld[12482]: and this may fail.
Jan 22 11:00:13 lt mysqld[12482]:
Jan 22 11:00:13 lt mysqld[12482]: key_buffer_size=0
Jan 22 11:00:13 lt mysqld[12482]: read_buffer_size=67104768
Jan 22 11:00:13 lt mysqld[12482]: max_used_connections=0
Jan 22 11:00:13 lt mysqld[12482]: max_connections=100
Jan 22 11:00:13 lt mysqld[12482]: threads_connected=0
Jan 22 11:00:13 lt mysqld[12482]: It is possible that mysqld could use up to
Jan 22 11:00:13 lt mysqld[12482]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3407471 K
Jan 22 11:00:13 lt mysqld[12482]: bytes of memory
Jan 22 11:00:13 lt mysqld[12482]: Hope that's ok; if not, decrease some variables in the equation.
Jan 22 11:00:13 lt mysqld[12482]:
Jan 22 11:00:13 lt mysqld[12482]: thd=(nil)
Jan 22 11:00:13 lt mysqld[12482]: Attempting backtrace. You can use the following information to find out
Jan 22 11:00:13 lt mysqld[12482]: where mysqld died. If you see no messages after this, something went
Jan 22 11:00:13 lt mysqld[12482]: terribly wrong...
Jan 22 11:00:13 lt mysqld[12482]: Cannot determine thread, fp=0xa1eb99e8, backtrace may not be correct.
Jan 22 11:00:13 lt mysqld[12482]: Stack range sanity check OK, backtrace follows:
Jan 22 11:00:13 lt mysqld[12482]: 0x81ea513
Jan 22 11:00:13 lt mysqld[12482]: 0x8344906
Jan 22 11:00:13 lt mysqld[12482]: 0x83461aa
Jan 22 11:00:13 lt mysqld[12482]: 0x83b1a0b
Jan 22 11:00:13 lt mysqld[12482]: 0x83dbcfb
Jan 22 11:00:13 lt mysqld[12482]: 0x83245e8
Jan 22 11:00:13 lt mysqld[12482]: 0xb7ecd4fb
Jan 22 11:00:13 lt mysqld[12482]: 0xb7ce1e5e
Jan 22 11:00:13 lt mysqld[12482]: New value of fp=(nil) failed sanity check, terminating stack trace!
Jan 22 11:00:13 lt mysqld[12482]: Please read http://dev.mysql.com/doc/mysql/en/using … trace.html and follow instructions on how to resolve the stack trace. Resolved
Jan 22 11:00:13 lt mysqld[12482]: stack trace is much more helpful in diagnosing the problem, so please do
Jan 22 11:00:13 lt mysqld[12482]: resolve it
Jan 22 11:00:13 lt mysqld[12482]: The manual page at http://www.mysql.com/doc/en/Crashing.html contains
Jan 22 11:00:13 lt mysqld[12482]: information that should help you find out what is causing the crash.


запустил мускул с
[mysqld]
innodb_force_recovery = 4

стартанул, но есть проблемы

Код:
Jan 22 11:00:39 lt mysqld_safe[12715]: started
Jan 22 11:00:39 lt mysqld[12719]: 110122 11:00:39  InnoDB: Database was not shut down normally!
Jan 22 11:00:39 lt mysqld[12719]: InnoDB: Starting crash recovery.
Jan 22 11:00:39 lt mysqld[12719]: InnoDB: Reading tablespace information from the .ibd files...
Jan 22 11:00:39 lt mysqld[12719]: InnoDB: Restoring possible half-written data pages from the doublewrite
Jan 22 11:00:39 lt mysqld[12719]: InnoDB: buffer...
Jan 22 11:00:39 lt mysqld[12719]: 110122 11:00:39  InnoDB: Starting log scan based on checkpoint at
Jan 22 11:00:39 lt mysqld[12719]: InnoDB: log sequence number 254 2518577586.
Jan 22 11:00:40 lt mysqld[12719]: InnoDB: Doing recovery: scanned up to log sequence number 254 2523587322
Jan 22 11:00:40 lt mysqld[12719]: InnoDB: 1 transaction(s) which must be rolled back or cleaned up
Jan 22 11:00:40 lt mysqld[12719]: InnoDB: in total 4 row operations to undo
Jan 22 11:00:40 lt mysqld[12719]: InnoDB: Trx id counter is 0 979812096
Jan 22 11:00:40 lt mysqld[12719]: 110122 11:00:40  InnoDB: Starting an apply batch of log records to the database...
Jan 22 11:00:46 lt mysqld[12719]: InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
Jan 22 11:00:46 lt mysqld[12719]: InnoDB: Apply batch completed
Jan 22 11:00:46 lt mysqld[12719]: InnoDB: Last MySQL binlog file position 0 4280554, file name /var/log/mysql/mysql-bin.003027
Jan 22 11:00:46 lt mysqld[12719]: 110122 11:00:46  InnoDB: Started; log sequence number 254 2523587322
Jan 22 11:00:46 lt mysqld[12719]: InnoDB: !!! innodb_force_recovery is set to 4 !!!
Jan 22 11:00:46 lt mysqld[12719]: 110122 11:00:46 [Note] /usr/sbin/mysqld: ready for connections.
Jan 22 11:00:46 lt mysqld[12719]: Version: '5.0.38-Ubuntu_0ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 7.04 distribution
Jan 22 11:00:46 lt /etc/mysql/debian-start[12814]: Upgrading MySQL tables if necessary.
Jan 22 11:00:49 lt /etc/mysql/debian-start[12823]: Checking for crashed MySQL tables.
Jan 22 11:01:03 lt mysqld[12719]: Error: index `dhs_last_update_date_status_idx` of table `UTM5/dhs_sessions_log` contains 561252 entries, should be 561291


соответственно говорит что в индексах проблема в таблице dhs_sessions_log
но удалить их нельзя

Код:
mysql> show index from dhs_sessions_log;
+------------------+------------+---------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name                        | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+---------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| dhs_sessions_log |          0 | PRIMARY                         |            1 | id               | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| dhs_sessions_log |          1 | dhs_last_update_date_status_idx |            1 | last_update_date | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| dhs_sessions_log |          1 | dhs_last_update_date_status_idx |            2 | Acct_Status_Type | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+------------------+------------+---------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE dhs_sessions_log DROP INDEX dhs_last_update_date_status_idx;
ERROR 1030 (HY000): Got error -1 from storage engine


так как стоит innodb_force_recovery = 4


Код:
Jan 22 11:03:17 lt mysqld[12719]: InnoDB: A new raw disk partition was initialized or
Jan 22 11:03:17 lt mysqld[12719]: InnoDB: innodb_force_recovery is on: we do not allow
Jan 22 11:03:17 lt mysqld[12719]: InnoDB: database modifications by the user. Shut down
Jan 22 11:03:17 lt mysqld[12719]: InnoDB: mysqld and edit my.cnf so that newraw is replaced
Jan 22 11:03:17 lt mysqld[12719]: InnoDB: with raw, and innodb_force_... is removed.


подскажите куда копнуть плиз?
как поправить индексы?

Код:
# mysqlcheck UTM5 dhs_sessions_log
UTM5.dhs_sessions_log
error    : Corrupt

# mysqlcheck -r UTM5 dhs_sessions_log
UTM5.dhs_sessions_log
note     : The storage engine for the table doesn't support repair


Код:

mysql> SELECT * FROM dhs_sessions_log INTO OUTFILE '/DB/dhs_sessions_log.sql';
ERROR 1034 (HY000): Incorrect key file for table 'dhs_sessions_log'; try to repair it