MySQL Forums
Forum List  »  MySQL Workbench

LOAD DATA INFILE with Workbench for newbies
Posted by: Mike Goodine
Date: April 18, 2019 11:52AM

I work on a Windows 10 machine and have administrator privileges for that machine.

Getting information from a text file into a database is where the work starts. Wanted to familiarize myself with MySQL have used another “nameless” product. Took a course on Lynda.com about MySQL. Step 1; create a database. Step 2; load info into the database. Step 3; query the database. Seemed simple until Step 2. Received; Error Code: 1148. The used command is not allowed with this MySQL version.

Okay, check online for a solution. Confusion begins. It appears the command “LOAD DATA LOCAL INFILE” work on versions of MySQL Server and MySQL Workbench before 8.0.. This appears to have been done for security reason. Okay, good enough but you need to load information from a text file into a table. There are numerous ways to do this, but for newbies here is a way to accomplish this in MySQL Workbench.

1. Make a backup of critical information.

2. Check the setting of “local infile”. If it returns a value “ON” your 2/3rd‘s of the way to loading data.

SHOW VARIABLES LIKE "local_infile";

a. If the value returns “OFF” the set the value to “ON”.

SET GLOBAL local infile = 'ON';

3.Now you need to check where your server will look for files.

SHOW VARIABLES LIKE "secure_file_priv";

4. The command above will return a location on your machine where you can copy files locally and then load them into a database table. It may look like this. Copy the location by right clicking the value with your mouse and selecting “Copy Field”. Then paste this value into your query to use later.

'C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\'

5. Copy the text file you want to load into a table the above location.

6. Review the information you want to load in the text file and match it to the table you want to load it into.

7. Write your query to load data into the table. Set the database you are working with to be the default schemas in MySQL Workbench. I do not use the word “LOCAL”, the server does not like this. On my “Windows machine” I use a forward slash “/”. Add the name of the file you want to load to the location. Tell MySQL how you want to deal with information inside the text file. (comma delimited, lines end in, ….)

LOAD DATA INFILE
'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/CA_DRU_proj_2010-2060.csv'
INTO TABLE pop_proj
#FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

8. Execute the query and wait. If the query runs successfully you will get a green checkmark. If it does not run successfully check the error message. When trying to load 3.6 million records my machine timed out. You can change this setting. When I ran the query for the second time, I had 7.2 million record in the table. Check your table before you run the query a second time.

9. If your query is not formatted correctly it may not work; you can work on this. Go back to Step 5.

When you search for solutions to this problem you will come across solutions that have you edit the “my.ini” file. If you are not comfortable with this follow the steps above and leave the file alone. If you want to edit the file; on a “Windows” machine it can be found at: C:/ProgramData/MySQL/MySQL Server 5.7/ or what ever server version you are using. At the end of the file you can add this on a new line: local_infile = 1 , this set local infile to “ON”.
I initially set Secure File Priv to: secure-file-priv="". The server on my Windows machine had trouble locating the file I was loading. I had to saving the files to be loaded next to the database files. I did not like this solution. I changed the Secure File Priv setting to secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

I hope this helps someone.

Options: ReplyQuote


Subject
Views
Written By
Posted
LOAD DATA INFILE with Workbench for newbies
2223
April 18, 2019 11:52AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.