Error using cpimport part of the job runs ok

10 posts / 0 new
Last post
lwiggers
lwiggers's picture
Offline
Last seen: 1 year 5 months ago
Joined: Jan 25 2012
Junior Boarder

Posts: 13

Lodewijk Wiggers
Error using cpimport part of the job runs ok

I am having stability issues on Windows 7, 64-bit using cpimport.

1) I have withnessed that the order!! of the files i wanted to add to a table was import to make it work -> i had fact_shift_xx.data with xx: 01..08 and it always crashed on 03, so i moved 03 as the first file to add, and it all worked ok..... how bizar.

2) I have run colxml to create the job file, removed some tables i didn't want to upload and ran cpimport. Boem error with some file error, BUT, if i look in log it did some tables without problem. No, i haven't altered the table definition after i ran colxml.

from error log:
2012-02-07 16:51:05 (1648) ERR : Error opening column file for OID-3568; DBRoot-1; partition-0; segment-0; filename-C:/Calpont/data1/000.dir/000.dir/013.dir/240.dir/000.dir/FILE000.cdf; opening a column file. The file was not found or was inaccessible. [1052]
2012-02-07 16:51:05 (1648) CRIT : Error in pre-processing the job file for table lwg.d_customer_sales [1052]

Strangely, if i go into explorer, the file is there, so it must have been accessible. No other process is working on those directories but cpimport at that time, so something in the code locks the file.

A larger part of the log file:

2012-02-07 17:04:24 (5872) INFO : Initializing import: Table-lwg.d_debit_credit...
2012-02-07 17:04:24 (5872) INFO : Initializing import: Table-lwg.d_debit_credit; Col-dimension_key; OID-3554; hwm-1; file-C:/Calpont/data1/000.dir/000.dir/013.dir/226.dir/000.dir/FILE000.cdf
2012-02-07 17:04:24 (5872) INFO : Initializing import: Table-lwg.d_debit_credit; Col-all_debit_credit_id; OID-3555; hwm-1; file-C:/Calpont/data1/000.dir/000.dir/013.dir/227.dir/000.dir/FILE000.cdf
2012-02-07 17:04:24 (5872) INFO : Initializing import: Table-lwg.d_debit_credit; Col-all_debit_credit_code; OID-3556; hwm-1; file-C:/Calpont/data1/000.dir/000.dir/013.dir/228.dir/000.dir/FILE000.cdf
2012-02-07 17:04:24 (5872) INFO : Initializing import: Table-lwg.d_debit_credit; Col-all_debit_credit_name; OID-3557; hwm-2; file-C:/Calpont/data1/000.dir/000.dir/013.dir/229.dir/000.dir/FILE000.cdf
2012-02-07 17:04:24 (5872) INFO : Opening existing store file for all_debit_credit_name; OID-3561; DBRoot-1; part-0; seg-0; hwm-0; file-C:/Calpont/data1/000.dir/000.dir/013.dir/233.dir/000.dir/FILE000.cdf
2012-02-07 17:04:24 (5872) INFO : Initializing import: Table-lwg.d_debit_credit; Col-debit_credit_id; OID-3558; hwm-1; file-C:/Calpont/data1/000.dir/000.dir/013.dir/230.dir/000.dir/FILE000.cdf
201

radams
radams's picture
Offline
Last seen: 2 hours 44 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: Error using cpimport part of the job runs ok

Hi,

The order of the import tables should not make any difference. Can you please post the schemas and the job file, or send them to 'community AT calpont dot com' ? We can look at the specifics of what your doing and try running the same test here.

Thanks,

Robert
Calpont

lwiggers
lwiggers's picture
Offline
Last seen: 1 year 5 months ago
Joined: Jan 25 2012
Junior Boarder

Posts: 13

Lodewijk Wiggers
Re: Error using cpimport part of the job runs ok

My problem mentioned is following:

I run sequentially the cpimport program with data files against same table:

First i tried this, running it from a bat file in windows 7 , 64-bit.

cpimport speedtest fact_shift fact_shift_0.data
cpimport speedtest fact_shift fact_shift_1.data
cpimport speedtest fact_shift fact_shift_2.data
cpimport speedtest fact_shift fact_shift_3.data
cpimport speedtest fact_shift fact_shift_4.data
cpimport speedtest fact_shift fact_shift_5.data
cpimport speedtest fact_shift fact_shift_6.data
cpimport speedtest fact_shift fact_shift_7.data

It errored on fact_shift_3.data, with the locking error i also have in problem 2 imentioned, i then loaded only that file into the truncated fact_shift and NO error...

Then i tried this order:
cpimport speedtest fact_shift fact_shift_3.data
cpimport speedtest fact_shift fact_shift_0.data
cpimport speedtest fact_shift fact_shift_1.data
cpimport speedtest fact_shift fact_shift_2.data
cpimport speedtest fact_shift fact_shift_4.data
cpimport speedtest fact_shift fact_shift_5.data
cpimport speedtest fact_shift fact_shift_6.data
cpimport speedtest fact_shift fact_shift_7.data

and it worked....

Hope this helps you. The files are way too large to attach,5GB each, and the content is ok, as i can load each single file separate into the emptied table.

Seems to me to be some nasty multi-threading issue, as it throws a file access violation which can only be caused by the program itself.

lwiggers
lwiggers's picture
Offline
Last seen: 1 year 5 months ago
Joined: Jan 25 2012
Junior Boarder

Posts: 13

Lodewijk Wiggers
Re: Error using cpimport part of the job runs ok

[attachment]C:fakepathJob_299.log[/attachment]

[attachment]C:fakepathJob_299.xml[/attachment]
Ok, some more of the same, i ran the job_299.xml which i created with colxml and i removed some tables, but it errors on d_customer_sales each time, but when i load d_customer_sales solo, it works fine.....

C:Calpontbulkdataimport>cpimport -j 299

Bulkload root directory : C:/Calpont/bulk
Job description file : C:/Calpont/bulk/job/Job_299.xml
Log file for this job: C:/Calpont/bulk/log/Job_299.log
2012-02-08 16:33:05 (1280) INFO : successfully loaded job file C:/Calpont/bulk/job/Job_299.xml
2012-02-08 16:33:05 (1280) INFO : Job file loaded, run time for this step : 7.17601 seconds
2012-02-08 16:33:05 (1280) INFO : PreProcessing check starts
2012-02-08 16:33:05 (1280) ERR : Error opening column file for OID-4623; DBRoot-1; partition-0; segment-0; filename-C:/Calpont/data1/000.dir/000.dir/018.dir/015.dir/000.dir/FILE000.cdf; opening a column file. The file was not found or was inaccessible. [1052]
2012-02-08 16:33:05 (1280) CRIT : Error in pre-processing the job file for table lwg.d_customer_sales [1052]

Error in loading job data

C:Calpontbulkdataimport>cpimport lwg d_customer_sales d_customer_sales.data

Using table OID 4618 as the default JOB ID
Bulkload root directory : C:/Calpont/bulk
Job description file : C:/Calpont/bulk/tmpjob/lwg_d_customer_sales_D20120208_T163430_Job_4618.xml
Log file for this job: C:/Calpont/bulk/log/Job_4618.log
2012-02-08 16:34:30 (5588) INFO : successfully loaded job file C:/Calpont/bulk/tmpjob/lwg_d_customer_sales_D20120208_T163430_Job_4618.xml
2012-02-08 16:34:30 (5588) INFO : Job file loaded, run time for this step : 0.1404 seconds
2012-02-08 16:34:30 (5588) INFO : PreProcessing check starts
2012-02-08 16:34:30 (5588) INFO : PreProcessing check completed
2012-02-08 16:34:30 (5588) INFO : preProcess completed, run time for this step : 0.0311999 seconds
2012-02-08 16:34:30 (5588) INFO : No of Read Threads Spawned = 1
2012-02-08 16:34:30 (5588) INFO : No of Parse Threads Spawned = 3
2012-02-08 16:34:30 (5588) INFO : For table lwg.d_customer_sales: 10441 rows processed and 10441 rows inserted.
2012-02-08 16:34:30 (5588) INFO : Bulk load completed, total run time : 0.280801 seconds

C:Calpontbulkdataimport>

bdempsey
bdempsey's picture
Offline
Last seen: 1 month 1 week ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: Error using cpimport part of the job runs ok

Just to keep you updated, we're having some difficulty reproducing your problem on any of our in-house systems. We'll keep trying, but in the meantime, any additional info you can provide about your specific environment might be helpful.

lwiggers
lwiggers's picture
Offline
Last seen: 1 year 5 months ago
Joined: Jan 25 2012
Junior Boarder

Posts: 13

Lodewijk Wiggers
Re: Error using cpimport part of the job runs ok

My environment is a new installed laptop pc with Windows 7 Professional, SP1, 64-bit, Intel Core i7, 6GB mem and 120GB SSD disk.

I can rerun the last problem i have,because i can reproduce that easily, but i can't ship you the data, is sensitive. Are there optional tracing options so i can provide you with more logging/tracing information?

Perhaps on your side, can you look up what situations can create the error i get? I only see 2 options for the error (if the error message is in sync with the real error).

1) The file could not be created in time before writing occurs to it (strange, because i think that threads would have to test if the file was there for writing, before attempting)
2) Multiple threads working on same target file, causing for the first to win the lock and the second to fail and cause for access denied.

Each time i ran the cpimport just to upload 1 file it worked. This tells me that there must be some threading communication conflict or sync problem occuring. Is it possible to have cpimport run only with single threads for wriite and parse? That would be an option to test.

Totally off topic: i wondered if there was some 'learning' effect in uploading the same data twice? I mean that the on the fly partitioning which is happening while loading the column data should/could be alot more efficient when the histogram of the data would be known. Or is this some option which is possible in the Enterprise version where you could optionally reorganise an existing stored column?

Also more documentation would be usefull to understand the storage strategy, it is clear that a file tree is build, but could performance be improved even more, if you split the column storages of columns you join on, across multipe physical disks. Mostly DWH's are IO bound and InfiniDB does a really good job making it a CPU bound challenge,but with large sets load time of the data into memory can make a significant portion of the total query time.

regards,
Lodewijk

bdempsey
bdempsey's picture
Offline
Last seen: 1 month 1 week ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: Error using cpimport part of the job runs ok

What version of InfiniDB are you running?

Version:
32/64 bit:
Enterprise/Community:

lwiggers
lwiggers's picture
Offline
Last seen: 1 year 5 months ago
Joined: Jan 25 2012
Junior Boarder

Posts: 13

Lodewijk Wiggers
Re: Error using cpimport part of the job runs ok

InfiniDB64-2.2.5-1, Community edition

bdempsey
bdempsey's picture
Offline
Last seen: 1 month 1 week ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: Error using cpimport part of the job runs ok

I have not been able to reproduce this problem. At this point I would ask you to contact our pre-sales engineering staff. They can hook you up with InfiniDB Enterprise and execute the necessary NDA's that should assist us in obtaining enough detail on you problem to continue finding a resolution.

lwiggers
lwiggers's picture
Offline
Last seen: 1 year 5 months ago
Joined: Jan 25 2012
Junior Boarder

Posts: 13

Lodewijk Wiggers
Re: Error using cpimport part of the job runs ok

More and more i get the feeling that it has nothing to do with my data or the schema/table structure i want to load in, but it has to do with cpimport itself.

Why?

I created a job with colxml for the entire schema (job 300).
I run this job and it crashes on the 25th table with the file not accessible error.

I create a job with colxml for only the table it crashes on (job 301). This job runs without any problem! Also loading this table with the simple cpimport call without job number works fine.

This makes me think that it could be a quantity thing for the loader (too much files or so), and i decide to split my 39 tables (which are in the 300 xml job file) into 4 parts, 10, 10, 10, 9 , and name them job 400,410,420,440 and i run those after each other, without any problem, also the table it crashed on when i ran it as one big xml file, loads without any problem.

How did i create those 4x0 job files? I copied the 300 file which was generated by colxml and i remove the tables i don't want to upload.

Very strange that when i split up the problem it works and when i want to load it as one big thing it crashes.

I played around with number of parsers and readers, but nothing different.

Hopefully you can find a similar reproducable situation on your side.

I also ran the whole test in InfiniDB Enterprise Edition, and exactly the same behaviour.