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
control.
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.
Regards,
Arjen.
--
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
