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_modification_time bigint(20) DEFAULT NULL,
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_accounting_id varchar(20) DEFAULT NULL,
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_modification_time (last_modification_time)
);
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="ins.$prefix.$database.$table.`date +%F-%H-%M`.log"
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}_name_${counter}"
,"${prefix}_key_${counter}"
,"${prefix}_pw_${counter}"
,"${prefix}_descr_${counter}"
,"active"
,"uid_dbs_${counter}"
,"A_accessorLoadtestCk_1") ;
-- INSERT INTO ${table} (id,c1,c2) values ("$counter}","${prefix}_${counter}","${counter}")
-- DELETE from ${table} where reg_key="${prefix}_DBS_${counter}";
-- truncate table $table ;
SQL
let counter=$counter+$increment
done >$logfile 2>&1
------------end------------
There are wsrep certification failures happen when min <= 99.
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.