Comment 14 for bug 1243156

CREATE TABLE `arts_deploy` (
  `art` int(10) unsigned NOT NULL,
  `face` smallint(5) unsigned NOT NULL,
  `added` datetime NOT NULL,
  `is_hot` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`art`,`face`),
  KEY `fi` (`face`,`is_hot`),
  KEY `fa` (`face`,`added`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tasty_arts` (
  `user` int(10) unsigned NOT NULL,
  `art` int(10) unsigned NOT NULL,
  `face` smallint(5) unsigned NOT NULL,
  `updated` datetime NOT NULL,
  `rate` float unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `uaf` (`user`,`art`,`face`),
  KEY `ufra` (`user`,`face`,`rate`,`art`),
  KEY `userupd` (`user`,`face`,`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `spam_hits` (
  `user` int(10) unsigned NOT NULL,
  `spam` int(10) unsigned NOT NULL,
  `time` datetime NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`time`,`spam`,`user`,`type`),
  KEY `spam_type` (`spam`,`type`,`user`),
  KEY `user_type` (`user`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 CREATE TABLE `spam_packs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `send_start` datetime DEFAULT NULL,
  `send_end` datetime DEFAULT NULL,
  `keyparam` varchar(50) DEFAULT NULL,
  `s_desc` varchar(1000) DEFAULT NULL,
  `type` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index2` (`keyparam`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

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` int(10) unsigned 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) DEFAULT NULL,
  `socnet_last_reminded` datetime DEFAULT '0000-01-01 00:00:00',
  `moved_from` smallint(5) unsigned DEFAULT NULL,
  `moved_to` smallint(5) unsigned DEFAULT NULL,
  `subscribe_new_buys` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `partner` (`partner`),
  KEY `partner_valid_till` (`partner_valid_till`),
  KEY `abonement_expires` (`abonement_expires`,`abonement_left_clicks`),
  KEY `puid` (`s_puid`),
  KEY `s_phone` (`s_phone`),
  KEY `s_mail` (`s_mail`),
  KEY `login` (`login`)
) ENGINE=InnoDB AUTO_INCREMENT=46288879 DEFAULT CHARSET=utf8

CREATE TABLE `spam_tickets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(10) unsigned NOT NULL,
  `channel` int(10) unsigned NOT NULL,
  `created` datetime NOT NULL,
  `valid_from` datetime NOT NULL,
  `valid_till` datetime NOT NULL,
  `last_update` datetime NOT NULL,
  `sent` datetime DEFAULT NULL,
  `status` enum('requested','rejected','waiting','rejected_on_lag','sending','sent','error','token_invalid','delivered','opened') DEFAULT NULL,
  `annoy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned DEFAULT '1' COMMENT '1 - активно, NULL - неактивно',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_active` (`user`,`active`),
  KEY `channel` (`channel`),
  KEY `saac` (`status`,`active`,`annoy`,`created`),
  KEY `av` (`active`,`valid_till`),
  KEY `sal` (`status`,`active`,`last_update`),
  KEY `ucl` (`user`,`channel`,`last_update`),
  CONSTRAINT `spam_tickets_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `spam_tickets_ibfk_2` FOREIGN KEY (`channel`) REFERENCES `spam_channels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=813435217 DEFAULT CHARSET=utf8

"INSERT IGNORE INTO arts_deploy" is executed with auto commit on, so it is not in transaction at all. But this query may be HIGHLY concurrent on the node level and the cluster level too, and there is a huge chance of a conflict (insertion of the same data from several threads on several nodes).

"restarting just one node restores it" - yes, but now you have to restart the right node, any random does not fit. We restart the one which has "WSREP: BF lock wait long" in the log (only one does, other nores hang silently).

And, btw, we now restart mysql by killing it with -9 as correct shutdown leads to SST in most cases - it shuts down slowly and seem to have some problems with those hanging queries termination or with something else, hard to say. While after -9 node recovers just fine (except the case when we kill this way one of the last two nodes, as I mentioned in #1258880 - it requires extra administration).

And I have commented out the peace of code that OFTEN hanged the cluster by making mass and higly-concurrent queries like this:

INSERT INTO banner_hits (baner,time,host,hits)
VALUES (?,?,?,?)
ON DUPLICATE KEY UPDATE hits = hits + ?

CREATE TABLE `banner_hits` (
  `baner` int(10) unsigned NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `host` smallint(5) unsigned NOT NULL,
  `hits` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`baner`,`time`,`host`),
  KEY `host` (`host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I am not sure this is the killer-SQL, it looks like the higher is the load the more chances to get "WSREP: BF lock wait long", no matter what the load is (BAD quaries only show their nature when the cluster is loaded). Anyway, with this code removed hang outs got seldom. This queries sometimes were in the processlist with "wsrep in pre-commit stage" state for 20s and more, several in the row for the same baner ID.

Hopу all this helps.

We will turn logging on, I'll share what we will get during the next "WSREP: BF lock wait long".