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
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3858555 Server version: 5.7.13–log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. mysql> select Host,user from mysql.user INTO OUTFILE ‘/tmp/useraccess1.txt’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’; ERROR 1290 (HY000): Unknown error 1290 mysql> |
As you can see it resulted in following error
| 1 | ERROR 1290 (HY000): Unknown error 1290 |
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
| 1 2 3 4 5 6 7 8 9 10 11 | mysql> SHOW VARIABLES LIKE ‘secure_file_priv’; +—————————+———————————–+ | Variable_name | Value | +—————————+———————————–+ | secure_file_priv | /var/lib/mysql–files/ | +—————————+———————————–+ 1 row in set (0.00 sec) mysql> |
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
| 1 2 3 | mysql> select Host,user from mysql.user INTO OUTFILE ‘/var/lib/mysql-files/useraccess1.txt’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’; |
As you can see the query succeeded.
How to change the “secure_file_priv” value?
- To disable secure_file_priv , please add the following in the my.cnf file and restart your mysql service
| 1 | secure_file_priv=“” |
2. To set a new folder say “/tmp” , add the following
| 1 | secure_file_priv=“/tmp” |
Don’t forget to restart mysql service once changes are done.