MySQL 5.6설치후 통계관련 테이블 에러 발생할때.

[출처] MySQL 5.6설치후 통계관련 테이블 에러 발생할때.|작성자 강아지스누피

MySQL 5.6을 설치하였는데 아래와 같은 에러가 발생되었다. 

Error: Table "mysql"."innodb_table_stats" not found.

(mysql DB에 전통적으로 myisam or memory만 있었는데 신기하게 innodb 엔진을 가진 테이블이다 ㅎ)

 

innodb_index_stats

innodb_table_stats

slave_master_info

slave_relay_log_info

slave_worker_info 

 

아무튼 show table에는 보이는데 describe는 안되는 신기한 현상이 -.-;

 

설치과정에서 Script가 제대로 안된것 같은것으로 보이는데. 현재까지의 버전인 5.6.19에서도 계속 발생되고 있다. ( ㅠㅠ)

http://bugs.mysql.com/bug.php?id=67179 

 

서비스운영에는 크게 상관없지만, Innodb의 통계정보를 살펴볼수 있는 MySQL에 유일한 테이블인데 그래도 힘들게 만들어줬는데 살려서 봐야지..

 

다른DB에서 Schema에서 dump해서 create해야 하는데 이미 tablespace상에서 잡혀버려 생성이 안된데...

알려진 해결책은 테이블을 삭제하고 DB Restart해야 하는것으로 나오는데. 서비스중이라 no!no!..

 

순서는 

1. 일단 삭제.

2. flush table해서 Table List 한번 refresh

3. myisam으로 생성해서.

4. 엔진변경.. 끝..

 

이후 통계정보가 제대로 들어오는지 확인해보자.

 

-- Script --

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;

 

FLUSH TABLES;

 

CREATE TABLE `innodb_index_stats` (

  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `stat_value` bigint(20) unsigned NOT NULL,

  `sample_size` bigint(20) unsigned DEFAULT NULL,

  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)

) ENGINE=myisam DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

 

CREATE TABLE `innodb_table_stats` (

  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `n_rows` bigint(20) unsigned NOT NULL,

  `clustered_index_size` bigint(20) unsigned NOT NULL,

  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,

  PRIMARY KEY (`database_name`,`table_name`)

) ENGINE=myisam DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

 

CREATE TABLE `slave_master_info` (

  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',

  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',

  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',

  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',

  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',

  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',

  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',

  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',

  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',

  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',

  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',

  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',

  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',

  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',

  `Heartbeat` float NOT NULL,

  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',

  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',

  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',

  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',

  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',

  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',

  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',

  PRIMARY KEY (`Host`,`Port`)

) ENGINE=myisam DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

 

CREATE TABLE `slave_relay_log_info` (

  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',

  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',

  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',

  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',

  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',

  `Number_of_workers` int(10) unsigned NOT NULL,

  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',

  PRIMARY KEY (`Id`)

) ENGINE=myisam DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

 

CREATE TABLE `slave_worker_info` (

  `Id` int(10) unsigned NOT NULL,

  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Relay_log_pos` bigint(20) unsigned NOT NULL,

  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Master_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,

  `Checkpoint_seqno` int(10) unsigned NOT NULL,

  `Checkpoint_group_size` int(10) unsigned NOT NULL,

  `Checkpoint_group_bitmap` blob NOT NULL,

  PRIMARY KEY (`Id`)

) ENGINE=myisam DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

 

ALTER TABLE innodb_index_stats ENGINE=innodb;

ALTER TABLE innodb_table_stats ENGINE=innodb;

ALTER TABLE slave_master_info ENGINE=innodb;

ALTER TABLE slave_relay_log_info ENGINE=innodb;

ALTER TABLE slave_worker_info ENGINE=innodb; 

[출처] MySQL 5.6설치후 통계관련 테이블 에러 발생할때.|작성자 강아지스누피