How can we tackle MySQL error ‘ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement’ while importing or exporting the data?

MySQLMySQLi Database

MySQL throws this error because of the two reasons, either no directory is specified under --secure--file--priv variable or we are giving the wrong path in our query while importing or exporting the data. To tackle this error we must have to check the value of –secure—file—priv variable by following query −

mysql> Select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| C:\mysql\bin\mysql-files\ |
+---------------------------+
1 row in set (0.00 sec)

We can see there is path under secure_file_priv variable and all the files would be created under this directory when we export the data.

But, if the above command shows NULL as result then it means there is no path specified and we need to specify the path in my.ini file before exporting the data. Suppose if we will write secure_file_priv = mysql-files/ in our my.ini file then secure_file_priv will have the path like above as its value. Then all the files will be created under this directory.

raja
Updated on 07-Feb-2020 05:31:58

Advertisements