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

Bug #1299116 reported by Teemu Ollakka on 2014-03-28
32
This bug affects 7 people
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Status tracked in 5.6
5.5
High
Seppo Jaakola
5.6
High
Seppo Jaakola
Percona XtraDB Cluster
Status tracked in 5.6
5.5
Undecided
Unassigned
5.6
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.

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.

Seppo Jaakola (seppo-jaakola) wrote :

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

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

Dmitry Gribov (grib-d) wrote :

Resolved

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...

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?

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.

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

Duplicates of this bug

Other bug subscribers