Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Mysql 5.7: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement: LOAD DATA INFILE #9528

Closed
mattab opened this issue Jan 15, 2016 · 10 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Milestone

Comments

@mattab
Copy link
Member

mattab commented Jan 15, 2016

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 #9419 (comment)

@theyosh
Copy link
Contributor

theyosh commented Jan 19, 2016

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
Copy link
Member Author

mattab commented Jan 20, 2016

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

@theyosh
Copy link
Contributor

theyosh commented Jan 20, 2016

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
Copy link
Contributor

theyosh commented Jan 20, 2016

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
Copy link

dazall commented Jan 16, 2017

Use LOAD DATA local INFILE ...

@yht
Copy link

yht commented Feb 1, 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
Copy link

chenzuoli commented Apr 28, 2018

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

@marcobeierer
Copy link

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
Copy link

chenzuoli commented Aug 24, 2018 via email

@RaviFefar
Copy link

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

$ SELECT @@GLOBAL.secure_file_priv;

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Development

No branches or pull requests

7 participants