📋

Description

This error indicates that the `LOAD DATA LOCAL INFILE` command cannot be executed because the capability is explicitly disabled. This is typically a security measure to prevent unauthorized file access from the client machine to the server.

💬

Error Message

The used command is not allowed because the MariaDB server or client has disabled the local infile capability

🔍

Known Causes

3 known causes

⚠️

Server Configuration Disabled

The MariaDB server's `local_infile` system variable is set to `OFF`, preventing any client from using local infile operations.

⚠️

Client Configuration Disabled

The client application or command-line tool explicitly disables the `local_infile` capability, overriding server settings or adhering to its own defaults.

⚠️

Default Security Policy

Many MariaDB distributions and environments disable `local_infile` by default due to security concerns regarding potential data exfiltration or unauthorized file access.

🛠️

Solutions

3 solutions available

1. Enable LOCAL INFILE Globally on the Server medium

Modify the MariaDB configuration to permanently allow LOCAL INFILE for all connections.

1

Locate your MariaDB configuration file. This is typically `my.cnf` or `mariadb.conf.d/50-server.cnf` on Linux systems. For Windows, it might be `my.ini` in the MariaDB installation directory.

2

Open the configuration file with a text editor.

3

Under the `[mysqld]` section, add or modify the following lines to ensure `local-infile=1` is present and not commented out. If `local-infile` is already present but set to `0`, change it to `1`.

[mysqld]
local-infile=1

4

Save the configuration file.

5

Restart the MariaDB service for the changes to take effect.

sudo systemctl restart mariadb

6

Verify the setting by connecting to MariaDB and running the following SQL command. The output should be 'ON'.

SHOW GLOBAL VARIABLES LIKE 'local_infile';

2. Enable LOCAL INFILE for a Specific Connection easy

Allow LOCAL INFILE for a single client connection without changing server configuration.

1

When connecting to MariaDB from a client application or command-line tool, use the appropriate option to enable local infile. The exact syntax depends on the client.

2

For the `mysql` command-line client, use the `--local-infile=1` option:

mysql -u your_user -p --local-infile=1 your_database

3

If you are using a programming language (e.g., Python with `mysql.connector`), check the connection parameters for an option like `allow_local_infile` or `local_infile`.

import mysql.connector

config = {
    "user": "your_user",
    "password": "your_password",
    "host": "your_host",
    "database": "your_database",
    "allow_local_infile": True
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

4

For other clients or tools, consult their documentation for the specific option to enable local infile.

3. Grant LOCAL INFILE Privilege to a User medium

Provide specific users with the ability to use LOCAL INFILE.

1

Connect to your MariaDB server as a user with sufficient privileges (e.g., `root`).

mysql -u root -p

2

Execute the following SQL statement to grant the `FILE` privilege to the specific user. This privilege implicitly allows the use of `LOCAL INFILE`.

GRANT FILE ON *.* TO 'your_user'@'your_host';
FLUSH PRIVILEGES;

3

Replace `'your_user'` with the username and `'your_host'` with the host from which the user connects (e.g., `'%'` for any host).

4

If the user already exists, you can also use `ALTER USER` to grant the privilege:

ALTER USER 'your_user'@'your_host' WITH FILE_PRIV;
FLUSH PRIVILEGES;

5

After granting the privilege, ensure that the server-side `local-infile` setting is also enabled (as per Solution 1) or that the client connection explicitly enables it (as per Solution 2). The `FILE` privilege alone is not sufficient if the server or client has disabled the capability.

🔗

Related Errors

5 related errors

MariaDB Error 4166: Local Infile Disabled