LOAD DATA LOCAL INFILE forbidden

Hello fellow Yii’ers!

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:




'db'=>array(

  'connectionString' => 'mysql:host=localhost;dbname=...',

  'username' => '...',

  'password' => '...',

  'attributes'=>array(

    PDO::MYSQL_ATTR_LOCAL_INFILE

  ),

),



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.

For everything else: http://stackoverflow.com/q/7638090/3012385

Da:Sourcerer - Thank you for your response!

  • 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.

I have the same problem as you. after I upload the script to function properly. but on localhost error "


PDOStatement :: execute () [<a href='pdostatement.execute'> pdostatement.execute </ a>]: LOAD DATA LOCAL INFILE forbidden

" when setting the PDO is active.

I use PCLinuxOS and server xampp 1.7.4

I think most likely there are settings that are not quite in xampp.

I have the same problem.

The error I get is:


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!

Perhaps this one: https://bugs.php.net/bug.php?id=54158 ?

Does anyone has the solution to this, I got the same error

Update PHP https://bugs.php.net/bug.php?id=54158

before config/main.php

‘db’=>array(

‘connectionString’ => ‘mysql:host=localhost;dbname=…’,

‘username’ => ‘…’,

‘password’ => ‘…’,

‘attributes’=>array(

PDO::MYSQL_ATTR_LOCAL_INFILE

),

),

CHANGE TO

‘db’=>array(

‘connectionString’ => ‘mysql:host=localhost;dbname=…’,

‘username’ => ‘…’,

‘password’ => ‘…’,

‘attributes’=>array(

PDO::MYSQL_ATTR_LOCAL_INFILE =&gt; true

),

),

And in controller

if($model->validate())

{

$csvFile->file=CUploadedFile::getInstance($model,‘file’);

$tempLoc=$csvFile->getTempName();

$sql=“LOAD DATA LOCAL INFILE '”.$tempLoc."’ INTO TABLE tbl_emp_master

FIELDS TERMINATED BY ','ENCLOSED BY ‘\"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES

(emp_id, name, cardnumber, uid, location, grade, department, emp_type, access_group, check_in, check_out)";

Sol :Remove LOCAL in LOAD DATA LOCAL INFILE

"Sol :Remove LOCAL in LOAD DATA LOCAL INFILE"

This worked for me. I tried everything but different versions of php.

I’m on mysql 5.5.22, php 5.3.10, ubuntu 12.04, 32 bit.

It was working just using mysql, but not php.

Thanks!