replication blues

[Bogdan TARU]

Hi guys,

I've got a weirdo problem with replicating a database. Sometimes I get
some duplicate keys problems for _only_ one table. There is nothing
special about this table, it looks like:

+----------+--------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------+--------------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL |
auto_increment |
| dialer | int(10) unsigned | YES | | NULL |
|
| uid | int(10) unsigned | | | 0 |
|
| action | enum('REGENERATE','CREATE','DELETE') | YES | | NULL |
|
| acc_no | int(10) unsigned | YES | | NULL |
|
| template | int(10) unsigned | YES | | NULL |
|
| name | varchar(20) | YES | | NULL |
|
| status | enum('OKAY','ERROR') | | | OKAY |
|
+----------+--------------------------------------+------+-----+---------+----------------+
And data is inserted into it with simple inserts, w/o specifing the id
(it's autoincrementing).

With a little debugging, I have located the problem. If I run 'alter
table xxx auto_increment=1' on both the master and the slave (this table
is empty at the time on both machines), and then I insert datas into the
master, they look like:

On master:

+----+--------+------+------------+--------+----------+---------------+--------+
| 1 | 3 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 2 | 4 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 3 | 5 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 4 | 6 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 5 | 13 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 6 | 14 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 7 | 18 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 8 | 19 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 9 | 20 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 10 | 21 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
+----+--------+------+------------+--------+----------+------+--------+

But on slave it looks like:

+----+--------+------+------------+--------+----------+------+--------+
| id | dialer | uid | action | acc_no | template | name | status |
+----+--------+------+------------+--------+----------+------+--------+
| 10 | 3 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 11 | 4 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 12 | 5 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 13 | 6 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 14 | 13 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 15 | 14 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 16 | 18 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 17 | 19 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 18 | 20 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
| 19 | 21 | 1007 | REGENERATE | NULL | NULL | NULL | OKAY |
+----+--------+------+------------+--------+----------+------+--------+
Why does it start on the id=10 on the slave? Of course, this is the
cause for the replication failures later on, because datas are deleted on
the master with 'delete from xxx where id=3', for example, action which
doesn't delete anything on the slave (because there is no id=3 entry),
thus inconsistency.

I'm using 4.0.13 on both machines.

Thanks,
bogdan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw