[MCOL-3717] cpimport fails intermittently with "invalid default value in system catalog" Created: 2020-01-09  Updated: 2023-10-25  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: cpimport
Affects Version/s: 1.4.1
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Faisal Saeed (Inactive) Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

RHEL 7 Linux with 48 cores 6TB SSD storage


 Description   

When loading a text data file I faced this issue intermittently that says a particular timestamp column has an invalid default value in the system catalog.

[root@mariadb02 data]# cpimport csdb t_master_cs t_master_trim.2m -s '|'
 
Locale is : C
 
Using table OID 3783 as the default JOB ID
 
Input file(s) will be read from : /mariadb/data
 
Job description file : /var/lib/columnstore/data/bulk/tmpjob/3783_D20200109_T000730_S069986_Job_3783.xml
 
2020-01-09 00:07:30 (67436) ERR  : Error loading job file /var/lib/columnstore/data/bulk/tmpjob/3783_D20200109_T000730_S069986_Job_3783.xml; Column issue_date in table csdb.t_master_cs has an invalid default value in system catalog. [1154]
 
Error in loading job information;  An XML Parsing error; cpimport.bin is terminating.

The column is defined as `ISSUE_DATE timestamp(6) NOT NULL`

Rerunning the same cpimport script a second or a third-time always works successfully.

 Comments   
Comment by David Hall (Inactive) [ 2020-01-09 ]

Please show the results of running:
select defaultvalue from calpontsys.syscolumn where columnname='issue_date';

The normal default is "current_timestamp() ON UPDATE current_timestamp()", but if somehow something else got in there, we need to know.

Also if your import file has values for the timestamp, rather than letting the default handle it, that may be a clue, especially if that value is "0000-00-00 00:00:00" or some variation of 0.

Comment by Ryan Wallace [ 2020-07-26 ]

I'm also experiencing a similar intermittent error using cpimport with the command:

tail <redacted>.csv --lines=+2 | cpimport <redacted> scheduled_and_hours_worked_daily -E '"' -s ','

Intermittently, the command returns the following error:

2020-07-26 09:26:43 (7481) ERR  : Error loading job file /var/lib/columnstore/data/bulk/tmpjob/5773_D20200726_T092643_S693129_Job_5773.xml; Column job_archived_date in table <redacted>.scheduled_and_hours_worked_daily has an invalid default value in system catalog. [1154]

show create table scheduled_and_hours_worked_daily returns the following entry for job_archived_date:

`job_archived_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

select defaultvalue from calpontsys.syscolumn where columnname='job_archived_date'; returns 

+---------------------+
 
| defaultvalue        |
 
+---------------------+
 
| 0000-00-00 00:00:00 |
 
| 0000-00-00 00:00:00 |
 
| 0000-00-00 00:00:00 |
 
| 0000-00-00 00:00:00 |
 
+---------------------+

(A column with the same name appears in 4 tables.)

Like for Fasial, rerunning the same command a second, third, or fourth time, cpimport always succeeds.

Generated at Thu Feb 08 02:44:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.