MySQL LOAD DATA LOCAL INFILE Julia

Question

I am using MySql 5.5.53-0ubuntu0.14.04.1, and Julia Version 0.5.0

In my julia script, I am using

conn = MySQL.mysql_connect(host, username, password, database)

command = string("LOAD DATA LOCAL INFILE 'infile' INTO TABLE temp_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';")

MySQL.mysql_execute(conn, command)

I got the error message “The used command is not allowed with this MySQL version”

The command does work when I was running it directly on the MySQL console, so I am assuming it is an issue with Julia MySQL.mysql_connect .

Looks like there is a similar problem with Python, and the following syntax solves the problem. MySQL LOAD DATA LOCAL INFILE Python

MySQLdb.connect(server, username, password, database, local_infile = 1)

I wonder how should I do this with Julia?

According to this, the problem might be with your mysql server not being configured to allow local-infile. You may need to change your my.cnf file as in this link: http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile — Tasos Papastylianou, Dec 08 '16 at 19:58
as for setting the option for the julia client, the closest I got to was `MySQL.mysql_options(conn, Dict(MySQL.MYSQL_OPT_LOCAL_INFILE=>1) );` which threw an internal mysql error, but seems pretty close; might work if you configure your mysqld properly — Tasos Papastylianou, Dec 08 '16 at 19:59
@Tasos Papastylianou ,Before I post this question, my my.cnf file was already configured to allow local-infile, I think this configure makes the command work when I run it directly on the MySQL console. Your following answer solved my problem. Thank you very much!! — Nina, Dec 08 '16 at 21:08
Yes, I think the entry under [mysql] allows your mysql client to use this functionality, but [mysqld] also needs to have it, so that the server is configured with it as well. The julia client is independent, so this option had to be passed to the connect command independently during construction of the MySQLHandle object. — Tasos Papastylianou, Dec 08 '16 at 21:39

score 1 · Answer 1 · answered Dec 08 '16 at 19:14

Reposting this as an answer instead of a comment because the particular code snippet breaks in comments.

If you're able to use underlying os facilities, and assuming you have a mysql client installed and what you're trying to do is populate a database from a file, as a workaround you could pipe your file's contents straight into your database:

run(pipeline(`mysql -hhost -uusername -ppassword db_name`; stdin="data.sql"))

(replace host, username, password, db_name, and filename as appropriate)

_{PS. I'm not too familiar with the "INFILE" option in sql. If the input is not an .sql file but a .csv file, you could process that file in julia to generate appropriate sql statements for manual execution instead}

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

I think I have it:

import MySQL;

conn = MySQL.mysql_connect(host, username, password, database; opts = Dict(MySQL.MYSQL_OPT_LOCAL_INFILE=>1));

command = string("LOAD DATA LOCAL INFILE 'infile' INTO TABLE temp_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';")

MySQL.mysql_execute(conn, command)

Please confirm. :)

_{PS: as mentioned in the comment above, you may have to enable local-infile in your mysql-server configuration first, as mentioned here.}

edited May 23 '17 at 12:30

Community

1
1

answered Dec 08 '16 at 20:07

Tasos Papastylianou

21,371
2
28
57

This works! Thanks a lot!!! How did you figure out this sytax? Is this in any document of Julia? – Nina Dec 08 '16 at 21:03
Well, the documentation for `MySQL.mysql_connect` implies an optional `opts` keyword argument, and the documentation for `MySQL.mysql_options` implied this is in the form of a dictionary. But it took me a while and lots of lucky / educated guesses to get there; (e.g. I had to figure out there's a MySQL.MYSQL_OPT_LOCAL_INFILE constant exported by the module -- some grepping was involved to figure this out :p ). I will agree the documentation leaves a lot to be desired :p – Tasos Papastylianou Dec 08 '16 at 21:30

MySQL LOAD DATA LOCAL INFILE Julia

2 Answers2