最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】wordpress中mysql出错:InnoDB: Error: Fetch of persistent statistics requested for table wp_options but the required system tables mysql.innodb_table_stats

MySQL crifan 962浏览 0评论
折腾:
【未解决】Wordpress启用Enfold主题后出错:500 Internal Server Error 且显示3个data:image/png;base64
期间,
注意到:
/Applications/MAMP/logs/mysql_error_log.err
中:
180715 21:07:37 mysqld_safe Logging to '/Applications/MAMP/logs/mysql_error_log.err'.
180715 21:07:38 mysqld_safe Starting mysqld daemon with databases from /Applications/MAMP/db/mysql56
2018-07-15 21:07:38 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-07-15 21:07:38 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2018-07-15 21:07:38 0 [Note] /Applications/MAMP/Library/bin/mysqld (mysqld 5.6.38) starting as process 24331 ...
2018-07-15 21:07:38 24331 [Warning] Setting lower_case_table_names=2 because file system for /Applications/MAMP/db/mysql56/ is case insensitive
2018-07-15 21:07:38 24331 [Note] Plugin 'FEDERATED' is disabled.
2018-07-15 21:07:38 24331 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-07-15 21:07:38 24331 [Note] InnoDB: The InnoDB memory heap is disabled
2018-07-15 21:07:38 24331 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-07-15 21:07:38 24331 [Note] InnoDB: Memory barrier is not used
2018-07-15 21:07:38 24331 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-07-15 21:07:38 24331 [Note] InnoDB: Using CPU crc32 instructions
2018-07-15 21:07:38 24331 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-07-15 21:07:38 24331 [Note] InnoDB: Completed initialization of buffer pool
2018-07-15 21:07:38 24331 [Note] InnoDB: Highest supported file format is Barracuda.
2018-07-15 21:07:38 24331 [Note] InnoDB: 128 rollback segment(s) are active.
2018-07-15 21:07:38 24331 [Note] InnoDB: Waiting for purge to start
2018-07-15 21:07:38 24331 [Note] InnoDB: 5.6.38 started; log sequence number 76898138
2018-07-15 21:07:38 24331 [Note] RSA private key file not found: /Applications/MAMP/db/mysql56//private_key.pem. Some authentication plugins will not work.
2018-07-15 21:07:38 24331 [Note] RSA public key file not found: /Applications/MAMP/db/mysql56//public_key.pem. Some authentication plugins will not work.
2018-07-15 21:07:38 24331 [Note] Server hostname (bind-address): '*'; port: 8889
2018-07-15 21:07:38 24331 [Note] IPv6 is available.
2018-07-15 21:07:38 24331 [Note]   - '::' resolves to '::';
2018-07-15 21:07:38 24331 [Note] Server socket created on IP: '::'.
2018-07-15 21:07:38 24331 [Note] Event Scheduler: Loaded 0 events
2018-07-15 21:07:38 24331 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
Version: '5.6.38'  socket: '/Applications/MAMP/tmp/mysql/mysql.sock'  port: 8889  MySQL Community Server (GPL)
2018-07-15 21:07:44 70000d793000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2018-07-15 21:07:44 70000d793000 InnoDB: Error: Fetch of persistent statistics requested for table "xxx"."wp_options" 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-07-15 21:07:56 70000d7d7000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2018-07-15 21:07:56 70000d7d7000 InnoDB: Error: Fetch of persistent statistics requested for table "xxx"."wp_users" 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-07-15 21:07:56 70000d7d7000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2018-07-15 21:07:56 70000d7d7000 InnoDB: Error: Fetch of persistent statistics requested for table "xxx"."wp_usermeta" 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.
好像是:
此处的xxx的mysql的一些表
wp_options,wp_users,wp_usermeta
都了问题。
wordpress InnoDB: Error: Fetch of persistent statistics requested for table wp_options but the required system tables mysql.innodb_table_stats
InnoDB: Error: Table “mysql”.”innodb_table_stats” not found after upgrade to mysql 5.6 – Database Administrators Stack Exchange
MySQL 5.6 解决InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.问题 – 陌上归人的博客 – 博客园
MySQL :: InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
Error: Table “mysql”.”innodb_table_stats” not found – Databases and Data Technologies
Innodb problem
Apache Friends Support Forum • View topic – Error: MySQL shutdown unexpectedly.
Apache Friends Support Forum • View topic – BUG Xampp 1.8.3 MySQL Start
Apache Friends Support Forum • View topic – [BUG] XAMPP 1.8.2-1 & 1.8.3-0 issues
此处还真有
ibdata1
想要进去MAMP的mysql中去看看,所以先去找找怎么进去
通过MAMP的Help-》MAMP Help:
看出是:
/Applications/MAMP/Library/bin/
下面有mysql等工具,包括mysqladmin
所以可以去:
➜  bin ls mysql*
mysql                      mysql_secure_installation  mysqlauditgrep             mysqldbimport              mysqlindexcheck            mysqlserverinfo
mysql_client_test          mysql_setpermission        mysqlbinlog                mysqldiff                  mysqlmetagrep              mysqlshow
mysql_client_test_embedded mysql_tzinfo_to_sql        mysqlbug                   mysqldiskusage             mysqlprocgrep              mysqlslap
mysql_config               mysql_upgrade              mysqlcheck                 mysqldump                  mysqlreplicate             mysqltest
mysql_config_editor        mysql_waitpid              mysqld                     mysqldumpslow              mysqlrpladmin              mysqltest_embedded
mysql_convert_table_format mysql_zap                  mysqld_multi               mysqlfabric                mysqlrplcheck              mysqluc
mysql_embedded             mysqlaccess                mysqld_safe                mysqlfailover              mysqlrplms                 mysqluserclone
mysql_find_rows            mysqlaccess.conf           mysqldbcompare             mysqlfrm                   mysqlrplshow
mysql_fix_extensions       mysqladmin                 mysqldbcopy                mysqlhotcopy               mysqlrplsync
mysql_plugin               mysqlauditadmin            mysqldbexport              mysqlimport                mysqlserverclone
➜  bin pwd
/Applications/MAMP/Library/bin

➜  bin ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| xxx      |
| crifan_com_wp      |
| xxx           |
| xxx        |
| mysql              |
| performance_schema |
+--------------------+
7 rows in set (0.00 sec)

mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.02 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table innodb_index_stats;
ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats'
mysql> drop table innodb_table_stats;
ERROR 1051 (42S02): Unknown table 'mysql.innodb_table_stats'
mysql> drop table slave_master_info;
ERROR 1051 (42S02): Unknown table 'mysql.slave_master_info'
mysql> drop table slave_relay_log_info;
ERROR 1051 (42S02): Unknown table 'mysql.slave_relay_log_info'
mysql> drop table slave_worker_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>
此处已经不存在这几个表了,所以也没法删除,也无需再删除。
然后继续去删除残留文件:
➜  mysql pwd
/Applications/MAMP/db/mysql56/mysql
➜  mysql ls -la innode*
ls: innode*: No such file or directory
➜  mysql ls -la innodb*
-rw-rw-r--  1 crifan  admin  98304  7  3  2017 innodb_index_stats.ibd
-rw-rw-r--  1 crifan  admin  98304  7  3  2017 innodb_table_stats.ibd
➜  mysql ls -la slave_*
-rw-rw-r--  1 crifan  admin  98304  7  3  2017 slave_master_info.ibd
-rw-rw-r--  1 crifan  admin  98304  7  3  2017 slave_relay_log_info.ibd
-rw-rw-r--  1 crifan  admin  98304  7  3  2017 slave_worker_info.ibd
➜  mysql rm -rf innodb*
➜  mysql rm -rf slave_*
➜  mysql
然后再去新建这几个表:
➜  bin pwd
/Applications/MAMP/Library/bin
➜  bin ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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> 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;
Query OK, 0 rows affected (0.03 sec)

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> 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=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
Query OK, 0 rows affected (0.02 sec)

mysql> 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';
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye
重启MAMP(自动重启mysql),看看效果
问题依旧。
但是看:
/Applications/MAMP/logs/mysql_error_log.err
貌似没什么错误了啊
难道是:
需要针对对应的wordpress的数据库去操作
即针对此处的:/Applications/MAMP/db/mysql56/xxx 中的数据库去操作?
好像也不对啊。
【总结】
此处是参考:
Error: Table “mysql”.”innodb_table_stats” not found – Databases and Data Technologies
修复了mysql的错误。

转载请注明:在路上 » 【已解决】wordpress中mysql出错:InnoDB: Error: Fetch of persistent statistics requested for table wp_options but the required system tables mysql.innodb_table_stats

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
83 queries in 0.158 seconds, using 22.14MB memory