← Back to team overview

maria-discuss team mailing list archive

Re: Replication and auto-increment


Hi Arjen,

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.


JPEG image

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 & 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 UPDATE ops.

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

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 not
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 failure(SPoF)?
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 situation.

This SPoF is our network link that can be a transient problem out of our
Both sides may need to keep inserting data during this transient issue. This is usually not an issue 99% of the time, but it's better to prepare
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

Follow ups