I know there are other solutions to this problem, but it did not work for me.
I am trying to execute a LOAD DATA LOCAL INFILE statement in a MySQL database via a Yii2 website. I have got this working in the past, so I am not sure why it doesn't work anymore.
I get the following error:
SQLSTATE: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version
I do have the following setting in my db.php config file.
'attributes' => [ PDO::MYSQL_ATTR_LOCAL_INFILE => true ],
I also tried disabling secure-file-priv to no avail.
I tried running the following query from Workbench and it throws an error via Workbench as well. However, it works on MySQL 5.7. I am currently using 8.0
LOAD DATA LOCAL INFILE 'D:/Temp/6_attlog.dat' IGNORE INTO TABLE att_log FIELDS TERMINATED BY '\t' ENCLOSED BY '''' LINES TERMINATED BY ' ' IGNORE 0 LINES (@id, `date_time`, `dev_id`, `mode`, `work_code`, `work_code1`) SET `id` = TRIM(@id)
I got it to work with "LOAD DATA INFILE"...
...but it does not work with "LOAD DATA LOCAL INFILE"
Here's the code I was using...
$inFile = str_replace('\\', '/', realpath('uploads/'.$model->file->name)); Yii::$app->db->createCommand(' LOAD DATA LOCAL INFILE \''.$inFile.'\' IGNORE INTO TABLE att_log FIELDS TERMINATED BY \'\\t\' ENCLOSED BY \'\'\'\' LINES TERMINATED BY \' \' IGNORE 0 LINES (@id, `date_time`, `dev_id`, `mode`, `work_code`, `work_code1`) SET `id` = TRIM(@id) ')->execute(); unlink($inFile);