0

I have a problem in executing the following query:

    $csv = $path . $filename;
    $query = sprintf("LOAD DATA local INFILE '%s' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES (`firstname`, `lastname`, `username`, `gender`, `email`, `country`, `ethnicity`, `education`  )", addslashes($csv));
    return DB::connection()->getpdo()->exec($query);

It works perfectly in Localhost. When I tried it in my server, the following error occurred: 

{"error":{"type":"PDOException","message":"SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version","file":"\/var\/www\/vhosts\/xxxxxxxx.com\/httpdocs\/xxxxxxxx.com\/app\/controllers\/UsersController.php","line":224}}

I changed the value of local-infile=1 in the my.cnf file. Still, getting the same error.

What might be the problem?

user1012181
  • 8,648
  • 10
  • 64
  • 106
  • Can you `echo` the `$query` and see the resultant SQL statement ? – Raptor Mar 02 '15 at 04:11
  • `string(288) "LOAD DATA local INFILE 'public/uploads/2015/03/1425270026-users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (`firstname`, `lastname`, `username`, `gender`, `email`, `country`, `ethnicity`, `education` )" ` This is the output – user1012181 Mar 02 '15 at 04:21
  • The error reads syntax error or access violation. I would give the pdo a very simple sql statement that you know is correct. If you get the same error, you know it is an access problem, else its a syntax error. – Reenactor Rob Mar 02 '15 at 04:27
  • @ReenactorRob: The syntax is same from locahost which is working fine. I've another sql queries which is working well. So I doubt some other problems. – user1012181 Mar 02 '15 at 04:28
  • I'm confused. You say your syntax is ok so no syntax error, and you have other sql queries that are working so no access problem. You've eliminated both possibilities, yet the error remains. Is that correct? – Reenactor Rob Mar 02 '15 at 04:33
  • @ReenactorRob: Any possibilities with the `LOCAL_INFILE` ? – user1012181 Mar 02 '15 at 04:35
  • Perms on public/uploads/2015/03/1425270026-users.csv? Could the path, which doesn't start with a slash be the problem? Also , above you have local-infile=1, did you change it to just local_infile under the each .cnf file area..[mysqld] and [mysql]? – Reenactor Rob Mar 02 '15 at 04:38
  • Tried both of them, still... :/ – user1012181 Mar 02 '15 at 04:43
  • After changed `local-infile=1` in the `my.cnf` have you restarted the mysql service in server.. if not please restart and check it. – Mahendran Sakkarai Mar 02 '15 at 05:23
  • @Mahendran: is it okay to restart the server? – user1012181 Mar 02 '15 at 05:24
  • @user1012181 the error will happen if we wont add local-infile=1 in my.cnf. But you added. So its giving the same error means the changed configuration is not affected still. To make it affect we need to restart the mysql. So restart the mysql server and then check. – Mahendran Sakkarai Mar 02 '15 at 05:28
  • Restarted. Still not fixed the problem – user1012181 Mar 02 '15 at 05:31
  • http://stackoverflow.com/q/23525111/3049065 have you tried this. – Mahendran Sakkarai Mar 02 '15 at 06:14
  • @Mahendran: If I do `sudo apt-get install php5-mysqlnd` will it affect my current working codes? – user1012181 Mar 03 '15 at 00:28
  • @user1012181 yes, Check this [link](http://dev.mysql.com/downloads/connector/php-mysqlnd/). In this the mysql extension is deprecated. So if you use old mysql extension means you need to switch over to mysqli or pdo extensions. Check the detailed extension [description](http://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.api.choosing.html) in php5-mysqlnd. And some [usefull informations](http://serverfault.com/a/400442/257624) about php5-mysqlnd. – Mahendran Sakkarai Mar 03 '15 at 05:31

1 Answers1

0

You probably have to enable this in the my.cnf file and for php PDO.

[mysqld]
local-infile 

[mysql]
local-infile

and ...

<?php
$pdo = new PDO($dsn, $user, $password, 
    array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)
);
Reenactor Rob
  • 1,508
  • 1
  • 11
  • 20