Yii Framework Forum: LOAD DATA LOCAL INFILE forbidden - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

LOAD DATA LOCAL INFILE forbidden Rate Topic: -----

#1 User is offline   groc426 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 17-February 11

Posted 12 July 2012 - 01:53 PM

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!
0

#2 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,222
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 12 July 2012 - 03:37 PM

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...7638090/3012385
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#3 User is offline   groc426 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 17-February 11

Posted 12 July 2012 - 05:06 PM

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

#4 User is offline   alif 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 23-January 10

Posted 17 October 2012 - 03:55 AM

View Postgroc426, on 12 July 2012 - 01:53 PM, said:

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!






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

#5 User is offline   tfotherby 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 11-January 11
  • Location:Reading, UK

Posted 15 November 2012 - 04:37 PM

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 ?
0

#6 User is offline   yiipas 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 29-January 13

Posted 13 February 2013 - 08:16 AM

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

#7 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,648
  • Joined: 17-January 09
  • Location:Russia

Posted 13 March 2013 - 05:00 AM

Update PHP https://bugs.php.net/bug.php?id=54158
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#8 User is offline   kiran.irali 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 11
  • Joined: 27-May 13
  • Location:Belgaum

Posted 27 May 2013 - 05:55 AM

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 => 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
0

#9 User is offline   jbowler 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 05-August 12

Posted 31 July 2013 - 02:00 AM

"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!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users