[MCOL-986] cpimport failed: Move data between two tables columnstore Created: 2017-10-25  Updated: 2019-07-10  Resolved: 2019-07-10

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

Type: Bug Priority: Major
Reporter: Nicola Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Two Virtual Machine (Vmware) with Oracle Linux 7.4.
Columnstore Architecture: Multiserver (Two P.M. and two U.M.) with nfs.

Attachments: Zip Archive columnstore_node1.zip     Zip Archive columnstore_node2.zip    

 Description   

Hi,
I've two table of type Columnstore and i've tried to use cpimport for move all data from c6_messaggio_out_2 to c6_messaggio_out but with two tables columnstore there is an error:

[root@dwh-cstore01 ~]# mcsmysql -q -e 'select * from c6_messaggio_out_2;' -N c6msg | cpimport -n1 -m1 c6msg c6_messaggio_out -s '\t' -f STDIN
2017-10-25 06:48:38 (35279) INFO : Reading input from STDIN to import into table c6msg.c6_messaggio_out...
2017-10-25 06:48:38 (35279) INFO : Running distributed import (mode 1) on all PMs...
ERROR 1815 (HY000) at line 1: Internal error: IDB-2039: Data file does not exist, please contact your system administrator for more information.

2017-10-25 06:49:49 (35279) INFO : For table c6msg.c6_messaggio_out: 5824512 rows processed and 5824512 rows inserted.
2017-10-25 06:49:49 (35279) INFO : Bulk load completed, total run time : 71.3008 seconds

This method with one table Innodb and one table Columnstore it works perfectly.

Best Regards.
Nicola Battista

 Comments   
Comment by Nicola [ 2017-10-25 ]

Log from Node 2 during import:
Oct 25 07:33:19 dwh-cstore02 cpimport.bin[21507]: 19.437500 |0|0|0| I 34 CAL0081: Start BulkLoad: JobId-3234; db-c6msg
Oct 25 07:35:14 dwh-cstore02 IDBFile[20820]: 14.835512 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Oct 25 07:35:15 dwh-cstore02 IDBFile[20820]: 15.835787 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Oct 25 07:35:16 dwh-cstore02 IDBFile[20820]: 16.836078 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Oct 25 07:35:17 dwh-cstore02 IDBFile[20820]: 17.836407 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Oct 25 07:35:18 dwh-cstore02 IDBFile[20820]: 18.836694 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Oct 25 07:35:19 dwh-cstore02 PrimProc[20820]: 19.836937 |0|0|0| C 28 CAL0053: PrimProc could not open file for OID 3230; /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf:No such file or directory
Oct 25 07:35:19 dwh-cstore02 PrimProc[20820]: 19.837992 |0|0|0| W 28 CAL0000: IDB-2039: Data file does not exist, please contact your system administrator for more information.
Oct 25 07:35:23 dwh-cstore02 writeengineserver[20904]: 23.323098 |0|0|0| D 32 CAL0000: 8068 : onReceiveEOD : child ID = 21507
Oct 25 07:35:23 dwh-cstore02 writeengineserver[20904]: 23.323217 |0|0|0| D 32 CAL0000: 8068 : Message Queue is empty; Stopping CF Thread
Oct 25 07:35:24 dwh-cstore02 cpimport.bin[21507]: 24.024812 |0|0|0| I 34 CAL0083: BulkLoad: JobId-3234; finished loading table c6msg.c6_messaggio_out; 5414723 rows inserted
Oct 25 07:35:24 dwh-cstore02 writeengine[21507]: 24.024871 |0|0|0| I 19 CAL0008: Bulkload |Job: /usr/local/mariadb/columnstore/data/bulk/tmpjob/3234_D20171025_T073319_S366404_Job_3234.xml |For table c6msg.c6_mes

Comment by David Thompson (Inactive) [ 2017-10-26 ]

can you try running the select to a local file then run cpimport on that file to to your target table. I have a suspicion the problem is with reading the source table not writing but not 100% sure.

If you still have a problem can you send a full columnstore support report as that will include the extent map which would help debugging this.

Comment by Nicola [ 2017-10-26 ]

Hi,
I've some tests:
1. Source : file csv DST: table columnstore = It works.
2. Source : Table Innodb DST: table columnstore = It works.
3. Source : Table Columnstore DST: tablecolumnstore = not work.

My fear is that when I run the cpimport read only the data on the PM1 and as a result the rest of the data will not be read because they are also distributed on the PM2.

Comment by Nicola [ 2017-11-03 ]

Hello all,
Any news?

Thanks,
Regards.
Nicola Battista

Comment by David Thompson (Inactive) [ 2017-11-06 ]

Are you able to provide the full columnstore support utility output?

Also if you just run:
mcsmysql -q -e 'select * from c6_messaggio_out_2;' -N c6msg > tmp.tbl

does this work?

Comment by Nicola [ 2017-11-06 ]

mcsmysql -q -e 'select * from c6_messaggio_out_2;' -N c6msg > /tmp/tmp.tbl
ERROR 1815 (HY000) at line 1: Internal error: IDB-2039: Data file does not exist, please contact your system administrator for more information.

The same error.

Comment by David Thompson (Inactive) [ 2017-11-14 ]

Sorry for the slow response. One more thing - does the error persist across a columnstore restart (do a shutdownSystem and startSystem to be thorough)?

Comment by Nicola [ 2017-11-14 ]

Hi David Thompson,
Yes, after restart of the PM and UM, i've the same error.

Regards.
Nicola Battista

Comment by David Thompson (Inactive) [ 2017-11-14 ]

Ok so this excludes an in memory extent map issue. For the failed to open file errors can you confirm:
1 - do they exist on the corresponding pm to the log file
2 - do they exist on the other pm

If yes on 1, can you show the file permissions, anything odd about that?

If answer is no to both you may have some form of extent map corruption. Do you have an ability to reload that data from source?

Comment by Nicola [ 2017-11-15 ]

Hi,
i've relaunched the cpimport :
On PM2 i've this erorr :
ov 15 12:33:01 dwh-cstore02 cpimport.bin[62851]: 01.425535 |0|0|0| I 34 CAL0081 : Start BulkLoad: JobId-3234; db-c6msg
Nov 15 12:34:17 dwh-cstore02 IDBFile[27957]: 17.362279 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Nov 15 12:34:18 dwh-cstore02 IDBFile[27957]: 18.362552 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Nov 15 12:34:19 dwh-cstore02 IDBFile[27957]: 19.362827 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Nov 15 12:34:20 dwh-cstore02 IDBFile[27957]: 20.363172 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Nov 15 12:34:21 dwh-cstore02 IDBFile[27957]: 21.363477 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf, exception: unable to open Unbuffered file
Nov 15 12:34:22 dwh-cstore02 PrimProc[27957]: 22.363747 |0|0|0| C 28 CAL0053: PrimProc could not open file for OID 3230; /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf:No such file or directory
Nov 15 12:34:22 dwh-cstore02 PrimProc[27957]: 22.364969 |0|0|0| W 28 CAL0000: IDB-2039: Data file does not exist, please contact your system administrator for more information.

ON PM2 :
I now verify if file /000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf exitst :
1. cd /usr/local/mariadb/columnstore/data2
2. ls -l 000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf
ls: cannot access 000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf: No such file or directory

This file does not exist in this pm.
ON PM1 :
1. cd /usr/local/mariadb/columnstore/data1
2. [root@dwh-cstore01 data1]# ls -l 000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf
rw-rr- 1 root nobody 2751733760 Oct 19 04:42 000.dir/000.dir/012.dir/158.dir/000.dir/FILE000.cdf

The file exist in PM1.

Why the PM2 try to find files on the PM1 ?

Thanks for your support.
Regards.

Comment by David Thompson (Inactive) [ 2017-11-16 ]

Can you produce a current full columnstore support file. I need more than the logs that you attached before so that i can see the schema definition and extent map. It does look like the persisted extent map is messed up for this table based on what you have above but we can confirm for sure from the extent map dump.

Comment by David Hill (Inactive) [ 2017-11-16 ]

I see you mentioned you restart Um and PM, does this mean you have done a restartSystem?

from pm1:

  1. mcsadmin restartsystem y

The ExeMgr is out of sync with the primproc and thus its sending the request to the wrong PM, most of the time, a restartsystem will correct. This sometimes happens after a ExeMgr or PrimProc process outage and restart has occured.

Comment by Nicola [ 2017-11-16 ]

Hi David,
I've restarted the entire system.
After restart :
[root@dwh-cstore01 ~]# mcsmysql -q -e 'select * from c6_messaggio_out_2;' -N c6msg | cpimport -n1 -m1 c6msg c6_messaggio_out -s '\t' -f STDIN
2017-11-16 06:42:29 (55867) INFO : Reading input from STDIN to import into table c6msg.c6_messaggio_out...
2017-11-16 06:42:29 (55867) INFO : Running distributed import (mode 1) on all PMs...
ERROR 1815 (HY000) at line 1: Internal error: IDB-2039: Data file does not exist, please contact your system administrator for more information.

2017-11-16 06:44:16 (55867) INFO : For table c6msg.c6_messaggio_out: 7871507 rows processed and 7871507 rows inserted.
2017-11-16 06:44:16 (55867) INFO : Bulk load completed, total run time : 106.373 seconds

After Import :
MariaDB [c6msg]> select count from c6_messaggio_out;
----------

count

----------

7871507

----------
1 row in set (0.17 sec)

MariaDB [c6msg]> select count from c6_messaggio_out_2; <- Realsize
-----------

count

-----------

317922849

-----------
1 row in set (2.06 sec)

DWH-CSTORE01 - LOG :
Nov 16 06:42:29 dwh-cstore01 writeengineserver[46776]: 29.853151 |0|0|0| D 32 CAL0000: 2453 : CMD LINE ARGS came in /usr/local/mariadb/columnstore/bin/cpimport.bin -s \t -n 1 -R /tmp/columnstore_tmp_files/BrmRpt101606422955867.rpt -m 1 -P pm1-55867 -u38148fcb-e1fc-4616-89b0-a109b1200beb c6msg c6_messaggio_out
Nov 16 06:42:29 dwh-cstore01 writeengineserver[46776]: 29.853238 |0|0|0| D 32 CAL0000: 2453 : Brm Rpt Filename Arrived /tmp/columnstore_tmp_files/BrmRpt101606422955867.rpt
Nov 16 06:42:29 dwh-cstore01 writeengineserver[46776]: 29.853302 |0|0|0| D 32 CAL0000: 2453 : Start Cpimport command reached!!
Nov 16 06:42:29 dwh-cstore01 cpimport.bin[55906]: 29.882747 |0|0|0| I 34 CAL0086: Initiating BulkLoad: -s \t -n 1 -R /tmp/columnstore_tmp_files/BrmRpt101606422955867.rpt -m 1 -P pm1-55867 -u38148fcb-e1fc-4616-89b0-a109b1200beb c6msg c6_messaggio_out
Nov 16 06:42:29 dwh-cstore01 cpimport.bin[55906]: 29.908679 |0|0|0| I 34 CAL0081: Start BulkLoad: JobId-3234; db-c6msg
Nov 16 06:44:06 dwh-cstore01 IDBFile[45831]: 06.819710 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/160.dir/000.dir/FILE001.cdf, exception: unable to open Unbuffered file
Nov 16 06:44:07 dwh-cstore01 IDBFile[45831]: 07.819960 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/160.dir/000.dir/FILE001.cdf, exception: unable to open Unbuffered file
Nov 16 06:44:08 dwh-cstore01 IDBFile[45831]: 08.820201 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/160.dir/000.dir/FILE001.cdf, exception: unable to open Unbuffered file
Nov 16 06:44:09 dwh-cstore01 IDBFile[45831]: 09.820454 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/160.dir/000.dir/FILE001.cdf, exception: unable to open Unbuffered file
Nov 16 06:44:10 dwh-cstore01 IDBFile[45831]: 10.820690 |0|0|0| D 35 CAL0002: Failed to open file: /000.dir/000.dir/012.dir/160.dir/000.dir/FILE001.cdf, exception: unable to open Unbuffered file
Nov 16 06:44:11 dwh-cstore01 PrimProc[45831]: 11.820931 |0|0|0| C 28 CAL0053: PrimProc could not open file for OID 3232; /000.dir/000.dir/012.dir/160.dir/000.dir/FILE001.cdf:No such file or directory
Nov 16 06:44:11 dwh-cstore01 PrimProc[45831]: 11.821240 |0|0|0| W 28 CAL0000: IDB-2039: Data file does not exist, please contact your system administrator for more information.
Nov 16 06:44:14 dwh-cstore01 writeenginesplit[55867]: 14.557790 |0|0|0| I 33 CAL0000: Send EOD message to All PMs
Nov 16 06:44:14 dwh-cstore01 writeengineserver[46776]: 14.570451 |0|0|0| D 32 CAL0000: 2453 : onReceiveEOD : child ID = 55906
Nov 16 06:44:14 dwh-cstore01 writeengineserver[46776]: 14.570544 |0|0|0| D 32 CAL0000: 2453 : Message Queue is empty; Stopping CF Thread
Nov 16 06:44:15 dwh-cstore01 writeenginesplit[55867]: 15.453769 |0|0|0| I 33 CAL0098: Received a Cpimport Pass from PM2.
Nov 16 06:44:16 dwh-cstore01 cpimport.bin[55906]: 16.123223 |0|0|0| I 34 CAL0083: BulkLoad: JobId-3234; finished loading table c6msg.c6_messaggio_out; 3940000 rows inserted
Nov 16 06:44:16 dwh-cstore01 writeengine[55906]: 16.123295 |0|0|0| I 19 CAL0008: Bulkload |Job: /usr/local/mariadb/columnstore/data/bulk/tmpjob/3234_D20171116_T064229_S896366_Job_3234.xml |For table c6msg.c6_messaggio_out: 3940000 rows processed and 3940000 rows inserted.
Nov 16 06:44:16 dwh-cstore01 cpimport.bin[55906]: 16.126130 |0|0|0| I 34 CAL0082: End BulkLoad: JobId-3234; status-SUCCESS
Nov 16 06:44:16 dwh-cstore01 writeengineserver[46776]: 16.138445 |0|0|0| I 32 CAL0000: 2453 : cpimport exit on success
Nov 16 06:44:16 dwh-cstore01 writeengineserver[46776]: 16.138627 |0|0|0| D 32 CAL0000: 2453 : onCpimportSuccess BrmReport Send
Nov 16 06:44:16 dwh-cstore01 writeengineserver[46776]: 16.138668 |0|0|0| D 32 CAL0000: 2453 : onReceiveEOD : child ID = 0
Nov 16 06:44:16 dwh-cstore01 writeengineserver[46776]: 16.138709 |0|0|0| D 32 CAL0000: 2453 : onReceiveEOD : child ID = 0
Nov 16 06:44:16 dwh-cstore01 writeengineserver[46776]: 16.144647 |0|0|0| D 32 CAL0000: 2453 : OnReceiveCleanup arrived
Nov 16 06:44:16 dwh-cstore01 writeenginesplit[55867]: 16.144645 |0|0|0| I 33 CAL0098: Received a Cpimport Pass from PM1.
Nov 16 06:44:16 dwh-cstore01 writeenginesplit[55867]: 16.204278 |0|0|0| I 33 CAL0000: Released Table Lock

DWH-CSTORE02-LOG:
Nov 16 06:44:14 dwh-cstore02 ExeMgr[52718]: 14.337009 |15|0|0| D 16 CAL0042: End SQL statement
Nov 16 06:44:14 dwh-cstore02 writeengineserver[52745]: 14.354842 |0|0|0| D 32 CAL0000: 2453 : onReceiveEOD : child ID = 53615
Nov 16 06:44:14 dwh-cstore02 writeengineserver[52745]: 14.354921 |0|0|0| D 32 CAL0000: 2453 : Message Queue is empty; Stopping CF Thread
Nov 16 06:44:15 dwh-cstore02 cpimport.bin[53615]: 15.222266 |0|0|0| I 34 CAL0083: BulkLoad: JobId-3234; finished loading table c6msg.c6_messaggio_out; 3931507 rows inserted
Nov 16 06:44:15 dwh-cstore02 writeengine[53615]: 15.222330 |0|0|0| I 19 CAL0008: Bulkload |Job: /usr/local/mariadb/columnstore/data/bulk/tmpjob/3234_D20171116_T064230_S738002_Job_3234.xml |For table c6msg.c6_messaggio_out: 3931507 rows processed and 3931507 rows inserted.
Nov 16 06:44:15 dwh-cstore02 cpimport.bin[53615]: 15.226250 |0|0|0| I 34 CAL0082: End BulkLoad: JobId-3234; status-SUCCESS
Nov 16 06:44:15 dwh-cstore02 writeengineserver[52745]: 15.235950 |0|0|0| I 32 CAL0000: 2453 : cpimport exit on success
Nov 16 06:44:15 dwh-cstore02 writeengineserver[52745]: 15.236123 |0|0|0| D 32 CAL0000: 2453 : onCpimportSuccess BrmReport Send
Nov 16 06:44:15 dwh-cstore02 writeengineserver[52745]: 15.236166 |0|0|0| D 32 CAL0000: 2453 : onReceiveEOD : child ID = 0
Nov 16 06:44:15 dwh-cstore02 writeengineserver[52745]: 15.236225 |0|0|0| D 32 CAL0000: 2453 : onReceiveEOD : child ID = 0
Nov 16 06:44:15 dwh-cstore02 writeengineserver[52745]: 15.928906 |0|0|0| D 32 CAL0000: 2453 : OnReceiveCleanup arrived

Comment by Nicola [ 2017-11-16 ]

Hi all,
I've another question.
I've some table in engine columnstore with charset latin_swedish_ci.
Can i to convert the tables from latin_swedish_ci to utf8?

Thanks.
Regards.
Nicola Battista

Comment by David Thompson (Inactive) [ 2017-11-17 ]

I don't think we support the alter table convert to charset syntax so you would need to extract the data out from table1 and reimport it to table2. Depending on your data an insert select may work. However if this is your problem table then you are out of luck. At this point you may be best dropping this table and starting over unfortunately.

Comment by Nicola [ 2017-11-21 ]

Hi all,
Are there news?

Thanks.
Regards.

Comment by Nicola [ 2017-12-04 ]

Hi all,
Are there news for this problem?

Thanks.
Regards.

Comment by David Thompson (Inactive) [ 2017-12-06 ]

hi nbattista89 did you have a chance to review my comment on 11/16?

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