mysql InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
By 때찌때찌맴매 - 10월 26, 2018
mysql - InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
* 고객 db가 죽는 상황이 발생. mysql 재구동을 하자 pid 생성 오류로 데몬이 안올라옴.
* 일단 살려야 되니 innodb_force_recovery = 1 부터 올려서 구동
전에 사용중이던 mysql db를 복원하고 로그 확인 결과 해당 에러들 발견됨.
* 원인은 기존에 사용하던 mysql버전이 5.5 였는데 지금 서버는 5.6.
MySQL 5.6 부터 mysql database 에 통계정보나 slave 관련 정보를 저장하기 위해 새로운 InnoDB table 5개가 추가 (innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info, slave_worker_info)되었는데 버전이 5.5 -> 5.6 변경시 mysql_upgrade 를 진행하지 않을 경우 해당 에러 발생. (물론 쿼리들을 해당 버전에 맞게 튜닝도 필요. 이 내용은 고객에게 안내)
[ database 에러 ] [root@localhost01:33:26:~]# tail -f /usr/local/mysql/data/localhost.localdomain.err 2018-10-26 13:24:11 7f92494d6700 InnoDB: Error: Fetch of persistent statistics requested for table "SY_NOTI"."DMND_ORGNZ2" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead. 2018-10-26 13:24:11 7f92494d6700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2018-10-26 13:24:11 7f92494d6700 InnoDB: Error: Fetch of persistent statistics requested for table "SY_NOTI"."PROD_CLAS_CODE" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead. 2018-10-26 13:24:14 7f924af31700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2018-10-26 13:24:14 7f924af31700 InnoDB: Recalculation of persistent statistics requested for table "SY_SESSION"."SPRING_SESSION" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
[ 해결 ] i. 위 언급한 mysql database에서 5개 테이블 제거 [root@localhost01:25:56:/usr/local/mysql/data]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 52789 Server version: 5.6.10-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql; mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 28 rows in set (0.00 sec) mysql> drop table innodb_index_stats ; drop table innodb_table_stats; drop table slave_master_info; drop table slave_relay_log_info; drop table slave_worker_info; ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats' ERROR 1051 (42S02): Unknown table 'mysql.innodb_table_stats' ERROR 1051 (42S02): Unknown table 'mysql.slave_master_info' ERROR 1051 (42S02): Unknown table 'mysql.slave_relay_log_info' ERROR 1051 (42S02): Unknown table 'mysql.slave_worker_info' mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.00 sec) mysql> quit * table drop 시 에러메시지는 위에서 force recorvery 로 강제 복구시 정도가 제거 된듯;; 확인 결과 정상적으로 테이블 제거 ii. ****.rbd 파일 제거 및 mysql 재구동 [root@localhost01:27:21:/usr/local/mysql/data]# /etc/init.d/mysqld stop Shutting down MySQL.... [ OK ] [root@localhost01:27:21:/usr/local/mysql/data]#cd mysql [root@localhost01:27:36:/usr/local/mysql/data/mysql]# rm -rf innodb_index_stats.ibd innodb_table_stats.ibd slave_master_info.ibd slave_relay_log_info.ibd slave_worker_info.ibd [root@localhost01:28:20:/usr/local/mysql/data/mysql]# /etc/init.d/mysqld start Starting MySQL... [ OK ] [root@localhost01:28:29:/usr/local/mysql/data/mysql]# /usr/local/mysql/bin/mysql_upgrade --force -uroot -p Enter password: Looking for 'mysql' as: /usr/local/mysql/bin/mysql Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' Warning: Using a password on the command line interface can be insecure. SY_ANLS.AUTO_ANLS OK SY_ANLS.AUTO_ANLS_GUGAN OK SY_ANLS.AUTO_ANLS_LOG OK SY_ANLS.AUTO_ANLS_PRF OK SY_ANLS.AUTO_ANLS_RESULT OK SY_ANLS.AUTO_ANLS_STAT_CD OK SY_ANLS.QUARTILE OK SY_APP.CUST_APP OK SY_APP.CUST_APP_BIZ OK SY_APP.SY_APP_MASTER OK SY_BILCLT.BILCLT OK SY_BILCLT.BILCLT_AGR OK SY_BILCLT.BILCLT_EXCCLC_RATE OK SY_BILCLT.BILCLT_HIST OK SY_BILCLT.BILCLT_HIST_BACK OK SY_BILCLT.RCPTMN OK SY_BILCLT.RCPTMN_HIST OK SY_BILCLT.TAXBL OK SY_CUST.ANLS_CHRGR_CHNG_HIST OK SY_CUST.CUST OK SY_CUST.CUST_ACRSLT_AP_ST OK SY_CUST.CUST_APP_CFG OK SY_CUST.CUST_BIZ OK SY_CUST.CUST_BIZ_HIST OK SY_CUST.CUST_CFG OK SY_CUST.CUST_CNCN_NOTI OK SY_CUST.CUST_CNTR_MNGT OK SY_CUST.CUST_COMSN_CNDTN OK SY_CUST.CUST_CONSL OK SY_CUST.CUST_CONSL_KIND OK SY_CUST.CUST_CRGRS OK SY_CUST.CUST_HIST OK SY_CUST.CUST_LICNS OK SY_CUST.CUST_LICNS_DEL_HIST OK SY_CUST.CUST_LICNS_HIST OK SY_CUST.CUST_LOGIN_HIST OK SY_CUST.CUST_MEMO_HIST OK SY_CUST.CUST_MXD_LICNS OK SY_CUST.CUST_MXD_PRVNCL OK SY_CUST.CUST_NOTI OK SY_CUST.CUST_NOTI_HIST OK SY_CUST.CUST_OMTP_ECPT_NOTI OK SY_CUST.CUST_PRFSH OK SY_CUST.CUST_PRTC_AMT OK SY_CUST.CUST_PRTC_AMT_HIST OK SY_CUST.CUST_SBID OK SY_CUST.CUST_SVC OK SY_CUST.CUST_TELNO OK SY_CUST.CUST_TKN OK SY_CUST.CUST_UTLZ_CLAUSE OK SY_CUST.CUST_UTLZ_CLAUSE_HIST OK SY_CUST.KAKAOTALK_USER OK SY_CUST.NCST OK SY_CUST.NCST_BSN OK SY_CUST.NCST_CONSL OK SY_CUST.NCST_LICNS OK SY_CUST.NCST_MNGT_FLDR OK SY_CUST.NCST_MNGT_FLDR_JNOW OK SY_CUST.NCST_MNGT_FLDR_TARGT OK SY_CUST.NCST_RECPTN_REJECT OK SY_CUST.NCST_TELNO OK SY_CUST.NCST_TELNO_HIST OK SY_CUST.TRANS OK SY_CUST.TRANS_TMPL OK SY_EMP.ACRSLT_NOTI OK SY_EMP.ACRSLT_ST_MNGT OK SY_EMP.ATTEND_LVFFC OK SY_EMP.AUTH OK SY_EMP.BDDPR_ASIST_MNGT OK SY_EMP.EMP OK SY_EMP.EMP_ALAM OK SY_EMP.EMP_AUTHZ_REQ OK SY_EMP.EMP_CNCN_NOTI OK SY_EMP.EMP_GBS_LICNS OK SY_EMP.EMP_HIST OK SY_EMP.EMP_LOGIN_HIST OK SY_EMP.EMP_LONG_OFFDAY OK SY_EMP.EMP_MEMO OK SY_EMP.EMP_MENU OK SY_EMP.EMP_MENU_AUTH OK SY_EMP.EMP_MENU_SKIL OK SY_EMP.EMP_NTBD OK SY_EMP.EMP_NTBD_JNOW OK SY_EMP.EMP_PC_REGST OK SY_EMP.EMP_REC OK SY_EMP.POSIT_TEAM OK SY_EMP.POSIT_TEAM_AUTH OK SY_ETC.ETCLINK OK SY_ETC.SCHEDULED_RUN OK SY_ETC.migrationCheck107 OK SY_NOTI.AREA_CODE OK SY_NOTI.BATCH_JOB_EXECUTION OK SY_NOTI.BATCH_JOB_EXECUTION_CONTEXT OK SY_NOTI.BATCH_JOB_EXECUTION_PARAMS OK SY_NOTI.BATCH_JOB_EXECUTION_SEQ OK SY_NOTI.BATCH_JOB_HIST OK SY_NOTI.BATCH_JOB_INSTANCE OK SY_NOTI.BATCH_JOB_SEQ OK SY_NOTI.BATCH_MASTER OK SY_NOTI.BATCH_STEP_EXECUTION OK SY_NOTI.BATCH_STEP_EXECUTION_CONTEXT OK SY_NOTI.BATCH_STEP_EXECUTION_SEQ OK SY_NOTI.CFMT_UNDWRT_ACRSLT_MLTP OK SY_NOTI.CFMT_UNDWRT_AVG_RATE OK SY_NOTI.CFMT_UNDWRT_CDLT_DISTMK OK SY_NOTI.CFMT_UNDWRT_CDLT_GROUP OK SY_NOTI.CFMT_UNDWRT_CDLT_ST OK SY_NOTI.CFMT_UNDWRT_ST OK SY_NOTI.CFMT_UNDWRT_ST_DISTMK OK SY_NOTI.CNTR_MTHD OK SY_NOTI.COMN_CODE OK SY_NOTI.COMN_CODE_GROUP OK SY_NOTI.DMND_ORGNZ OK SY_NOTI.DMND_ORGNZ2 OK SY_NOTI.KEP_REQ_HDR OK SY_NOTI.LICNS_CODE OK SY_NOTI.LICNS_NEED_TCNXPRT OK SY_NOTI.NOTI OK SY_NOTI.NOTI_AREA OK SY_NOTI.NOTI_AREA_HIST OK SY_NOTI.NOTI_ATCHMNFL OK SY_NOTI.NOTI_BDDPR_PRTC_CMP OK SY_NOTI.NOTI_COMPNO_PRDPRC OK SY_NOTI.NOTI_HIST OK SY_NOTI.NOTI_LICNS OK SY_NOTI.NOTI_LICNS_HIST OK SY_NOTI.NOTI_ORIGN OK SY_NOTI.NOTI_SBID_PRARG_CMP OK SY_NOTI.PROD_CLAS_CODE OK SY_NOTI.RPSNT_ORGNZ OK SY_NOTI_TEST.AREA_CODE OK SY_NOTI_TEST.BATCH_JOB_EXECUTION OK SY_NOTI_TEST.BATCH_JOB_EXECUTION_CONTEXT OK SY_NOTI_TEST.BATCH_JOB_EXECUTION_PARAMS OK SY_NOTI_TEST.BATCH_JOB_EXECUTION_SEQ OK SY_NOTI_TEST.BATCH_JOB_HIST OK SY_NOTI_TEST.BATCH_JOB_INSTANCE OK SY_NOTI_TEST.BATCH_JOB_SEQ OK SY_NOTI_TEST.BATCH_MASTER OK SY_NOTI_TEST.BATCH_STEP_EXECUTION OK SY_NOTI_TEST.BATCH_STEP_EXECUTION_CONTEXT OK SY_NOTI_TEST.BATCH_STEP_EXECUTION_SEQ OK SY_NOTI_TEST.CFMT_UNDWRT_ACRSLT_MLTP OK SY_NOTI_TEST.CFMT_UNDWRT_AVG_RATE OK SY_NOTI_TEST.CFMT_UNDWRT_CDLT_DISTMK OK SY_NOTI_TEST.CFMT_UNDWRT_CDLT_GROUP OK SY_NOTI_TEST.CFMT_UNDWRT_CDLT_ST OK SY_NOTI_TEST.CFMT_UNDWRT_ST OK SY_NOTI_TEST.CFMT_UNDWRT_ST_DISTMK OK SY_NOTI_TEST.CNTR_MTHD OK SY_NOTI_TEST.COMN_CODE OK SY_NOTI_TEST.COMN_CODE_GROUP OK SY_NOTI_TEST.DMND_ORGNZ OK SY_NOTI_TEST.DMND_ORGNZ2 OK SY_NOTI_TEST.LICNS_CODE OK SY_NOTI_TEST.NOTI OK SY_NOTI_TEST.NOTI_AREA OK SY_NOTI_TEST.NOTI_AREA_HIST OK SY_NOTI_TEST.NOTI_ATCHMNFL OK SY_NOTI_TEST.NOTI_BDDPR_PRTC_CMP OK SY_NOTI_TEST.NOTI_COMPNO_PRDPRC OK SY_NOTI_TEST.NOTI_HIST OK SY_NOTI_TEST.NOTI_LICNS OK SY_NOTI_TEST.NOTI_LICNS_HIST OK SY_NOTI_TEST.NOTI_ORIGN OK SY_NOTI_TEST.NOTI_SBID_PRARG_CMP OK SY_NOTI_TEST.RPSNT_ORGNZ OK SY_SESSION.SPRING_SESSION OK SY_SESSION.SPRING_SESSION_ATTRIBUTES OK SY_TRSMIS.TRSMIS OK SY_TRSMIS.TRSMIS_ALAM OK SY_TRSMIS.TRSMIS_ALAM_OBJPS OK SY_TRSMIS.TRSMIS_ATCHMNFL OK SY_TRSMIS.TRSMIS_SCLAS OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK mysql_20181026.columns_priv OK mysql_20181026.db OK mysql_20181026.event OK mysql_20181026.func OK mysql_20181026.general_log OK mysql_20181026.help_category OK mysql_20181026.help_keyword OK mysql_20181026.help_relation OK mysql_20181026.help_topic OK mysql_20181026.innodb_index_stats Error : Table 'mysql_20181026.innodb_index_stats' doesn't exist status : Operation failed mysql_20181026.innodb_table_stats Error : Table 'mysql_20181026.innodb_table_stats' doesn't exist status : Operation failed mysql_20181026.ndb_binlog_index OK mysql_20181026.plugin OK mysql_20181026.proc OK mysql_20181026.procs_priv OK mysql_20181026.proxies_priv OK mysql_20181026.servers OK mysql_20181026.slave_master_info Error : Table 'mysql_20181026.slave_master_info' doesn't exist status : Operation failed mysql_20181026.slave_relay_log_info Error : Table 'mysql_20181026.slave_relay_log_info' doesn't exist status : Operation failed mysql_20181026.slave_worker_info Error : Table 'mysql_20181026.slave_worker_info' doesn't exist status : Operation failed mysql_20181026.slow_log OK mysql_20181026.tables_priv OK mysql_20181026.time_zone OK mysql_20181026.time_zone_leap_second OK mysql_20181026.time_zone_name OK mysql_20181026.time_zone_transition OK mysql_20181026.time_zone_transition_type OK mysql_20181026.user OK mysql_back.columns_priv OK mysql_back.db OK mysql_back.event OK mysql_back.func OK mysql_back.general_log OK mysql_back.help_category OK mysql_back.help_keyword OK mysql_back.help_relation OK mysql_back.help_topic OK mysql_back.innodb_index_stats Error : Table 'mysql_back.innodb_index_stats' doesn't exist status : Operation failed mysql_back.innodb_table_stats Error : Table 'mysql_back.innodb_table_stats' doesn't exist status : Operation failed mysql_back.ndb_binlog_index OK mysql_back.plugin OK mysql_back.proc OK mysql_back.procs_priv OK mysql_back.proxies_priv OK mysql_back.servers OK mysql_back.slave_master_info Error : Table 'mysql_back.slave_master_info' doesn't exist status : Operation failed mysql_back.slave_relay_log_info Error : Table 'mysql_back.slave_relay_log_info' doesn't exist status : Operation failed mysql_back.slave_worker_info Error : Table 'mysql_back.slave_worker_info' doesn't exist status : Operation failed mysql_back.slow_log OK mysql_back.tables_priv OK mysql_back.time_zone OK mysql_back.time_zone_leap_second OK mysql_back.time_zone_name OK mysql_back.time_zone_transition OK mysql_back.time_zone_transition_type OK mysql_back.user OK test.board OK test.board_reply OK test.test OK test.users OK Repairing tables mysql_20181026.innodb_index_stats Error : Table 'mysql_20181026.innodb_index_stats' doesn't exist status : Operation failed mysql_20181026.innodb_table_stats Error : Table 'mysql_20181026.innodb_table_stats' doesn't exist status : Operation failed mysql_20181026.slave_master_info Error : Table 'mysql_20181026.slave_master_info' doesn't exist status : Operation failed mysql_20181026.slave_relay_log_info Error : Table 'mysql_20181026.slave_relay_log_info' doesn't exist status : Operation failed mysql_20181026.slave_worker_info Error : Table 'mysql_20181026.slave_worker_info' doesn't exist status : Operation failed mysql_back.innodb_index_stats Error : Table 'mysql_back.innodb_index_stats' doesn't exist status : Operation failed mysql_back.innodb_table_stats Error : Table 'mysql_back.innodb_table_stats' doesn't exist status : Operation failed mysql_back.slave_master_info Error : Table 'mysql_back.slave_master_info' doesn't exist status : Operation failed mysql_back.slave_relay_log_info Error : Table 'mysql_back.slave_relay_log_info' doesn't exist status : Operation failed mysql_back.slave_worker_info Error : Table 'mysql_back.slave_worker_info' doesn't exist status : Operation failed Running 'mysql_fix_privilege_tables'... Warning: Using a password on the command line interface can be insecure. OK [root@localhost01:28:35:/usr/local/mysql/data/mysql]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 333 Server version: 5.6.10-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 28 rows in set (0.00 sec) mysql> quit * 해당 작업 후 에러 제거되고 정상 메시지만 출력. 근데 이 서버는 리플리케이션 제거 되고 단독으로 도는 서버.. mysql_upgrade 명령어를 하고 아래 에러 들이 출력 된것 보니 리플리케이션 관련 내용들이 없어서 뜬것...
H