一、字段类型截图
下面介绍通过INSERT和LOAD两种形式插入0000-00-00 00:00:00数据到datetime类型字段的报错。
二、insert报错截图
三、load报错日志截图
四、错误模拟
1、建表
gbase> create table test_20230720 (a datetime);
Query OK, 0 rows affected (Elapsed: 00:00:00.12)
2、测试数据
[gbase@czg2 ~]$ cat TestData.txt
0000-00-00 00:00:00
3、LOAD数据报错
gbase> LOAD DATA INFILE 'sftp://root:qwer1234@192.168.142.12//home/gbase/TestData.txt' into table test_20230720 DATA_FORMAT 3 FIELDS TERMINATED BY '|' MAX_BAD_RECORDS 0;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: Task 6684683 failed, [192.168.142.12:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01-600) Gbase internal error: Task 6684683, Too many bad records!
SQL: LOAD /*+ TID('15794264') */ DATA INFILE 'sftp://root:********@192.168.142.12//home/gbase/TestData.txt#offset=0&length=20&firstblock&ffsize=20' INTO TABLE `czg`.`test_20230720_n1` DATA_FORMAT 3 FILE_FORMAT UNDEFINED FIELDS TERMINATED BY '|' MAX_BAD_RECORDS 0 HOST '::ffff:192.168.142.12' CURRENT_TIMESTAMP 16
4、查看记载日志
[gbase@czg2 Src]$ cat /opt/gcluster/log/gcluster/loader_logs/6684683/6684683_czg_test_20230720_n1_\:\:ffff\:192.168.142.12_20230720160427.trc
file_name | file_offset | record_len | column | reason
sftp://root:********@192.168.142.12//home/gbase/TestData.txt|0|20|1|Out of range value
5、INSERT数据报错
gbase> insert into test_20230720 values('0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1
五、解决方法
将0000-00-00 00:00:00改成0001-01-01 00:00:00即可。
1、INSERT测试
gbase> insert into test_20230720 values('0001-01-01 00:00:00');
Query OK, 1 row affected (Elapsed: 00:00:00.15)
2、LOAD测试
[gbase@czg2 ~]$ cat TestData.txt
0001-01-01 00:00:00
gbase> LOAD DATA INFILE 'sftp://root:qwer1234@192.168.142.12//home/gbase/TestData.txt' into table test_20230720 DATA_FORMAT 3 FIELDS TERMINATED BY '|' MAX_BAD_RECORDS 0;
Query OK, 1 row affected (Elapsed: 00:00:00.90)
Task 6684685 finished, Loaded 1 records, Skipped 0 records
3、验证
gbase> select * from test_20230720;
+---------------------+
| a |
+---------------------+
| 0001-01-01 00:00:00 |
| 0001-01-01 00:00:00 |
+---------------------+
2 rows in set (Elapsed: 00:00:00.09)