MySQL Bugs: #905: REPLICATION SETUP PROBLEM

Bug #905	REPLICATION SETUP PROBLEM
Submitted:	24 Jul 2003 1:40	Modified:	24 Aug 2003 4:45
Reporter:	Andrew Sitnikov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.14	OS:	Linux (Linux/SuSE)
Assigned to:		CPU Architecture:	Any

Description:
Can not setup replication, slave exists wirh error:

Version: '4.0.14-standard'  socket: '/tmp/mysql.sock'  port: 3306
030724  2:44:23  Slave I/O thread: connected to master 'replica@xxx.xxx.xxx.xxx:3306',  replication started in log 'FIRST' at position 4
ERROR: 1062  Duplicate entry '44520' for key 1
030724  2:44:24  Slave: error 'Duplicate entry '44520' for key 1' on query 'INSERT INTO online_status (date, date2, number) VALUES (NOW(), NOW(), NULL)', error_code=1062
030724  2:44:24  Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001' position 1667

How to repeat:
1. (master & slave) Install 4.0.14
2. (master) mysqlhotcopy db_name --noindices --resetmaster --flushlog
3. (master) scp db_name_copy  into
4. (slave) myisamchk -r dn_name/*.MYI (
5. (slave) start mysqld
6. cat error.log
Version: '4.0.14-standard'  socket: '/tmp/mysql.sock'  port: 3306
030724  2:44:23  Slave I/O thread: connected to master 'replica@xxx.xxx.xxx.xxx:3306',  replication started in log 'FIRST' at position 4
ERROR: 1062  Duplicate entry '44520' for key 1
030724  2:44:24  Slave: error 'Duplicate entry '44520' for key 1' on query 'INSERT INTO online_status (date, date2, number) VALUES (NOW(), NOW(), NULL)', error_code=1062
030724  2:44:24  Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001' position 1667

Hi!

How to repeat:
1. (master & slave) Install 4.0.14
Was it a fresh install on the slave, or had MySQL already been installed on the slave before?

2. (master) mysqlhotcopy db_name --noindices --resetmaster --flushlog

--resetmaster will delete all binlogs and create a new binlog (.001) so you don't need --flushlog on top of it.

3. (master) scp db_name_copy  into

4. (slave) myisamchk -r dn_name/*.MYI (
This possibly made the master and slave's data become different (if myisamchk -r actually had to repair some hidden corruption in the table).
You should rather run myisamchk -r on the master, then do the scp. So that the master and slave have exactly the same data for sure before replication starts.

5. (slave) start mysqld
6. cat error.log
Version: '4.0.14-standard'  socket: '/tmp/mysql.sock'  port: 3306
030724  2:44:23  Slave I/O thread: connected to master
'replica@xxx.xxx.xxx.xxx:3306',  replication started in log 'FIRST' at
position 4
ERROR: 1062  Duplicate entry '44520' for key 1
030724  2:44:24  Slave: error 'Duplicate entry '44520' for key 1' on query
'INSERT INTO online_status (date, date2, number) VALUES (NOW(), NOW(),
NULL)', error_code=1062

This says that the slave could not insert a row with number 44520 (like it was inserted on the master) because there was already such a row in the slave's table.

So this is either a bug in MySQL, or a misuse on your side.
If you can find a simple way to repeat it (a table on the master, exactly the same table on the slave, then queries which cause the slave to give an error), then this is very probably a bug and we would appreciate to have the table and queries to reproduce the problem on our side.

Thanks for your help.
Guilhem

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".