On Thu, Sep 18, 2003 at 01:14:47PM -0400, Christopher Ferry wrote:
> We're running bi-directional replication between two servers running
> MySQL 3.23.56.
>
> One server being active the other a hot standby.
> Wanting to switch to the hot standby I noticed that replication had
> ceased on the HS server. The show slave status:
> | 192.168.1.10 | mysql_replicator | 3309 | 60 |
> hostname-bin.075 | 1159 | No
> | | | 1062 | error
> 'Duplicate entry '11609031'
> for key 1' on query 'insert into CLOCK_INSTALL (URL, MACHINE_ID,
> INSTALL_TIME,REMOTE_IP,LASTURL) values
> ('FRZE1002','81E7A3FBE4A64E6A8CDE92EB00B4D8B0',Now(),'127.0.0.1','')' |
> 0 |
>
> The install_id('11609031') for the table is of auto_increment and that
> id is already present in the HS server.
> The previous entry '11609030' is the same accross the servers. How
> could this happen and why doesn't mysql just increment to '11609032' for
> this insert?
It doesn't use auto increment on the slave because the record wouldn't
be identical to the master's record.
> The replication has not been touched for weeks and it was working
> properly when it was set up. The HS was never in an active situation so
> corruption is ruled out on its end.
>
Are you sure that no insert was done on the HS? You can check this in
its binlog with mysqlbinlog. Look for the server id in the output
comments like this:
#030815 11:59:59 server id 1 Query thread_id=135377 exec_time=0
error_code=0
Did the HS ever crash or reboot? Did you ever change its server id? Just
guessing here.
> I'd like to solve this without downtime to the primary server.
>
If there really were two distinct records (one on the active and the
other on the HS) you will need to remove the record on the HS and fix
anything that may depend on this record. Only then can it resume
replication.
If there are too many conflicts, you can:
- shut down MySQL on the HS
- FLUSH TABLES WITH READ LOCK on the active server
- copy all tables on the active server somewhere that takes the least
time (to minimize the 'downtime' of the active server)
- note the last binlog file and size on the active server
- UNLOCK TABLES on the active server
- move the copy to the HS
- update the HS's master.info with the active server's binlog file and
size
- restart MySQL on the HS
Maybe there's an easier way, but this is (from memory) what I've used
before with 3.23.xx.
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
