Better handling for "LOAD DATA INFILE"

[aureq]

Hi Matomo team,

So a few things...

1. The Matomo database user already has the FILE privilege
2. The Matomo server and the DB server are on separate hardware

I'm having the following error in the system checks page:

Using LOAD DATA INFILE will greatly speed Matomo's archiving process up. To make it available to Matomo, try updating your PHP & MySQL software and make sure your database user has the FILE privilege.
If your Matomo server tracks high traffic websites (eg. > 100,000 pages per month), we recommend to try fix this problem.
Error: LOAD DATA INFILE failed... Error was:
Try #1: LOAD DATA INFILE : SQLSTATE[HY000]: General error: 13 Can't get stat of '/usr/share/matomo/tmp/assets/piwik_option-66adb5e071910f7e2ebfe96f50fdcc2a.csv' (Errcode: 2 - No such file or directory),
Try #2: LOAD DATA LOCAL INFILE : SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version[42000]

So,

1. is there a way to avoid this warning/error being displayed on the system check page when Matomo and the DB aren't hosted on the same server?

2. for security reasons, is there a way to specify a separate folder for the CSV file? By this, I mean it would be great to place the CSV file in a separate location, outside of Matomo existing folder. That would play nicely with secure_file_priv (link)

Thank you.

[fdellwing]

1. The message should not be shown if that is the case. You could set multi_server_environment=1, but that will also break other things (https://matomo.org/faq/new-to-piwik/faq_134/). I don't know why they decided to reuse this setting for disabling that function (Enable LOAD DATA LOCAL INFILE for PHP >= 5.6.17 when mysqlnd is used. #12710 (comment)). Maybe @mattab can say something about this?
2. Try to use path from MySQL "secure_file_priv" system variable for batch inserting via load infile #9529

[tsteur]

Should definitely take advantage of multi_server_environment config here.

[GreenReaper]

I got really confused by the default production php.ini because it has:

; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; http://php.net/mysqli.allow_local_infile
;mysqli.allow_local_infile = On

To me, this implies that On is the default. But it isn't, as of PHP 7.2.16 and 7.3.3. You have to remove the semicolon, so it matches with all the others in the [mysqli] section.

Just thought I'd mention it here in case it confused anyone else looking for help with this issue. 😅