mariadb - How do I set a LOAD DATA working directory in DataGrip?

Question

I have a task in school to load some CSV data into my MariaDB instance. The import is as follows:

LOAD DATA LOCAL INFILE 'products.csv'
INTO TABLE products
CHARSET utf8
FIELDS
    TERMINATED BY ','
    ENCLOSED BY '"'
LINES
    TERMINATED BY '
'
IGNORE 1 LINES
(product_id, name ...)
;

Both SQL and CSV files are in the same directory in the Files panel.

The (quite logical) error I get is:

[2021-01-22 12:34:41] [22000][-1] (conn=184) Could not send file : products.csv (No such file or directory)

I know DataGrip has got a "Import Data from File" tool. But there must be a way to set the working directory when using LOAD DATA right?

question from:https://stackoverflow.com/questions/65844554/how-do-i-set-a-load-data-working-directory-in-datagrip

Answer 1

There is no option to set a default directory for LOAD DATA LOCAL INFILE.

After sending LOAD DATA LOCAL INFILE statement to the server, the server parses the statement extracts the filename and requests content of specified filename from the client.

If the client is not able to open the file, an error will be returned.

So if you don't specify a path together with file name, make sure that the file is in your current working directory (which might differ from the directory where your application was started).

Best practice is always to specify the full path.

MySQL 8 for example offers to set the option MYSQL_OPT_LOAD_DATA_LOCAL_DIR,
which affects the client-side LOCAL capability for LOAD DATA operations. It specifies the directory in which files named in LOAD DATA LOCAL statements must be located.