LOAD DATA support

[chulkilee]

I tested just LOAD DATA - and it failed as myxql is using prepared statement protocol.

{:ok, pid} = MyXQL.start_link(database: db_name)

MyXQL.query!(pid,
"""
LOAD DATA INFILE 'foo.csv' INTO TABLE posts
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\\r\\n'
"""
)

It fails with following error:

** (MyXQL.Error) (1295) (ER_UNSUPPORTED_PS) This command is not supported in the prepared statement protocol yet
    (myxql 0.4.2) lib/myxql.ex:260: MyXQL.query!/4

I tried to pass prepare: :unamed on start_link or query, but it didn't work, since apparently it still uses prepared statment protocol.

• myxql 0.4.2
• mysql: Server version: 5.7.31 Homebrew
• elixir: Elixir 1.10.4 (compiled with Erlang/OTP 23)
• erlang: Erlang/OTP 23 [erts-11.0.4] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe]

---

Also there is LOAD DATA LOCAL which requires more work by client side - actually I'm not sure whether that's feasible to implement it though.

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

So.. this requires lots of work on client side.

mariaex does not support it - xerions/mariaex#34

---

Feel free to close it as "won't fix" for now - just trying to make it for the record :)

[josevalim]

What happens if you pass the query_type: :text option?

MyXQL.query(pid, query, [], query_type: :text)

[chulkilee]

MyXQL.query(pid, query, [], query_type: :text)
{:error,
 %MyXQL.Error{
   connection_id: 104,
   message: "(1290) (ER_OPTION_PREVENTS_STATEMENT) The MySQL server is running with the --secure-file-priv option so it cannot execute this statement",
   mysql: %{code: 1290, name: :ER_OPTION_PREVENTS_STATEMENT},
   statement: "LOAD DATA INFILE 'foo.csv' INTO TABLE posts\n  FIELDS TERMINATED BY ',' ENCLOSED BY '\"'\n  LINES TERMINATED BY '\\r\\n'\n"
 }}

Yeah! That works. I'll create a MR to add that info to typespec...

Closing it as it works. LOAD DATA LOCAL could be separate github issue for tracking..