Load data infile


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


#21

From the CentOS docs.

my.cnf

set-variable=local-infile=1

#22

Unfortunately, that didn’t work either.
I forgot to mention that the version of mysql is 5.1
I did a lot of research last two days, posted in mysql forums ( with no answer so far ), but couldn’t find a solution.


#23

Are you on your own server or is this a host of some kind?


#24

Yes. A dedicated Centos Server.


#25

But through a hosting provider? Is it possible they could be doing something to block/disable that feature in the DB? Just trying to think of other things at this point. And you are sure SELinux is not preventing this? Double check your audit.log that it is nothing to do with that.


#26

Yes, I’m through a hosting provider.
Couldn’t find anything in audit.log, checked it many times.
If there was anything disabled by the provider, what whould that be?
Mysql shows that local-infile is on and folder permissions are ok.
What else should that be?
And I’ve checked all the logs I know to find something.


#27

Have you tried manually running load data infile to a test DB or something similar? Verify that it is working outside of Piwik or if it is everything.

Have you tried the settings in both [mysql] and [mysqld] in you my.cnf file?

I would first see if its just Piwik that is having the issue or if its your mysql install. Another thing is if you have SELinux actually running on your box try adding the path to the tmp file then making sure to run restorecon after.

But to me if it says its on, you properly gave the user of the DB MySQL file permissions and the actual folders have the proper permissions, no errors any place but the one you mentioned which is a missing perm issue it has to be something blocking it or something along those lines. Also if you have not talked to your hosting providers support I would ask them, just to confirm.

Also keep in Mind 5.1 is OLD!


(Markus) #28

I have the same problem, but in my case the test file get created, but it seems that piwik doesn’t recognize it.


#29

By testing the LOAD DATA INFILE command I got it working when loading the file from /tmp but I got the error when loading the same file from piwik’s assets folder.
By using the sestatus command I got


#30

After this

i found out that the mysql user doesn’t have access to the piwik’s folder even if I grand 777 permissions to it. The folder is owned by apache user. So I add mysql user to apache group but again no luck. I guess it will work if I change the owner to mysql user but then there will be problems with piwik’s installation to access tmp folder. Any suggestions?


#31

I finally fixed it. I just add mysql user to psaserv group and after mysql restart worked.
I know that this might be a security risk but i can’t find any other way to make it work


#32

if you are using ubuntu:

vi /etc/apparmor.d/usr.sbin.mysqld

add followings or what is shown in the check
/path/to/piwik/misc/assets/* rw,
/path/to/piwik/tmp/assets/* rw,

/etc/init.d/apparmor restart


#33

@kokdemir, thanks for the update here. What did it fix? The first, or the second attempt?

The first one is using LOAD DATA INFILE, but the second one uses LOAD DATA LOCAL INFILE …

I got the second one working. Here’s how to got there in German: 301 Moved Permanently and in English: 301 Moved Permanently

Will try to get the first one working, because I want to use the open_basedir setting for PHP :wink:


#34

@SimonSimCity, I actually did 2 things to overcome it.
1 . in mysql command line (this probably overcome LOAD DATA INFILE problem)
mysql> grant file on . to piwik@localhost
2. the config change mentioned above. (I think this solved LOAD DATA LOCAL INFILE problem)

I referred to this link Differences between DATA INFILE and LOAD DATA LOCAL INFILE ? - MySQL Database


#35

You have to grant the user mysql executable-rights to all the parent-folders of the piwik installation and down to tmp/assets. Read or write doesn’t help if it doesn’t have executable. In fact, it does not need read or write access on any of these folders :wink:

This fixed it for me. I had the same issue as @johniem, that the content of the folder could not be listed (what in addition needs read-access to tmp/assets for mysql, but that’s not needed to execute the SQL command).


(Matthieu Aubry) #36

Thanks for the discussion and tips. I added them in a new FAQ: How do I make LOAD DATA INFILE work on my server?

If you can think of improvement to this FAQ (or others) you can use the feedback form at end of page (or post here)


#37

A little up.

How are you supposed to do that securely when the user / group is set everywhere to www-data:www-data ? And by “parent-folders”, it means the parent-folder of the piwik folder ?
I’m far for expert, and i don’t see how i can currently set permissions for mysql only since chmod is only able to set permissions for the current user / current group / all the others users / all users.

Everything else is properly set (local_infile=1 in show variables), file permissions are granted to piwik.
Errors are the same than anyone else :

Try #1: LOAD DATA INFILE : SQLSTATE[HY000]: General error: 13 Can’t get stat of ‘/home/…/piwik/tmp/assets/…’ (Errcode: 13),
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]

MySQL version : 5.5.37

Edit : i’ve created a group “piwik” with www-data and mysql just for piwik folder and all subfolders / files. It didn’t work. I added “adapter = MYSQLI” in the config file, it works. I set back the group for piwik folder and all subfolders / files to www-data, it still works… Mindfuck.


(Andrew) #38

Matthieu, the FAQs and most of the replies here appear for local MySQL instance. I have a remote DB instance. RackSpace Cloud DB running MySQL 5.6.x ; server is PHP 5.5.x ; running the standard install as per the fast install docs.

and I’m getting:

Try #1: LOAD DATA INFILE : SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user ‘omitted’@’%’ (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]

The only other Flag on the System Check is: set_time_limit [more than likely I have that disabled in my php.ini] Are the two related? Any assistance for remote DB’s? Thanks!


(pedro smith) #39

I was facing this same issue but don’t get any help from anywhere.