[CONJ-961] LOAD DATA LOCAL INFILE not supported anymore Created: 2022-04-12  Updated: 2022-04-15  Resolved: 2022-04-15

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 3.0.4
Fix Version/s: 3.0.5

Type: Bug Priority: Major
Reporter: Patrick Neyer Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None
Environment:

MariaDB 10.4.18 Docker Image


 Description   

Using MariaDB Java Client version 3.0.4 (and latest Spring Data JPA/Hibernate), we get following exception:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [LOAD DATA LOCAL INFILE 'xyz.csv' INTO TABLE xyz CHARACTER SET cp850 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';]; nested exception is java.sql.SQLSyntaxErrorException: (conn=8) The used command is not allowed with this MariaDB version
 
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
 
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
 
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
 
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
 
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
 
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:558)
 
	at c.x.y.z.XYZDatabaseService.loadIntoTable(XYZDatabaseService.java:100)
 
	...
 
Caused by: java.sql.SQLSyntaxErrorException: (conn=8) The used command is not allowed with this MariaDB version
 
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:270)
 
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:358)
 
	at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:133)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:815)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:754)
 
	at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:673)
 
	at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:616)
 
	at org.mariadb.jdbc.Statement.executeInternal(Statement.java:906)
 
	at org.mariadb.jdbc.Statement.executeUpdate(Statement.java:888)
 
	at org.mariadb.jdbc.Statement.executeUpdate(Statement.java:142)
 
	at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119)
 
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
 
	at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:546)
 
	at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:543)
 
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)

Was working with MariaDB Java Client 2.7.5 (and all older 2.x versions).

 Comments   
Comment by Diego Dupin [ 2022-04-14 ]

LOAD DATA INFILE is now disabed by default.
an option allowLocalInfile permit to enable that behavior.
see : https://mariadb.com/kb/en/about-mariadb-connector-j/#essential-parameters

Still, error message is not very indicative, and will be improved in next version

Comment by Diego Dupin [ 2022-04-15 ]

Problem is a little bit different than expected :
2.7 documentation has always indicate that allowLocalInfile was disable by default, problem is that implementation has always have allowLocalInfile enable by default contrary to what documentation indicate.

3.0 rewrite is based on documentation default, resulting in this problem. There is no reason to break compatibility for this option. Best solution is to correct 3.0, permitting LOCAL INFILE by default ( and adding a Local infile validation at the same time)

Generated at Thu Feb 08 03:19:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.