0

I have a php web page that let's a person upload a file. I want to extract the file name from $_FILE and then load all the data into my database.

My code looks like this:

 $myfile = $_FILE['file']['tmp_file'];
 $executebatchloadsql = 'LOAD DATA LOCAL INFILE "'. $myfile .'" INTO TABLE testtable (fname, lname);
 mysql_query($executebatchloadsql) or die(mysql_error());

But the error message I'm getting says:

The used command is not allowed with this MySQL version

My questions are as follows

  1. Am I supposed to use the tmp_file name or the name?
  2. What do I need to do to get the load data command to work? I've tried to follow the post found at MySQL: Enable LOAD DATA LOCAL INFILE but it's still giving me the same error.

EDIT 1:

This is what my /etc/mysql/my.cnf looks like in part:

[mysqld]

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
local-infile

The last line is what I added. I also added the same line to the [mysql] section:

[mysql]
local-infile

After making these changes, I restarted sql:

/etc/init.d/mysql restart

But I'm still having the problem

EDIT 2:

I've also tried local-infile=1 as per the same post I mention above. But that doesn't work either.

EDIT 3

I've tried to add the FILE prvil to the user.. like so:

GRANT FILE ON *.* TO root;

and then when i showit grants for root, i can see that its been added. but i'm still getting an error message that the used command is not allowed on my version of mysql. Do I need the "=1" after the infile in my my.cfg file?

EDIT 4:

As a further test, i tried loading the file manually via command line instead of via php and it accepted it. I logged in as root (which is the same id i'm using my app for testing purposes) and then I tried the following command:

mysql> load data local infile '/var/www/testwebsite/abc.csv' into table testtable;
Query OK, 3 rows affected, 48 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 48

But I can't seem to get the PHP code working. Thanks.!

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
dot
  • 14,928
  • 41
  • 110
  • 218
  • what version of mysql are you using, and are you using mysql_, mysqli_ or pdo? – Ascherer Jan 15 '13 at 22:39
  • 2
    Does your server allow `LOAD DATA` and is it configured to do so? – Kermit Jan 15 '13 at 22:40
  • 2
    Make sure your MySQL user has the `FILE` privilege granted. There's also a chance your hosting provider has blocked you doing this command if you're using shared hosting. – hohner Jan 15 '13 at 22:42
  • Please see my updated comments next to question 2. also, mysql version is 5.5.28. Also, I'm using mysql_connect() which I guess means I'm not using mysqli or pdo... – dot Jan 15 '13 at 22:47
  • sorry, i tried formatting the ini file data properly... but for some reason, it keeps turning out really difficult to read! – dot Jan 15 '13 at 22:54
  • 1
    As Jamie suggested exec command `SHOW GRANTS FOR username;` and see if the user has `GRANT FILE ON *.*` OR `GRANT ALL PRIVILEGES ON *.*` with `mysql` or whatever IDE you use (phpAdmin, Sequel Pro...) – peterm Jan 15 '13 at 23:06
  • @jamie can u expand on the file privilege comment a little further? I will googlehow to do that – dot Jan 15 '13 at 23:30
  • @peterm I will give that a try when I get back to the office. Thankyou for clarification – dot Jan 15 '13 at 23:42
  • `GRANT FILE ON *.* TO username` will give the necessary right for the user username. – peterm Jan 15 '13 at 23:44

1 Answers1

2

After going from MySQL 5.0 to 5.5 I found out that I suddenly have to enable LOCAL INFILE specifically when creating the connection in PHP.

Using mysql:

mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE
mysql_select_db(database);

Using mysqli:

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);
MaX
  • 1,765
  • 13
  • 17