-1

Hi I am new to the world of mysql and sql in general. I am trying to create a table in mysql so I can use it analyse the data in jupyter later on. The problem is the data is too large so just importing it is taking too long. I read that you can use load data infile for this purpose.

So here is what I did:

1) Created a table in mysql with just the column names not the data, since that is in csv 2) used the load query to import the data from the csv to the table created

Problem: The Error 1148 just keeps coming!

I have attached a screenshot, please let me know what I did wrong?

mysql workbench

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
Myrazz
  • 1
  • 1
  • 1
  • 1
    There is a reported bug in MySQL Workbench 8.0 https://bugs.mysql.com/bug.php?id=91891 a workaround (and to confirm that the bug is with MySQL Workbench) is to use a different client such as the mysql command line client. there is system configuration (mysql system variable settings) required to allow "local infile". another option is to load from a non-local filesystem (a file on a filesystem accessible to mysql server) but there is server configuration required to allow access. As a first step at debugging, perform the LOAD DATA statement in the mysql command line client. – spencer7593 Jan 10 '20 at 16:57
  • @spencer7593 I tried running the statement in mysql shell and got the same error, what should I do? https://imgur.com/a/StIFAks – Myrazz Jan 11 '20 at 13:35
  • for mysql command line client, explicitly add the `--local-infile=1` option. i.e. include the option on the command line that starts the client e.g. `# mysql -u username -p --local-infile=1 ...` Reference https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html – spencer7593 Jan 13 '20 at 19:41

2 Answers2

0

LOAD DATA is disabled by default as it is a security risk.

See this answer for causes of error 1148: ERROR 1148: The used command is not allowed with this MySQL version

Your attempt to use the console failed because you're running it in JavaScript mode. See this answer: MySQL: SyntaxError: Unexpected identifier

Another option is to use mysqlimport.

Pan
  • 331
  • 1
  • 7
  • I changed the local infile to ON, ran it in mysql and no longer in JS, I am still getting the same error https://imgur.com/a/StIFAks – Myrazz Jan 11 '20 at 14:02
0

Figured it out. There were actually a couple of issues that were involved.

1) Couldn't load mysql directly in command line client, had to address that first. Error was mysql not found.

2) Had to establish connection to database next

3) Change global local_infile= 1

4) exit mysql (this is important!)

5) enter again with mysql --local-infile=1 -u username -p password

6) now use the load command. Used this one: 

load data local infile 'F:\\Data Science\\SQL Practice\\p1.csv' into table p1
fields terminated by ','
lines terminated by '\n'
ignore 1 lines;

Check out this video for steps 1 to 5 : https://www.youtube.com/watch?v=XM2xx-PD4cg

Check out this link for the load command (step 6): MySQL Error 29. SQL not using correct file path

Myrazz
  • 1
  • 1
  • 1