Infobright.org Forums |

2 of 3

Problems using LOAD DATA INFILE—can anyone help me?

XSPRADA_MAN

Posted: 02 March 2009 12:48 PM

[ Ignore ] [ # 16 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Couple questions:

what is this data generator and where is it located?

the sample you sent doesnt come back on SELECT * either (but count(*) works, just like my sample).

I’m wondering if it’s the ODBC driver or the client—I am using QTODBC 7.0 connected to the 64-bit MySql ODBC 5.1 driver. Perhaps I should try the 32-bit version huh?

Profile

XSPRADA_MAN

Posted: 02 March 2009 12:50 PM

[ Ignore ] [ # 17 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Ok same results with the 32-bit driver

was worth a shot…

Profile

David Lutz

Posted: 02 March 2009 12:54 PM

[ Ignore ] [ # 18 ]

Sr. Member

Total Posts: 738

Joined 2008-08-18

Brian’s post contained a link to DataMelt.com which can be found here - http://www.datamelt.com/java-datamelt/process?action=bsh&scriptname=downloads

Signature

Profile

XSPRADA_MAN

Posted: 02 March 2009 01:03 PM

[ Ignore ] [ # 19 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Ah DUH, missed the link on your previous post to the generator.

Ok fine, nice. Unfortunately it still doesnt work for me—are you running on a wintel box?

Profile

XSPRADA_MAN

Posted: 02 March 2009 01:10 PM

[ Ignore ] [ # 20 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Hey can that data generator pump out terabytes and if so, how fast? Is it single or multi threaded? Looks like a decent tool.
Thanks.

Profile

David Lutz

Posted: 02 March 2009 04:55 PM

[ Ignore ] [ # 21 ]

Sr. Member

Total Posts: 738

Joined 2008-08-18

XSPRADA_MAN,

The DataMelt data generator is an application/project written by one of our prospects. He alerted us to it during his evaluation of Infobright for a data warehouse. We haven’t put it through any terribly arduous testing so I can’t answer your questions specifically. (We did set it up internally for purposes of answering some questions specifically related to Infobright in a database that was loaded with data generated by this tool. But that’s about it, so far.)

The developer, Uwe Geercken, can discuss the particulars with you if you would like more information. I can facilitate an introduction or you can reach him via his web site (JavaScript must be enabled to view this email address) (http://www.datamelt.com/java-datamelt/process?action=bsh&scriptname=contact).

If you have good experiences with DataMelt, please share them with us and our Community.

Cheers

Signature

Profile

Brian Beharry

Posted: 05 March 2009 03:48 PM

[ Ignore ] [ # 22 ]

Jr. Member

Total Posts: 96

Joined 2008-08-18

Jerome,

Sorry for the late reply. I was on vacation.

Anyway, I used a 64 bit machine running:

Windows Server 2003, Enterprise x64 Edition
Microsoft Windows [Version 5.2.3790] 

and I took this data:

012717906;-84816883;017796263;-83720239;gvqJL
065857363;019935432;032836681;021714780;zAMqL
-89088488;-46366250;026632425;020053433;uTh7l
032159212;-01959562;060433380;-57249608;LlSa5
-50609942;-79496980;-07003982;-43424357;28gOm
-75228283;-95647777;-34363516;-65064622;FqE0B
-86679095;-73179209;-35948226;092371310;Npks9
-95987068;019862786;-46408020;035140167;XJgW0
005535196;009964138;-49303840;-69488450;T4s4j
-86752063;036505309;-68984498;-48814321;4jWjU
066822788;-24019431;-21156385;052570375;IeTIK
028406252;033804510;039221891;-09240801;jKarL
090653760;-91733482;061556878;-22901829;LDCcI
-48774030;-26057436;028826012;-24878173;eB7L2
-98363212;030109121;-74244153;-31569027;UvcT1
047772084;-88693302;-08792545;016444895;M64I6
026838746;-34068082;096781822;-53741703;iop6X 

and then I ran these commands:

warnings;

drop database if exists jerome_test;
create database jerome_test;
use jerome_test;


set autocommit=1;


drop table if exists jerome;
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)
) engine=brighthouse CHARACTER SET ascii COLLATE ascii_bin;


set @bh_dataformat = 'txt_variable';
--  The next command is all on one line
load data infile 'C:/bbeharry/2009Mar05/data.txt' into table jerome       fields terminated by ';' enclosed by 'null' lines terminated by '\n';

select * from jerome limit 1000;  
select count(*) from jerome; 

I was able to load and query without a problem.

Could you tell me all the details of the environment where you issue the selects? I use the mysql client in the bin directory and then I use the parameters: -u root -P3306
(You’ll use a different number than 3306)

So:


mysql -u root -P3306

Signature

Brian Beharry, QA
Infobright

Profile

XSPRADA_MAN

Posted: 05 March 2009 03:49 PM

[ Ignore ] [ # 23 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Yeah I’m hitting it with QTODBC 7.0

Profile

XSPRADA_MAN

Posted: 05 March 2009 03:52 PM

[ Ignore ] [ # 24 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Ok I see mySql is a command line interface…blah!

Profile

Brian Beharry

Posted: 06 March 2009 11:16 AM

[ Ignore ] [ # 25 ]

Jr. Member

Total Posts: 96

Joined 2008-08-18

Jerome,

I just used MySQL ODBC 5.1 driver with Excel to connect to a 64 bit windows machine with the database.

I can run this:

select REPORT_DATE_INT,
  HO_WORK_ORDER_HISTORY_ID int,
  HO_PHONE_MASTER_ROW_ID int,
  HO_SUBSCRIBER_ROW_ID from jerome_test.jerome; 

And, I can’t run this:

select REPORT_DATE_INT,
  HO_WORK_ORDER_HISTORY_ID int,
  HO_PHONE_MASTER_ROW_ID int,
  HO_SUBSCRIBER_ROW_ID,
  SUB_CTYPE from jerome_test.jerome; 

We get a crash in Microsoft Query when importing to Excel. Like you said, it’s the varchar. I strongly suspect that the problem is the MySQL ODBC 5.1 driver because when I use the MySQL client to connect, everything works fine.

I will search the MySQL site to see if there are any known issues with the driver. We appreciate your patience.

Signature

Brian Beharry, QA
Infobright

Profile

XSPRADA_MAN

Posted: 06 March 2009 11:21 AM

[ Ignore ] [ # 26 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Excel is limited to 32-bit ODBC drivers as best I can tell. I am using the MySQL 5.1 64-bit driver. I have tried with the MySQL 32-bit driver with same results though.

Ok well thanks for following up—best.

Profile

Brian Beharry

Posted: 06 March 2009 11:26 AM

[ Ignore ] [ # 27 ]

Jr. Member

Total Posts: 96

Joined 2008-08-18

You may find this link to be useful.

Signature

Brian Beharry, QA
Infobright

Profile

XSPRADA_MAN

Posted: 06 March 2009 11:28 AM

[ Ignore ] [ # 28 ]

Newbie

Total Posts: 19

Joined 2009-01-24

Thanks—no one has responded to it it seems right?

Hard to imagine how a problem with such a fundamental data type would make it past QA and not get noticed before

Profile

Brian Beharry

Posted: 06 March 2009 11:47 AM

[ Ignore ] [ # 29 ]

Jr. Member

Total Posts: 96

Joined 2008-08-18

I’ve heard that MySQL QA can be accommodating. I bet that if you post your exact situation with details there, they’ll help you.

Signature

Brian Beharry, QA
Infobright

Profile

Brian Beharry

Posted: 06 March 2009 12:04 PM

[ Ignore ] [ # 30 ]

Jr. Member

Total Posts: 96

Joined 2008-08-18

Jerome,

Are you in a situation where you absolutely have to use the MySQL ODBC driver? Is it possible for you to just use the MySQL client instead? With that, you can run scripts that do load, query, and export.

Signature

Brian Beharry, QA
Infobright

Profile

2 of 3

‹‹ How do I use LOAD DATA INFILE Using standard MySQL Admin package ››

Username:
Password:
Auto-login on future visits
Forgot your password?