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.
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).
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.
Save the file and close notepad.
Re-start the MySQL service using the services management console.