Load data local

linuxqueric · 05-16-2009, 08:53 PM

I'm working with MySQLrefman-5.0 and running MySQL 5.0.51a. When I try to use the command as indicated in the tutorial in Ch. 3, I get the message that "this command is not allowed with this version." In section 5.6.4 of the manual it talks about enabling Load Data Local. But I'm not understanding it.

The manual says, "By default, all MySQL clients and libraries in binary distributions are compiled with the --enable-local-infile option." So why can't I use that command? (I did not build it from source but installed it using Yast.)

A bit further the manual says, "For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the --local-infile[=1] option." But it doesn't say where to specify it. I tried including it in my command to start mysql but got a syntax error message.

Quote:

eric@linux-pczd:~> mysql --local-infile[=1] -u eric -p
mysql: unknown variable 'local-infile[=1]'

I also tried restarting mysqld with the --local-infile=1 option. That command seemed to work. But I still can't use the Load Data Local command, so something is still wrong on the client side, I guess. (Client and server are on the same host.)

Thanks.

bathory · 05-17-2009, 03:38 AM

Quote:

A bit further the manual says, "For the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the --local-infile[=1] option." But it doesn't say where to specify it. I tried including it in my command to start mysql but got a syntax error message.

The "--local-infile[=1]" expressions means that you have to add "--local-infile" or "--local-infile=1" in the startup options of mysqld, to enable LOAD DATA LOCAL.
If you want, you can to do the same by adding this option (add the line: local-infile=1) in the [mysqld] part of /etc/my.cnf.
Regarding the client, you can also add this option in the [client] part of my.cnf. Take a look at this to see the security issues about it.

Regards

linuxqueric · 05-18-2009, 02:19 PM

Thanks, Bathory.

I tried this and still get the same error message.

Quote:

mysql> LOAD DATA LOCAL INFILE '/home/eric/animal.txt' INTO TABLE pet;ERROR 1148 (42000): The used command is not allowed with this MySQL version

This is the relevant part of my.cnf:

Quote:

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
local-infile = 1

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
local-infile=1
user=mysql

By the way, the page you referred me to is the one I was studying. When it says that all versions since ... are enabled with this option, doesn't that mean it should work as the default?

linuxqueric · 05-18-2009, 02:27 PM

I got it to work--had to specify "local-infile=1" in my command to start mysql. I was trying to specify it in the "Load Data Local Infile" command.

But once I've made the change (noted in previous post) to the client part of my.cnf, I shouldn't have to also do it when starting mysql, should I?

Thanks.

bathory · 05-18-2009, 04:50 PM

I guess that you have to specify the localt-infile option in the startup script of mysqld, because the settings in my.cnf get overwritten by the startup options.
Anyway glad to see you've made it.

Regards