Sample Header Ad - 728x90

DataGrip Issue Load Data Local Infile

2 votes
2 answers
1892 views
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: 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] 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.
Asked by Vivek Jha (155 rep)
May 9, 2020, 12:14 AM
Last activity: May 9, 2020, 11:08 PM