mysql log stats_mysql/innodb_table_stats问题解决

解决MySQL Innodb分区表创建时表名过长导致被截断的问题,通过增加mysql.innodb_index_stats及mysql.innodb_table_stats表中table_name字段长度至199字符,确保兼容性并提供升级指南。

官网bug修改

mysql 官网Bug Fixed

Bugs Fixed

Important Change; Partitioning: After creating partitioned InnoDB tables with very long names, the table_name columns in the corresponding entries in the mysql.innodb_index_stats and mysql.innodb_table_stats system tables were truncated. To fix this issue, the length of the table_name column in each of these tables has been increased from 64 to 199 characters. In both cases, this is now the same as the lengths of these columns in MySQL 8.0.

When upgrading to this release, use mysql_upgrade to effect these changes in your MySQL installation. In the event that you fail to do this, MySQL generates the warning Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade in the error log.

2025-06-19 13:54:33 10592 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Normal shutdown 2025-06-19 13:54:33 10592 [Note] Giving 15 client threads a chance to die gracefully 2025-06-19 13:54:33 10592 [Note] Event Scheduler: Killing the scheduler thread, thread id 116 2025-06-19 13:54:33 10592 [Note] Event Scheduler: Waiting for the scheduler thread to reply 2025-06-19 13:54:33 10592 [Note] Event Scheduler: Stopped 2025-06-19 13:54:33 10592 [Note] Event Scheduler: Purging the queue. 2 events 2025-06-19 13:54:33 10592 [Note] Shutting down slave threads 2025-06-19 13:54:35 10592 [Note] Forcefully disconnecting 3 remaining clients 2025-06-19 13:54:35 10592 [Warning] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Forcing close of thread 35 user: 'wcs' 2025-06-19 13:54:35 10592 [Warning] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Forcing close of thread 8 user: 'wcs' 2025-06-19 13:54:35 10592 [Warning] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Forcing close of thread 3729 user: 'wcs' 2025-06-19 13:54:35 10592 [Note] Binlog end 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'partition' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_METRICS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_CMPMEM' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_CMP' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_LOCKS' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'INNODB_TRX' 2025-06-19 13:54:35 10592 [Note] Shutting down plugin 'InnoDB' 2025-06-19 13:54:35 10592 [Note] InnoDB: FTS optimize thread exiting. 2025-06-19 13:54:35 10592 [Note] InnoDB: Starting shutdown... 2025-06-19 13:54:37 10592 [Note] InnoDB: Shutdown completed; log sequence number 31950984072 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'BLACKHOLE' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'ARCHIVE' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'MRG_MYISAM' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'MyISAM' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'MEMORY' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'CSV' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'sha256_password' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'mysql_old_password' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'mysql_native_password' 2025-06-19 13:54:37 10592 [Note] Shutting down plugin 'binlog' 2025-06-19 13:54:37 10592 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Shutdown complete 2025-06-19 13:54:38 3896 [Note] Plugin 'FEDERATED' is disabled. 2025-06-19 13:54:38 289c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator. 2025-06-19 13:54:38 3896 [Note] InnoDB: Using atomics to ref count buffer pool pages 2025-06-19 13:54:38 3896 [Note] InnoDB: The InnoDB memory heap is disabled 2025-06-19 13:54:38 3896 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2025-06-19 13:54:38 3896 [Note] InnoDB: Memory barrier is not used 2025-06-19 13:54:38 3896 [Note] InnoDB: Compressed tables use zlib 1.2.3 2025-06-19 13:54:38 3896 [Note] InnoDB: Not using CPU crc32 instructions 2025-06-19 13:54:38 3896 [Note] InnoDB: Initializing buffer pool, size = 8.0G 2025-06-19 13:54:38 3896 [Note] InnoDB: Completed initialization of buffer pool 2025-06-19 13:54:38 3896 [Note] InnoDB: Highest supported file format is Barracuda. 2025-06-19 13:54:39 3896 [Note] InnoDB: 128 rollback segment(s) are active. 2025-06-19 13:54:39 3896 [Note] InnoDB: Waiting for purge to start 2025-06-19 13:54:39 3896 [Note] InnoDB: 5.6.21 started; log sequence number 31950984072 2025-06-19 13:54:39 3896 [Note] Server hostname (bind-address): '*'; port: 3306 2025-06-19 13:54:39 3896 [Note] IPv6 is available. 2025-06-19 13:54:39 3896 [Note] - '::' resolves to '::'; 2025-06-19 13:54:39 3896 [Note] Server socket created on IP: '::'. 2025-06-19 13:54:39 3896 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2025-06-19 13:54:39 3896 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 2025-06-19 13:54:39 3896 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2025-06-19 13:54:39 3896 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2025-06-19 13:54:39 3896 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened. 2025-06-19 13:54:39 3896 [Note] Event Scheduler: Loaded 2 events 2025-06-19 13:54:39 3896 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: ready for connections. Version: '5.6.21-log' socket: '' port: 3306 MySQL Community Server (GPL) 2025-06-19 13:54:39 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.22'. 2025-06-19 13:54:39 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.22'. 2025-06-19 13:54:39 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:39 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:39 3896 [Note] - fe80::659c:c088:9d63:4346%17 2025-06-19 13:54:39 3896 [Note] - fe80::659c:c088:9d63:4346%17 2025-06-19 13:54:39 3896 [Note] - 11.1.85.24 2025-06-19 13:54:39 3896 [Note] - 169.254.119.0 2025-06-19 13:54:39 26c InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 26c2025-06-19 13:54:39 2a90 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 2a90 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedercameradata" 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. InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedercameradata" 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. 2025-06-19 13:54:39 2cb0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 2cb0 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."alreadysortinfo" 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. 2025-06-19 13:54:39 2028 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 20282025-06-19 13:54:39 249c InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 249c InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."alreadysortinfo" 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. InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."alreadysortinfo" 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. 2025-06-19 13:54:39 1c84 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 1c84 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."errorrecord" 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. 2025-06-19 13:54:39 808 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:39 808 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."systemlog" 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. 2025-06-19 13:54:41 2d04 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:41 2d04 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."devicestate_monitorinfo" 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. 2025-06-19 13:54:41 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.31'. 2025-06-19 13:54:41 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:41 3896 [Note] - fe80::e985:a0f1:2751:eada%17 2025-06-19 13:54:41 3896 [Note] - 169.254.170.71 2025-06-19 13:54:42 24b8 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:42 24b8 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."userinfo" 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. 2025-06-19 13:54:42 3896 [Warning] Hostname 'DESKTOP-1LL2I5F' does not resolve to '11.1.85.33'. 2025-06-19 13:54:42 3896 [Note] Hostname 'DESKTOP-1LL2I5F' has the following IP addresses: 2025-06-19 13:54:42 3896 [Note] - fe80::7082:fd95:7ec0:bcc8%17 2025-06-19 13:54:42 3896 [Note] - 169.254.255.9 2025-06-19 13:54:42 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.26'. 2025-06-19 13:54:42 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:42 3896 [Note] - fe80::daa7:903b:6a3c:c735%17 2025-06-19 13:54:42 3896 [Note] - 11.1.85.31 2025-06-19 13:54:42 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.22'. 2025-06-19 13:54:42 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:42 3896 [Note] - fe80::659c:c088:9d63:4346%17 2025-06-19 13:54:42 3896 [Note] - 11.1.85.37 2025-06-19 13:54:43 808 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:43 808 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."portcode_timing" 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. 2025-06-19 13:54:43 25a0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:43 25a0 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."helpmanual" 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. 2025-06-19 13:54:45 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.30'. 2025-06-19 13:54:45 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:45 3896 [Note] - fe80::6b84:3a08:e1d0:e750%17 2025-06-19 13:54:45 3896 [Note] - 11.1.85.31 2025-06-19 13:54:45 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.24'. 2025-06-19 13:54:45 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:45 3896 [Note] - fe80::daa7:903b:6a3c:c735%17 2025-06-19 13:54:45 3896 [Note] - 11.1.85.28 2025-06-19 13:54:47 3896 [Warning] Hostname 'DESKTOP-S1Q82IV' does not resolve to '11.1.85.27'. 2025-06-19 13:54:47 3896 [Note] Hostname 'DESKTOP-S1Q82IV' has the following IP addresses: 2025-06-19 13:54:47 3896 [Note] - fe80::3711:88ce:2316:8596%17 2025-06-19 13:54:47 3896 [Note] - 169.254.226.201 2025-06-19 13:54:47 808 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2c6c InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2c6c2025-06-19 13:54:47 808 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedinginfo" 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. InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedinginfo" 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. 2025-06-19 13:54:47 24b8 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 24b82025-06-19 13:54:47 78c InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedinginfo" 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. InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 78c InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedinginfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."alreadysortinfohist" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."arrival" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."arrival_copy" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."bagcheckalarmforsto" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."bagcode_base64" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."baglifeinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."bagscanresultinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."basisdata" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."billlog" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."billloghist" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."checkscaleinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."countinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."customerinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."departure" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."departure_copy" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."devicestateinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."devicestateinfooferrorclosewcs" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."eachscanresult" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."electricenergyrecord" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."event_log" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."fail_upload" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."failsortinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."failsortinfohist" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."failuploaddata" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedinginfo1" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."feedinginfohist" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."interfaceusagedetail" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."jdv2picinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."loading_occupancy" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."manualcodingresult" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."manualcodingresulthist" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."moduleversion" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."nccountinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."orderauto" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."packagecode" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."packageprintinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."port_bagid" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."port_printer" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."portandboxcode" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."portcode_timinghist" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."queue_task" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."readtarget" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."reuploadopccodedata" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."routingtable" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."scannersortinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."scanresult" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."show_billcode" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."sortcodeinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."statisticsofpackagesupplyrecords" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."switchshiftofsto" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."ts" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."upload_fail" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."uploaddata" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."uploadpicinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."user" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."useractioninfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."userrank_rankname" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."userrankinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."webpointparameter" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."webpointparameter2" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."yzgjsortinfo" 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. 2025-06-19 13:54:47 2738 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2025-06-19 13:54:47 2738 InnoDB: Error: Fetch of persistent statistics requested for table "db_wcs"."yzshift" 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. 2025-06-19 13:54:49 3896 [Warning] Hostname 'DESKTOP-S1Q82IV' does not resolve to '11.1.85.25'. 2025-06-19 13:54:49 3896 [Note] Hostname 'DESKTOP-S1Q82IV' has the following IP addresses: 2025-06-19 13:54:49 3896 [Note] - fe80::a18:a682:b681:52a8%17 2025-06-19 13:54:49 3896 [Note] - 169.254.226.201 2025-06-19 13:54:49 3896 [Warning] Hostname 'DESKTOP-S1Q82IV' does not resolve to '11.1.85.39'. 2025-06-19 13:54:49 3896 [Note] Hostname 'DESKTOP-S1Q82IV' has the following IP addresses: 2025-06-19 13:54:49 3896 [Note] - fe80::416:a93d:15ac:92bc%17 2025-06-19 13:54:49 3896 [Note] - 169.254.226.201 2025-06-19 13:54:49 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.40'. 2025-06-19 13:54:49 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:49 3896 [Note] - fe80::e985:a0f1:2751:eada%17 2025-06-19 13:54:49 3896 [Note] - 11.1.85.28 2025-06-19 13:54:50 3896 [Warning] Hostname 'DESKTOP-S1Q82IV' does not resolve to '11.1.85.32'. 2025-06-19 13:54:50 3896 [Note] Hostname 'DESKTOP-S1Q82IV' has the following IP addresses: 2025-06-19 13:54:50 3896 [Note] - fe80::3711:88ce:2316:8596%17 2025-06-19 13:54:50 3896 [Note] - 169.254.226.201 2025-06-19 13:54:50 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.38'. 2025-06-19 13:54:50 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:50 3896 [Note] - fe80::e985:a0f1:2751:eada%17 2025-06-19 13:54:50 3896 [Note] - 169.254.178.147 2025-06-19 13:54:51 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.36'. 2025-06-19 13:54:51 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:51 3896 [Note] - fe80::e985:a0f1:2751:eada%17 2025-06-19 13:54:51 3896 [Note] - 169.254.170.71 2025-06-19 13:54:52 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.28'. 2025-06-19 13:54:52 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:52 3896 [Note] - fe80::daa7:903b:6a3c:c735%17 2025-06-19 13:54:52 3896 [Note] - 11.1.85.36 2025-06-19 13:54:53 3896 [Warning] Hostname 'DESKTOP-56G0R2L' does not resolve to '11.1.85.37'. 2025-06-19 13:54:53 3896 [Note] Hostname 'DESKTOP-56G0R2L' has the following IP addresses: 2025-06-19 13:54:53 3896 [Note] - fe80::daa7:903b:6a3c:c735%17 2025-06-19 13:54:53 3896 [Note] - 11.1.85.28 windows2019下安装的mysql5.6 今天启动报错1067,重启服务器后正常了,以上是收集的mysql日志,帮忙分析报错1067的原因
06-21
<think>我们正在解决用户遇到的访问系统表被拒绝的问题,特别是关于 `mysql.innodb_index_stats` 表的访问错误(错误3554)。根据引用资料,这个问题通常是由于系统表结构不匹配或损坏引起的。以下是分步解决方案: ### 问题分析 错误信息通常表现为: ``` Warning: InnoDB: Cannot open table mysql/innodb_index_stats ``` 或 ``` Access to system table 'mysql.innodb_index_stats' is rejected ``` 这可能是由于以下原因: 1. MySQL升级后未运行`mysql_upgrade`,导致系统表结构不匹配[^3][^5]。 2. 系统表损坏或权限问题。 ### 解决方案 #### 步骤1: 运行mysql_upgrade修复系统表 这是最直接的解决方法,适用于MySQL升级后未正确更新系统表的情况。 ```bash # 停止MySQL服务 sudo systemctl stop mysql # 运行mysql_upgrade(使用正确的用户名和密码) mysql_upgrade -u root -p # 启动MySQL服务 sudo systemctl start mysql ``` > 注意:在MySQL 8.0中,`mysql_upgrade`已被废弃,但在5.7到8.0的升级中仍然需要[^5]。对于MySQL 8.0,推荐使用`mysql_upgrade`替代工具,但实际命令不变。 如果上述命令无效,可以尝试强制升级: ```bash mysql_upgrade -u root -p --force ``` #### 步骤2: 手动重建系统表(如果步骤1无效) 如果运行`mysql_upgrade`后问题依旧,可能需要手动重建系统表。 ```bash # 停止MySQL服务 sudo systemctl stop mysql # 备份数据目录(重要!) sudo cp -r /var/lib/mysql /var/lib/mysql_backup # 删除系统表(危险操作,确保备份!) sudo rm /var/lib/mysql/mysql/innodb_table_stats.frm sudo rm /var/lib/mysql/mysql/innodb_table_stats.ibd sudo rm /var/lib/mysql/mysql/innodb_index_stats.frm sudo rm /var/lib/mysql/mysql/innodb_index_stats.ibd # 重新初始化系统表(仅限MySQL 5.7及以上) mysqld --initialize-insecure --user=mysql # 启动MySQL服务 sudo systemctl start mysql # 再次运行mysql_upgrade mysql_upgrade -u root -p ``` > **警告**:此操作会重置系统表,包括用户和权限,因此需要提前备份[^2][^5]。 #### 步骤3: 检查表权限 如果问题仍然存在,检查系统表的权限: ```bash # 查看文件权限 ls -l /var/lib/mysql/mysql/innodb_index_stats.* # 正确权限应为mysql:mysql sudo chown mysql:mysql /var/lib/mysql/mysql/innodb_index_stats.* sudo chmod 660 /var/lib/mysql/mysql/innodb_index_stats.* ``` #### 步骤4: 导入数据时排除系统表(预防措施) 在导出数据时,避免包含系统表,以防止导入时出现冲突[^4]: ```bash mysqldump -u root -p --all-databases --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > dump.sql ``` ### 验证修复 ```sql -- 登录MySQL mysql -u root -p -- 尝试查询系统表 SELECT * FROM mysql.innodb_index_stats LIMIT 1; ``` 如果查询成功,则问题解决。 ### 预防措施 1. **升级后必运行**:每次升级MySQL版本后,立即运行`mysql_upgrade`。 2. **备份系统表**:升级前备份`mysql`数据库。 3. **使用官方工具**:利用`mysql_upgrade`工具处理升级后的系统表更新。 ### 常见错误处理 - **错误3554**:通常与字符集问题相关,但若在修复字符集后出现系统表错误,则按上述步骤处理。 - **表不存在**:如果错误提示表不存在,则需手动重建(步骤2)。 §§相关问题§§ 1. 如何安全升级MySQL 5.7到8.0而不损坏系统表? 2. 运行mysql_upgrade时遇到权限错误怎么办? 3. MySQL系统表损坏后如何恢复用户权限?</think>### 解决 MySQL 访问系统表被拒绝错误(错误 3554) 当访问 `mysql.innodb_index_stats` 等系统表时出现拒绝访问错误(错误 3554),通常是由于系统表损坏或版本不兼容导致。以下是完整解决方案: --- ### 问题原因分析 1. **系统表损坏**:`innodb_index_stats`/`innodb_table_stats` 表结构损坏或缺失[^2][^3] 2. **版本升级残留**:MySQL 5.7→8.0 升级后未正确更新系统表[^5] 3. **权限异常**:系统表权限被意外修改 4. **字符集冲突**:列长度不匹配(常见于跨版本迁移)[^3] --- ### 解决方案(逐步操作) #### 步骤1:强制重建系统表 ```bash # 停止MySQL服务 sudo systemctl stop mysql # 备份数据(重要!) sudo cp -R /var/lib/mysql /backup/mysql_data # 重建系统表(保持用户数据) sudo mysqld --initialize-insecure --user=mysql # 恢复所有权 sudo chown -R mysql:mysql /var/lib/mysql # 启动服务 sudo systemctl start mysql ``` > **注意**:`--initialize-insecure` 会重置 root 密码为空,操作后需立即修改密码[^5] #### 步骤2:运行 mysql_upgrade ```bash # 升级系统表结构 sudo mysql_upgrade -u root -p --force # 重启生效 sudo systemctl restart mysql ``` > 必须使用 `--force` 参数覆盖现有系统表[^3][^5] #### 步骤3:修复表权限 ```sql -- 登录MySQL mysql -u root -p -- 修复系统表权限 REPAIR TABLE mysql.innodb_index_stats; REPAIR TABLE mysql.innodb_table_stats; -- 刷新权限 FLUSH PRIVILEGES; ``` #### 步骤4:验证修复 ```sql -- 检查表状态 SELECT * FROM mysql.innodb_index_stats LIMIT 1; -- 查看错误日志 SHOW GLOBAL VARIABLES LIKE 'log_error'; sudo tail -f /var/log/mysql/error.log ``` 正常应返回统计信息而非权限错误[^1] --- ### 预防措施 1. **升级后必执行**: ```bash # 任何版本升级后必须执行 mysql_upgrade -u root -p --force sudo systemctl restart mysql ``` 2. **安全导出数据**: ```bash # 排除系统表导出 mysqldump -u root -p --all-databases \ --ignore-table=mysql.innodb_index_stats \ --ignore-table=mysql.innodb_table_stats > full_backup.sql ``` [^4] 3. **自动维护任务**: ```sql /* 每月自动优化系统表 */ CREATE EVENT optimize_system_tables ON SCHEDULE EVERY 1 MONTH DO OPTIMIZE TABLE mysql.innodb_index_stats, mysql.innodb_table_stats; ``` --- ### 高级修复(当常规方法失效) 若仍出现 `Column length mismatch` 错误[^3]: ```bash # 删除损坏的系统表 mysql -u root -p -e "DROP TABLE mysql.innodb_index_stats" # 从模板重建 mysql -u root -p < /usr/share/mysql/mysql_system_tables.sql # 强制升级 mysql_upgrade --force ``` --- ### 验证成功标志 1. 可正常查询系统表: ```sql SELECT * FROM mysql.innodb_index_stats WHERE table_name='users'; ``` 2. 错误日志无警告: ``` [Note] mysql_upgrade: Upgrade process completed successfully ``` 3. 系统表状态正常: ```sql CHECK TABLE mysql.innodb_index_stats; -- 返回 OK ``` > 提示:若数据重要,操作前务必使用 `mysqldump` 完整备份[^4]
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值