maria-discuss team mailing list archive
Mailing list archive
Re: Replication and auto-increment
The idea behind the server_id column is to avoid inc_inc = 10, for
example. Inc_Inc/Offset is excellent for 1, 2, or even 3 MM servers.
Where things go wild, I mean, more then 3 servers, connected via ADSL
we must be more creative.
And if you are using MyIsam you can make server_id + auto_increment
column as PK. Unfortunately, innoDB doesn't allow us to do that.
The scenario I have described is a very INSERT app with helps a lot.
Em 27/01/2010, às 23:19, Arjen Lentz escreveu:
Hi Alexandre, Brian
On 28/01/2010, at 7:18 AM, Alexandre Almeida wrote:
Allow me to share how we are handling this issue. We have a
customer that develop and sell a system that control employees in &
They have many customers with multiple plants & sites.
To avoid the related issue, we did a simple workaround, we add a
new column at each table. The column name ;-) server_id :-)
Now each insert is running like this: INSERT INTO foo SET server_id
= @server_id, auto_increment_column = NULL, bla bla bla
BinLog format chosen = ROW.
The worse and complex scenario is a customer with 17 sites. All of
them linked by ADSL Internet ;-) Yes, believe me.
The problem of INSERT / DELETE / SELEC was successfuly solved. The
only issue to take care is the possibility of race condition on
If you set the auto-increment-increment and auto-increment-offset
options, you achieve the same, without the extra column.
The possible race with UPDATEs remains either way, depends on the
Em 27/01/2010, às 18:51, Brian Evans escreveu:
I was reading over the replication discussion on maria-developers
I thought I'd share some thoughts that are not so technical as I
understand the back end that well.
Our system is currently a master-master WAN replication setup, each
writing to a different DB with an offline process to do copies.
Our databases extensively uses auto-increments on tables (more than
necessary, but that's another story).
Would future developments allow synchronous replication to occur
single database when the WAN link has a single point of
How might an auto-increment system handle such a failure?
Basically I think the problem is that you're asking about auto-
increment in this context; I don't see why auto-inc would be
relevant to the question?
Naturally, having sync replication when a single secondary server
fails is an oxymoron, since then a) you wouldn't have a copy and b)
no replication active.
If you'd wish to continue operations anyway in this scenario, you'd
want a setup that allows the master to continue even if sync
replication were to fail, relying on monitoring to alert of this
This SPoF is our network link that can be a transient problem out
Both sides may need to keep inserting data during this transient
This is usually not an issue 99% of the time, but it's better to
for downtime than trying to fix data errors in that 1%.
With this requirement, all you're asking for is asynchronous
replication, handling auto-inc appropriately. That already exists
Under normal circumstances, the extra thing sync repl provides is an
immediate secondary copy of the changes. But with your question
above you've essentially released that requirement, making it
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/
OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org