Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G
For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'
. This indicates that you should look up the value 174465
in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:
OPTION 1
Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G
When you view the Slave Status, here is what to expect
OPTION 2
Remove the row to allow replication to continue
Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:
STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G
At the risk of sounding redundant...
When you view the Slave Status, here is what to expect
What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:
I faced a similar error some days ago. I was able to solve that error based on some Google search results. Here are some of the suggestions that might hopefully help you too:
Check whether you created the replication user with required privileges. If not, create a new user on master with the following command:
Create user ‘repl’@’192.168.2.46’ identified by ‘replpwd’;
Grant replication slave on *.* to ‘repl’@’192.168.2.46’;
If you already created the required replication user, check the privileges of the replication user. You can check the privileges by using the following command:
show grants for repl;
Usually, this issue occurs because of the incorrect password. In that case you can reset the password on the slave by using the following command:
change master to master_password = 'rplpassword';
Also, check the for any mistakes in the given replication user and host IP address.
Since, you are able to ping the master server, it is most probably because of the above mentioned reason.
Best Answer
Explain the code (sort of)
From the B.4 Client Error Codes and Messages page of the "MySQL 5.7 manual", the error code 2027 means:
That's not very informative. Looking for this error code, I've found this bug, fixed on MySQL 5.7.8:
You may check the value that you have for
read_rnd_buffer_size
variable if you have mysql 5.7.7 or lower in a hope to fix your issue, but it might not work.The replication incompatibility
So, what is your problem. The problem is that you can't have a replication between versions 5.1 and 5.7. From the manual page Replication Compatibility Between MySQL Versions:
Check that page for more information (there are incompatibilities on having many different minor versions, for setups with 3 or more servers).
I haven't really confirmed this by configuring a similar setup, but it seems that you need to upgrade your Mysql 5.1 to at least 5.6 (but preferably to 5.7). If your new 5.7 slave is fresh, you might try to downgrade it from mysql 5.7 to 5.5 (reinstalling and importing fresh data).
But the upgrade from 5.1 to 5.7 option is preferable, as you will have better and improved mysql installations.