Load data infile


(Andreas Schnederle-Wagner) #1

Hi,

just checked the new System-Check page in Piwik and it’s telling me that LOAD DATA INFILE can’t be used. Piwik DB User already got the FILE permission. Is there anything else I need to take care of to use this functionality?

Thank you
Andreas Schnederle-Wagner

EDIT: see this FAQ Troubleshooting - Analytics Platform - Matomo


(Andreas Schnederle-Wagner) #2

no comment at all? :-/


(macada) #3

Im very intereseting on that too.

Our piwik db is 13GB and We have over 15million pageviews per month.

Any help will be welcome.

Thanks in advance.


(Matthieu Aubry) #4

try updating PHP and Mysql on the server as some older versions are buggy with this feature!


(Andreas Schnederle-Wagner) #5

[quote=matt]
try updating PHP and Mysql on the server as some older versions are buggy with this feature![/quote]

we are using PHP 5.3.16 and MySQL 5.5.15

What is the minimum Version needed for this Feature? (as updating is a bit problematic as we also run a SAAS CMS on same Server which is pretty PHP Version specific …)


#6

go into my.cnf

in the following sections add this


[mysqld]
local-infile

[mysql]
local-infile

Works for me on MySQL 5.5.* on ArchLinux


(Matthieu Aubry) #7

Thanks for the tip. Does it work for someone else? if so I can add it to the FAQ!


#8

It might be a little different per distro, some need =1 and so on. But keep in mind I have yet to see a distro that just has this on in their mysql release. It is seen as a security risk using this you should be aware of what other issues it may bring, some distro’s do not even compile this in their binaries.
Distro’s like Ubuntu need to be compiled from source and pass this


cmake /mysql-5.5/ -DENABLED_LOCAL_INFILE=1

Incase anyone cant find the issues that are related to using this in MySQL you can read about it in the SQL docs MySQL :: MySQL 5.5 Reference Manual :: 6.1.6 Security Issues with LOAD DATA LOCAL


(Andreas Schnederle-Wagner) #9

my MySQL already shows

local infile ON

So that’s not the problem here (on my Server)


(Markus) #10

is there any new information for this problem ?


#11

If you are still having problems with the infile issue. Make sure the user for the DB has file permissions. running something like this should help


grant file on *.* to piwik@localhost

HTH


(Markus) #12

I set this right but the problem still exists.

markus


#13

Did some research on that because I am facing the same issue.

I wrote some additional information down here 301 Moved Permanently but still haven’t come to a solution …


#14

If the problem persists then there is something not set properly on your SQL install. You need the File permissions to use load data infile. Most will have to give those file permissions to ALL ie . … That is also why its looked at as a security risk. vwyoda’s last post is all that is needed to make it work.


#15

Has anyone found any solution to this?
My tmp folder and subfolders permissions are all 0777 and I’m still having this issue.
local-infile is on as mysql variables states.
So what else needs to be done to make this work?


#16

Using 777 is never a proper solution for anything in my opinion.

Have you tried all the listed things in this thread and the other one? What OS are you on? Have you verified you are not on an OS that does not built mysql with data infile compiled into it? Did you grant the file permission to all?


#17

Sorry for the lack of information on my post.
My server uses Centos 6.4.
Piwik database user has all file permissions. Mysql shows than data infile is on.
The piwik’s system check error message is


Error: 
Try #1: LOAD DATA INFILE : SQLSTATE[HY000]: General error: 13 Can't get stat of 'path/to/file' (Errcode: 13)

where path/to/file/ is piwik /tmp/assets directory.
I should mession that the owner for tmp directory is the ‘ftp’ user but for the folders inside it, is the ‘apache’ user.
tmp folder permissions are 777 atm.


#18

try specifically granting as so


grant file on *.* to piwik@localhost

That error means it does not have the permissions it needs. With 777 user no longer matters. Anyone can read write execute in that location. Its a MySQL permission issue you have not web server.

Edit: The key is . for file permission for the user. It cant be YourPiwikDB.* has to be .


#19

I’ve already done that when granding file permissions to the database user.
I know from mysql documentation that in order for mysql to access files with LOAD DATA INFILE command, it needs permission not only for the directory that the file exists but for parent directories too.
Piwik is installed as subdomain on my dedicated server in the path /var/www/vhosts/domain.com/analytics/.
So until the piwik’s tmp folder permissions can’t be the same. I don’t know how deep mysql needs permissions but according to LOAD DATA INFILE requirments I don’t know what else I can do to make this work.


#20

Well I dont have to put 777 (775) on my Piwik tmp folder and I have done exactly everything I have stated in this thread to get it working. So there must be something you have missed. Did you reload priveleges or restart mysql? The problem is in something with your MySQL install, perhaps hit up the CentOS forums maybe they put some special sauce layer in their package that is giving you issues.

Also make sure something like SELinux is not preventing this.