doudun2212 2018-07-11 13:06
浏览 101
已采纳

Yii2 LOAD DATA LOCAL INFILE

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[42000]: 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.

EDIT

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)

EDIT 2

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);
  • 写回答

2条回答 默认 最新

  • duanmei1350 2018-07-11 19:19
    关注

    According to MySQL documentation, server needs to be configured to allow LOCAL keyword:

    LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with the local_infile system variable disabled, LOCAL does not work.

    https://dev.mysql.com/doc/refman/8.0/en/load-data.html

    Are you sure that your server is correctly configured to allow this keyword?

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图的问题
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名