0

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 '\n' 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?

R2D2
  • 2,620
  • 4
  • 24
  • 46
  • Just a thought, has the data you are loading changed in some subtile way that is causing a hiccup – RiggsFolly Sep 28 '16 at 10:07
  • Check your Mysql version. To do echo `$loadfile` & then try to run the raw query in your mysql either from cmd or phpmyadmin. This is not related to php version. – Dipanwita Kundu Sep 28 '16 at 10:07
  • That SO question is not a duplicate. I have checked that the load data command is enabled. And the data format hasnt changed. I know that our host (1&1) updated our PHP version recently, but I dont know if the mysql version has changed. – R2D2 Sep 28 '16 at 10:14
  • mysql version is 5.1.73 – R2D2 Sep 28 '16 at 10:21

1 Answers1

0

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

Dipanwita Kundu
  • 1,637
  • 1
  • 9
  • 14