DataGrip – Issue with Load Data Local Infile in MySQL 8.0

datagripmysql-8.0

I am having trouble using the LOAD DATA LOCAL INFILE statement in Datagrip. Suppose I have source_data.csv such as:

rating,statement
-2,"I'm hungry!"
5,"I'm satisfied."
1,"Ok, but could be better."

After running the following code block in DataGrip:

# loading_sample_table.sql

USE sample_db;

DROP TABLE IF EXISTS sample_table;
CREATE TABLE sample_table (rating int, statement varchar(50), id serial);

LOAD DATA
    LOCAL INFILE 'absolute/path/to/my/source_data.csv'
    INTO TABLE sample_table
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY  '\''
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (rating, statement);

I receive the following error:

[42000][1148] The used command is not allowed with this MySQL version

Running SHOW VARIABLES LIKE "%version%"; in either MySQL shell or DataGrip produces

+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.18                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.18                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | macos10.14                    |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+

Then, if I try the same same command above but just drop LOCAL from the code block, I receive the following error:

[HY000][13] Can't get stat of 'absolute/path/to/my/source_data.csv' (OS errno 13 - Permission denied)

Also, I checked SHOW GLOBAL VARIABLES LIKE 'local_infile'; and can see:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

However, if from my terminal I run:

mysql --local-infile=1 -uroot -p < 'absolute/path/to/my/loading_sample_table.sql'

After entering my root password, the file runs successfully. Ideally, I would like to do everything in DataGrip. Unsure on how to debug this issue.

Best Answer

I was able to add the following parameter to the server-side connection string and it worked without having to downgrade the driver version:

jdbc:mysql://localhost:3306/?allowLoadLocalInfile=true