Geeks, please accept the hero post of 2021 Microsoft x Intel hacking contest>>>

MySQL import CSV format file data execution prompt error (error 1290)

The MySQL server is running with the –secure-file-priv option so it cannot execute this statement.

【1】 Analyze the reasons

In fact, the reason is very simple, because the import and export directory permissions are limited when MySQL is installed. It can only be imported in the specified directory

You can use the following command to see what the current value of secure file priv is

SHOW VARIABLES LIKE "secure_file_priv";

Results: the results were as follows

As you can see, the value of local value is null. What does null mean?Information:

(1) Null, which means forbidden

(2) If the value value has a folder directory, it means that only files in the directory are allowed (PS: Test subdirectory is not allowed)

(3) If it is empty, the directory is not restricted

【2】 Solutions

The cause of the problem has been found, and the solution depends on the business needs

(1) Scheme 1:

Put the import file into the path corresponding to the current value of secure file priv

(2) Scheme 2:

Change the value of secure file priv to the path where the file is to be imported

(3) Scheme 3: modify the configuration

Remove the Import Directory restrictions. You can modify the MySQL configuration file (my.ini under windows and my.cnf under Linux). Under [mysqld], check whether there is any:

secure_ file_ priv =

If there is no such line, add it manually. If it exists, as shown in the following table:

secure_ file_ priv = /home

Such a line indicates that it is restricted to the/home folder. For example:

secure_ file_ priv =

Such a line of content means that there is no restriction on the directory, and the equal sign must exist, otherwise MySQL cannot be started

After modifying the configuration file, restart Mysql to take effect

After restart:

Close: Service mysqld stop

Start: Service mysqld start

Re query results:

After verification, the import file is normal

Good Good Study, Day Day Up.

Sequence selection cycle summary