1

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.

1

It looks like MySQL JDBC driver issue.

Switch to MySQL JDBC driver 5.1.47 provided by DataGrip to resolve the issue.

To change JDBC Driver version:

  • go to data source
  • click to Mysql and select Go to driver
  • change driver version to 5.1.47

Change JDBC Driver version

Load data local infile result:

Load infile

| improve this answer | |
  • While this technically works, I ideally would like to still have access to features offered in MySQL-8 such as CTE's and Analytic Functions. – Vivek Jha May 9 at 23:07
1

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

| improve this answer | |
  • Thank you, Vivek, this helped a lot. – 2ps Oct 17 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.