I am running an xampp stack on top of a windows machine for local development and attempting to load large batch files into mysql. I have been essentially following this tutorial:
Importing CSV file to mysql table using "LOAD DATA" command
My code looks a little like this:
$sql = 'LOAD DATA LOCAL
INFILE "' . $tempLoc . '"
INTO TABLE charges
FIELDS
TERMINATED BY ","
ENCLOSED BY "\'"
LINES
TERMINATED BY "\\n"
(
id,
loc,
chgs,
post_dt
)';
$connection = Yii::app()->db;
$transaction = $connection->beginTransaction();
try {
$connection->createCommand($sql)->execute();
$transaction->commit();
} catch (Exception $e) { // an exception is raised if a query fails
print_r($e);
exit;
$transaction->rollBack();
}
When I load the file I get this error:
“PDOStatement::execute() [<a href=‘pdostatement.execute’>pdostatement.execute</a>]: LOAD DATA LOCAL INFILE forbidden”
I have followed the advice given here Load data local infile MySQL does not work by adding the attribute option into my db connection as such:
I have also added the following line to my.ini file, under [mysqld]:
local-infile=1
These setting have not changed the error at all. I have searched for an answer, but I have not been able to resolve the issue. I should also mention, if I run the same sql statement within phpMyAdmin, it works flawlessly. I’m hoping someone more knowledgeable about this issue may be able to assist me. Thank you!
Perhaps the specified database user holding the connection has insufficient rights? Also: Make sure $tempLoc is an absolute location, not a relative one.
The connection is done through root, so I don’t believe there are insufficient rights.
I have tried $tempLoc as both an absolute path and relative path, but still receive the same error.
I thought about the compiling issue and I don’t believe that could be it either, otherwise it would fail with phpMyAdmin (or I would assume).
I believe I can dismiss mysql as being the issue because I have been able to also execute the script through mysql command line. So that leads me to believe its an issue with PHP. As I stated above, would phpMyAdmin be able to run the same script if php had been incorrectly compiled as the above link states? Then again, I have no clue what else it could be.
I may have to try my hand at re-compiling php on a windows machine.
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version. The SQL statement executed was: LOAD DATA LOCAL INFILE....
I’m using Ubuntu 12.10 (which has PHP version 5.4.6).
Here’s some code that shows 3 different attempts to execute a “LOAD DATA LOCAL INFILE” query:
$sql="LOAD DATA LOCAL INFILE '".$tempLoc."'
INTO TABLE `sitemap_keywords`
LINES
TERMINATED BY '\\n'
IGNORE 1 LINES
(`keyword`)";
// Attempt 111111111111111111111111111111111111
// Yii version DOES NOT WORK:
Yii::app()->db->createCommand($sql)->execute();
// Attempt 222222222222222222222222222222222222
// PDO version DOES NOT WORK:
$myHost = 'localhost';
$myUser = '<user>';
$myPass = '<pwd>';
$myDB = '<dbname>';
$dsn = "mysql:dbname=$myDB;host=$myHost";
$dbc = new PDO($dsn,$myUser,$myPass,array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));
$dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
try {
$dbc->exec($sql);
} catch(PDOException $e) {
echo $e->getMessage();
}
// Attempt 33333333333333333333333333333333
// mysql version WORKS FINE
mysql_connect($myHost, $myUser, $myPass, false, 128) or die(_ERROR15.": ".mysql_error()); // 128 (enable LOAD DATA LOCAL handling)
mysql_select_db($myDB);
mysql_set_charset('utf8');
mysql_query($sql);
Also, the SQL query works fine from the command line as long as you have –local-infile as a parameter to the mysql client:
mysql -uroot -p --local-infile dbname
(or you can set local-infile=1 in /etc/mysql/my.cnf and restart mysql).
The fact that the native mysql version works and PDO doesn’t work leads me to think this is a bug in PHP!