This question already has an answer here:
I have a php script that runs daily from a cron job, to unzip a large zip file containing text data and save this back to a mySQL database.
The zip file is updated and uploaded daily to the FTP site where the php file is hosted.
This has all worked 100% fine for several months, but I think recently our hosting has upgraded itself to PHP version 5.6, and now I get these errors:
Error populating table: The used command is not allowed with this MySQL version
The code that populates the mySQL table is this:
$loadfile = "LOAD DATA LOCAL INFILE '$textfile' INTO TABLE $tablename FIELDS TERMINATED BY '|' LINES TERMINATED BY '
' IGNORE 1 LINES";
mysqli_query($conn, $loadfile)
What I really dont understand is that sometimes it seems to run ok and sometimes it fails! Maybe once a week it works ok now, and on all other days it fails.
Has something changed in PHP that means the above code is now wrong?
Can anyone help with the above code to get it running reliably on PHP 5.6?
</div>
Run this : show variables like '%local%'
If local_infile = 'Off' , then 'On' it
For more details, please check this link Mysql Load data
Try to follow the steps:
1) Open my.cnf and added the line “local-infile=1” under [mysqld] and [mysql]
[mysqld] local-infile=1
[mysql] local-infile=1
Save and quit the file.
/etc/init.d/mysql restart
2) Grant file privilege for the user
mysql> grant file on . to user@’localhost’;
mysql> flush privileges;
3) Restart mysql
/etc/rc.d/init.d/mysql restart
1) Opened my.cnf and added the line “local-infile=1” under [mysqld] and [mysql]
[mysqld] local-infile=1
[mysql] local-infile=1
Save and quit the file.
/etc/init.d/mysql restart
2) The above should allow you to run the command in mysql shell, but you are having problems while running it over browser as a php script. So check if it is enabled in php.
root@abc#grep mysql.allow_local_infile /etc/php5/apache2/php.ini mysql.allow_local_infile = On
Now, the above command shows it is enabled. If it is Off, change it to On and restart apache.
3) I tried modifying my script a bit by editing the connect command as follows.
mysql_connect(“localhost”,”myuser”,”mypass”,”false”,128);
Link reference: The used command is not allowed with this MySQL version – LOAD DATA LOCAL INFILE