在生产库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`);