unavoidable wsrep certification failures with character unique key
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership | Status tracked in 5.6 | |||||
5.5 |
Fix Committed
|
Undecided
|
Seppo Jaakola | |||
5.6 |
Fix Released
|
Undecided
|
Seppo Jaakola | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
High
|
Unassigned | |||
5.6 |
Fix Released
|
High
|
Unassigned |
Bug Description
Given table:
CREATE TABLE reg_0 (
reg_name varchar(127) NOT NULL,
reg_key varchar(64) NOT NULL DEFAULT '',
reg_secret varchar(128) NOT NULL,
description varchar(255) DEFAULT NULL,
reg_status varchar(16) NOT NULL,
user_id varchar(255) NOT NULL,
owner_id varchar(64) DEFAULT NULL,
category varchar(64) DEFAULT NULL,
creation_time bigint(20) DEFAULT NULL,
last_modificati
app_platform varchar(64) DEFAULT NULL,
company_name varchar(64) DEFAULT NULL,
company_website varchar(256) DEFAULT NULL,
customer_lead_id varchar(36) DEFAULT NULL,
usage_type varchar(36) DEFAULT NULL,
country char(3) DEFAULT NULL,
state char(2) DEFAULT NULL,
customer_
has_note tinyint(4) DEFAULT NULL,
UNIQUE KEY unq_reg_key (reg_key),
PRIMARY KEY (reg_key),
KEY reg_key (reg_key),
KEY user_id (user_id),
KEY reg_status (reg_status),
KEY owner_id (owner_id),
KEY category (category),
KEY last_modificati
);
Multiple clients can not insert into this table without getting wsrep certification failures (reported as deadlock at commit) even though the inserts do no conflict for the given unique key. This problem ONLY occurs with alphanumeric unique keys. An auto_increment works properly.
Here is a script to demonstrate the problem:
------Insert Script foo.sh-----
#!/bin/bash
min=$1
max=$2
#!/bin/bash
min=$1
max=$2
database=test
increment=$3
prefix=`hostname -a`
counter=$min
table=reg_D
# for table in reg_0 reg_A reg_B reg_C ;do
#!/bin/bash
min=$1
max=$2
database=test
increment=$3
prefix=`hostname -a`
counter=$min
table=reg_F
# for table in reg_0 reg_A reg_B reg_C ;do
logfile=
while [ ${counter} -lt ${max} ] ;do
mysql --debug-info=true -vvvvvvvvvv $database <
-- SET GLOBAL AUTOCOMMIT=off;
INSERT INTO
${table} (reg_name
, reg_key
, reg_secret
, description
, reg_status
, user_id
, owner_id)
VALUES ("${prefix}
,"${prefix}
,"${prefix}
,"${prefix}
,"active"
,"uid_dbs_
,"A_accessorLoa
-- INSERT INTO ${table} (id,c1,c2) values ("$counter}
-- DELETE from ${table} where reg_key=
-- truncate table $table ;
SQL
let counter=
done >$logfile 2>&1
-------
There are wsrep certification failures happen when min <= 99.
Changed in codership-mysql: | |
assignee: | nobody → Seppo Jaakola (seppo-jaakola) |
status: | New → In Progress |
milestone: | none → 5.6.19-25.6 |
Just to be clear, there are no duplicate key violations being generated by the script, but wsrep certification fails regardless.
I will try to come up with a simplified test case.