docker-springboot2项目通过mybatis连接mysql使用load data找不到文件的解决方法

关于springboot2项目打war包放入docker后，如何与mysql容器通信的问题，参见：docker-配置mysql+外置数据+连接另一个容器（ip法和network法）

问题描述

我希望做一个批量的数据导入功能，主要利用mysql中的load data语句。在win10下，进行Junit Test时，任意路径都可以，测试代码为：

void testTableDao() {
	String path = "C:\\Users\\Dell\\Desktop\\test.chr2acc.txt";
	File file = new File(path);
	if (file.exists()) {
		System.out.println("ok");
	}
	path = path.replace('\\', '/');
	try {
		this.tableDao.loadChrToAccTable(path); // dao层导入函数
	} catch (Exception e) {
		System.out.println(e.toString());
	}
}

这样不会报错，可以成功导入数据，说明我的dao层写得没问题。

打好war包后，一开始我将数据文件 test.chr2acc.txt 放在war包所在的容器，能够访问的位置，但是会报错：

报错1：The MySQL server is running with the --secure-file-priv …

完整报错如下：

### Error querying database.  Cause: java.sql.SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
### The error may exist in com/example/demo/mapper/TableDao.xml
### The error may involve com.example.demo.mapper.TableDao.loadChrToAccTable-Inline
### The error occurred while setting parameters
### SQL: load data infile ?    into table chr_to_acc    fields terminated by "\t"    lines terminated by "\n" (chr,acc,taxon)
### Cause: java.sql.SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
; uncategorized SQLException; SQL state [HY000]; error code [1290]; The MySQL server is running with the --secure-file-priv option so it cannot execute this statement; nested exception is java.sql.SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

报错2：OS errno 2 - No such file or directory

完整报错如下：

### Error querying database.  Cause: java.sql.SQLException: Can't get stat of '/mnt/share/test.chr2acc.txt' (OS errno 2 - No such file or directory)
### The error may exist in com/example/demo/mapper/TableDao.xml
### The error may involve com.example.demo.mapper.TableDao.loadChrToAccTable-Inline
### The error occurred while setting parameters
### SQL: load data infile ?    into table chr_to_acc    fields terminated by "\t"    lines terminated by "\n" (chr,acc,taxon)
### Cause: java.sql.SQLException: Can't get stat of '/mnt/share/test.chr2acc.txt' (OS errno 2 - No such file or directory)
; uncategorized SQLException; SQL state [HY000]; error code [13]; Can't get stat of '/mnt/share/test.chr2acc.txt' (OS errno 2 - No such file or directory); nested exception is java.sql.SQLException: Can't get stat of '/mnt/share/test.chr2acc.txt' (OS errno 2 - No such file or directory)

解决

报错1

针对这个问题，我查看了本地和docker的mysql版本，本地是8.0.17，docker是8.0.24。二者关于这个的设置，都是：

mysql> show global variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         |       |
+--------------------------+-------+
2 rows in set, 1 warning (0.21 sec)

经过查询，我发现需要在my.cnf中加入一句：

local-infile=1

然后重新创建容器。

报错2

这个问题其实比较乌龙，因为war包所在的容器跟mysql所在的容器是各自独立的，前者可以访问的路径，后者肯定是访问不到了。那么一种解决办法就是在my.cnf中设置secure_file_priv，然后把数据文件放到那个路径下面。总之一定要把数据文件放到mysql容器可以访问的位置。

dao层写法

TableDao.java

void loadChrToAccTable(String path);

TableDao.xml

<select id="loadChrToAccTable" parameterType="java.lang.String">
	load data infile #{path} 
	into table chr_to_acc 
	fields terminated by "\t" 
	lines terminated by "\n" (自定义的列名们)
</select>