0

I'm going through the MySQL documentation tutorial. It's really great so far, but I've run into a problem. The step where the tut explains how to load initial table data from a file doesn't work.

Below are the steps I have taken which include a deep dive into documentation and trying many answers to similar questions (linked), all to no avail.

  1. I googled the error and found in the docs that loading local data is a security risk so it has to be enabled. I tried this answer and this answer, which was to use the following when logging into MySQL:
# neither of these worked:
mysql -u [username] -p --local-infile
mysql -u [username] -p --local-infile=1
mysql --local-infile -u [username] -p

Maybe when I log in with this option, I'm just setting it on the client side and not the server? I really don't know the difference.

  1. Next, I dug into the documentation a little more and read about the "local_infile" variable. There is even a section in the documents titled Enabling or Disabling Local Data Loading Capability, where it talks about a system variable called local_infile but doesn't say a damned thing about how to enable or disable it.

  2. So I next read the documentation about server system variables and how to look at what I've got going on using:

mysqld --verbose --help

Which printed out a massive amount of text, but I found a line that said:

Default options are read from the following files in the given order: C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 8.0\my.ini C:\Program Files\MySQL\MySQL Server 8.0\my.cnf

I figured I could find my local_infile variable in one of those .ini or .cnf files and change it to 1 or True or whatever... However, exactly none of those files exist in any of those locations.

  1. Using this answer, or more specifically the comments on the answer, I found this file: mysql.conf.sample which has a few variables listed in it but not local_infile, nor the host of others that were listed in the print out from mysqld --verbose --help. To this file I added the lines
[mysqld]
local_infile=1

No luck there.

  1. SET GLOBAL local_infile='ON' and then SHOW GLOBAL VARIABLES LIKElocal_infile` results in:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

It was OFF before. But guess what, I still can't load two rows into a database from a text file. Shocker.

  1. I found a blog post that said that config files could be located in the directory %PROGRAMDATA%/MySQL/MySQL Server 8.0 as a my.ini. Sure enough, that's where it was. I added local-infile=1 under [mysql] and [mysqld]. It still didn't work, so I tried local-infile and local-infile=ON (I have seen all of these used in various accepted answers). None of them worked.

So, as is the norm, documentation absolutely sucks, and I'm lost. But at least I've done my due diligence of searching the docs, so the pedantics of SO are satisfied and I can ask my question:

How do I enable loading local data by default?

rocksNwaves
  • 5,331
  • 4
  • 38
  • 77
  • Does this answer your question? [MySQL: Enable LOAD DATA LOCAL INFILE](https://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile) – nbk Apr 13 '20 at 17:31
  • @nbk No it does not. If you had read the question, you would see that I already tried that. – rocksNwaves Apr 13 '20 at 17:57
  • Load data when enable it, only loads data from a specified folder https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql when you enter there a folder c:\temp it accepts file from there. – nbk Apr 13 '20 at 18:30

1 Answers1

1

This is on windows. I had same problem. Cannot find a way to turn that local_infile option on. Solution is to use batch files to start mysqld. I use standalone apache/mysql system which starts from where ever directory you extract the zip.

Keys are:

  1. To have non blocking batch to start mysqld. First set environment varibles how ever you want but especially note that in that batch you better add mysql bin directory to %path% environment variable.

call ".\..\..\set_roots.bat"

Then start mysqld as parallel process. Again use "call" command:

call start "MYSQL" mysqld --defaults-file=%my_ini% --port=%mysql_port% --standalone --console --log-timestamps=SYSTEM --explicit_defaults_for_timestamp --verbose

  1. Use timer to wait so that mysqld is ready to take queries

    timeout /t 5

  2. Now that mysqld has started we can execute mysql commands at the end of original batch.

Example:

mysql -u root -p%rootpw% -e "SET GLOBAL local_infile = 'ON';"

Note: Leave no space between -p and actual password and it does not ask input

Tonecops
  • 127
  • 9