How LOAD_FILE() Works in MariaDB

by

In MariaDB, the LOAD_FILE() function reads a file and returns the file contents as a string.

Syntax

The syntax goes like this:

LOAD_FILE(file_name)

Where file_name is the full path name to the file.

Example

Suppose we have a file called pets.csv that contains the following text:

1,"Wag","Dog"
2,"Bark","Dog"
3,"Meow","Cat"

We can use the LOAD_FILE() function to read and return those contents:

SELECT LOAD_FILE('/Users/barney/data/pets.csv');

Result:

+---------------------------------------------+
| LOAD_FILE('/Users/barney/data/pets.csv')    |
+---------------------------------------------+
| 1,"Wag","Dog"
2,"Bark","Dog"
3,"Meow","Cat" |
+---------------------------------------------+
1 row in set (0.186 sec)

In this case, the full path to the file is /Users/barney/data/pets.csv.

Wrong File Path or Privileges

Providing a file path that doesn’t satisfy the following criteria results in null:

  • The file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege.
  • The file must be readable by all and it must be less than the size, in bytes, of the max_allowed_packet system variable.
  • If the secure_file_priv system variable is set to a non-empty directory name, the file to be loaded must be located in that directory.

Here’s how to check those two system variables:

SELECT
    @@GLOBAL.secure_file_priv,
    @@GLOBAL.max_allowed_packet;

Result:

+---------------------------+-----------------------------+
| @@GLOBAL.secure_file_priv | @@GLOBAL.max_allowed_packet |
+---------------------------+-----------------------------+
| NULL                      |                    16777216 |
+---------------------------+-----------------------------+

In my case, I don’t have any directories specified in the secure_file_priv system variable, and my max_allowed_packet variable is set at its default (which is more than enough for the file I uploaded in the previous example).

However, here’s what happens when I try to upload a file that doesn’t exist:

SELECT LOAD_FILE('pets.csv');

Result:

+-----------------------+
| LOAD_FILE('pets.csv') |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set (0.000 sec)

In this case, I forgot to specify the full path to the file.

Multi-Byte File Names

The character_set_filesystem system variable is used to control interpretation of file names that are given as literal strings. It’s used for converting file names specified as a string literal from character_set_client to character_set_filesystem before opening the file.

By default, character_set_filesystem is set to binary, so no conversion takes place.

You can check this value using the following code:

SELECT @@GLOBAL.character_set_filesystem;

Result:

+-----------------------------------+
| @@GLOBAL.character_set_filesystem |
+-----------------------------------+
| binary                            |
+-----------------------------------+

Null File Path

Providing a null file path results in null:

SELECT LOAD_FILE(null);

Result:

+-----------------+
| LOAD_FILE(null) |
+-----------------+
| NULL            |
+-----------------+
1 row in set (0.000 sec)

Missing File Path

Failing to provide a file path results in an error:

SELECT LOAD_FILE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LOAD_FILE'