Hi,

Just stumbled across Infobright and am really interested to see if if can replace my MyISAM based database. But at the moment I’m having problems trying to load some test data.

I am trying to load a very large datafile (~25GB).

My table is

and I’m loading with

And I get the error

However, if I load a file with a single row in it and then retry the larger file - it errors on a different line

Which suggests to me that the problem is not with the data, at least not a specific line.

Would be grateful for any suggestions.

RHEL 5.3 x86_64, 2 x Intel E5450, 8GB RAM.
ServerMainHeapSize=3000
ServerCompressedHeapSize=400
LoaderMainHeapSize=400

Thanks,

Alan

Hi,

Here will be my approach to address this:

- take only 1000 line of data and load, see if it succeed.
- chop /home/griffa10/ifInOctets.dmp.1 into pieces and load them one after another, to pin point the error line.

E.g. assume this file has 100M rows, you do

$head -n 50000000 [50M] original.dmp > first_half.dmp
$tail -n 50000000[50M] original.dmp > second_half.dmp

If problem happen at second half, continue chop 50M into 2x25M chunk, and so on. Until you pin point the error lines.

We saw this happen before, and it’s because of bad data inside.

If you can share us your finding (error lines), or even better you can send us your raw data and DDL, you will be greatly appreciated.

Thanks

Hi,

OK here are my results.

I have a load file I got from a MySQL server by doing SELECT ... INTO OUTFILE. It has 7474146 rows.

If I try to load it I get: ERROR 2 (HY000): Wrong data or column definition. Row: 7474146, field: 3.

If I split it in half - both halves load.

I will happily send you the load data, but the forum software refuses to upload it, even though it is within the 750000KB advertised limit.

Thanks,

Alan

Hi,

Can you compress your halves of the data file or split the files once more?
Or can you put the data on a web/ftp server?

Hello!

I have a same problem.
Do you have something in the field or is it empty?

Have a nice day,
Tom

TomIB and ap_grifffiths,

If you can send us your data, we’d be glad to help you.

You can send us by:
1) cutting your file into smaller pieces and uploading it here; or
2) sending it to us by ftp,http

We’re also open to any other ideas you have.

Let us know.

Hello Brian!

Unfortunately I cannot send data because it is sensitive.
Everything I can publish is the errormessage: ERROR 2 (HY000): Wrong data or column definition. Row: 1, field: 4.
Field 4 have a varchar type and I updated it to 32 character long string.

Hope this helps
Tom

TomIB - 08 October 2009 09:39 AM

Hello Brian!

Unfortunately I cannot send data because it is sensitive.
Everything I can publish is the errormessage: ERROR 2 (HY000): Wrong data or column definition. Row: 1, field: 4.
Field 4 have a varchar type and I updated it to 32 character long string.

Hope this helps
Tom

Tom, if it’s happening on Row 1 field 4, could you reproduce the problem with some fake data that you could send us or post here?  Hopefully you don’t have too many columns and hopefully your columns aren’t too big.

Also, there’s a free java-based data generator here:
http://www.datamelt.com/java-datamelt/process?action=bsh&scriptname=downloads

Hi !

A typical reason for such an error message would be trying to load UTF8 characters. If this is your case, see http://www.infobright.org/?ACT=25&fid=30&aid=336_hKUf9oPYSoFFBUWKY0c5&board_id=1.

BTW, soon UTF8 will be supported.

Another reason could be not using ENCLOSED BY ‘NULL’ clause if your strings in the source file are not enclosed.

J.

Hi everybody!

I think I got it.

First time you must dump values into a file:
SELECT * FROM tablename INTO OUTFILE ‘dump.dmp’ FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘“’ ESCAPED BY ‘`’ LINES TERMINATED BY ‘\r\n’;

After them you need to load data:
LOAD DATA INFILE ‘dump.dmp’ INTO TABLE tablename_brighthouse FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘“’ ESCAPED BY ‘`’ LINES TERMINATED BY ‘\r\n’;

So you need the same separators and escape char like at SELECT.

Best regards and have a nice day,

Tom