entering duplicate record on table with unique key crashes galera
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| MySQL patches by Codership |
Fix Released
|
High
|
Teemu Ollakka | ||
| 0.7 |
Fix Released
|
High
|
Teemu Ollakka | ||
| 0.8 |
Fix Released
|
Undecided
|
Teemu Ollakka | ||
Bug Description
When entering a duplicate value into a column for which a unique index has been created the server stays in a loop eating all memory until it runs out, crashing the server.
Server version: 5.1.43 wsrep_0.7.3
Tried this on both a singel node and one in a cluster of 3
Steps to reproduce:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` (`email`) VALUES ('<email address hidden>');
# reports Query OK, 1 row affected
INSERT INTO `test` (`email`) VALUES ('<email address hidden>');
# just tries over and over until all available memory runs out
I compared the same on a stock ubuntu mysql (Server version: 5.1.41-3ubuntu12)
the result there is (as it should be)
ERROR 1062 (23000): Duplicate entry '<email address hidden>' for key 'email'
Any error logs are available on request.
| Changed in codership-mysql: | |
| status: | New → Confirmed |
| Seppo Jaakola (seppo-jaakola) wrote | #1 |
| Seppo Jaakola (seppo-jaakola) wrote | #2 |
| Changed in codership-mysql: | |
| importance: | Undecided → High |
| milestone: | none → 0.7.5 |
| Michiel van Leening (leening) wrote | #3 |
| Changed in codership-mysql: | |
| milestone: | 0.7.5 → 0.7.4 |
| assignee: | nobody → Teemu Ollakka (teemu-ollakka) |
| Changed in codership-mysql: | |
| status: | Confirmed → Fix Committed |
| Changed in codership-mysql: | |
| milestone: | 0.7.4 → none |
| status: | Fix Committed → In Progress |
| Changed in codership-mysql: | |
| status: | In Progress → Fix Committed |
| Changed in codership-mysql: | |
| status: | Fix Committed → Fix Released |
This behavior is due to configuration, which makes MySQL server to retry automatically a failed autocommit query. The retrying feature was added to comply with Drupal's expectations of MySQL response in multi-master mode. (Drupal is rather picky about MySQL response in certain occasions)
The retrying is controlled with:
'wsrep_
Setting these variables to 0, will make MySQL server to return the duplicate key error, as expected.
We need to review the retrying feature to see if it can be made usable. I see at least following actions points for the fix:
* retrying should be off by default
* retrying should be fired only for aborts caused by multi-master conflicts
(now it seems to retry whatever failing AC transactions)
* there should be a sensible limit for how many retries will be allowed
Thanks for the bug report, a pretty severe vulnerability discovered here