Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL innodb_table_stats table does not exist solution     - Ubuntu 14.04 Configuring cuda-convnet (Linux)

- To install the latest version of the EPEL on CentOS 5.x or 6.x (Linux)

- Vim useful plugin: vundle (Linux)

- The simple multi-threaded Python (Programming)

- How to enable curl command HTTP2 support (Linux)

- Oracle procedure or function Empty Table (Database)

- When Vim create Python scripts, vim autocomplete interpreter and encoding method (Programming)

- Resolve the host via KVM console can not connect clients (Linux)

- Effect MongoDB plan cache (Database)

- Drawing from the Android source code analysis View (Programming)

- How apt-get limited use IPv4 or IPv6 protocol to download (Linux)

- Install the latest Pinta graphics editing software on Ubuntu 14.04 (Linux)

- iTerm - let your command line can also be colorful (Linux)

- Linux kernel RCU (Read Copy Update) lock Brief - prequel (Linux)

- CentOS 6.7 compile and install LAMP (Server)

- Ubuntu uses conky add desktop control (Linux)

- How to use the Docker Machine cluster deployment Swarm (Server)

- Graphical interface for the CentOS 6.4 installed and connected by remote VNC (Linux)

- MongoDB 3.2 Cluster Setup (Database)

- Based Docker build stand-alone high-availability cluster Hadoop2.7.1 Spark1.7 (Server)

 
         
  MySQL innodb_table_stats table does not exist solution
     
  Add Date : 2017-08-31      
         
       
         
  MySQL version 5.6.14

The company has several MySQL server error log shows that there are several system tables do not exist.
Innodb_table_stats
 Innodb_index_stats
 Slave_master_info
 Slave_relay_log_info
 Slave_worker_info

This is because the database initialization time, dba ibdata1 may delete the file
Although the restart, the database will automatically create an ibdata1 file, but the system table is innodb engine, it can not be accessed.
This will not affect the business, but the use of innobackupex backup, the error log will be written.
The final error log, this information is the impact of routine inspection.

 the solution.
1. Delete the above system tables
Drop table mysql.innodb_index_stats;
 Drop table mysql.innodb_table_stats;
 Drop table mysql.slave_master_info;
 Drop table mysql.slave_relay_log_info;
 Drop table mysql.slave_worker_info;

 

 2. Delete the associated .frm .ibd file
Rm -rf innodb_index_stats *
 Rm -rf innodb_table_stats *
 Rm -rf slave_master_info *
 Rm -rf slave_relay_log_info *
 Rm -rf slave_worker_info *

 

 3. Re-create the above system 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 = InnoDB 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 = InnoDB 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 'indications 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 'whereby whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY ( `Host`,` Port`)
 ) ENGINE = InnoDB 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 = InnoDB 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 = InnoDB DEFAULT CHARSET = utf8 STATS_PERSISTENT = 0 COMMENT = 'Worker Information';

 

 4. If the slave machine, stop replication, record pos and other information
Mysql> stop slave;
 Query OK, 0 rows affected (0.09 sec)

 5. Restart the database

6. If the Slave starts an error, use the information in step 4 to change the master

And then delete the ibdata1 file, we must think of the system database there are five innodb table ...
     
         
       
         
  More:      
 
- Java MD5 encryption implementation (Programming)
- Oracle Shared Server Configuration (Database)
- Android Studio commonly used shortcuts and how to follow the Eclipse Shortcuts (Linux)
- Installation and use the telnet command under CentOS 6.x (Linux)
- Binary search is really easy as you think you do (Programming)
- Eclipse-4.4 crash problem solving under Debian-7.6 (Linux)
- Android Notification (Programming)
- MariaDB 10.1 and MySQL 5.7 in general performance on commodity hardware (Database)
- Linux systems for entry-learning - Install Go language in Linux (Linux)
- Ubuntu users Steam controller does not work solutions (Linux)
- DM9000 timing settings (Programming)
- Windows7 / 8 / 8.1 hard drive to install Ubuntu 14.04 dual system (Linux)
- The oh-my-zsh into true my zsh (Linux)
- Nginx Beginner Guide (Server)
- Ubuntu Thunderbird 24.4.0 (Linux)
- How to choose the first programming language based on the life you want (Programming)
- Ubuntu 14.04 + xRDP + Xfce implement Windows Remote Desktop Connection (Linux)
- Shell script to crawl through AWR SQL Report Problems (Database)
- C language files update in real time (Programming)
- Job achieve automation in Ubuntu 14.04 - Using Cron (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.