Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.2(EOL)
Description
I get complete table deadlock with simple test case like below:
1. setup default two-node galera cluster on the same host, e.g. on ports 3314 and 3315
2. Create table:
create table test.t select 1;
3. Start following concurrent load:
( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3314 -e "insert into t select $i1" test || : ; done ) &> /dev/null & |
( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3315 -e "insert into t select $i1" test || : ; done ) &> /dev/null & |
|
|
( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3314 -e "optimize table t" test & wait ; done ) &>/dev/null & |
( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3315 -e "optimize table t" test & wait ; done ) &>/dev/null & |
|
|
|
|
while true ; do |
mysql -h127.0.0.1 -P3314 -e 'show processlist' |
mysql -h127.0.0.1 -P3315 -e 'show processlist' |
mysql -h127.0.0.1 -P3314 -e 'select count(*) from t' test |
mysql -h127.0.0.1 -P3315 -e 'select count(*) from t' test |
sleep 2 |
done |
The cluster usually hangs up within 1 min, this is what observed:
(11:17 sql) > mysql -h 127.0.0.1 -P 3314 -uroot -e 'show processlist'
|
+-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+
|
| 1 | system user | | test | Sleep | 748 | Waiting for table flush | optimize table t | 0.000 |
|
| 2 | system user | | NULL | Sleep | 815 | wsrep aborter idle | NULL | 0.000 |
|
| 518 | a | localhost:37078 | test | Query | 748 | query end | insert into t select 487 | 0.000 |
|
| 519 | a | localhost:37080 | test | Query | 748 | Waiting for table flush | select count(*) from t | 0.000 |
|
| 520 | a | localhost:37122 | test | Query | 738 | checking permissions | optimize table t | 0.000 |
|
| 526 | root | localhost:38382 | NULL | Query | 0 | init | show processlist | 0.000 |
|
+-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+
|
(11:17 sql) > mysql -h 127.0.0.1 -P 3315 -uroot -e 'show processlist'
|
+-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+
|
| 1 | system user | | NULL | Sleep | 799 | wsrep aborter idle | NULL | 0.000 |
|
| 2 | system user | | NULL | Sleep | 748 | committed 992 | NULL | 0.000 |
|
| 517 | a | localhost:46558 | test | Query | 748 | query end | insert into t select 505 | 0.000 |
|
| 518 | a | localhost:46562 | test | Query | 738 | checking permissions | optimize table t | 0.000 |
|
| 524 | root | localhost:47822 | NULL | Query | 0 | init | show processlist | 0.000 |
|
+-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+
|
It is kid of strange to me that I cannot reproduce the problem with alter command below instead of OPTIMIZE:
alter table t engine=innodb, algorithm=copy
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Attachment | gdb.txt [ 43884 ] | |
| Description |
I get complete table deadlock with simple test case like below:
1. setup default two-node galera cluster on the same host, e.g. on ports 3314 and 3315 2. Create table: create table test.t select 1; 3. Start following concurrent load: {code:bash} ( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3314 -e "insert into t select $i1" test || : ; done ) &> /dev/null & ( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3315 -e "insert into t select $i1" test || : ; done ) &> /dev/null & ( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3314 -e "optimize table t" test & wait ; done ) &>/dev/null & ( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3315 -e "optimize table t" test & wait ; done ) &>/dev/null & while true ; do mysql -h127.0.0.1 -P3314 -e 'show processlist' mysql -h127.0.0.1 -P3315 -e 'show processlist' mysql -h127.0.0.1 -P3314 -e 'select count(*) from t' test mysql -h127.0.0.1 -P3315 -e 'select count(*) from t' test sleep 2 done {code} The cluster usually hangs up within 1 min, this is what observed: {noformat} (11:17 sql) > mysql -h 127.0.0.1 -P 3314 -uroot -e 'show processlist' +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+ | 1 | system user | | test | Sleep | 748 | Waiting for table flush | optimize table t | 0.000 | | 2 | system user | | NULL | Sleep | 815 | wsrep aborter idle | NULL | 0.000 | | 518 | a | localhost:37078 | test | Query | 748 | query end | insert into t select 487 | 0.000 | | 519 | a | localhost:37080 | test | Query | 748 | Waiting for table flush | select count(*) from t | 0.000 | | 520 | a | localhost:37122 | test | Query | 738 | checking permissions | optimize table t | 0.000 | | 526 | root | localhost:38382 | NULL | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+ (11:17 sql) > mysql -h 127.0.0.1 -P 3315 -uroot -e 'show processlist' +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+ | 1 | system user | | NULL | Sleep | 799 | wsrep aborter idle | NULL | 0.000 | | 2 | system user | | NULL | Sleep | 748 | committed 992 | NULL | 0.000 | | 517 | a | localhost:46558 | test | Query | 748 | query end | insert into t select 505 | 0.000 | | 518 | a | localhost:46562 | test | Query | 738 | checking permissions | optimize table t | 0.000 | | 524 | root | localhost:47822 | NULL | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+ {noformat} |
I get complete table deadlock with simple test case like below:
1. setup default two-node galera cluster on the same host, e.g. on ports 3314 and 3315 2. Create table: create table test.t select 1; 3. Start following concurrent load: {code:bash} ( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3314 -e "insert into t select $i1" test || : ; done ) &> /dev/null & ( for i1 in {1..100000} ; do mysql -h127.0.0.1 -P3315 -e "insert into t select $i1" test || : ; done ) &> /dev/null & ( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3314 -e "optimize table t" test & wait ; done ) &>/dev/null & ( for k1 in {1..1000}; do sleep 10; mysql -h127.0.0.1 -P3315 -e "optimize table t" test & wait ; done ) &>/dev/null & while true ; do mysql -h127.0.0.1 -P3314 -e 'show processlist' mysql -h127.0.0.1 -P3315 -e 'show processlist' mysql -h127.0.0.1 -P3314 -e 'select count(*) from t' test mysql -h127.0.0.1 -P3315 -e 'select count(*) from t' test sleep 2 done {code} The cluster usually hangs up within 1 min, this is what observed: {noformat} (11:17 sql) > mysql -h 127.0.0.1 -P 3314 -uroot -e 'show processlist' +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+ | 1 | system user | | test | Sleep | 748 | Waiting for table flush | optimize table t | 0.000 | | 2 | system user | | NULL | Sleep | 815 | wsrep aborter idle | NULL | 0.000 | | 518 | a | localhost:37078 | test | Query | 748 | query end | insert into t select 487 | 0.000 | | 519 | a | localhost:37080 | test | Query | 748 | Waiting for table flush | select count(*) from t | 0.000 | | 520 | a | localhost:37122 | test | Query | 738 | checking permissions | optimize table t | 0.000 | | 526 | root | localhost:38382 | NULL | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------------+------+---------+------+-------------------------+--------------------------+----------+ (11:17 sql) > mysql -h 127.0.0.1 -P 3315 -uroot -e 'show processlist' +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+ | 1 | system user | | NULL | Sleep | 799 | wsrep aborter idle | NULL | 0.000 | | 2 | system user | | NULL | Sleep | 748 | committed 992 | NULL | 0.000 | | 517 | a | localhost:46558 | test | Query | 748 | query end | insert into t select 505 | 0.000 | | 518 | a | localhost:46562 | test | Query | 738 | checking permissions | optimize table t | 0.000 | | 524 | root | localhost:47822 | NULL | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------------+------+---------+------+----------------------+--------------------------+----------+ {noformat} It is kid of strange to me that I cannot reproduce the problem with alter command below instead of OPTIMIZE: alter table t engine=innodb, algorithm=copy |
| Link |
This issue relates to |
| Link |
This issue relates to |
smaller lock_wait_timeout isn't really helping as well: with lock_wait_timeout=30 client shows timeout error trying to read table, but other locked connections are not affected:
+ cluster1/sql.sh 'select count(*) from t'
|
m8 :944
|
m9 :ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
|
+ sleep 2
|
+ true
|
+ m8-10.1.25/sql.sh show processlist
|
1 system user NULL Sleep 128 committed 928 NULL 0.000
|
2 system user NULL Sleep 184 wsrep aborter idle NULL 0.000
|
505 root localhost test Query 127 query end insert into t select 485 0.000
|
506 root localhost test Query 117 checking permissions optimize table t 0.000
|
513 root localhost test Query 0 init show processlist 0.000
|
+ m9-10.1.25/sql.sh show processlist
|
1 system user test Sleep 128 Waiting for table flush optimize table t 0.000
|
2 system user NULL Sleep 168 wsrep aborter idle NULL 0.000
|
487 root localhost test Query 128 query end insert into t select 465 0.000
|
490 root localhost test Query 118 checking permissions optimize table t 0.000
|
499 root localhost test Query 0 init show processlist 0.000
|
+ cluster1/sql.sh 'select count(*) from t'
|
m8 :944
|
m9 :ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
|
|
|
+ sleep 2
|
+ true
|
+ m8-10.1.25/sql.sh show processlist
|
1 system user NULL Sleep 160 committed 928 NULL 0.000
|
2 system user NULL Sleep 216 wsrep aborter idle NULL 0.000
|
505 root localhost test Query 159 query end insert into t select 485 0.000
|
506 root localhost test Query 149 checking permissions optimize table t 0.000
|
515 root localhost test Query 0 init show processlist 0.000
|
+ m9-10.1.25/sql.sh show processlist
|
1 system user test Sleep 160 Waiting for table flush optimize table t 0.000
|
2 system user NULL Sleep 200 wsrep aborter idle NULL 0.000
|
487 root localhost test Query 160 query end insert into t select 465 0.000
|
490 root localhost test Query 150 checking permissions optimize table t 0.000
|
501 root localhost test Query 0 init show processlist 0.000
|
+ cluster1/sql.sh 'select count(*) from t'
|
m8 :944
|
m9 :ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
|
|
|
+ sleep 2
|
+ true
|
+ m8-10.1.25/sql.sh show processlist
|
1 system user NULL Sleep 192 committed 928 NULL 0.000
|
2 system user NULL Sleep 248 wsrep aborter idle NULL 0.000
|
505 root localhost test Query 191 query end insert into t select 485 0.000
|
506 root localhost test Query 181 checking permissions optimize table t 0.000
|
517 root localhost test Query 0 init show processlist 0.000
|
+ m9-10.1.25/sql.sh show processlist
|
1 system user test Sleep 192 Waiting for table flush optimize table t 0.000
|
2 system user NULL Sleep 232 wsrep aborter idle NULL 0.000
|
487 root localhost test Query 192 query end insert into t select 465 0.000
|
490 root localhost test Query 182 checking permissions optimize table t 0.000
|
503 root localhost test Query 0 init show processlist 0.000
|
+ cluster1/sql.sh 'select count(*) from t'
|
m8 :944
|
m9 :ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
|
|
|
+ sleep 2
|
+ true
|
+ m8-10.1.25/sql.sh show processlist
|
1 system user NULL Sleep 224 committed 928 NULL 0.000
|
2 system user NULL Sleep 280 wsrep aborter idle NULL 0.000
|
505 root localhost test Query 223 query end insert into t select 485 0.000
|
506 root localhost test Query 213 checking permissions optimize table t 0.000
|
519 root localhost test Query 0 init show processlist 0.000
|
+ m9-10.1.25/sql.sh show processlist
|
1 system user test Sleep 224 Waiting for table flush optimize table t 0.000
|
2 system user NULL Sleep 264 wsrep aborter idle NULL 0.000
|
487 root localhost test Query 224 query end insert into t select 465 0.000
|
490 root localhost test Query 214 checking permissions optimize table t 0.000
|
505 root localhost test Query 0 init show processlist 0.000
|
| Assignee | Andrei Elkin [ elkin ] | Stepan Patryshev [ stepan.patryshev ] |
| Labels | need_feedback |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.2.6 [ 22527 ] | |
| Affects Version/s | 10.1.25 [ 22542 ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Labels | need_feedback | need_verification |
| Assignee | Stepan Patryshev [ stepan.patryshev ] | Ramesh Sivaraman [ JIRAUSER48189 ] |
| Workflow | MariaDB v3 [ 81783 ] | MariaDB v4 [ 140370 ] |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Won't Fix [ 2 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
See also
MDEV-10245, which is also about concurrent OPTIMIZE causing a deadlock. Stack traces are different, though.