Load data infile

Support & Bugs
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!

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

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.

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!

39

I was facing this same issue but donā€™t get any help from anywhere.

40

I have this set up and working on an old set of servers (even remotely) but now that Iā€™m trying to rebuild these servers with configuration management, and I seem to have met all the requirements Iā€™m still seeing the error message.

Iā€™ve checked everything between the servers that I can think of (and everything on the checklist) but this hasnā€™t helped me to resolve this but I need to fix this because we see millions of requests per month so this will be important for the performance of the eventual system.