南大通用GBase 8a 加载LOAD数据无法入库错误 skipped records，Out of range value

GBase 8a 在加载LOAD时，如果发现数据和目标表不匹配，会将错误数据和原因，写入到日志里。 其中Out of range value错误是因为数据超过了范围，包括数字过大或过小，日期超过限制等。

测试样例

一个3列的表

gbase> create table tload_3(id int, name varchar(100),birth date);
Query OK, 0 rows affected (Elapsed: 00:00:00.33)

样例数据

制造了异常数据，一个超过int最大，一个日期不正确

[gbase@localhost ~]$ cat tload.txt
3,Third,2020-01-02
4,Fouth,2020-14-16
9999999999,MAX,2020-12-13

执行加载

有1行正常入库，2行错误数据

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.107/home/gbase/tload.txt' into table tload_3;
Query OK, 1 row affected (Elapsed: 00:00:00.68)
Task 1572896 finished, Loaded 1 records, Skipped 2 records

查看表数据

确实有1行

gbase> select * from tload_3;
+------+-------+------------+
| id   | name  | birth      |
+------+-------+------------+
|    3 | Third | 2020-01-02 |
+------+-------+------------+
1 row in set (Elapsed: 00:00:00.00)

查看错误日志和数据

此处为了方便，直接用SQL方式读取。可以看到报错信息 Out of range value。注意用SQL方式，可以从结果里直接看到错误数据。如果从文件查看，是分成2个文件保存的。

gbase> show load logs limit 2;
+---------+---------+---------+-------------------+--------------------------------------------------------+-------------+------------+------------+---------------------+----------------------------+
| task_id | db_name | tb_name | err_data_ip       | file_name                                              | file_offset | record_len | err_column | err_reason          | err_data                   |
+---------+---------+---------+-------------------+--------------------------------------------------------+-------------+------------+------------+---------------------+----------------------------+
| 1572896 | testdb  | tload_3 | ::ffff:10.0.2.107 | sftp://gbase:*********@10.0.2.107/home/gbase/tload.txt | 19          | 19         | 3          | Out of range value
 | 4,Fouth,2020-14-16
        |
| 1572896 | testdb  | tload_3 | ::ffff:10.0.2.107 | sftp://gbase:*********@10.0.2.107/home/gbase/tload.txt | 38          | 26         | 1          | Out of range value
 | 9999999999,MAX,2020-12-13
 |
+---------+---------+---------+-------------------+--------------------------------------------------------+-------------+------------+------------+---------------------+----------------------------+
2 rows in set (Elapsed: 00:00:00.00)

通过文件查看错误日志和数据

[gbase@localhost ~]$ cd /opt/gcluster/log/gcluster/loader_logs/1572896
[gbase@localhost 1572896]$
[gbase@localhost 1572896]$ ll
total 12
-rw------- 1 gbase gbase 220 Sep  3 19:16 1572896_loader_result.log
-rw------- 1 gbase gbase  45 Sep  3 19:16 1572896_testdb_tload_3_n1_::ffff:10.0.2.107_20200903191637.err
-rw------- 1 gbase gbase 239 Sep  3 19:16 1572896_testdb_tload_3_n1_::ffff:10.0.2.107_20200903191637.trc
[gbase@localhost 1572896]$
[gbase@localhost 1572896]$ cat 1572896_testdb_tload_3_n1_\:\:ffff\:10.0.2.107_20200903191637.trc
file_name    |   file_offset    |   record_len   |   column    |    reason
sftp://gbase:*********@10.0.2.107/home/gbase/tload.txt|19|19|3|Out of range value
sftp://gbase:*********@10.0.2.107/home/gbase/tload.txt|38|26|1|Out of range value
[gbase@localhost 1572896]$ cat 1572896_testdb_tload_3_n1_\:\:ffff\:10.0.2.107_20200903191637.err
4,Fouth,2020-14-16
9999999999,MAX,2020-12-13

解决方案

修复错误数据，或者调整数据库表字段类型

其它加载错误，请参考

GBase 8a数据库加载LOAD报错信息分析和解决文章汇总