[MySQL] 페이지 크래쉬 현상

테이블을 조회하였을 때 DB가 죽는 현상이 확인이 되었다.

mysql errlog를 확인하였을 때 아래와 같은 코드가 확인이 되었는데 느낌상 페이지가 깨진 것 같았으나 정확한 이슈를 찾기가 어려웠다.

2024-06-10 15:44:03 0x7f1a33000700  InnoDB: Assertion failure in thread 139750501517056 in file fsp0fsp.cc line 3787
InnoDB: Failing assertion: xdes_get_state(descr, mtr) == XDES_FSEG
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
06:44:03 UTC - 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=54
max_threads=5000
thread_count=55
connection_count=54
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1995106 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f1910000d80
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 = 7f1a32fffe88 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf89a75]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4b9)[0x8028f9]
/lib64/libpthread.so.0(+0x12cf0)[0x7f1fc9622cf0]
/lib64/libc.so.6(gsignal+0x10f)[0x7f1fc7f25aff]
/lib64/libc.so.6(abort+0x127)[0x7f1fc7ef8ea5]
/usr/local/mysql/bin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7f193e]
/usr/local/mysql/bin/mysqld[0x1337bf1]
/usr/local/mysql/bin/mysqld(_Z14fseg_free_stepPhbP5mtr_t+0x29b)[0x133ba9b]
/usr/local/mysql/bin/mysqld[0x128cd1d]
/usr/local/mysql/bin/mysqld(_Z18btr_free_if_existsRK9page_id_tRK11page_size_tmP5mtr_t+0xec)[0x128d04c]
/usr/local/mysql/bin/mysqld(_Z20dict_drop_index_treePhP10btr_pcur_tP5mtr_t+0x1ad)[0x12e804d]
/usr/local/mysql/bin/mysqld[0x122351a]
/usr/local/mysql/bin/mysqld(_Z7row_updP10upd_node_tP9que_thr_t+0x6f)[0x1224bdf]
/usr/local/mysql/bin/mysqld(_Z12row_upd_stepP9que_thr_t+0x103)[0x1224ed3]
/usr/local/mysql/bin/mysqld(_Z15que_run_threadsP9que_thr_t+0x5e8)[0x11b8b38]
/usr/local/mysql/bin/mysqld(_Z12que_eval_sqlP11pars_info_tPKcmP5trx_t+0x6e)[0x11b92ce]
/usr/local/mysql/bin/mysqld(_Z24row_drop_table_for_mysqlPKcP5trx_tbbP12dict_table_t+0xae2)[0x11ef042]
/usr/local/mysql/bin/mysqld(_Z27row_drop_database_for_mysqlPKcP5trx_tPm+0x472)[0x11f3d82]
/usr/local/mysql/bin/mysqld(_ZN11ha_innobase12delete_tableEPKc+0x607)[0x1120e47]
/usr/local/mysql/bin/mysqld(_Z15ha_delete_tableP3THDP10handlertonPKcS4_S4_b+0x108)[0x855438]
/usr/local/mysql/bin/mysqld(_Z23mysql_rm_table_no_locksP3THDP10TABLE_LISTbbbb+0xd7f)[0xdb69af]
/usr/local/mysql/bin/mysqld(_Z14mysql_rm_tableP3THDP10TABLE_LISTcc+0x21f)[0xdb7eff]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0xdf4)[0xd4d2e4]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x395)[0xd517e5]
/usr/local/mysql/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x153e)[0xd52dce]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0x194)[0xd53ae4]
/usr/local/mysql/bin/mysqld(handle_connection+0x2ac)[0xe26a8c]
/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x174)[0x13f6034]
/lib64/libpthread.so.0(+0x81cf)[0x7f1fc96181cf]
/lib64/libc.so.6(clone+0x43)[0x7f1fc7f10e73]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f19100048a0): is an invalid pointer
Connection ID (thread ID): 2363
Status: NOT_KILLED

 

그래서 에러로그를 tail -f 로 따면서 오류를 발생하는 쿼리를 조회해보니...

[ERROR] InnoDB: Database page corruption on disk or a failed file read of page [page id: space=44, page number=2362]. You may have to recover from a backup.

같은 오류가 찍히는 것을 확인하였다.

 

해당 서버가 테스트를 위해 디스크를 여러번 바꾸고 데이터도 해당 디스크로 카피뜨고 서버 재부팅도 빈번하게 작업하다 보니 데이터가 깨진게 아닌게 싶다..

아쉽게도 mysql의 깨진 데이터를 복원하는 방법은 아직 찾지 못하였고 빠르게 작업을 대체하기 위해 신규 테이블을 생성하고 기존 데이터 중 페이지가 깨지지않은 일부 부분만 옮겨 처리하였다.

 

 

---

이러한 상황에서 어떻게 대처해야할까?

1. 덤프 실행 --> 덤프 실패

mysqldump --single-transaction --quick  --hex-blob --routines --triggers --events --no-autocommit=1 --extended-insert=1 --disable-keys --dump-date -P -u -p'' --no-create-db game_log game_play_time -r minjae.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `game_play_time` at row: 289310

#innodb_force_recovery = 1 ~ 3
mysqldump --single-transaction --quick  --hex-blob --routines --triggers --events --no-autocommit=1 --extended-insert=1 --disable-keys --dump-date -P -u -p'' --no-create-db game_log game_play_time -r minjae.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `game_play_time` at row: 289310

check table 을 진행하기 위해 MyISAM 으로 엔진변경을 하니 테이블이 꼬여서 더이상의 테스트가 불가능했다...
ibd, frm 파일을 가지고 DISCARD TABLESPACE, IMPORT TABLESPACE로 다시 생성하려하였지만
TABLESPACE를 IMPORT 하는 과정에서 IBD 파일을 읽는 부분이 있는지 DATA CORRUPT 에러가 발생하여 할당 실패.

 

 

2. 작업 중 생긴 $sql-ib***.ibd 파일과 테이블들

Data 파일 폴더에 다음과 같은 파일들이 생겨났었다.

해당 파일들은 mysql 을 통해 삭제하도록 하자

확인 방법 

SELECT * FROM information_schema.innodb_sys_tables;

을 통해 table_id 와 name을 확인

SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%1053924488%';

 

table_id로 columns와 index 확인

SELECT * FROM information_schema.INNODB_SYS_COLUMNS WHERE table_id = 443 ;
SELECT * FROM information_schema.INNODB_SYS_INDEXES WHERE table_id = 443 ;

 

drop table 오류 발생

DROP TABLE `#sql-ib100-1053924488`
--
SQL Error [1051] [42S02]: Unknown table 'game_log.#sql-ib100-1053924488'

1. 동일한 테이블 frm을 가진 파일을 복사
cp table.frm #sql-ib100-1053924488.frm
2. #mysql50# 프리픽스를 붙여서 table drop 
DROP TABLE `#mysql50##sql-ib100-1053924488`

1. 동일한 구조를 가진 테이블 생성
CREATE TABLE `#mysql50##sql-ib101-1053924489` LIKE table
--> 오류 발생
SQL Error [1813] [HY000]: Tablespace '`game_log`.`#sql-ib101-1053924489`' exists.