Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
  • MySQL Master version: 5.5.16-1
  • MySQL Slave version: 5.5.18-1

The master's snapshot is created by:

mysql> FLUSH TABLES WITH READ LOCK;
shell> mysqldump --all-databases --master-data > dbname_`date +%F`.sql

This dump file is imported on the slave (which is started with --skip-slave-start option) without error:

shell> pv dbname_`date +%F`.sql | mysql -u root -p

But I got the following error when executing the mysql> start slave;:

    Last_SQL_Errno: 1062
    Last_SQL_Error: Error 'Duplicate entry '115846' for key
'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO
request_posted (id, user_id, channel, message, link, picture, name, ...

There is only one record with ID 115846 on the master:

mysql> select count(*) from request_posted where id=115846;
Current database: db

+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

Try to skip some queries with:

mysql> STOP SLAVE; 
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
mysql> START SLAVE;

didn't help. I don't want to skip those errors by adding:

slave-skip-errors = 1062

to my.cnf file because it may bring slave inconsistent.

What may be the reason for this error?


UPDATE

This is not how I usually setup mySQL replication

Which steps that you think I don't follow the document?

I wonder if you will encounter the same problem if you were to setup the entire configuration rather that passing the mysqldump command.

No, it works as normally if I also change the master to corresponding coordinates.

I would try dropping the database on the slave, make sure the binlogs are clear, and start again. Also check the table in question on the master to assure the indexes do not have errors.

Is delete (move) all the datadir enough? I did that and get the same result.


Reply to @Dmytro Leonenko

'show slave status\G' on slave to ensure that it is properly configured, MASTER_LOG_POS is 0

Only 'show slave statug\G' after import but before 'start slave;' can give us the answer

I backed up the datadir, delete all and run mysql_install_db, import the dump file, execute change master to and here's the results:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: x.x.x.x
                  Master_User: xx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

I'm wondering why Master_Log_Pos is 4?

share|improve this question
1  
There can only be one record with that id, hence the error, it will never get written. When you issue SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 Does the query causing the error change in the least? Was the slave binlog position setup correctly? –  thinice Dec 2 '11 at 14:51
    
Everytime I skip counter, it changes to another ID. The --master-data option is already write the binary log coordinates to the dump file. I only need to change master to master_host, master_user, master_password. –  quanta Dec 2 '11 at 15:04
    
This is not how I usually setup mySQL replication (I generally setup replication per URL here: dev.mysql.com/doc/refman/5.0/en/replication-howto.html) However, reading through the mysqldump options, there is no reason why it shouldn't work. I wonder if you will encounter the same problem if you were to setup the entire configuration rather that passing the mysqldump command. –  Rilindo Dec 5 '11 at 4:12
    
Do you mean I shouldn't use the --master-data option when creating a data snapshot? If it still happens when I use --lock-all-tables option and change master to master_log_file='', master_log_pos='', ..., what may be the causes of? –  quanta Dec 5 '11 at 4:25
    
No, that is not what I am saying. As I alluded to, what you have done should be work as intended - as far I can see, there is no existing bug with that option. However, it doesn't mean that there isn't, so as an isolation step, I would follow the convention provided by mySQL first through that URL. t. If that works, at least you have the direction to start troubleshooting. If that doesn't, well, we have a different problem. :) –  Rilindo Dec 5 '11 at 13:47

3 Answers 3

What to try to fix your problem:

  1. You should remove master.info on slave first and restart mysql
  2. issue CHANGE MASTER TO MASTER_HOST='XX.XX.XX.XX', MASTER_USER='repl', MASTER_PASSWORD='slavepass';
  3. do mysqldump with '--flush-logs' option on master
  4. 'mysql -u user -p < dump.sql' on slave
  5. 'show slave status\G' on slave to ensure that it is properly configured, MASTER_LOG_POS is 0
  6. 'start slave;' on slave.

What to check also:

  • Binlog format: MIXED
  • server_ids are different on master and slave
share|improve this answer
    
Issuing the whole change master string (including the log name and position number) actually fixes it, but at this point, I think the core question is why Quanta has to re-enter the log-name and position number when that is already in the dump file. –  Rilindo Dec 8 '11 at 14:48
    
Only 'show slave statug\G' after import but before 'start slave;' can give us the answer –  Dmytro Leonenko Dec 8 '11 at 15:26
    
appended the requested info into my original post. –  quanta Dec 12 '11 at 2:46
    
How did you end up with "Master_Host: x.x.x.x Master_User: xx" without issuing "CHANGE MASTER ...". Or you just didn't mentioned it in answer? Have you checked binlog format and what was the command line for mysqldump? –  Dmytro Leonenko Dec 12 '11 at 6:30
    
I already mentioned that in my post "import the dump file, execute change master to". I'm using MIXED-based logging. I've tested with MySQL 5.0.77 (statement-based), it also causes this error. The full mysqldump is mysqldump -u root -p --all-databases --master-data --flush-logs > alldb_$(date +%F).sql –  quanta Dec 12 '11 at 7:48

The problem is caused by setting up the master on a running production server BEFORE doing the dump (as far as I can tell). So, there are queries written in the master_log that have already been executed on the data residing on the slave. I never actually saw a solution on the mysql website or mailing list. So, I came up with the following solution that solved my problem.

on slave:

mysql> STOP SLAVE;
mysql> FLUSH PRIVILEGES;  # dump likly included users too

on master:

mysql> RESET MASTER;

on slave:

mysql> RESET SLAVE;
mysql> START SLAVE;

by the way, I ran my dump with the following on the slave:

mysqldump -uROOTUSER -pROOTPASSWORD -hMYSQLMASTER.EXAMPLE.COM --all-databases --delete-master-logs | mysql -uROOTUSER -pROOTPASSWORD

I hope this helps someone else.

http://dev.mysql.com/doc/refman/5.0/en/reset-master.html

http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html

share|improve this answer
    
edited to include FLUSH PRIVILEGES; I realized after another error that even tho my users had been imported with the dump, their privileges weren't active yet. –  BroknDodge Jan 9 '13 at 21:45

If you don't want REDO the complete procedure, a good fix would be to use

STOP SLAVE;    
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

If there are too many such errors, a good idea would be to automate it using a bash script.

Ref: Fixing Duplicate Entry Error

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.