Uploading Large Files to MySQL Server- the Quest

I have been using mySQL server for the past month to run SQL queries. I realized the other day when I tried to upload an incredibly large dataset for a technical interview that the normal "table data inspector wizard" was going to take about 3 days to upload my file.

So, I have put about 10 hours this weekend into getting my computer to allow me to change the privacy settings to use the LOAD DATA INFILE command. I am writing this in case it helps anyone who comes after me who has run into this problem as well, and realized it is unsustainable to not be able to quickly upload large files. I am using Windows. This will not be the same on non-windows.

Be warned- this is pretty complex!!

So first of all, the issue if you cannot use the LOAD DATA INFILE command in your mySQL Workbench is that your "secure_file_priv" option is set to only allow uploads from a certain place- but I already tried putting my file there, and it didn't help. Additionally, your "'local_infile' is set to "OFF".

This is (FOR UNKNOWN REASONS- very annoying) set to these privacy settings that block you by default.

You can check it by going down to your windows explorer to find your MySQL command line client. This is not a pretty interface.

Once you open it, you will be prompted to put in your password that you set up with mySQL once you installed it.

Once you are in, copy in:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Yes, the semicolon at the end is essential. Once it is copied in, hit enter.

+---------------+-------+| Variable_name | Value |+---------------+-------+| local_infile | OFF |+---------------+-------+1 row in set (0.00 sec)

^ Something like this but formatted as a table should pop up.

You can go back to this later to check it worked. You can also check the other setting:

SHOW VARIABLES LIKE "secure_file_priv"; 

This should show up as a file extension, you do not want that. To show what the screen will look like:

BUT!! You will have a path specified in the second cell. The empty cell is what you want by the end.

So, this is where it gets extra tricky. You need to go into your program files and find your notepad app. Right click on it, and choose "Run as administrator".

Next, you need to find the file path to the file you want- which has to do with how your MySQL operates but cannot be found in the workbench (as far as I can tell).

Go back to your computer explorer feature (like how you search to open apps, for me it is a picture of the windows logo on the taskbar) and open up "services".

Find MySQL, right click, and click on "properties".

Then look for the file location under "Path to executable". Mine looks like this:

C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MySQL80

What you need is the second file path - C:\ProgramData\MySQL\MySQL Server 8.0\my.ini. Copy that.

Now, go back into your notepad app. Choose "open file" and it will pop up the normal browse files option. Go up to the top, like where a URL usually is on a webpage. Paste in: The path you copied (C:\ProgramData\MySQL\MySQL Server 8.0\my.ini).

Now, you should open up a file with all of the program details which the server reads. It is overwhelming. Ignore most of it. Use Control f to find certain things:

Look for :

[client]

and paste:

loose-local-infile=1

Look for :

# Secure File Priv.

And take the path out in the line below so now it just reads:

secure-file-priv=""

I also put:

loose-local-infile=1

At the bottom of the file on an earlier attempt. I don't think you need it there, but I've got it there and it is working, so I am keeping it.

Now, "save as". You will see a screen that looks like this pop up:

You file is not showing up because it is not a text file. Do not worry about this. Just click save. It will ask "my.ini already exists. Do you want to replace it?". Say yes.

Now, close out of your command line client which we opened earlier. Go back to services, right click on mySQL, and restart it. If you ever are in this process and the command line client does not start or the workbench doesn't connect to the server, it is because it needs to be restarted, or started manually. I had to restart my computer a few times as I went along as well.

Reopen and sign back into your command line client. IF it exits out right away, go start it again.

Now, go back to the beginning of this process where we checked the priv setting and the infile setting. Make sure they are both showing as we want- local_infile and secure_file_priv should just be blank.

Now, your settings are ready to accept the LOAD INFILE query! First though, turn on warnings so you can see if there are errors. Type:

\W;

Now warnings are turned on.

The best way to get it to work from me was to continue with this command line client application. Before creating the table, you need to specify the database (schema). I was trying to create the table "sales" in the database "sales" (which is a little confusing, but here I am referring to the database/schema, not my table):

USE sales;

By the way- when you are in the workbench, if you try to switch which database you are using and it keeps you in an older one- go over to where it says "Schemas" and double click the one you want! Took me until my technical interview to figure that one out too.

Now, here is a table I created:

CREATE TABLE sales(

Store int,

dept int,

Date datetime,

Weekly_Sales double,

IsHoliday varchar(45),

entry int NOT NULL,

PRIMARY KEY (entry) );

I had to try this several times because the primary key needed to not have any duplicates. Now here is the query I used (NOT in the workbench, in the command line client) in order to get the csv uploaded:

LOAD DATA LOCAL INFILE "C:/Users/kanel/Documents/salesdata/sales.csv"

INTO TABLE `sales`

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES

I copy and pasted most of this from stack overflow. The "Ignore 1 lines" at the end makes it so it does not import the row names. The first time I did this, I actually thought it hadn't worked because I got warnings. But then I tried it again, and I got a lot of errors about duplicate primary keys. So I went to the Workbench and tried out: SELECT *

FROM `sales`

LIMIT 1000

And boom! My table was all there just as I needed it. all 421570 rows. I used a count function to verify.

I went to a lot of different Stack Overflow pages to figure this out, and I pulled information at different times. So I can't give you an exact path, but here's the ones I used that I know were helpful:

• https://stackoverflow.com/questions/64414761/how-to-change-secure-file-priv-option-in-mysql-on-ubuntu-20-04

• https://stackoverflow.com/questions/63361962/error-2068-hy000-load-data-local-infile-file-request-rejected-due-to-restrict

• https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client

• https://stackoverflow.com/questions/55605623/cant-load-csv-to-mysql-os-errno-13-permission-denied

• https://stackoverflow.com/questions/37596163/disable-secure-priv-for-data-loading-on-mysql

• https://stackoverflow.com/questions/32737478/how-should-i-resolve-secure-file-priv-in-mysql

• https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

Did this help you? Let me know - anniesanalytics@gmail.com . I would love to hear that I saved someone else the pain.