Results tagged “MySQL” from Bill Benac

MySQL Slave Revolt: Avoid 'Duplicate entry for key 1'

|
amistad.jpg
Here's a post that will be of little interest to my normal readers but that may be helpful to Googlers. If this helps you, please drop a comment letting me know. I need encouragement to go so far off topic from my normal posts.

Several years ago I bought the history book on which the movie Amistad had been based. The Amistad was a ship carrying slaves to the Americans, and its captives revolted. The movie, which I didn't see, was apparently exciting enough, but the book was tedious. I wanted to never revisit it or anything like it again. But alas, I've encountered what could be called a slave revolt.

MySQL has a strange behavior on slaves with the CHANGE MASTER command that cost me a few hours of sleep. Sometimes when values are set with the command, those values merge into the master.info file. However in other cases after using the command, the values in master.info are lost. A sequence of commands that seemed reasonable to me left me without the proper master bin-log and offset log position, and this caused my slave to get errors like 'Duplicate entry for key 1.'

Here's how I discovered this behavior:

First, I created a dump using the syntax that places within the dump an update statement to set the master's position:

mysqldump --all-databases --master-data=1 --add-locks -u myuser -p > full.db.`date +"%F"`.dmp

Afterward, I can check my dump and find that indeed, it provides the master's bin-log and position:

CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000494', MASTER_LOG_POS=169;

I then bring the dump to my slave server. If I first import the dump and rely on its values to set the master's position, I'll get errors when replication begins. The errors are caused because the replication picks up at the oldest bin-log instead of the right one. The errors, found after running "show slave status\G;" are like this:

Last_Error: Error 'Duplicate entry '3363837' for key 1' on query. Default database: 'myapp'. Query: 'INSERT INTO mytable (
                                    blah,
                                    blah2,
                                    blah3
                                ) VALUES(
                                    '1',
                                    '2009-11-01T00:06:16-05:00',
                                    'stuff'
                                )'

                                
What I really should have done to avoid the errors would have been to run a CHANGE MASTER command that stated everything rather than skipping the details that the dump included.

After looking into this further, I find that as expected, the dump creates a master.info file with the master's proper bin-log and offset, and that master.info doesn't yet have the server connection details. Then after providing just the connection details through CHANGE MASTER, contrary to my expectation it then wipes out the bin-log and offset values rather than properly merging. I can fix this by then providing just the bin-log and offset values, which are properly merged into master.info.

Commands illustrating this are below:

[root@myhost ~]# # import the master's data
[root@myhost ~]# mysql -u root -p{secret} < /tmp/full.db.2009-11-14.dmp
[root@myhost ~]# # see what the dump put into master.info
[root@myhost ~]# cat /var/lib/mysql/master.info # notice this first iteration of the file has no connection info
14
bin-log.000494
169

test

3306
60
0





[root@myhost ~]# # set the partial details as documented
[root@myhost ~]# mysql -u root -p{secret} --execute="CHANGE MASTER TO MASTER_HOST='10.1.1.14', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='supersecret';"
cat /var/lib/mysql/master.info
[root@myhost ~]# # check if that put anything in master.info
[root@myhost ~]# cat /var/lib/mysql/master.info # notice this second iteration dropped the bin-log and log position
14

4
10.1.1.14
repl
supersecret
3306
60
0





[root@myhost ~]# # set the remaining details as though nothing had been in dump
[root@myhost ~]# mysql -u root -p{secret} --execute="CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000494', MASTER_LOG_POS=169;"
[root@myhost ~]# # check if that put anything in master.info
[root@myhost ~]# cat /var/lib/mysql/master.info # notice this third iteration merged in the bin-log and log position
14
bin-log.000494
169
10.1.1.14
repl
supersecret
3306
60
0





[root@myhost ~]# # set everything and see the results:
[root@myhost ~]# mysql -u root -p{secret} --execute="CHANGE MASTER TO MASTER_HOST='10.1.1.14', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='supersecret', MASTER_LOG_FILE='bin-log.000494', MASTER_LOG_POS=169;"
cat /var/lib/mysql/master.info
[root@myhost ~]# cat /var/lib/mysql/master.info # notice this fourth iteration that sets everything looks like the third iteration
14
bin-log.000494
169
10.1.1.14
repl
supersecret
3306
60
0



So in short, don't rely on the dump to set master.info values for you. Just put them all into your mysql prompt similar to this:

mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.14',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='supersecret',
    -> MASTER_LOG_FILE='bin-log.000494',
    -> MASTER_LOG_POS=169;


Enjoy!

Find recent content on the main index or look in the archives to find all content.