Hi all,

just pulled ICE 3.1 for Windows and running it on XP x64 but now I’m trying to load data—I have this issue here:

—when I try this:
LOAD DATA LOCAL INFILE ‘C:\ WO.TXT ‘ INTO TABLE WO FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;

I get this:
<eb1>Brighthouse: Session terminated unexpectedly, uncommitted transactions rolled back.
State: S1000; Native: 1402; Origin: [MySQL][ODBC 5.1 Driver] [mysqld-5.1.14-log]</eb1 >

Looked about everywhere on Google and ICE forums/wiki but cant see what the problem is—anything objvious?

Thanks!

XSPRADA_MAN - 27 February 2009 03:47 PM

Hi all,

just pulled ICE 3.1 for Windows and running it on XP x64 but now I’m trying to load data—I have this issue here:

—when I try this:
LOAD DATA LOCAL INFILE ‘C:\ WO.TXT ‘ INTO TABLE WO FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;

I get this:
<eb1>Brighthouse: Session terminated unexpectedly, uncommitted transactions rolled back.
State: S1000; Native: 1402; Origin: [MySQL][ODBC 5.1 Driver] [mysqld-5.1.14-log]</eb1 >

Looked about everywhere on Google and ICE forums/wiki but cant see what the problem is—anything objvious?

Thanks!

Hi,

I see a couple of things:

1) We don’t support LOAD DATA LOCAL INFILE today. Use LOAD DATA INFILE instead. The load file needs to be present on the same server where the database is running.

We are looking to support LOCAL INFILE in the future, but it’s not there yet.

2) Use forward slashes in the file path: ‘C:/WO.TXT’

In the load command, backslash is interpreted as an escape character.

3) IGNORE 1 LINES isn’t supported today. Delete the header line from the file and try the load again.

Let us know if this helps…

Geoffrey

Yes I had just tried changing the path name succesfully and so it works as such now:

LOAD DATA INFILE ‘/../../WO.TXT’ INTO TABLE WO FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ ;

using a relative path.  So I load the data and do a select count(*) and it has all the rows however on a select * I get this informative message:

<eb1>An unknown error has occurred.</eb1>

Anyway to examine what’s in that table?

BTW I’m using QTODBC 7.0 with the MySQL ODBC driver 5.1

Thanks!

XSPRADA_MAN - 27 February 2009 04:15 PM

Yes I had just tried changing the path name succesfully and so it works as such now:

LOAD DATA INFILE ‘/../../WO.TXT’ INTO TABLE WO FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ ;

using a relative path.  So I load the data and do a select count(*) and it has all the rows however on a select * I get this informative message:

<eb1>An unknown error has occurred.</eb1>

Anyway to examine what’s in that table?

BTW I’m using QTODBC 7.0 with the MySQL ODBC driver 5.1

Thanks!

Maybe the data got corrupted by some previous errors. Try dropping and recreating the table and do the load again.

Also look in the error log (brighthouse.log, bh.err) and see if there’s any info there.

Geoffrey

Yup, done all that—same results.

Please provide us with some sample data, your create table statement, and your load statement.

Hi XSPRADA_MAN,

Can send us the error file bh.err, general_query.log from data directory. Also if you can send your data we can try loading it.

thanks,
kethees

I cant give you any data as it’s live data and proprietary from a client.  (Besides it’s way too large)—it’s contained in a CSV flat file.

DDL is here:

CREATE TABLE wo (
  REPORT_DATE_INT int ,
  HO_WORK_ORDER_HISTORY_ID int ,
  HO_PHONE_MASTER_ROW_ID int ,
  HO_SUBSCRIBER_ROW_ID int ,
  SUB_CTYPE varchar(5) ,
  WKH_RCD char(3) ,
  WKH_DISC_REASON varchar(20) ,
  MARKET_CODE varchar(4) ,
  SALE_TYPE char(3)  ,
  STORE_CHANNEL_ID int ,
  WKH_SALESMAN varchar(10) ,
  WKH_CSR_INIT varchar(20) ,
  FGG smallint ,
  RETURNS smallint ,
  REACTS smallint ,
  TRANSFERS smallint ,
  GG smallint ,
  GROSS_ADDS smallint ,
  ADJ_GROSS_ADDS smallint ,
  GROSS_DEACTS smallint ,
  DEACTS smallint ,
  ADJ_DEACTS smallint ,
  NET_ADDS smallint ,
  REPORT_DATE timestamp ,
  IMPORT_FILE_HISTORY_ID int ,
  DATE_CREATED timestamp ,
  WKH_D_RATE_PLAN_ID smallint ,
  WKH_A_RATE_PLAN_ID smallint ,
  PH_RATE_PLAN_ID smallint ,
  WBK_ADDS smallint ,
  WBK_REACTS smallint ,
  WBK_DEACT smallint ,
  CRTALK_DEACT smallint ,
  WKH_CAMPAIGN_ID smallint ,
  SA smallint ,
  CPE smallint ,
  ESN_ACT varchar(30) ,
  ESN_DEACT varchar(30) ,
  PAR smallint ,
  PAR_ADDR smallint ,
  PAR_SSN smallint ,
  PAR_DRV smallint ,
  PAR_BAL smallint ,
  PAR_DOB smallint ,
  TRANSFER_DEACT smallint ,
  HO_CC_BILL char(1) ,
  SUB_ISSUE_BILL char(1) ,
  Cricket_Location_ID_Key int ,
  Store_Sale_Type char(3) ,
  MIGRATIONS smallint ,
  TRANSFERS_PREPAID smallint ,
  TRANSFERS_POSTPAID smallint ,
  TRANSFERS_OTHER smallint ,
  A_FEATURE_SET_ID int ,
  D_FEATURE_SET_ID int ,
  PHONE_FEATURE_SET_ID int ,
  IS_CRICKET_HANDSET smallint );

I am loading with:

LOAD DATA INFILE ‘/../../WO.TXT’ INTO TABLE WO FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ ;

Thanks for the DDL. We will try to reproduce it here. In the mean time, can you try loading a smaller set of data like 10 lines and selecting from that.

We have tested the 32 bit version on a 32 bit machine but not on 64. Is there is anything in the error file or general_query log file?

Yeah that’s what I first did, with about 57 rows. Same problem.

It runs 32 bits on the WOW64 emulator anyway right? 

Nothing of interest in the logs—i checked em all.

Thanks for the help.
Jerome.

Ok so I think it’s a CSV format issue.  I simplified it down to this:

CREATE TABLE jerome (
  REPORT_DATE_INT int ,
  HO_WORK_ORDER_HISTORY_ID int ,
  HO_PHONE_MASTER_ROW_ID int ,
  HO_SUBSCRIBER_ROW_ID int ,
  SUB_CTYPE varchar(5)
);

It’s when it gets to the last col there’s a problem.

The CSV is this:
20080401,157290848,3763,970079,“01”

with a CR-LF at the end.

Also tried
20080401,157290848,3763,970079,01

to no avail.

But it’s when I add that varchar(5) at the end that it blows up on SELECT * FROM…

Is there documented direction somewhere on how to best format CSV for presentation to the ICE/mySQL engine?

Thanks.

XSPRADA_MAN - 27 February 2009 06:04 PM

I am loading with:

LOAD DATA INFILE ‘/../../WO.TXT’ INTO TABLE WO FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ ;

Try putting

in the load command.

Tried:
LOAD DATA INFILE ‘/../../jerome.txt’ INTO TABLE jerome FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ OPTIONALLY ENCLOSED BY ‘“’

Got:
<eb1>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OPTIONALLY ENCLOSED BY ‘“’’ at line 1
State: 37000; Native: 1064; Origin: [MySQL][ODBC 5.1 Driver][mysqld-5.1.14-log]</eb1>

OPTIONALLY ENCLOSED BY applies to the FIELDS section.. try this

Geoffrey

Thanks but same result

changed data to:

20080401,157290848,3763,970079,“01”

Schema:
CREATE TABLE jerome (
  REPORT_DATE_INT int ,
  HO_WORK_ORDER_HISTORY_ID int ,
  HO_PHONE_MASTER_ROW_ID int ,
  HO_SUBSCRIBER_ROW_ID int ,
  SUB_CTYPE varchar(5)
);

select * from jerome yields:

<eb1>An unknown error has occurred.</eb1>

Jerome,

When I use this data generator, and I configure the data generator like this:

Then, I can load and select with these commands:

Here’s a sample of the data I generated (can you load and select from this with my statements above?):