Piwk, RDS and LOAD DATA INFILE


#1

Hi there,

I use piwik to track an high traffic website and the infrastructure is composed by EC2 instances and a RDS instance.
Everything works fine except an error I get in “Database abilities”

LOAD DATA INFILE
Using LOAD DATA INFILE will greatly speed Piwik's archiving process up. To make it available to Piwik, try updating your PHP & MySQL software and make sure your database user has the FILE privilege.
If your Piwik 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[28000]: Invalid authorization specification: 1045 Access denied for user 'tamcdrupal'@'%' (using password: YES)[28000],
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]

Are there some best practices to avoid this error, using Piwik and RDS?
Thanks
Vincenzo


#2

Usually LOAD DATA INFILE is disabled by your web host for security purposes.
refer to this:

http://piwik.org/faq/troubleshooting/faq_194/


(Sam) #3

Hello,

I have a quite same error:

 LOAD DATA INFILE
Using LOAD DATA INFILE will greatly speed Piwik's archiving process up. To make it available to Piwik, try updating your PHP & MySQL software and make sure your database user has the FILE privilege.
If your Piwik 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[28000]: Invalid authorization specification: 1045 Access denied for user 'espion'@'%' (using password: YES)[28000],
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]

My user “espion” has all the granted privileges to the piwikDB.

Here are my server details (Debian Jessie):

Server: Localhost via UNIX socket
Server version: 5.5.46-0+deb7u1
Protocol version: 10
User: root@localhost
MySQL charset: UTF-8 Unicode (utf8)

Web server
nginx/1.9.7
MySQL client version: 5.5.46
PHP extension: mysqli Documentation

Any kind help would be appreciated cause I actually only have menus and options available in Piwik but no stats.

Thanks,


(Matthieu Aubry) #4

Hi there

the fact that you have no data shown is different problem from LOAD DATA INFILE. LOAD DATA is optional and only useful for slightly better performance. See: Piwik doesn't track any visits and pages, and shows "There is no data for this report" in all reports. - Analytics Platform - Matomo


#5

Be wary of the mysql file_priv permission. First, it is only set for a given user if it is set “globally”, ie, to *.*. Even if you have a user, such as piwik, set up with grant all privileges to piwik_user@'%' on piwik.* ;, the file privilege will not be granted. Rather, you must run it on the entire database: grant file to piwik_user@'%' on *.*. You can test to see if the user has the privilege: select user,host,file_priv from mysql.user to see if your particular user has the ability or not.