Error in Lighting Data Import

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: lighting 数据导入报错

| username: GreenGuan

[TiDB Usage Environment] Production Environment
[TiDB Version]
Database version 5.4.x
Lighting version 5.4.x
[Reproduction Path]
Error when importing data to MySQL 5.6, the target data table has been successfully created, and manually creating the table also works fine.

Error: fetch table columns failed: Error 1054: Unknown column ‘generation_expression’ in ‘field list’
tidb lightning encountered error: fetch table columns failed: Error 1054: Unknown column ‘generation_expression’ in ‘field list’

2
| username: 考试没答案 | Original post link

Please provide the table structure of the target end. The target end is 5.6, right?

3
| username: 考试没答案 | Original post link

You posted a SQL file extracted by Lightning. Let’s take a look.

4
| username: GreenGuan | Original post link

Table structure

CREATE TABLE `t1` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `advertiserid` int(10) DEFAULT NULL COMMENT '',
  `disabled` tinyint(1) DEFAULT '0' COMMENT '',
  `startdate` date DEFAULT NULL COMMENT '',
  `enddate` date DEFAULT NULL COMMENT '',
  `type` varchar(255) DEFAULT NULL COMMENT '',
  `typevlaue` float DEFAULT NULL COMMENT '',
  `camnumber` varchar(100) DEFAULT NULL COMMENT '',
  `memo` varchar(255) DEFAULT NULL COMMENT '',
  `approveflag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `contractid` varchar(25) DEFAULT NULL COMMENT '',
  `contracttype` tinyint(1) NOT NULL DEFAULT '1' COMMENT '',
  `userid` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `archive` tinyint(1) DEFAULT '0' COMMENT '',
  `createtime` datetime DEFAULT NULL COMMENT '',
  `status` tinyint(4) unsigned NOT NULL DEFAULT '1'  '',
  `budget` bigint(20) DEFAULT NULL COMMENT '',
  `budgetflag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '',
  `starttime` time DEFAULT NULL COMMENT '',
  `endtime` time DEFAULT NULL COMMENT '',
  `level` int(10) DEFAULT NULL COMMENT '',
  `testflag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `importflag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `deleteflag` tinyint(1) DEFAULT '0' COMMENT '',
  `searchengine` int(10) DEFAULT NULL COMMENT '',
  `balancepoint` int(10) DEFAULT NULL COMMENT '',
  `balanceratio` float(10,4) DEFAULT NULL COMMENT '',
  `incomesource` tinyint(1) DEFAULT NULL COMMENT '',
  `balancetype` varchar(10) NOT NULL DEFAULT '0' COMMENT '',
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  `last_modified_by` int(3) unsigned NOT NULL DEFAULT '0' COMMENT '',
  `revenueid` int(11) DEFAULT NULL,
  `equalbudgetflag` tinyint(1) NOT NULL DEFAULT '1' COMMENT '',
  `entity` varchar(50) NOT NULL COMMENT '',
  `publishertype` enum('network','exchange') NOT NULL DEFAULT 'network' COMMENT '',
  `displaytype` enum('pc_display','mobile_display','keyword_link','product_mapping','all_display','pc_native','pc_automatic','mix_display','encourage_display','cube_display','context_display','pc_mobile_mix_display','content_display','iaa_display') NOT NULL DEFAULT 'pc_display' COMMENT '',
  `budget_limited_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '',
  `urlparam` text DEFAULT NULL COMMENT '',
  `urlfrontparam` text DEFAULT NULL COMMENT '',
  `advanceflag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `qihu_campaignid` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '',
  `qihu_status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '',
  `price_rate` smallint(3) unsigned NOT NULL DEFAULT '100' COMMENT '',
  `status_consume` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `version_consume` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `newflag` tinyint(1) DEFAULT '0' COMMENT '',
  `cpt_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `ocpc_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `price` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `show_budget` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `show_budget_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `ocpc_url` varchar(5000) NOT NULL DEFAULT '' COMMENT '',
  `budget_mtime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '',
  `order_type` tinyint(4) NOT NULL DEFAULT '-1' COMMENT '',
  `is_offline_screen` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `mobile_native` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `offline_budget` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `offline_budgetflag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '',
  `weekly_budget` bigint(20) NOT NULL DEFAULT '0' COMMENT '',
  `budget_limited_alert` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `order_source` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
  `business_type_id` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `bid_policy` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  `accelerate_consume_flag` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`ID`) /*T![clustered_index] CLUSTERED */,
  KEY `advertiserid` (`advertiserid`),
  KEY `userid` (`userid`),
  KEY `qihu_campaignid` (`qihu_campaignid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1

SQL statement

INSERT INTO `t1` VALUES
(2330792,'Information Flow - Keyword',560607,1,'2018-06-26','2030-12-31',NULL,NULL,NULL,NULL,0,NULL,1,866682,0,'2018-06-26 13:10:06',1,10000,0,NULL,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,'0','2022-11-16 22:47:48',0,NULL,1,'galileo','exchange','pc_native','0000-00-00 00:00:00',NULL,NULL,0,0,'0000-00-00 00:00:00',1,100,1,0,1,0,0,0,0,0,'','2018-06-26 13:10:06',-1,0,0,0,0,0,0,0,0,0,0);

Supplement: The export was done using the dumpling tool, and the version is also 5.4.x.

5
| username: 考试没答案 | Original post link

There is no generation_expression column at all. Is it a function column? Or what kind of column is it?
Please share the table structure of the source table for us to take a look.

6
| username: 考试没答案 | Original post link

This SQL mode may cause an error.

7
| username: GreenGuan | Original post link

When I imported this table into other TiDB clusters, there was no problem, but when I imported it into MySQL alone, this issue occurred. The SQL mode I set in Lightning is sql-mode = “NO_ENGINE_SUBSTITUTION”.

8
| username: GreenGuan | Original post link

It seems to be a compatibility issue. When importing data with Lightning, it needs to update data in information_schema.COLUMNS, but MySQL 5.6 does not have the generation_expression column, so an error occurs.

9
| username: tidb菜鸟一只 | Original post link

Lighting is originally a tool for importing data into the TiDB cluster. To import data into MySQL, you can use the load file method.

10
| username: GreenGuan | Original post link

Well, the logic has been imported, it seems we can’t play like this.