Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Add setting to disable LOAD DATA INFILE, for easy Mysql replication in AWS #4358

Closed
anonymous-piwik-user opened this Issue · 12 comments

2 participants

@anonymous-piwik-user

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 @ (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

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

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
Owner

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

@anonymous-piwik-user

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

@mattab
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

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

@anonymous-piwik-user

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

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

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
Owner

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

@sabl0r sabl0r referenced this issue from a commit in sabl0r/piwik
@mattab mattab Adding config setting to explicitely disable LOAD DATA INFILE to prev…
…ent error when doing mysql replication on AWS Fixes #4358
1894442
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.