Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Better handling for "LOAD DATA INFILE" #13134

Open
aureq opened this issue Jul 4, 2018 · 3 comments
Open

Better handling for "LOAD DATA INFILE" #13134

aureq opened this issue Jul 4, 2018 · 3 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Milestone
For Prioritization

Comments

@aureq
Copy link

aureq commented Jul 4, 2018

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
Copy link
Contributor

  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
Copy link
Member

tsteur commented Jul 4, 2018

Should definitely take advantage of multi_server_environment config here.

@GreenReaper
Copy link

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

@Findus23 Findus23 added the Bug For errors / faults / flaws / inconsistencies etc. label May 24, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Milestone
For Prioritization
Development

No branches or pull requests
6 participants
@tsteur @GreenReaper @aureq @Findus23 @fdellwing @innocraft-automation