I have an interesting issue with MySQL replication. The environment consists of two MySQL nodes, replicated Master-Master through standard Mysql mechanisms, and simple database with following structure:
CREATE DATABASE `replication_test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci */;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(65) COLLATE utf8_polish_ci DEFAULT NULL,
`value` int(11) DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `value_UNIQUE` (`value`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
now it all works flawlessly, until I hit somehow common situation, that is unique constraint violation. Simple code, like one below
try {
$stmtL = $this->connectionL->prepare('INSERT INTO test (name, value) VALUES (:name, :value)');
$stmtL->bindParam(':name', "name", PDO::PARAM_STR);
$stmtL->bindParam(':value', 1, PDO::PARAM_INT);
echo $stmtL->execute(), "<br/>";
//usleep($delay);
$stmtL = $this->connectionL->prepare('INSERT INTO test (name, value) VALUES (:name, :value)');
$stmtL->bindParam(':name', "name", PDO::PARAM_STR);
$stmtL->bindParam(':value', 1, PDO::PARAM_INT);
echo $stmtL->execute(), "<br/>";
} catch (Exception $ex) {
echo $ex->getMessage(), "<br>";
}
works as expected, that is throws PDO exception, but at the same time blows the replication with Slave SQL: Error 'Duplicate entry
. Now I do not believe that such simple thing could stop replication. Anyone faced similar issue, and care to share the solution?