MySQL syntax error when query run by app on server but not when run in workbench

Question

I have a MySQL query like this:

truncate table A;load data local infile '~/some.csv' into table A fields terminated by ',' OPTIONALLY ENCLOSED BY '\"' (COLUMN1,COLUMN2,COLUMN3)

It runs perfectly when I run it in workbench but when it is executed by the java app:

String qry = "truncate table A;load data local infile '~/some.csv' into table A fields terminated by ',' OPTIONALLY ENCLOSED BY '\"' (COLUMN1,COLUMN2,COLUMN3)";
Connection con = DriverManager.getConnection(HOST, USER, PASSWORD);
java.sql.Statement stmt = con.createStatement();
stmt.execute(qry);

I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'load data local infile '~/some.csv' into table A' at line 1

Version on Server is: MySQL 5.6.27

On my local machine it is: 5.7.19, for Linux (x86_64)

PS: The query I run in workbench is the one I logged in the java app.

I will try this: https://stackoverflow.com/questions/29340015/mysql-query-gives-syntax-error-in-java-but-works-fine-in-mysql-workbench — IwantToKnow, Sep 08 '17 at 11:01

Tim Biegeleisen · Accepted Answer · 2017-09-08T11:37:02.040

As was mentioned in a now deleted comment, your problem is probably trying to execute more than one MySQL statement while MySQL is in a mode to disallow more than a single statement. One workaround is to change your connection URL to something like this:

String dbUrl = "jdbc:mysql://localhost:3306/yourDB?allowMultiQueries=true";

Here is a code snippet showing how you would do this:

public static void main (String[] args) {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String HOST = "jdbc:mysql://localhost:3306/yourDB?allowMultiQueries=true";
        String qry = "truncate table A;load data local infile '~/some.csv' into table A fields terminated by ',' OPTIONALLY ENCLOSED BY '\"' (COLUMN1,COLUMN2,COLUMN3)";
        Connection con = DriverManager.getConnection(HOST, USER, PASSWORD);
        java.sql.Statement stmt = con.createStatement();
        stmt.execute(qry);
}
catch (Exception e) {
    System.out.println("failed");
    e.printStackTrace(System.out);
}

This might be the quickest fix. If not, then you could look into using batches, or just to execute more than one statement for the truncation and loading of data. Based on the comment below by @GaborSch executing separate statements may be the safer option here.

Futher reading:

Multiple queries executed in java in single statement

Thx, I will try this right now. And sorry to the guy, who commented and removed it! :-( — IwantToKnow, Sep 08 '17 at 11:13
I think his name is @fancyPants , loyal Stack Overflow user he certainly is! — Tim Biegeleisen, Sep 08 '17 at 11:15
Athough this solves your problem, I strongly recommend not to use the `allowMultiQueries=true` feature; There is a reason why it is `false` by default: it may impose a security threat to your system. It is much better to call two SQL statements. — gaborsch, Sep 08 '17 at 11:24

MySQL syntax error when query run by app on server but not when run in workbench

1 Answers1