I found some issues in the FAQ, which somehow disable security features to get MySQL's LOAD DATA INFILE to work:
Check that the sql user that Piwik is using has the permission to import the files: GRANT FILE on *.* to piwik<a class='mention' href='https://github.com/localhost'>@localhost</a>
It should be
or even better
which would affect all databases (not only the piwik database).
give the process mysqld executable-access (+x) to files in path/to/piwik/tmp/assets/* and all parent folders.
Why would mysqld need to execute files in tmp/assets? Instead, read access to files should do, execution is only needed for directories.
check that the request isn’t blocked by apparmor or any other security-software when accessing this folder. If you are using Ubuntu, you may have to disable apparmor which prevents mysql from accessing files in path/to/piwik/tmp/* Edit the file /etc/apparmor.d/usr.sbin.mysqld and add the following path in the file: /path/to/piwik/tmp/assets/* rw, Then restart apparmor with sudo /etc/init.d/apparmor restart in your Mysql configuration (my.cnf) set the following options: [mysqld] local-infile and [mysql] local-infile Then restart Mysql.
The local-infile would be system-wide, not only piwik-specific. In general, this opens up mysql. Plus, disabling apparmor is not an option for many users. Plus, why is writeable access proposed for mysqld (rw)?
If the LOAD DATA INFILE is still not working, try the following: update to the latest PHP version or use the mysqli client (there are some known bugs with older PDO clients for mysql) and/or switch the client to adapter=MYSQLI in the config/config.ini.php and/or disable in your php configuration php.ini the options open_basedir and safe_mode. Restart webserver.
Switching to MYSQLI is not an option for everyone. Playing around with open_basedir and safe_mode does not have something to do with PDO/MYSQLI as the reader might assume when reading the text. Plus, open_basedir and safe_mode are important security features which should NOT be disabled just to fix some errors. Users should be taught about that. Plus: What does file read access in the context of mysql have to do with PHP's open_basedir/safe_mode settings? These settings will only affect how apache/PHP process behaves. MySQL is a separate process which will not respect these settings at all!
If you still cannot make LOAD DATA INFILE work, you can disable this feature completely. This is not recommended for medium to high traffic Piwik where this feature should be enabled. To disable LOAD DATA INFILE add to your config.ini.php file under [General] section: enable_load_data_infile=0
Yes, that's the best option for most users who would otherwise insecure their servers.
Documentation needs to be reviewed in terms of security (I just red the part I was writing about). Unexperienced users will insecure their systems otherwise.
And for the code: In general, I would suggest to not use LOAD DATA INFILE in the context of mysqld at all. For PostgreSQL there exists a set of PDO-functions allowing bulk inserts in the context of the PHP-client via file or STDIN. Check out https://bugs.php.net/bug.php?id=63656 . Eventually, there is also an option for MySQL, but I did not search for that right now. That's the way to go: No file access via the database process -> no hassle. And it's fast as hell on PostgreSQL, still allowing transactions by reusing the context of client's connection. I even found a way to reuse doctrine connections for that on PostgreSQL. If you are interested in that, read my comment on the mentioned php.net site (I feel sorry that you used MySQL...).
For users who don't use BULK INSERTs, please check out if a MySQL transaction is used around the whole dataset which is to be inserted. Plus, check out if the MySQL Multi Row syntax insert is used. Both measurements could massively improve performance.
If inserts are done via ORM (doctrine, etc.), please make sure that you use MySQL transactions (doctrine supports that) around the mass INSERTs. Within the transaction, you can flush multiple times using flush(array). Make sure you destroy objects between flushes, and make sure that only few entity objects are instantiated at any time. Having lots of objects instantiated heavily slows down doctrine performance.
Hi @thomaszbz Thanks for the suggestion to improve the documentation about this feature.
since you seem knowledgeable about the security implications, maybe you would be able to paste here your suggested updated text for this FAQ? it would help us make this change faster. cheers
How do I get LOAD DATA INFILE to work on my server?
Piwik processes huge amount of data and then stores this data in the database. For improved performance, Piwik tries to import a lot of data in the database at once using a Mysql feature called “LOAD DATA INFILE”. You can check whether your server supports this performance improvement in Administration > System Check menu. The system checks two commands: LOAD DATA INFILE and LOAD DATA LOCAL INFILE. It’s enough if either one of these work.
In order to do this, connect to your webserver with SSH. You'll need ROOT ACCESS OR ROOT PERMISSIONS.
ssh root<a class='mention' href='https://github.com/your_webserver'>@your_webserver</a>.com
First check that the sql user that Piwik is using has the permission to import the files:
mysql -u root -p GRANT FILE on your_db_name.* to piwik<a class='mention' href='https://github.com/localhost'>@localhost</a>; exit
Check the “mysqld” process can access the file created in the tmp/assets directory in the piwik-installation: give the process mysqld read-access to files in path/to/piwik/tmp/assets/* and all parent folders.
path/to/piwik/tmp/# chmod -R 755 assets/
check that the request isn’t blocked by apparmor or any other security-software when accessing this folder. If you are using Ubuntu, you may have to disable apparmor which prevents mysql from accessing files in path/to/piwik/tmp/* Edit the file /etc/apparmor.d/usr.sbin.mysqld and add the following path in the file:
vim /etc/apparmor.d/usr.sbin.mysqld # Allow piwik access tmp files path/to/piwik/tmp/assets/* rw,
sudo /etc/init.d/apparmor restart
in your Mysql configuration (my.cnf) set the following options: [mysqld] local-infile, secure-file-priv = "" and [mysql] local-infile
vim /etc/mysql/my.cnf [mysqld] local-infile=1 secure-file-priv = "" [mysql] local-infile=1
sudo service mysql restart
Now you can have that nice "Huzzah! There are no problems with your Piwik setup. Give yourself a pat on the back." message on Piwik's System Check page.
@fortinux Nice catch. It's just that ordinary web space hosters won't let anyone change an apparmor profile. Now that the documentation already suggests to do exactly that, it should be documented that this step requires root access.
I just started out using Matomo and you should really rework this mentioned section in the help.
I am sorry to say, this is not offending, but it is as insecure and wrong as it could get.
You propose to set the FILE grant for the db user and then in the same section you propose to set the directory permissions and set local-infile. This absolutely makes no sense at all, either you use LOAD DATA INFILE : Then you need to grant a FILE permission to the user and make the directory accessible somehow, this is true. But then you don't need local_infile option at all. For what?
Or you use local_infile, then you absolutely don't need the other steps, no FILE grant and no directory permissions for mysqld process as LOAD DATA LOCAL does exactly not need that because it is read by the "client" which has access anyways and is "uploaded" to the server. I put this in "" because this also works if you are just working on localhost.
But as load_infile works only as a global setting like @thomaszbz mentioned this completely opens up your whole mysql install.
Users have a lot of problems to get LOAD DATA to work properly, but this is for sure not the optimal way. Most of them give up and just enable LOCAL, many without knowing the risks.
They should use either one of it not open up everything.
@thomaszbz sadly your suggestion to grant file on db.* makes no sense because this is even not possible. FILE is a global privilege and cannot be used on db level. You give the user all access for the server. That's it. If you have to use LOAD DATA you have to choose to open up directories that might be closed otherwise for 'mysql' user or use insecure LOAD DATA LOCAL.
This is not the best way to get it, especially for users that might have their install in /home/... directories, which is the case for many that use webpanel software. Won't get deeper into it in a public thread.
But there would be really easy and far more secure ways to handle that. Really easy. Please consider that.
as you can see in a previous comment @fortinux did exact that. He opened up both LOAD DATA possibility and then on top activated LOAD DATA LOCAL which makes the first things complete obsolete. This is not good