Search code examples
mysqlcsvloadmybatisdatagrip

Uploading with load local data

I'm trying to upload a csv file to a local database using MySQL with mybatis. I created a mysql container to act as the server and use datagrip to access the data. I want to use:

<insert id="batchInsertCsv" parameterType="string">
    LOAD DATA LOCAL INFILE #{filePath}
        INTO TABLE DATA
        COLUMNS TERMINATED BY ','
        IGNORE 1 LINES
</insert>

to upload the data but my current error is: Loading local data is disabled; this must be enabled on both the client and server sides. Using:

SET GLOBAL local_infile = true;

I have set local_infile on the server side and checked it with:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Can anyone offer help or advice? Thanks.

Solution

  • LOAD DATA LOCAL INFILE must be allowed both in server and client and you have already done the server part.

    As MyBatis is built on top of JDBC API, you should add a connection property to the JDBC URL to enable the feature in client. e.g.

    jdbc:mysql://127.0.0.1:3309/test?allowLoadLocalInfile=true

    Note that there are several properties related to this feature.

    • allowLoadLocalInfile
    • allowLoadLocalInfileInPath
    • allowUrlInLocalInfile

    The first one is a simple TRUE/FALSE switch.
    The latter two allow you to set some restriction on the file being uploaded.

    Please see the documentation for the details.
    https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html