@anonymous-piwik-user opened this Issue on November 28th 2013

I'm a fan of Piwik so far, but when we deployed Piwik to our live site yesterday, it broke our MySQL replication.

Here's the error I'm getting from the slave:

Error 'Access denied for user <a class='mention' href='https://github.com/'>@_ (using password: NO)' on query. Default database: 'piwik'. Query: 'LOAD DATA INFILE '/rdsdbdata/tmp/(numbers removed by me in case they're sensitive).data' REPLACE INTO TABLE piwik_archive_blob_2013_11 FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n' (idarchive, idsite, date1, date2, period, ts_archived, name, value)'

I thought it was weird that there was no username or hostname in the error, as well as the password being absent. Anyone have any thoughts on what this could be? We can't use our slave until this is figured out.

@anonymous-piwik-user commented on November 28th 2013

I forgot to mention this is all within Amazon's RDS, I didn't set up the replication myself. Let me know if you need more information from me.

@anonymous-piwik-user commented on November 28th 2013

I dug into this a little more and it looks like AWS just does not allow this:

https://forums.aws.amazon.com/thread.jspa?messageID=494201&#494201

Is there any way to turn off the Load Infile stuff from the config files? I realize this is going to slow everything down, but we apparently can't use Piwik given our setup if the load infile stuff is happening.

@mattab commented on December 8th 2013 Owner

We could add a config setting to disable the use of LOAD DATA INFILE. Thanks for the suggestion!

@anonymous-piwik-user commented on December 8th 2013

Thanks for checking on on this. Any thoughts on how I can stop the LOAD DATA INFILE from happening in the mean time?

@mattab commented on December 9th 2013 Owner

at line 66 of core/Db/BatchInsert
replace

    if (Db::get()->hasBulkLoader()) {

by

    if (false && Db::get()->hasBulkLoader()) {

it will skip the batch load.

@anonymous-piwik-user commented on December 9th 2013

This is on line 2358 of core/Piwik.php if you're in 1.12, I think. Thanks for your help!

@anonymous-piwik-user commented on December 9th 2013

Nope, this still isn't working. I've tried commenting out lines in the MySQL adapters that enable the load data infile options, but my replication is still being broken. Any other thoughts on how I can fix this in 1.12?

@mattab commented on December 9th 2013 Owner

in the function createTableFromCSVFile

can you add the first line:

    debug_print_backtrace();

Then it will show you which code is calling this function. Maybe there's one more place to disable bulk loader. As far as I know there's only the one caller.

@anonymous-piwik-user commented on December 12th 2013

Hi Matt,

Thanks for your help on this. Once I killed the existing slave and started a new one, the problems went away. So the first edit you suggested was probably enough to fix the replication problem.

Thanks again!

@mattab commented on December 26th 2013 Owner

Actually I'm not so keen to add a new setting for this. Maybe it would be better to catch the exception?

If anyone experiences the issue please comment and i'll reopen the ticket

@anonymous-piwik-user commented on December 26th 2013

Currently, Piwik set up to use LOAD INFILE will break Mysql replication in AWS RDS. Are you saying you won't fix that issue or that you won't add a config option? All I care about is that it doesn't break our replication in future versions.

@mattab commented on December 27th 2013 Owner

In 1894442934c0061e03d8e39a0748dbb0fc8c7620: Adding config setting to explicitely disable LOAD DATA INFILE to prevent error when doing mysql replication on AWS Fixes #4358

This Issue was closed on January 6th 2014
Powered by GitHub Issue Mirror