maria-discuss team mailing list archive
Mailing list archive
Re: Replication and auto-increment
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 & out.
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 app
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 do
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 on a
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
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 today.
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 asynchronous.
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