How to fix mysql error “ERROR 1290 (HY000): Unknown error 1290” ?

Fixing error “ERROR 1290 (HY000): Unknown error 1290” 

I was trying to export the mysql User table into a csv file for a security audit. I used the mysql “INTO OUTFILE” feature like following

As you can see it resulted in following error 

Whats is the cause?

Mysql has a system variable named “secure_file_priv”  which is used to limit the effect of data import and export. As per the mysql docs

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server will not create it.” 

Then i checked the value set for this variable on my machine

As you can see , it is set to the folder “/var/lib/mysql-files/” . Now i changed the query to export the csv into this folder

As you can see the query succeeded. 

error-1290-hy000-unknown-error-1290

How to change the “secure_file_priv” value?

  1. To disable secure_file_priv , please add the following in the my.cnf file and restart your mysql service

       2.    To set a new folder say “/tmp” , add the following

Don’t forget to restart mysql service once changes are done. 

 

Leave a Reply

Your email address will not be published. Required fields are marked *