would it be possible to change this query:
to set exact number instead of incrementing the sequence? This would make Piwik more resistant in case there are DB replication issues. We should avoid situations when sequence has lower value on one of the nodes.
:+1: for strengthening this
Context from mysql docs:
When there are any issues with DB replication, this table won't sync automatically because this query is only incrementing the value. It's not related to #7554.
master-slave shouldn't cause any issues but I presume master-master does here? Can you provide us the solution which queries to rewrite? We had a quick look but we're not sure. Eg here's the used code: https://github.com/piwik/piwik/blob/2.14.0-rc1/core/Sequence.php#L93
I'm not sure what you mean with exact value, it has to be atomic so we cannot read and then set the value.
I don't really understand full problem, moving out of milestone until we have more information cc @quba
piwik_sequence is a crucial table so we need to make sure that values are synchronized across all databases.
There are two ways to go:
The thing is, that if these tables are desynchronized, Piwik produces really random results for reports (e.g. mixed site IDs). It's also really hard to debug. Therefore I wanted to let you know that maybe we could improve something here.
Can maybe an administrator or whoever has more experience with master-master replication have a look at the queries and tell us how to improve? Otherwise we won't be able to do much.
@tsteur the thing is that it can't be implemented like
set value = value + 1. In previous comment you mentioned that it makes no sense to select the value before executing the update query so I think that there's no real solution.
As I said, currently it's a minor issue for us (we implemented additional monitoring) but I wanted to discuss this further and ask for your feedback.
In previous comment you mentioned that it makes no sense to select the value before executing the update query so I think that there's no real solution.
Yes it has to be atomic.
Maybe a solution would be at some point to generate random values including [0-9a-f]? The DB index would get a bit bigger etc but something like this might work and should be backwards compatible but haven't thought too much about it. We'd just need to try to avoid collisions (which should be doable)