[Maria-discuss] Galera cluster as slave: Duplicate entry errors
Hi all, i'm new to this list :) My setup: MySQL Server 5.5.37 -> Replication -> Galera 10 Cluster (currently 2 nodes) The old MySQL Master is receiving inserts to a table with a auto_increment field (primary key). The Galera Cluster is configured as classic slave to the master. From time to time i'm getting duplicate entry issues on the galera cluster: 140905 12:06:15 [ERROR] Slave SQL: Error 'Duplicate entry '16383' for key 'PRIMARY'' on query. ... 140905 12:06:15 [Warning] Slave: Duplicate entry '16383' for key 'PRIMARY' Error_code: 1062 I cannot see the problem where there could came from... My thought was if the insert (auto_increment) succeeds on the master then is must also succeed on the galera side... someone with an idea on it? BR Otto
Hmm, maybe an idea by myself on the cause: the initial sync to galera was done as followed: - dump on the (locked) master - inserted the dumps on a galera node - started replication (change master to...) with the saved state of the master I've read somewhere that galera handles the auto_increments with a offset for each node (by design) - so you cannot rely on it. That is no problem for the application at all. But as i use ROW based replication the autoincrements (created on the master) could exist on the galera side already, right? what would be a way to avoid it? i think i need another migration scenario... Otto Am 05.09.14 um 18:59 schrieb Otto Berger:
Hi all,
i'm new to this list :)
My setup:
MySQL Server 5.5.37 -> Replication -> Galera 10 Cluster (currently 2 nodes)
The old MySQL Master is receiving inserts to a table with a auto_increment field (primary key). The Galera Cluster is configured as classic slave to the master. From time to time i'm getting duplicate entry issues on the galera cluster:
140905 12:06:15 [ERROR] Slave SQL: Error 'Duplicate entry '16383' for key 'PRIMARY'' on query. ... 140905 12:06:15 [Warning] Slave: Duplicate entry '16383' for key 'PRIMARY' Error_code: 1062
I cannot see the problem where there could came from... My thought was if the insert (auto_increment) succeeds on the master then is must also succeed on the galera side...
someone with an idea on it?
BR Otto
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
----- Original Message -----
Hmm, maybe an idea by myself on the cause:
the initial sync to galera was done as followed:
- dump on the (locked) master - inserted the dumps on a galera node - started replication (change master to...) with the saved state of the master
I've read somewhere that galera handles the auto_increments with a offset for each node (by design) - so you cannot rely on it.
This should only matter if you are writing directly to the galera nodes. It can be disabled however. http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep...
That is no problem for the application at all. But as i use ROW based replication the autoincrements (created on the master) could exist on the galera side already, right?
Did you compare the contents of the row on master and galera?
what would be a way to avoid it? i think i need another migration scenario...
It looks quite sane to me. -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
hi,all
I also came into the almost same situation, my migration like below:
1. use xtrabackup back a mysql5.5.x database
2. set up the first node of PXC5.6 with the backup
3. start the master-slave replication between mysql5.5 and the first node of pxc5.6
4. add new node to the PXC5.6
when i process the step 4, I found the replication is stopped caused by the auto-increment issue.
And i found the binlog-format of mysql5.5.x is statement-based, So i setup a middle node with row
based binlog as the master of PXC, and it works. the operation steps like below:
1. use xtrabackup back a mysql5.5.x database
2. setup a middle node mysql5.6.x with the backup and binlog-format=row
3. start the master-slave replication between mysql5.5 and mysql5.6.x
4. set up the first node of PXC5.6 with the backup
5. start the master-slave replication between mysql5.6.x and the first node of pxc5.6
6. add new node to the PXC5.6
I do not deep into find the reason, but i know there must be something different for pxc and normal slave
at the method of handling auto-increment field,So with row-based binlog, there is no need to handle the
auto-increment field values, and the behaviour of them will be the same.
On Sep 6, 2014, at 8:00, Daniel Black
----- Original Message -----
Hmm, maybe an idea by myself on the cause:
the initial sync to galera was done as followed:
- dump on the (locked) master - inserted the dumps on a galera node - started replication (change master to...) with the saved state of the master
I've read somewhere that galera handles the auto_increments with a offset for each node (by design) - so you cannot rely on it.
This should only matter if you are writing directly to the galera nodes.
It can be disabled however. http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep...
That is no problem for the application at all. But as i use ROW based replication the autoincrements (created on the master) could exist on the galera side already, right?
Did you compare the contents of the row on master and galera?
what would be a way to avoid it? i think i need another migration scenario...
It looks quite sane to me.
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Daniel Black -
Otto Berger -
oucboy