We help IT Professionals succeed at work.

Execute LOAD DATA INFILE Remotely

Hello
I need to Execute the LOAD DATA INFILE Command on a MySql dase remotely.
I have tried using
sqlstring = "LOAD DATA INFILE '/home/toolsfor/tmp/x_web.txt' REPLACE INTO TABLE x_web;"
WebSQLEx.Execute sqlstring

From VBA and get the following error
[MySql][odbc 5.1 Driver][mysqld-5.0.92-community-log]Access denied for user'user@ipaddress'(using password: YES)

I have tried to put the LOAD DATA INFILE in a stored Procedure but get
ERROR 1314: LOAD DATA is not allowed in stored procedures

Any Idea's?
Comment
Watch Question

Commented:
The error suggests that the user you are trying to access the server with doesn't have this permission granted, or that LOAD DATA INFILE might be disabled altogether. It is usually disabled as it is security concern.

You will need to check a few things,
1. does the server allow remote connections,
2. is LODA DATA INFILE disabled,
3. Does your MySQL user have the relevant permissions granted.

Author

Commented:
If I login to the database with PhpMyAdmin with the same user I can run the "LOAD DATA INFILE"
I can execute Select and Update Scripts from VBA using the same (WebSQLEx.Execute sqlstring) command.

Commented:
Sounds like the mysql server doesnt allow remote connections.

Most likely phpmyadmin runs on the same server as the mysql server, so it can access it locally or connections are allowed to the mysql server from specific IPs.

Do you have access to the mysl servers conf file? or access to the server it sits on?

If not, then best contact the system administrator for that server.

Author

Commented:
"I can execute Select and Update Scripts from VBA using the same (WebSQLEx.Execute sqlstring) command. "

This is from my Local machine in VBA so the server must be set up for remote connections
Top Expert 2012

Commented:
Top Expert 2012

Commented:
Clicked submit too soon.  Basically For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
Commented:
The trouble is the permission for the specific user that you are using.

Try creating another user with this command

GRANT SELECT, INSERT, UPDATE, DELETE, FILE ON YOU_DB_NAME.* TO 'another_username'@'%' IDENTIFIED BY 'SOME_PASSWORD';


This will create a user with FILE permission. You must do it with a user with GRANT Privilege (such root).

keep in mind that the file must EXIST ON REMOTE SERVER, not in you local computer.
theGhost_k8Database Consultant

Commented:
If there is no permission issue you should do:

LOAD DATA LOCAL INFILE '/home/toolsfor/tmp/x_web.txt' REPLACE INTO TABLE x_web;