1

I am trying to load data localy from pc to MySQL database ('texnologialogismikou') but I get a lot of errors. I searched a lot in forums and in MySQL site but I couldn't find a solution. I tried to change variables, move the file in the Workbench directory and some other things but nothing worked.

load data local infile 'C:\Users\Mattheos\Desktop\ActualTotalLoad-10days.csv'
Into table texnologialogismikou.actualtotalload
fields terminated by ';' enclosed by '\"' lines terminated by '\n'

Some of the errors I got:

Error Code: 2. File 'C:ProgramDataMySQLMySQL Server 8.0Uploads' not found (OS errno 2 - No such file or directory)

Error Code: 1148. The used command is not allowed with this MySQL version

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

P.S. I know that there are posts like this but this is the last thing I could do. I tried everything I could find and nothing worked.

Community
  • 1
  • 1
Mattheos Fikardos
  • 55
  • 1
  • 5
  • first check if the folder mentioned exists and then you have two choices, copy your data in that filder or changeing the folder, but i had here a case where this also didn't work – nbk Feb 23 '20 at 00:02
  • Related: https://stackoverflow.com/a/60717467/6064933. – jdhao Oct 02 '22 at 14:49

3 Answers3

2

On mySQL worckbench at the Manage Server Connections window -> Select your connection -> Go to the Advance tab and insert OPT_LOCAL_INFILE=1 in the others textinput.

Mattheos Fikardos
  • 55
  • 1
  • 5
0

The last error message is the relevant one:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The --secure-file-priv option restricts from which directory files can be loaded, as explained in the documentation:

If set to the name of a directory, the server limits import and export operations to work only with files in that directory.

You can display the configured directory with:

show variables like "secure_file_priv";

Just move the file to import to that directory, and run the load statement again.

GMB
  • 216,147
  • 25
  • 84
  • 135
0

try mysqlimport

https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html

or Try community edition of tools like Pentaho ETL which will load data really fast.

Ganesh Chandrasekaran
  • 1,578
  • 12
  • 17