MySQL load data: This command is not supported in the prepared statement protocol yet

user_3305546 0
Viewed 10436

I'm trying to write a MySQL script to import data into a table for my Linux server. Here is the script named update.sql:

SET @query = CONCAT("LOAD DATA LOCAL INFILE '", @spaceName, "' INTO TABLE tmp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"); 
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And also, I write a bash script named main.sh:

mysql -h "localhost" -u "root" "-pmypassword" "mydb" -e "set @spaceName=\"$1\";source update.sql;"

Then I execute ./main.sh France.list. The France.list is the data file that I'm trying to import into my database.

However I get an error:

ERROR 1295 (HY000) at line 2 in file: 'update.sql': This command is not supported in the prepared statement protocol yet

I've found this question:
MySQL - Load Data Infile with variable path

So, does it mean that there is no way to pass arguments to LOAD DATA query?

parameter-passing import linux mysql bash
2 Answers

If you are using Java 8+, Mysql 8.0+ and Connector/J library 8.0.18 then might be you will face this issue like "command is not supported by this version" or something or similar to that.

To solve the issue, you have to add the property which tells preparedStatement to all load local infile. YOu have to add configure it, I configure it in connection url and resolve the issue.

jdbc:mysql://localhost:3306/tempDB?allowLoadLocalInfile=true

Few changes I have also made in my.cnf file like:

  1. local_infile = 1
user_39486970
Related