Confusing MySQL Replication Error Message

Confusing MySQL Replication Error Message

PREVIOUS POST
NEXT POST

I already wrote about some MySQL Error Messages which are confusing, here is one more:

After setting up new slave Server I’m getting error log file flooded with messages like this and there is no hint in the message what would explain what is wrong.

In fact the issue in this case is (because of configuration error) two slave servers got the same server-id.

Seriously in this case Master clearly sees the problem in this case as there are 2 servers with same server-id connected and replicating so it should report it to the slave instead of sending end packet.

At very least it would be nice to include possible reason for this error message which MySQL already does in many other cases.

I’ve now filed it as a bug.

PREVIOUS POST
NEXT POST

Share this post

Comments (14)

  • Ernesto Vargas Reply

    One day it happen to me, and took me almost an hour to find that out.

    Moving foward I always use a base my.cnf to I copy to any other server and the first thing is to increase the server-id.

    Could MySQL just use the servername intead of a numeric value?

    June 5, 2008 at 9:56 am
  • peter Reply

    Ernesto,

    I think a lot of people have script or process to set server-id but things tend to break from time to time and it is great to get a good error message.

    Server-id is stored in binary log and is integer this is why server-name would not work – too muich overhead.

    June 5, 2008 at 7:45 pm
  • Cosimo Reply

    Great post! I just got this error, and all the other related pages I could find were completely off-track.

    Anyway, of the two slaves with the same server-id, one lagged behind with no apparent reason (of course now I know why), the other I just stopped it. Is the former slave going to ever reach the master (Seconds_behind_master = 0) or not?

    January 12, 2010 at 6:14 am
  • yingkuan Reply

    Thanks whole bunch! Got the error in one of our clusters. Saved me a lot of time.
    Definitely misleading and confusing. Almost got our network guy killed.
    It really looks like intermittent network problem, the slave is catching up with master but through errors every second.

    March 4, 2010 at 7:23 pm
  • yingkuan Reply

    btw, our version is 5.0.84-64

    March 4, 2010 at 7:25 pm
  • Sean Scullion Reply

    Amazing! Thank you! 😀

    August 20, 2010 at 3:07 am
  • Harsha Reply

    Guys,

    i get exactly the same error, but server-ids are unique

    How to get out of this?

    thanks,
    Harsha

    August 30, 2010 at 11:06 pm
  • Harsha Reply

    Additional info:

    Master is 5.1.48 [server-id 1]
    1st Slave is 5.1.50 [server-id 2] and
    2nd slave is 5.1.48 [server-id 3]

    Does anyone see incompatibility issue?

    Note: 1. replication is happening but showing the error message that is confusing

    August 31, 2010 at 2:54 am
  • Guano Reply

    I’ve created 2 extra slaves with the same server-id as the original slave, after some time, one of them caught up with the master and the other one started getting further behind, I noticed the I/O message in the logs.
    Should I rebuild the slaves from scratch (200 GB of data) or can I just change the server-id and restart?

    October 26, 2010 at 7:39 am
  • Rakesh Reply

    Hi,

    I have exactly same issue, but my setup is Server with Server ID 1 and slave ID’s 2 and 3. (toatlly 3 boxes)

    My Mysql versions on all 3 server are 5.1.54

    Am not sure where it went wrong…. 🙁

    Any help will be appreiciated!!

    March 6, 2012 at 8:32 am
  • Michael Wehrle Reply

    I have observed this is still a problem with MySQL 5.6 today. The problem and resolution are slightly different though. My server-id were properly set differently, but the server-uuid was the same, because this new slave was a clone. My resolution was to change the server-uuid

    April 19, 2013 at 2:54 pm
  • Carlos Vazquez Reply

    I had the same issue as Michael Wehrle. To change the server-uuid, I stopped MySQL, renamed the auto.cnf file in my data directory, and restarted MySQL. Basically like this:

    /etc/init.d/mysql stop
    cd /var/lib/mysql
    mv auto.cnf auto.cnf.bk
    /etc/init.d/mysql start

    This is on MySQL 5.6.13 on CentOS 6.4.

    It also resolved duplicate messages like this:
    [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more information.

    Thanks Michael!

    September 5, 2013 at 10:38 am
  • Abhijit Buchake Reply

    I was getting this error because two (out of four) slaves of the same master had same server_uuid value although server_id were unique for all of them.
    It stopped when I changed the server_uuid of one of the slave.

    August 4, 2015 at 8:55 am
  • Wagner Bianchi Reply

    In times of MySQL 5.6++, maybe you can receive that message also when the server-uuid from auto.cnf is the same in case you’ve just copied the DATADIR to start a new server, for example. Make you you always have unique server_id and unique server-uuid for each of the servers in a replication topology.

    April 25, 2016 at 2:49 pm

Leave a Reply