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:
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.
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
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.