@mattab opened this Issue on January 15th 2016 Member

Mysql 5.7 can return an error during archiving process: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement: LOAD DATA INFILE

See more info as provided by @oraclerob in https://github.com/piwik/piwik/issues/9419#issuecomment-171588963

@theyosh commented on January 19th 2016 Contributor

This also happens when you try to install Piwik for the first time. It will give an error during the checks.

The solution I used:
in /etc/mysql/my.cnf add below [mysqld]

secure-file-priv = ""

More info: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

This is maybe less save, but getting all the data within the right path is even more hard to fix.

@mattab commented on January 20th 2016 Member

@theyosh would you mind trying our latest 2.16.0 beta release? it should fix the issue :+1:

@theyosh commented on January 20th 2016 Contributor

Ok, I have reverted my MySQL config file, so the default value for secure-file-priv is enabled again. The installation check is now OK. No error / warning message about this MySQL setting. So looks good!

used 2.16.0.b4

Have not tested the logfile import yet. Also archiving is not yet tested.

@theyosh commented on January 20th 2016 Contributor

Problem is still there... I have the following error after installation.

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[HY000]: General error: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement,
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]
Troubleshooting: FAQ on piwik.org

so problem is still there. Not during installation, but when I look at the System check option in the interface

@dazall commented on January 16th 2017

Use LOAD DATA local INFILE ...

@yht commented on February 1st 2017

I think it's a MySQL issues, not piwik.
I found this, maybe it can help someone.
http://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql

@chenzuoli commented on April 28th 2018

i get this error on windows, how can i do?
where is my.cnf on windows?
who can help me?
very thanks

@marcobeierer commented on August 22nd 2018

I just had the same issue and setting secure-file-priv = "" works fine, but it's not the best solution from a security point of view. A short quote from the documentation: If empty, the variable has no effect. This is not a secure setting.

It would be better to set the value as narrow as possible. The following did work for me:

[mysqld]
secure-file-priv = "/path/to/matomo/tmp/assets/"
@chenzuoli commented on August 24th 2018

very thinks , it's ok!

At 2018-08-23 01:53:10, "Marco Beierer" <notifications@github.com> wrote:

I just had the same issue and setting secure-file-priv = "" works fine, but it's not the best solution from a security point of view. A short quote from the documentation: If empty, the variable has no effect. This is not a secure setting.

It would be better to set the value as narrow as possible. The following did work for me:

[mysqld]
secure-file-priv = "/path/to/matomo/tmp/assets/"


You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

@RaviFefar commented on September 16th 2020

you can store CSV file another location and MySQL store CSV file this location /var/lib/mysql-files/.

$ SELECT @<a class='mention' href='https://github.com/GLOBAL'>@GLOBAL</a>.secure_file_priv;

More info: https://devnote.in/error-1290-hy000-the-mysql-server-is-running-with-the-secure-file-priv/

This Issue was closed on January 20th 2016
Powered by GitHub Issue Mirror