EmergencyEMERGENCY? Get 24/7 Help Now!

Confusing MySQL Replication Error Message

 | June 4, 2008 |  Posted In: Insight for DBAs

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
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

14 Comments

  • 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?

  • 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.

  • 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?

  • 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.

  • 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

  • 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?

  • 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!!

  • 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

  • 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!

  • 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.

  • 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.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.