When you update your site with new data from a file, you may see the error “Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”.

Why did it happen all of a sudden? Is your data corrupted?

Not to worry. This is quite easy to fix and usually happens after a MySQL upgrade.

As part of our Server Management Services, we help server owners and webmasters resolve MySQL errors such as this.

Today we will help you fix error code 1290 in a few easy steps.

Why this MySQL error code 1290 appears?

From version 5.7.6 onwards MySQL introduced a new security feature.

This will prevent website attackers from loading malicious data directly into your database.

It is done by restricting the databases to load data from only a single special directory in the server.

That directory is accessible only by the server administrator, and therefore attackers won’t be able to load malware.

By default this directory will be “/var/lib/mysql-files/” in Linux servers.

So, if you have a program or script that loads site data from a different location, MySQL will think that it’s an unauthorized upload attempt, and block it.

Is there any solution?

We have two solutions for your problem with code 1290.

• One is moving the data file into the directory
• The other is reconfiguring and restarting MySQL server

Today, let’s discuss these in detail.

Fixing the error by moving the data file into the directory

Recently, one of our customers using Linux approached us with the error code 1290.

He said he was able to load files without failure before. But from the last few days, he is facing this error.

When checked, we found that he recently upgraded his MySQL to 5.7.6 and the default path is restricting the upload.

Let’s see how we fix this.

We asked him to use the query to determine where –secure-file-priv option is.

SHOW VARIABLES LIKE 'secure_file_priv'

This returned a variable along with its value as:

+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

We asked the customer to use  /var/lib/mysql-files/ as the file path to run LOAD DATA as this is the configured directory and he needs to move the data file here.

Finally, suggested running the LOAD DATA again with the full path.

This fixed the error.

For Windows Users

In Windows, we do the following for fixing the MySQL error 1290

We look for the file being used when the service starts at MySQL57 Windows service.

From there, we get a path similar to C:\ProgramData\MySQL\MySQL Server 5.7\my.ini.

Open the file and under [mysqld] section we can see secure-file-priv along with value as shown:

secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.7/Uploads”

This is the path we need to use. So, reissue the LOAD DATA statement with this right path for fixing the error.

Fixing the MySQL error 1290 by reconfiguring and restarting

In some cases, changing the directory won’t solve the issue. In such a case we need to do the following:

For Windows Users

• Go to start menu and type services.msc then press Ctrl+Shift+Enter to run it as an administrator.
• Locate the MySQL service and double-click to open its properties dialog.
• Check the Path to Executable for the –defaults-file option to determine where my.ini file is located.
• In windows, C:\ProgramData\MySQL\MySQL Server 5.7\my.ini is the default file being used when the service starts.

Note: It may vary according to various versions

• Stop the service then close the dialog.
• From the start menu, take notepad then press Ctrl+Shift+Enter to run it as an administrator.
• Then open my.ini file previously located in notepad and search the file for secure-file-priv.
• finally, comment out the line by adding a # in front.

For Linux Users

In Linux, we suggest searching for either of the most common locations: /etc/my.cnf or /etc/mysql/my.cnf.

Search the file for secure-file-priv. We get it along with the value. Comment the line and restart the service.

This will fix the issue.

But, we won’t suggest this as it is not secure.

The secure way is always by moving the data file into the directory.

[Need assistance in fixing MySQL errors? Click here to talk to our experts.]

Conclusion

In short, we discussed in detail on MySQL error code 1290 and saw how our Support Engineers find fix for this in different scenarios.