[MariaDB:Load-Local-Infile] The used command is not allowed with this MariaDB version

问题描述

使用jdbc执行load data local infile ‘sql.csv’ into table test fields terminated by ‘,’;时报错:

The used command is not allowed with this MariaDB version.

环境参数

mariadb:10.4.10
mysql-connector-java:8.0.20

官方文档说明及解决办法

查看了官方文档关于load-data-local-infile的说明：

The LOAD DATA LOCAL INFILE statement can be disabled on the server by 
setting the local_infile system variable to 0.

因此，首先需检查mariadb中的local_file参数的值。在数据库中执行语句：

show global variables like ‘%local%’;

结果如下：

MariaDB [mysql]> show global variables like '%local%';'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.001 sec)

处于开启的状态，但为什么执行报错？
查看文档的后续说明：

If the LOAD DATA LOCAL INFILE statement is disabled by 
either the server or the client and if the user attempts to execute it, 
then the server will cause the statement to fail with the following 
error message:
The used command is not allowed with this MariaDB version.

Note that it is not entirely accurate to say that the MariaDB version 
does not support the command. It would be more accurate to say that 
the MariaDB configuration does not support the command. 
See MDEV-20500 for more information.
From MariaDB 10.5.2, the error message is more accurate:
The used command is not allowed because the MariaDB server or client 
  has disabled the local infile capability

说明不仅需在服务端将local_infile参数设置为开启，在客户端也应设置相应的参数。
关于mysql-connector-java的连接参数设置，参见：jdbc-mysql连接参数配置。
其中，关于local_infile的设置，参见：
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html
中的allowLoadLocalInfile一项，即，需在url中添加allowLoadLocalInfile=true。
示例：

jdbc:mysql://127.0.0.1:3306/test?
useUnicode=true&characterEncoding=utf8&allowLoadLocalInfile=true