MySQL replication and error Duplicate entry 'value' for key 'PRIMARY'' on query

Question

MySQL

We have the following configuration: Master -> Slave. Replication in one direction. No requests are sent to the Slave at all.
BUT!!! Regularly Slave gets up with Duplicate entry error
Here is a specific example:
Error 'Duplicate entry '10375' for key 'PRIMARY'' on query. Default database: 'parts_nm_new'. Query: 'INSERT INTO vsm_user_passwords (login, password, date_till) VALUES ('drivent', 'password here', '2013-07-16')'
How so?
PRIMARY KEY is there alone. AUTO_INCREMENT value. How can it be duplicated?
Here is the table structure:
CREATE TABLE IF NOT EXISTS `vsm_user_passwords` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login` varchar(100) NOT NULL,
`date_till` date NOT NULL,
`date_check` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `login` (`login`),
KEY `password` (`password`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
And this problem with different tables comes out :(
What can be recommendations for solving the problem?

Answer 1

Hello.
This error is more typical for master-master ( see autoincrement_offset and *_increment dev.mysql.com/doc/refman/5.0/en/replication-options-master.html ).
If such a problem is for the master-slave, switching the logging mode to mixed may help (but I don’t quite believe it).
And be sure to make sure that when you start the slave, not only identical data, but also positioned in the binlog

Answer 2

Check if your counters are off or if the data on the master and slave is different.
If different, then fix it like this:
Run all queries in the wizard
CREATE TABLE IF NOT EXISTS `vsm_user_passwords_new` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
`date_till` date NOT NULL,
`date_check` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `login` (`login`),
KEY `password` (`password`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
(Before creating a table, it is advisable to look in SHOW CREATE TABLE `vsm_user_passwords` to
make sure that the table structure you provided is up-to-date.)
INSERT INTO `vsm_user_passwords_new` (SELECT * FROM `vsm_user_passwords`);
TRUNCATE TABLE `vsm_user_passwords`;
DROP TABLE `vsm_user_passwords`;
RENAME TABLE `vsm_user_passwords_new` TO `vsm_user_passwords`;
ps MySQL version? Are there any triggers on this table?
pss Have you tried to monitor the slave, from whom are the requests coming?

Answer 3

This also occurs.
I added an error to the exception, but of course this is not entirely correct.

Answer 4

I have the same thing - it turned out that the actoincrement field reached its maximum. I created an id by generating a random number, not everything.
I decided as follows: I
went into php mayadmin -% table% - options.
there is an autoincrement input with a maximum value - it needs to be changed to a smaller one.
Important!!! - in the table in the autoincrement field, you need to change its values ​​​​to some smaller one.
Hope it helps.