Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql encountered load data import file data 1290 error how to do, the article is very detailed, has a certain reference value, interested friends must read it!

Error occurrence scenario

Use the mysql command in cmd to add data to the student information table. Use load data to simply import data in batches.

Prepare text data: split xueshengxinxi.txt file data with tab key

Execute the load data infile text data path into table tab_load_data pop-up error.

# load data (load data) syntax, to load the data as follows: 1 Zhang Sannan Jiangxi 12 Li Si male Sichuan 23 Wang Wu male Shanghai 14 Zhao Liu female Hubei 3 5 grandchild Hubei 36 week 8 male Hunan 1 # test data table create table tab_load_data (id int auto_increment primary key, name varchar (10), sex enum ('male', 'female'), native varchar (10) F5 int)

Error description

Using load data to import file data always pops up an error (Error 1290.) as follows:

ERROR 1290 (HY000): The MySQL server is running with the-- secure-file-priv option so it cannot execute this statement

Solution idea (process)

1) since the error indicates that the secure-file-priv option is running and the load service cannot be executed, find the configuration item in the MYSQL configuration file "my.ini".

2) shut down the mysql service before configuration

3) Open my.ini, search for the keyword "secure-file-priv" to find this option, comment out the option directly, save it, and start the MYSQL service.

4) the result is still the same mistake. Indicates that comments have no effect. What's going on?

Execute the command "show variables like'% secure%'" and find that after I commented out, the value of secure-file-priv is NULL, which means import and export is prohibited.

This shows that it is not possible to comment out. If you don't delete the configuration file entry, you'll have to modify the directory.

5) modify the path, execute the command "show variables like'% secure%'" to view, and restart MYSQL.

6) execute load data again, and the execution is successful

For more complex uses of load, see the mysql manual. At this point, the problem is solved.

Third, the summary explains

Learn from this error:

1) load data: you can import data into a table from an external notepad file.

The data in this notepad usually requires "neatness", with a line of text corresponding to a line of data, and a line using certain characters (such as the tab character) to separate the value of a field.

2) the parameter secure-file-priv is used to limit the effect of data import and export operations.

Such as: execute LOAD,SELECT...INTO OUTFILE statement and LOAD_FILE () function. These operations require file permissions.

3) if the secure-file-priv parameter is set to a directory name, the MYSQL service only allows files to be imported and exported from that directory.

This directory must exist and the MYSQL service will not create it.

4) if the parameter secure-file-priv is NULL,MYSQL service, import and export operations will be disabled.

These are all the contents of this article "what to do when mysql encounters 1290 errors in load data import file data". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.