MySQL: server or client has disabled the local infile capability

If you use LOAD DATA LOCAL INFILE to quickly load data into a table with MySQL, you may come across the following error on a fresh, unprepared MySQL instance:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: The used command is not allowed because the MariaDB server or client has disabled the local infile capability

This is, as the message suggests, caused by either:

• The server doesn’t allow LOCAL INFILE to be used
• The client doesn’t allow LOCAL INFILE to be used

Now, I’ve experienced this with the server not allowing it plenty of times, but after updating MySQL .NET to 8.0.30, the client was also not allowing it!

On the server, we enable local_infile by using the SET GLOBAL command:

root@server:~# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 119
Server version: 10.6.9-MariaDB-1:10.6.9+maria~ubu2004 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET GLOBAL local_infile = true;
Query OK, 0 rows affected (0.000 sec)

You can verify it’s enabled by using the SHOW GLOBAL VARIABLES command:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.000 sec)

A word of warning: I don’t believe this is persistent. If your MySQL instance or server restarts, it may toggle back to off. Some suggestions I found were to create a /etc/mysql/conf.d/enable-local-infile.cnf file with the following:

[mysqld]
local-infile=1

[mysql]
local-infile=1

But this didn’t seem to do anything (/etc/mysql/conf.d/* files are loaded, so that isn’t the problem). If you know of a better way, please let me know!

On the client, we use a connection string for the MySQL connection, and we now have to specify AllowLoadLocalInfile for connections where we need it:

using (MySqlConnection conn = new MySqlConnection("Server=#; Port=#; Uid=#; Pwd=#; Database=#; AllowLoadLocalInfile=true;")) {
     // your code
}