As per MCOL-570 these are written to .bad and .err files under mysql/db. Agreed that it would be more useful to have more informative warnings but this is certainly not a critical /p1 issue.

I have same problem, but i have a reproductible test.

My table :

CREATE TABLE `tmp__pierre` (

  `tiny` tinyint(10) unsigned DEFAULT NULL,

  `small` smallint(10) unsigned DEFAULT NULL,

  `inte` int(10) unsigned DEFAULT NULL

) ENGINE=Columnstore DEFAULT CHARSET=utf8 

STEP 1 : I use this csv file :
252;65532;4294967292

mysql> LOAD DATA INFILE '/mnt/tarnasdev/developpement/pierre/fic.csv' INTO TABLE tmp__pierre FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 1 row affected (1.28 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> SELECT * FROM tmp__pierre;
---------------------

---------------------

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

STEP 2 , this file :
253;65533;4294967293

mysql> LOAD DATA INFILE '/mnt/tarnasdev/developpement/pierre/fic.csv' INTO TABLE tmp__pierre FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 1 row affected (1.27 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> SELECT * FROM tmp__pierre;
---------------------

---------------------

---------------------
2 rows in set (0.04 sec)

STEP 3, this file :
254;65534;4294967294

mysql> LOAD DATA INFILE '/mnt/tarnasdev/developpement/pierre/fic.csv' INTO TABLE tmp__pierre FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 1 row affected, 3 warnings (1.25 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 3

mysql> show warnings;
-----------------------------

-----------------------------

-----------------------------
3 rows in set (0.01 sec)

mysql> SELECT * FROM tmp__pierre;
---------------------

---------------------

---------------------
3 rows in set (0.03 sec)

STEP 4 : this file :
255;65535;4294967295

mysql> LOAD DATA INFILE '/mnt/tarnasdev/developpement/pierre/fic.csv' INTO TABLE tmp__pierre FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 1 row affected, 3 warnings (1.26 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 3

mysql> show warnings;
-----------------------------

-----------------------------

-----------------------------
3 rows in set (0.00 sec)

mysql> SELECT * FROM tmp__pierre;
---------------------

---------------------

---------------------
4 rows in set (0.01 sec)

STEP 5, this file :
256;65536;4294967296

mysql> LOAD DATA INFILE '/mnt/tarnasdev/developpement/pierre/fic.csv' INTO TABLE tmp__pierre FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 1 row affected, 6 warnings (1.25 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 6

mysql> show warnings;
-----------------------------------------------------------

-----------------------------------------------------------

-----------------------------------------------------------
6 rows in set (0.00 sec)

mysql> SELECT * FROM tmp__pierre;
---------------------

---------------------

---------------------
5 rows in set (0.02 sec)

CONCLUSION :
it can be seen that for tinyint for example, for the values 254, and 255, the warning message does not indicate any information either the line or the column.

It is very problematic to debug the LOAD

The current way this works is looks for the cpimport error count in the log file and then spits out that many warnings.

I think at the moment the best way to implement it would be to use the /tmp/columnstore_tmp_files/BrmRpt*.rpt file indicated in the log file and parse this to generate relevant warnings. For Pierre's example it contains this:

#CP:   startLBID max min seqnum type newExtent

#HWM:  oid partition segment hwm

#ROWS: numRowsRead numRowsInserted

#DATA: columNum columnType columnOid numOutOfRangeValues

#ERR:  error message file

#BAD:  bad data file, with rejected rows

#MERR: critical error messages in cpimport.bin

CP: 9118720 253 253 -1 16 0

CP: 9119744 65533 65533 -1 17 0

CP: 9121792 4294967293 4294967293 -1 20 0

HWM: 15455 0 0 2

HWM: 15456 0 0 4

HWM: 15457 0 0 8

ROWS: 1 1

DATA: 0 16 15455 1

DATA: 1 17 15456 1

DATA: 2 20 15457 1

Longer term we will use the API's writeengine calls instead of cpimport and get real data.

same problem with date :

mysql> create table tmp__date (madate date) engine=columnstore;
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO tmp__date VALUES ('0201-11-11');
Query OK, 1 row affected, 1 warning (0.28 sec)

mysql> show warnings;
---------------------------------------------------------------------

---------------------------------------------------------------------

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

mysql> LOAD DATA INFILE '/mnt/tarnasdev/developpement/pierre/date.csv' INTO TABLE tmp__date FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 1 row affected, 1 warning (1.28 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

mysql> show warnings;
-----------------------------

-----------------------------

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

date.csv :
0201-11-11

if I look .rpt file generated by LOAD date.csv :

1. cat BrmRpt102111430331777.rpt
   #CP: startLBID max min seqnum type newExtent
   #HWM: oid partition segment hwm
   #ROWS: numRowsRead numRowsInserted
   #DATA: columNum columnType columnOid numOutOfRangeValues
   #ERR: error message file
   #BAD: bad data file, with rejected rows
   #MERR: critical error messages in cpimport.bin
   CP: 114681856 -9223372036854775806 9223372036854775807 -1 8 0
   HWM: 89003 0 0 2
   ROWS: 1 1
   DATA: 0 8 89003 1

how to interpret this file ??

Thanks

Pierre

In case of out of range values you should look at the rows containing 'DATA:' since they contain all the information you need: column_number column_type column_oid out_of_range_number.

Hi Roman,

Unfortunately that log doesn't show which row or value, just how many. The code currently takes this count and loops to create warnings at the end. It would not be possible to give more information without modifying the log files or cpimport since the data is basically piped to cpimport and we don't get a real-time response for this.

With 1.2 we will be moving to a different method of doing LOAD DATA and INSERT...SELECT which will be able to process the warnings properly.

We will be doing LDI differently in future so closing as "won't fix"