mysqlimport -L

[tobyheywood]

Hi,

I'm currently learning the basics of MySQL and have come across a small show stopper!

I need to import data from a delimited text file, but when I type in the following it tells me it can't be done, and I'm making an assumption (always a bad idea) that this is related to the --local-infile=1 flag.

mysqlimport -L -h localhost -u user -pthepassword '/home/userdir/address.txt' --fields-terminated-by=::: --fields-enclosed-by="

When I used the

LOAD DATA LOCAL INFILE

command this initially didn't work, and I soon found that I had to load mysql with the flag

--local-infile=1

, after this load data local infile worked fine.

I thought this maybe the same for mysqlimport, but unfortunately it's not.

Can anyone suggest a way in which I can get mysqlimport to function properly?

Thank you in advance! Toby Heywood

 

[piti]

i think your prob is that mysqlimport needs the termination and enclusion strings/characters to be enclosed by a quote

e.g. i use
/usr/local/mysql/bin/mysqlimport -u user -ppass --fields-terminated-by=',' --fields-enclosed-by='"' --lines-ter
minated-by='\r\n' mydb /home/upload_download/UploadDataTbl.txt

 

[tobyheywood]

I have attempted to enclose the termination and enclosure strings with quotes but this still generates the same problem.

After the command (with all of the relevant options) has been entered and i hit the enter key, it just scrolls through the mysqlimport help screen.

I piped the output through more but no error messages of any description where given!

Don't suppose you have any further suggestions I can try?

Thanks again. Toby Heywood

 

[piti]

first put the input file at the end of the command
and try it without that -L and -h switches

 

[tobyheywood]

Piti,

Thanks for your prompt reply, I tried, as suggested by you to put the input file at the end of the command and to remove the -L and -h switches.

This resulted in getting the exact same error message which I receive when using

LOAD DATA LOCAL INFILE

without the --local-infile=1 switch being added to load command for the mysql monitor.

1148: The used command is not allowed with this MySQL version.

Obviously I can work around this by using

LOAD DATA LOCAL INFILE

, but I find it a little annoying that the mysqlimport command doesn't work as it should!

I've looked in the MySQL documentation and it says something about loading mysql with --enable-local-infile=1 but I has yet been unable to start mysqld with this option.

Any further thoughts on this subject would be very welcome.

Kind regards

Toby Heywood

 

[piti]

so i read about it in mysql doc

mybe you could override it mysing your /etc/my.cnf config file, add there:
local-infile

try to add it in both [mysqld] and [client] section

if that does not work, it looks like the only way would be recompiling mysql with --enable-local-infile option or download the precompiled binaries, they should be compiled with that option

 

[scoots987]

tobyheywood, what version are you using of Mysql? Try your LOAD DATA without "LOCAL". It worked for me.

Scoots987

 

[tobyheywood]

Sorry for the delay in responding.

The version of MySQL is the standard that ships with RedHat Linux 7.3 (Ver 11.16 Distrib 3.23.49).

If I start MySQL with the switch --local-infile=1 then I can use the LOAD DATA LOCAL INFILE option.

It's when I want to use MYSQLIMPORT that the problems begin, because there doesn't seem to be an available switch to enable a local infile.

I shall on last quest with regards to the my.cnf file.

What would be the correct way of appending --enable-local-infile=1 for use when starting mysqlimport?

Cheers
Toby Heywood