阅读:592回复:0
MySql Generated Column 引发 ERROR 3105 (HY000) 错误
问题描述
MySql 服务器,导入备份的数据 (mysqldump 导出的SQL 文件),提示以下错误信息: ERROR 3105 (HY000) at line 3000: The value specified for generated column 'y2018' in table 'fund_risk' is not allowed. 排错过程 1. SQL 语句 # 定位备份文件中的第 3000行,SQL 语句如下: INSERT INTO `fund_risk` VALUES ('Account15','2017-07-21','1.0730','0.326585','0.073000','0.322319','-0.094579','0.048920','-0.076047','-0.034653','0.000000','0.000000','-0.034653','0.006703','0.339123','-0.012538','0.100513','0.073000','0.007512','{\"y2017\": 0.07299999999999995}','2.447117','5.997243','0.164869','-3.101997','-0.011177','0.119890',0,'2018-03-12 05:40:38','2018-05-03 13:28:46',NULL,'0.073000',NULL,NULL,NULL,NULL),(...))尝试手动执行 SQL,仍然提示 ERROR 3105 错误信息。 2. 检查表结构 MySQL > show create table fund_risk; ...省略 | fund_risk | CREATE TABLE `fund_risk` ( `inner_code` varchar(30) NOT NULL COMMENT 'fund', ...省略 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `y2018` decimal(10,6) GENERATED ALWAYS AS (json_extract(`ret_a`,'$.y2018')) VIRTUAL, ...省略 `y2013` decimal(10,6) GENERATED ALWAYS AS (json_extract(`ret_a`,'$.y2013')) VIRTUAL, PRIMARY KEY (`inner_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='fund_risk' | +-----------+---------------------------------------------------------------------------- 35 rows in set (0.00 sec) # xxx GENERATED ALWAYS AS xxx VIRTUAL, 这个“列”怎么这么特别? 3. 使用 google 大法找到错误原因 参考文章2,描述的故障相同,其中一段话直接说明了问题原因如下: if you have generated columns you need to use the version of mysqldump from MySQL 5.7.9 or higher, because earlier versions have a bug. # 也就是说 mysqldump 导出带有 generated columns的数据,要使用 mysqldump 5.7.9 以上的版本,否则会遇到 ERROR 3105 (HY000) 这个问题。 # 验证下备份所使用的 myqldump 版本。 mysqldump --version mysqldump Ver 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64) 解决方法 # 找到问题原因,解决的方法就是更新 mysql/mysqldump 版本 。 1. 更新 mysqldump (mysql)版本。 mysqldump --version mysqldump Ver 10.13 Distrib 5.7.17, for Linux (x86_64) 2. 验证 # 新版本 mysqldump 导出 SQL 数据,正常执行,仔细观察两次dump 的数据是不同的,新版 mysqldump 列的数量是小于 老版本的。 INSERT INTO `fund_risk` (`inner_code`, `trade_date`, `nv_accum`, `ret`, `ret_cum`, `alpha`, `beta`, `dvol`, `ir`, `mdd`, `mdd_tm`, `mdd_tw`, `mdd_ty`, `r2`, `ret_b`, `ret_ex`, `ret_tm`, `ret_ty`, `ret_tw`, `ret_a`, `sharpe`, `sortino`, `te`, `treynor`, `var`, `vol`, `nm_days`, `create_time`, `update_time`) VALUES ('Account15','2017-07-21',1.0730,0.326585,0.073000,0.322319,-0.094579,0.048920,-0.076047,-0.034653,0.000000,0.000000,-0.034653,0.006703,0.339123,-0.012538,0.100513,0.073000,0.007512,'{\"y2017\": 0.07299999999999995}',2.447117,5.997243,0.164869,-3.101997,-0.011177,0.119890,0,'2018-03-12 05:40:38','2018-05-03 13:28:46'); 参考 文章1: MySQL 5.7新特性之 Generated Column Generated Column 是MySQL 5.7 引入的新特性,所谓Cenerated Column,就是数据库中这一列由其他列计算而得。 在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。 文章2: how to restore mysql backup that have generated always as column? 文章3: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html # |
|