티스토리 뷰
* 개요
alterAt 명령 실행위한 select 중 동일한 오류 계속 발생함.
* 로그
2018-08-22T05:23:24.996223Z 14 [ERROR] [MY-012611] [InnoDB] InnoDB: Operating system error number 2 in a file operation.
2018-08-22T05:23:24.996991Z 14 [ERROR] [MY-012216] [InnoDB] InnoDB: Cannot open datafile for read-only: '.\130514cidogir\fts_0000000000000456_deleted.ibd' OS error: 71
2018-08-22T05:23:24.999323Z 14 [ERROR] [MY-012155] [InnoDB] InnoDB: Trying to do I/O on a tablespace which does not exist. I/O type: read, page: [page id: space=57, page number=4], I/O length: 16384 bytes
2018-08-22T05:23:25.000366Z 14 [ERROR] [MY-011966] [InnoDB] InnoDB: trying to read page [page id: space=57, page
[ 동일 에러 반복 생략 ]
2018-08-22T05:23:25.178384Z 14 [ERROR] [MY-011825] [InnoDB] InnoDB: [FATAL] Unable to read page [page id: space=57, page number=4] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/8.0/en/ for more details. Aborting...
2018-08-22T05:23:25.180602Z 14 [ERROR] [MY-000000] [InnoDB] InnoDB: Assertion failure: ut0ut.cc:675
InnoDB: thread 14752
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
05:23:25 UTC - mysqld got exception 0x80000003 ;
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=8192
max_used_connections=6
max_threads=151
thread_count=7
connection_count=6
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49561 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x1c725ec0850
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...
7ff6dd87a6d2 mysqld.exe!?my_errno@@YAHXZ()
7ffb5852d6cb ucrtbase.dll!raise()
7ffb5852e761 ucrtbase.dll!abort()
7ff6ddac6a67 mysqld.exe!??1?$lock_guard@Vmutex@std@@@std@@QEAA@XZ()
[ 동일오류 반복 생략 ]
7ff6dcbf5ca1 mysqld.exe!?init_search@Item_func_match@@QEAA_NPEAVTHD@@@Z()
7ff6dccbe16b mysqld.exe!?init_ftfuncs@@YA_NPEAVTHD@@PEAVSELECT_LEX@@@Z()
7ff6dcd61d45 mysqld.exe!?optimize@JOIN@@QEAAHXZ()
7ff6dcdabac6 mysqld.exe!?optimize@SELECT_LEX@@QEAA_NPEAVTHD@@@Z()
7ff6dcda89cf mysqld.exe!?execute_inner@Sql_cmd_dml@@MEAA_NPEAVTHD@@@Z()
7ff6dcda87d6 mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff6dccd07f4 mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff6dccd12d6 mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff6dcccad98 mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff6dcccbcc5 mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff6dcb5d068 mysqld.exe!?pop_front@?$list@PEAVChannel_info@@V?$allocator@PEAVChannel_info@@@std@@@std@@QEAAXXZ()
7ff6ddc5e347 mysqld.exe!??1?$lock_guard@Vmutex@std@@@std@@QEAA@XZ()
7ff6dd87a66c mysqld.exe!?my_thread_join@@YAHPEAUmy_thread_handle@@PEAPEAX@Z()
7ffb584dc4be ucrtbase.dll!_o_ceil()
7ffb5b883034 KERNEL32.DLL!BaseThreadInitThunk()
7ffb5c161431 ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1c72600d7f8): /* criteria query */ select this_.idconcrete_info_overview as idconcrete1_16_0_, this_.bw_id as bw2_16_0_, this_.category_expla as category3_16_0_, this_.ci_info_place_id as ci4_16_0_, this_.cic_extractor_executor as cic5_16_0_, this_.cic_extractor_name as cic6_16_0_, this_.cic_extractor_version as cic7_16_0_, this_.cipd_id as cipd8_16_0_, this_.cntAipr as cntAipr16_0_, this_.content_url as content10_16_0_, this_.produce_date as produce11_16_0_, this_.detailTheme as detailT12_16_0_, this_.dtAnaly as dtAnaly16_0_, this_.dtClassify as dtClassify16_0_, this_.dtNlp as dtNlp16_0_, this_.dtRecogIpd as dtRecogIpd16_0_, this_.dtRefl as dtRefl16_0_, this_.extractor_executor as extractor18_16_0_, this_.extractor_name as extractor19_16_0_, this_.extractor_version as extractor20_16_0_, this_.fAlterIpd as fAlterIpd16_0_, this_.f_alter_theme as f22_16_0_, this_.fC2bMul as fC23_16_0_, this_.f_check_map_info as f24_16_0_, this_.f_content_removed as f25_16_0_, this_.f_err_collect_cic as f26_16_0_, this_.f_exist_map_info as f2
Connection ID (thread ID): 14
Status: NOT_KILLED
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.
* 원인 가설
- 테이블이 깨져서?
- 쿼리가 너무 커서? key 를 가져오기 힘들어서?
* 해결
- 해결방법 검토는 다음 <고려했던 해결방법> 참고
- mysqlcheck --optimize
했으나 효과 없었음
- my.ini 에 innobdb_force_recovery = 4 하고 실행했으나 효과 없음
http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21875&cat1=14&cat2=422&cat3=442&lang=k
- (용량 확보 위해) 다른 테이블 데이터 삭제해봤으나
- (실제 해결)
- 1) REMASTER 통해서 binlog 정리하고
http://dogcowking.tistory.com/237
- 2) 그전에 임의로 삭제했던 fts_0000000000000495_being_deleted.ibd 파일 되살린 후 정상작동함
http://dogcowking.tistory.com/238
- ...ibd 파일과 binlog 을 용량 문제로 임의로 삭제했었는데 그게 문제가 됐던걸로 추정
- 앞의 두가지 중 어떤게 직접 원인인지, 다른것과 관련 있는지는 알 수 없음.
[[ 고려 했던 해결 방법
* 테이블 복구
- optimize (mysqlcheck --optimize ... )
- innobdb_force)recovery = 4 적용
무슨 효과가 있는건가?
http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21875&cat1=14&cat2=422&cat3=442&lang=k
"이 옵션 값을 4로 해서 테이블을 덤프 한다면...."
덤프(SELECT INTO OUTFILE) 시에만 유효한듯.
- 다른 테이블 데이터 삭제(용량 보전용)
효과 없었음
* 테이블 수정
인덱스 삭제
* 쿼리 방법 수정
- 하이버네이트 lazy 옵션 이용하여 불러오는 컬럼 수 줄이기
* 설정 변경
- key buffer 수정
상황상 버퍼의 부족이 아니라 메모리를 너무 많이 사용해서 mysql 이 중단되는 것으로 추정
로그 내용을 보면 '버퍼를 줄여라' 라고 되어 있음.
추가 참고 :
- 쿼리 최대 길이 늘리기?
관계가 있는건가 ?