Load data infile

[1step2heaven]

Hello,
My hosting say the pleskpanel has no root access but i get from piwik below error.

Database abilities

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

[minos]

Same here, but with root access… how to solve that ?

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

[dukenukem]

Getting the same error. Was working OK but checked stats today and can only get a line list for a menu and none of the links work. Can’t even see the generated tracking code

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

Anyone found a solution to this?

[halfdan (Fabian Becker)]

What MySQL Server version do you use?

[minos]

Infos showed in Phpmyadmin :

Version du serveur: 5.5.28-1
Version du protocole: 10
UTF-8 Unicode (utf8)

Apache/2.2.22 (Debian)
Version du client MySQL: 5.5.28
Extension PHP: mysqli

[dukenukem]

I found this which solved my problem. May not be the total solution but it now works. Have asked my hoster to look at increasing this time as the problem only happen after a software update.

The minifier is taking too long and your server has a max_execution limit. In your config/config.ini.php, add the following:

[Debug]
disable_merged_assets = 1

This is normally used for debugging, but it’s the only quick fix I can propose at this time.

[SimonSimCity]

@dukenukem: But this can’t be the solution to this problem, can it? We’re talking about a MySQL restriction here …

Just to provide more information about my environment:

As you can see, I use Ubuntu 12.04 and did install all packages out of their repository

PHP: 5.3.10-1ubuntu3.6
MySQL Server: mysqld Ver 5.5.31-0ubuntu0.12.04.1
MySQL Client: mysql Ver 14.14 Distrib 5.5.31
PHP Client:

• mysql: 5.5.31
• mysqli: 5.5.31
• pdo-mysql: 5.5.31

After calling the scripts, I, too, get these errors:

Try #1: LOAD DATA INFILE : SQLSTATE[HY000]: General error: 29 File ‘/srv/http/localhost/www/piwik/tmp/assets/option-edce5cdcb5520b1f5314561c44e52132.csv’ not found (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]

But the interesting part is, that the file does exist in the filesystem and everyone has all kind of access to it. Read, write and execute …

Here’s some interesting notes I found during research:

@http://stackoverflow.com/questions/14203203/uploaded-tmp-file-not-found-in-query#answer-14205695

Most likely the file is there, but MySQL doesn’t allow you to read files from that location. MySQL is fairly restrictive from where it reads LOAD DATA … INFILE, with good reasons. You may need to move your file first to a path that MySQL can read from, or change the settings for your MySQL server.

@http://dev.mysql.com/doc/refman/5.1/en/load-data.html

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, “Privileges Provided by MySQL”. For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

FYI: The value for secure_file_priv is an empty string …

I also found some information about a php-bug on that … don’t know if it’s still valid … https://bugs.php.net/bug.php?id=54158

EDIT: Some people here are writing about a setting called local-infile http://forum.piwik.org/read.php?2,99711#msg-100760 but this is set to ON for me anyways …

[flippy]

And you have to give the user that acceses the DB for yout Piwik install file permissions on all .

[SimonSimCity]

Hi, flippy

Isn’t it exactly that, what phpmyadmin is doing by this commands? Or am I missing something?

REVOKE ALL PRIVILEGES ON * . * FROM 'piwik'@'localhost';

REVOKE GRANT OPTION ON * . * FROM 'piwik'@'localhost';

GRANT FILE ON * . * TO 'piwik'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

[flippy]

I think it should be no spaces .

This is how I did it on my two Piwik install and do not have issues with the infile stuff any longer.

grant file on *.* to 'user'@'localhost';

[SimonSimCity]

@flippy, I copied the code, replaced the username by the one I use and ran it … the command was executed, I reloaded the permissions, but I still get the same error …

[flippy]

What OS and version, and what SQL and version

[SimonSimCity]

@flippy, still the same

@SimonSimCity

As you can see, I use Ubuntu 12.04 and did install all packages out of their repository

PHP: 5.3.10-1ubuntu3.6
MySQL Server: mysqld Ver 5.5.31-0ubuntu0.12.04.1
MySQL Client: mysql Ver 14.14 Distrib 5.5.31
PHP Client:

• mysql: 5.5.31
• mysqli: 5.5.31
• pdo-mysql: 5.5.31

[flippy]

and in your my.cnf do you have

local-infile=1

Also see how Ubuntu starts it as a service. LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work

I avoid Ubuntu like the plague so it could be some wonderful special sauce they added with their fun layer they put over packages that is preventing it.

The key with ubuntu is you need to see if they build mysql as such

cmake ../mysql-5.5/ -DENABLED_LOCAL_INFILE=1

I do not think they do, because this is looked at as a security issue, please google to research why its looked at as such if you do not know.

[SimonSimCity]

Should it be enough if I see it set to ON, when I list the variables using this command?

mysql> SHOW VARIABLES;

And if you now ask about secure_file_priv, this is set to an empty string.

This is all stuff I wrote before in this thread …

[flippy]

Again read about Ubuntu 12.04 and MySQL on it, from what I read they disabled it by default. I do not have any Ubuntu machines to test that on. But also check how its started like I said, check your system file for mysqld

Here is a blog stating why 12.04 mysql does not allow this and how to solve it.
http://www.sudo-juice.com/install-mysql-5-5-from-source-ubuntu-linux/

[dukenukem]

SimonSimCity

Hi. The solution I posted worked for me. The issue I had was that my service provided change me onto another server and I think PHP was also updated. It was after that change that Piwik stopped displaying correctly. I did try to get them to try changing settings as suggested, but in the end, it was just easier for me to add the infomation as suggested:

The minifier is taking too long and your server has a max_execution limit. In your config/config.ini.php, add the following:

[Debug]
disable_merged_assets = 1

Sorry. I don’t know how it all ties in, however as stated, it works.

[SimonSimCity]

@dukenukem, just curiouse what minifying some files has to do with an error by an SQL query …

[SimonSimCity]

If anyone is still interested on my status on that: I got it working!

I did not get it working with “LOAD DATA INFILE”, but I did get “LOAD DATA LOCAL INFILE” working.

To get Piwik to use the keyword “LOCAL”, you have to disable the php-options “open_basedir” and “safe_mode”.

This is only of value to you, if it works on the commandline. Try to execute the following code in your commandline and see if it works. If yes, read on. If no, fix that first!

Replace the path and the filename to your needs.

$ mysql -u root -p --local-infile piwik

mysql>  LOAD DATA LOCAL INFILE '/path-to-your-piwik-installation/tmp/assets/option-somehash.csv' REPLACE INTO TABLE `option` CHARACTER SET latin1 FIELDS TERMINATED BY ' ' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY ' ' (option_name,option_value) ;

Now, you should still get the same error as @minos.
While reading the code, I found a hint to a PHP bugreport where this error occures when using PDO as driver for MySQL. Replacing it by MySQLi is a workaround, that’s worth testing.

So, I updated my Piwik-configuration and added the following line:

adapter = MYSQLI

After that, it worked like a charm!

Read here for details (German only)
http://forum.piwik.org/read.php?5,102235

[SimonSimCity]

I now also got the command “LOAD DATA INFILE” working, what you need, if you want to use the PHP setting open_basedir. Please follow the different statements of this post 301 Moved Permanently and ensure, that PHP and the MySQL server is on the same server, or at least that the Piwik-installation is accessible on both installations in the same path.