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