OpenEMR External Data Load Query Error

LOAD DATA LOCAL INFILE

When trying to import ICD10 or RXNORM data files in OpenEMR, you may encounter an error that looks like this.

OpenEMR external data loads are archives which contain data files (delimited text files) that can be loaded into the OpenEMR database. These files are loaded using mysql’s LOAD DATA routine. This error is informing you that either MySQL, or your PHP mysqli extension is not configured to load data from an external local file stored on your disk.

Text of Error

ERROR: query failed: LOAD DATA LOCAL INFILE ‘/tmp/ICD10/icd10cm_order_2019.txt’ INTO TABLE icd10_dx_order_code FIELDS TERMINATED BY ” (@var) SET revision = 0, dx_code = trim(Substring(@var, 7, 7)),valid_for_coding = trim(Substring(@var, 15, 1)),short_desc = trim(Substring(@var, 17, 60)),long_desc = trim(Substring(@var, 78, 300))Error: LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile/var/www/openemr/library/standard_tables_capture.inc at 551:sqlStatement
/opt/www/openemr/interface/code_systems/standard_tables_manage.php at 98:icd_import(ICD10)

How to Fix

These steps detail how to fix this error on a Linux system. The fix requires access to your server’s mysql command prompt and configuration files. Accessing the database as root user, and editing the files will require root access:

sudo su

1.) Check MySQL

The first thing to check is that MySQL is that your MySQL database allows loading from a local infile. To do this, open your mysql command prompt. Don’t know how to do this? Ask for support.

mysql -u root

Once in the mysql command prompt, type this command:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';

You will see an output that looks like this:

 +---------------+-------+

 | Variable_name | Value |

 +---------------+-------+

 | local_infile  | ON    |

 +---------------+-------+

 1 row in set (0.00 sec)

If this value is “ON” then you know the issue is not MySQL. If the value is “OFF” then you need to update the value to turn it on:

mysql> SET GLOBAL local_infile = 'ON';

2.) Check PHP

The next thing is to check your php configuration. This requires opening your php.ini files for editing. This can be risky, as these files affect the behavior of your application. It is wise to make a backup of your php.ini files before editing so you can revert if you need to. If you are not comfortable with this, please ask for support.

vim /etc/php/7.2/apache2/php.ini 

Once you have the file open for editing, find the following lines, and remove the semicolon from the line “mysqli.allow_local_infile = On” like this:

; Allow accessing, from PHP's perspective, local files with LOAD DATA statements

; http://php.net/mysqli.allow_local_infile

mysqli.allow_local_infile = On

Save your changes and exit your text editor. Now you must restart Apache web server for your changes to take affect.

service apache2 restart

Once your server has restarted, you should be able to successfully load your data. If it still doesn’t work, feel free to ask for support.