mysql 5.5 - how to do LOAD DATA LOCAL INFILE in laravel 5.4

Question

i need to convert the following query in laravel 5.4

$loadDataToTempTableSql = "LOAD DATA LOCAL INFILE '".$filename."' INTO TABLE ABC FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
' IGNORE 1 LINES";
                    $loadDataToTempTableRes = mysqli_query($link,$loadDataToTempTableSql);

                    $loadedData = mysqli_affected_rows($link);

what i did

Step1:

DB::select($load_data_to_temp_table_sql);

which is throwing exception:

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. (SQL: LOAD DATA LOCAL INFILE '/Library/WebServer/Documents/public/abc copy.csv' INTO TABLE ABC FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 LINES)

Step2:

$pdo = DB::connection()->getPdo();
$pdo->exec($load_data_to_temp_table_sql);

again exception:

PDO::exec(): LOAD DATA LOCAL INFILE forbidden

Please guide, what to do?

Answer 1

I have faced same kind of problems and after googling it has been solved now. You should add the following to your mysql/my.cnf file:

[Server]
local_infile=true

After that add 'options' => array(PDO::MYSQL_ATTR_LOCAL_INFILE => true) into your project's config/database.php file.

Now restart your MySQL from cmd/terminal. It will be fine to run the LOAD DATA LOCAL INFILE. If you still face a problem, you can visit https://tenerant.com/blog/using-load-data-local-infile-in-a-laravel-migration/.