'mysql.column_stats' doesn't exist and Table 'mysql.index_stats' doesn'
短信预约 -IT技能 免费直播动态提醒
在生产库MariabDB中修改字段类型,提示如下错误:
Table 'mysql.column_stats' doesn't exist
Table 'mysql.index_stats' doesn't exist
MariaDB版本如下:
MariaDB [mysql]> select @@version;
+---------------------+
| @@version |
+---------------------+
| 10.0.12-MariaDB-log |
+---------------------+
连接mysql数据库检查表:
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| innodb_index_stats |
| innodb_table_stats |
| inventory |
| 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 |
+---------------------------+
检查发现缺少提示错误的几张表,column_stats、index_stats、table_stats
解决方法:
在主库创建以上表:
CREATE TABLE IF NOT EXISTS `column_stats` (
`db_name` varchar(64) NOT NULL COMMENT 'Database the table is in.',
`table_name` varchar(64) NOT NULL COMMENT 'Table name.',
`column_name` varchar(64) NOT NULL COMMENT 'Name of the column.',
`min_value` varchar(255) DEFAULT NULL COMMENT 'Minimum value in the table (in text form).',
`max_value` varchar(255) NOT NULL COMMENT 'Maximum value in the table (in text form).',
`nulls_ratio` decimal(12,4) DEFAULT NULL COMMENT 'Fraction of NULL values (0 - no NULLs, 0.5 - half values are NULLs, 1 - all values are NULLs).',
`avg_length` decimal(12,4) DEFAULT NULL COMMENT 'Average length of column value, in bytes. Counted as if one ran SELECT AVG(LENGTH(col)). This doesn''t count NULL bytes, assumes endspace removal for CHAR(n), etc.',
`avg_frequency` decimal(12,4) DEFAULT NULL COMMENT 'Average number of records with the same value',
`hist_size` tinyint(3) unsigned DEFAULT NULL COMMENT 'Histogram size in bytes, from 0-255.',
`hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') DEFAULT NULL COMMENT 'Histogram type. See the histogram_type system variable.',
`histogram` varbinary(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `index_stats` (
`db_name` varchar(64) NOT NULL COMMENT 'Database the table is in.',
`table_name` varchar(64) NOT NULL COMMENT 'Table name',
`index_name` varchar(64) NOT NULL COMMENT 'Name of the index',
`prefix_arity` int(10) unsigned NOT NULL COMMENT 'Index prefix length. 1 for the first keypart, 2 for the first two, and so on. InnoDB''s extended keys are supported.',
`avg_frequency` decimal(12,4) DEFAULT NULL COMMENT 'Average number of records one will find for given values of (keypart1, keypart2, ..), provided the values will be found in the table.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `table_stats` (
`db_name` varchar(64) NOT NULL COMMENT 'Database the table is in .',
`table_name` varchar(64) NOT NULL COMMENT 'Table name.',
`cardinality` bigint(21) DEFAULT NULL COMMENT 'Number of records in the table.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `column_stats`
ADD PRIMARY KEY (`db_name`,`table_name`,`column_name`);
ALTER TABLE `index_stats`
ADD PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`);
ALTER TABLE `table_stats`
ADD PRIMARY KEY (`db_name`,`table_name`);
Table 'mysql.column_stats' doesn't exist
Table 'mysql.index_stats' doesn't exist
MariaDB版本如下:
MariaDB [mysql]> select @@version;
+---------------------+
| @@version |
+---------------------+
| 10.0.12-MariaDB-log |
+---------------------+
连接mysql数据库检查表:
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| innodb_index_stats |
| innodb_table_stats |
| inventory |
| 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 |
+---------------------------+
检查发现缺少提示错误的几张表,column_stats、index_stats、table_stats
解决方法:
在主库创建以上表:
CREATE TABLE IF NOT EXISTS `column_stats` (
`db_name` varchar(64) NOT NULL COMMENT 'Database the table is in.',
`table_name` varchar(64) NOT NULL COMMENT 'Table name.',
`column_name` varchar(64) NOT NULL COMMENT 'Name of the column.',
`min_value` varchar(255) DEFAULT NULL COMMENT 'Minimum value in the table (in text form).',
`max_value` varchar(255) NOT NULL COMMENT 'Maximum value in the table (in text form).',
`nulls_ratio` decimal(12,4) DEFAULT NULL COMMENT 'Fraction of NULL values (0 - no NULLs, 0.5 - half values are NULLs, 1 - all values are NULLs).',
`avg_length` decimal(12,4) DEFAULT NULL COMMENT 'Average length of column value, in bytes. Counted as if one ran SELECT AVG(LENGTH(col)). This doesn''t count NULL bytes, assumes endspace removal for CHAR(n), etc.',
`avg_frequency` decimal(12,4) DEFAULT NULL COMMENT 'Average number of records with the same value',
`hist_size` tinyint(3) unsigned DEFAULT NULL COMMENT 'Histogram size in bytes, from 0-255.',
`hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') DEFAULT NULL COMMENT 'Histogram type. See the histogram_type system variable.',
`histogram` varbinary(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `index_stats` (
`db_name` varchar(64) NOT NULL COMMENT 'Database the table is in.',
`table_name` varchar(64) NOT NULL COMMENT 'Table name',
`index_name` varchar(64) NOT NULL COMMENT 'Name of the index',
`prefix_arity` int(10) unsigned NOT NULL COMMENT 'Index prefix length. 1 for the first keypart, 2 for the first two, and so on. InnoDB''s extended keys are supported.',
`avg_frequency` decimal(12,4) DEFAULT NULL COMMENT 'Average number of records one will find for given values of (keypart1, keypart2, ..), provided the values will be found in the table.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `table_stats` (
`db_name` varchar(64) NOT NULL COMMENT 'Database the table is in .',
`table_name` varchar(64) NOT NULL COMMENT 'Table name.',
`cardinality` bigint(21) DEFAULT NULL COMMENT 'Number of records in the table.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `column_stats`
ADD PRIMARY KEY (`db_name`,`table_name`,`column_name`);
ALTER TABLE `index_stats`
ADD PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`);
ALTER TABLE `table_stats`
ADD PRIMARY KEY (`db_name`,`table_name`);
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341