PDO/MySQL Unbuffered Query Issue with LOAD DATA INFILE

Beginners - Learning PHP
#1

Hi all. Just getting back into PHP and creating my first app. What better way to start than to automate something to help you in your day job.

The script is nothing complicated, I write a .csv file from an array and then load it into MySQL using “LOAD DATA LOCAL INFILE”.
I am using this load method as the file consists of circa 40k lines of data.

The problem I am having is that I’m getting the error:-

Fatal error : Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in C:\xampp\htdocs\

I have literally stripped this right back to the folllowing script, so I know there are no other queries running.

My PDO Connection:

$pdo = new PDO($dsn, $db_user, $db_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$pdo->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);

The Script:

$fp = fopen('thefile.csv', 'w');

// code here that writes an array into the file with fputcsv($val1, $val2, $val3, intval($val4);

$qry = "LOAD DATA LOCAL INFILE 'thefile.csv' INTO TABLE thetable FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (col1, col2, col3, col4)";

if($pdo->query($qry))
{
    fclose($fp); unlink("thefile.csv");
    exit('Success');
} else {
    exit("Failed");
}

Scratching my head over this one and my best friend Google is not turning up trumps for me, so welcome any input you can give, even if it is recommending an alternative [fast load] method for putting large data sets into MySQL.

I did read that there is a potential MySQL bug, so I went ahead and uninstalled\reinstalled XAMPP, but still nothing.

I am working in the XAMPP environment by the way (PHP 7.3.27 & 10.4.17-MariaDB)

Thanks in advance

/Danny

#2

The LOAD DATA LOCAL INFILE … query must use the ->exec() method. Probably, because it involves multiple communications (the query is sent to the database server, where it is parsed and executed, then php opens, reads, and sends the content of the file to the database server.)

If you added the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY setting in an attempt to solve this problem, I would remove that setting.

Next, you will be getting a LOAD DATA LOCAL INFILE forbidden … error. You need to set the PDO::MYSQL_ATTR_LOCAL_INFILE setting to true using the options array parameter when you make the connection. It cannot be set by calling the setAttribute() method.

Lastly, you should close the file before you execute the query so that all the data you have sent to the file will actually be written to the disk.

Edit: and since you are using exceptions for errors, if the ->query(), now ->exec(), call fails, your else {} logic will never be executed. The main point of using exceptions for errors is that your ‘main’ code only ‘sees’ and has to deal with error free execution, since execution transfers to the nearest correct type exception handlining or to php’s un-caught exception handling if there’s no exception handling in your code. You don’t need any conditional logic to detect if a call is successful, because php will only execute the lines of code following a call if there was no error.

1 Like
#3

Excellent. Thank you, phdr, that worked a treat. I changed the attributes in my PDO connection and then used the $pdo->exec() method, wrapped in a try/catch.

I clearly need to do go more in-depth on the PDO class. I found the ‘phpdelsuions’ page/guide which seems to be popular?

The [new] PDO Connection:

$pdo = new PDO($dsn, $db_user, $db_pass, array(PDO::MYSQL_ATTR_LOCAL_INFILE=>true));
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

The [new] Script:

$fp = fopen('thefile.csv', 'w')

//  code here that writes an array into the file
//  with fputcsv($val1, $val2, $val3, intval($val4);

fclose($fp);

try
{
    $qry = "LOAD DATA LOCAL INFILE 'ftr.csv' INTO TABLE mtd_ftr
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
            LINES TERMINATED BY '\\n' (col1, col2, col3, col4)";

    $pdo->exec($qry);

    echo 'Loaded';

} catch(PDOException $e) {

    echo 'DB load error: ' . $e->getMessage();

}
#4

As a matter of interest, can the $stmt->exec() method be wrapped in a transaction ?

I’m just picking up on the uses of beginTransaction(), and I may have scenarios where it would be useful e.g. where I have multiple table loads with the last having a dependency on the previous being executed cleanly.
I could of course nest the load code, but as I understand it, with transactions, I could do multiple prepares and then commit() on them all being successful, at the end of the script?

Sponsor our Newsletter | Privacy Policy | Terms of Service