BF abort does not work with inserts to table with single unique key

Bug #1299116 reported by Teemu Ollakka
32
This bug affects 7 people
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Status tracked in 5.6
5.5
Fix Released
High
Seppo Jaakola
5.6
Fix Released
High
Seppo Jaakola
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC
Status tracked in 5.6
5.5
Fix Released
Undecided
Unassigned
5.6
Fix Released
Undecided
Unassigned

Bug Description

From https://bugs.launchpad.net/codership-mysql/+bug/1280896/comments/2

Test

  create table uniq (u varchar(10), unique key `unique_key`(`u`));
  echo "insert into uniq (u) values ('const');" | mysql -h node1 &
  echo "insert into uniq (u) values ('const');" | mysql -h node2 &

will make applying to fail on either of the nodes node1 or node2 with duplicate key error:

140328 18:50:51 [ERROR] Slave SQL: Could not execute Write_rows event on table t
est.uniq; Duplicate entry 'const' for key 'unique_key', Error_code: 1062; handle
r error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 81, Err
or_code: 1062
140328 18:50:51 [Warning] WSREP: RBR event 2 Write_rows apply warning: 121, 3

The reason for this is that locally executing insert does not get BF aborted.

Revision history for this message
Dmitry Gribov (grib-d) wrote :

I have seeing no such a problem in 5.5.34-25.9. It's now present in testing. Perhaps, this is а fresh-made bug.

Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

This issue is a side effect of the fix for lp:1280896.

Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

The fix was to both skip check for duplicate keys in secondary indexes *and* also avoid duplicate key error. Fixes pushed in revisions:

wsrep-5.5: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3983
wsrep-5.6: http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4078

Revision history for this message
Dmitry Gribov (grib-d) wrote :

Resolved

Revision history for this message
Dmitry Gribov (grib-d) wrote :
Download full text (3.8 KiB)

But now we can have two equal records on the unique key

SELECT id, login FROM users WHERE login LIKE 'andy002715724513%';
30684245 "andy002715724513"
30684248 "andy002715724513"

SELECT DISTINCT login FROM users WHERE login LIKE 'andy002715724513%';
"login"
"andy002715724513"

Table haves
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`)

see full structure below

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(100) DEFAULT NULL,
  `pwd` varchar(100) DEFAULT NULL,
  `s_mail` varchar(50) DEFAULT NULL,
  `s_www` varchar(255) DEFAULT NULL,
  `s_inn` varchar(50) DEFAULT NULL,
  `s_descr` text,
  `s_phone` varchar(100) DEFAULT NULL,
  `offert_accepted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `s_full_name` varchar(255) DEFAULT NULL,
  `s_first_name` varchar(100) DEFAULT NULL,
  `s_middle_name` varchar(100) DEFAULT NULL,
  `s_last_name` varchar(100) DEFAULT NULL,
  `s_city` varchar(100) DEFAULT NULL,
  `s_address` varchar(255) DEFAULT NULL,
  `last_used` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_host_id` smallint(5) unsigned DEFAULT NULL,
  `mail_confirmed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `msisdn_confirmed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `msisdn_confirm_code` varchar(4) DEFAULT NULL,
  `msisdn_req_time` datetime DEFAULT NULL,
  `java_phone_model` varchar(30) DEFAULT NULL,
  `java_font` varchar(30) DEFAULT NULL,
  `java_safe_mode` tinyint(4) NOT NULL DEFAULT '0',
  `user_pic` varchar(16) DEFAULT NULL,
  `denied_libs` varchar(255) DEFAULT NULL,
  `recenser_type` int(11) DEFAULT NULL,
  `partner_id` int(11) DEFAULT NULL,
  `creat_date` datetime DEFAULT NULL,
  `partner` int(10) unsigned DEFAULT NULL,
  `partner_valid_till` date DEFAULT NULL,
  `partner_pin` varchar(32) DEFAULT NULL,
  `account` decimal(9,2) NOT NULL DEFAULT '0.00',
  `abonement_start` datetime DEFAULT NULL,
  `abonement_expires` date NOT NULL DEFAULT '2006-01-01',
  `abonement_period` smallint(6) DEFAULT NULL,
  `abonement_delay` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `abonement_max_price` decimal(6,2) DEFAULT NULL,
  `abonement_downloads` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `abonement_left_clicks` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `abonement_left_summ` decimal(6,2) DEFAULT NULL,
  `user_pic_height` tinyint(3) unsigned DEFAULT NULL,
  `user_pic_width` tinyint(3) unsigned DEFAULT NULL,
  `show_pay_btn` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `s_puid` varchar(255) DEFAULT NULL,
  `discount` decimal(4,4) NOT NULL DEFAULT '0.0000',
  `money_bonus` decimal(9,2) NOT NULL DEFAULT '0.00',
  `subscr_last_reminded` datetime DEFAULT NULL,
  `subscr_free_arts_given` datetime DEFAULT NULL,
  `subscr_type` tinyint(3) unsigned NOT NULL DEFAULT '2',
  `subscr_period` tinyint(3) unsigned DEFAULT '1',
  `subscr_content` tinyint(3) unsigned DEFAULT '2',
  `subscr_genres` text,
  `s_subscr_text_authors` text,
  `subscr_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `s_subscr_text_pattern` text,
  `subscr_languages` text,
  `prefered_currency` char(3) DEFAULT NULL,
  `last_paymethod` tinyint(3) unsigned DEFAULT NULL,
  `utc_offset` char(6) DEFAULT NULL,
  `last_ip` varchar(15) DEFAUL...

Read more...

Revision history for this message
Dmitry Gribov (grib-d) wrote :

And it only gets worse, yep, with the current release as well. Duplicate entry creation is the most funny thing after we had external keys "on delete cascade" not working.
Any idea on when is this going to be fixed?

Revision history for this message
Seppo Jaakola (seppo-jaakola) wrote :

The parent bug: lp:1280896 was re-opened and a better fix has been pushed in for further testing. Changing this bug also to fix committed state for now.

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1660

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.