What is error code: 1290 and how to fix it.

Trying to load some data into mysql using LOAD DATA and encountering the following error?

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

This error occurs when the server has been configured to use the --secure-file-priv startup option. This option limits where mysql will look for files to a specific directory when processing a LOAD DATA statement.

There are two ways you can resolve this error and get your data loaded.

Option 1: Move your data file

The file you want to load must be contained within the directory specified by --secure-file-priv. To determine where this is you can query the server variables:

SHOW VARIABLES LIKE 'secure_file_priv'

That query will return a result showing the variable and it's value which will be the configured directory. Move your data file into that directory.

Next re-issue your LOAD DATA statement with the full path to the file. The full path is necessary to ensure mysql looks in the correct place. Mysql has some unusual rules for trying to locate the file if you use a relative path.

Option 2: Reconfigure and restart (Windows)

Open the start menu and type services.msc then press Ctrl+Shift+Enter to run it as an administrator.

Open services.msc via start menu.

Locate the MySQL service and double-click to open it's properties dialog. Check the Path to Executable for the --defaults-file option to determine where your my.ini file is located. By default it should be in a path similar to C:\ProgramData\MySQL\MySQL Server 5.7 (adjust appropriately for your specific version).

Locate the my.ini file via the service properties.

Stop the service then close the dialog.

Open the start menu and type notepad then press Ctrl+Shift+Enter to run it as an administrator.

Open the my.ini file you previously located in notepad and search the file for secure-file-priv. Comment out the line by adding a # in front of the line.

Comment out the secure-file-priv option.

Save the file and close notepad.

Re-start the MySQL service using the services management console.