Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.11
-
None
-
Windows 2008 R1 32bit - running on VMware ESXi 6.0 in a Vcenter cluster.
Description
Full-log-file.txt
Our Primary Live data factory at 05:25 started to hang
We also have a SLAVE; with 0 seconds delay to master; and this experienced EXACTLY the same issue; and this Slave is on a totally different Virtual machine and different backend.
When the problem started 05:25; user traffic and actual database activity was extremely low.
new connections were able to logon to the database ok; but any activity against the INNODB database - datafactoryDB just hung and messages such as the below appeared in the MariaDB log files:-
2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait:
--Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait:
--Thread 908 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait:
--Thread 4860 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore:
S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file buf0buf.cc line 4136
Last time write locked in file buf0buf.cc line 5363
2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait:
--Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait:
--Thread 1460 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.cc line 3568
Last time write locked in file buf0buf.cc line 5363
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 2448 has waited at ibuf0ibuf.cc line 2532 for 240.00 seconds the semaphore:
S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 1688 has waited at buf0flu.cc line 1246 for 232.00 seconds the semaphore:
SX-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 908 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 4860 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore:
S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file buf0buf.cc line 4136
Last time write locked in file buf0buf.cc line 5363
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait:
--Thread 1460 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.cc line 3568
Last time write locked in file buf0buf.cc line 5363
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending reads 0, writes 0
=====================================
2018-03-28 05:25:59 0x98c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 64 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 37468 srv_active, 0 srv_shutdown, 56188 srv_idle
srv_master_thread log flush and writes: 93655
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 626368
--Thread 2448 has waited at ibuf0ibuf.cc line 2532 for 249.00 seconds the semaphore:
S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 1688 has waited at buf0flu.cc line 1246 for 241.00 seconds the semaphore:
SX-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 250.00 seconds the semaphore:
Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
--Thread 908 has waited at ibuf0ibuf.cc line 4578 for 250.00 seconds the semaphore:
S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471
a writer (thread id 1460) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 4860 has waited at buf0buf.cc line 4136 for 250.00 seconds the semaphore:
S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file buf0buf.cc line 4136
Last time write locked in file buf0buf.cc line 5363
--Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 250.00 seconds the semaphore:
Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2
--Thread 1460 has waited at buf0buf.cc line 4136 for 250.00 seconds the semaphore:
S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.cc line 3568
Last time write locked in file buf0buf.cc line 5363
OS WAIT ARRAY INFO: signal count 520921
RW-shared spins 0, rounds 798992, OS waits 390861
RW-excl spins 0, rounds 1141727, OS waits 14298
RW-sx spins 2663, rounds 33568, OS waits 503
Spin rounds per wait: 798992.00 RW-shared, 1141727.00 RW-excl, 12.61 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 137843677
Purge done for trx's n:o < 137843524 undo n:o < 0 state: running but idle
History list length 15
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281475041773368, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041772808, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041772248, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041771688, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041771128, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041770568, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041769448, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041768888, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041768328, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041759368, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041755448, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041753768, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041753208, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041739208, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041738088, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041725208, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041724088, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041767768, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041767208, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041766648, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041766088, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041765528, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041764968, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475041764408, not started
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is blocked by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Attachment | Full-log-file.txt [ 45413 ] |
| Description |
Our Primary Live data factory at 05:25 started to hang
We also have a SLAVE; with 0 seconds delay to master; and this experienced EXACTLY the same issue; and this Slave is on a totally different Virtual machine and different backend. When the problem started 05:25; user traffic and actual database activity was extremely low. new connections were able to logon to the database ok; but any activity against the INNODB database - datafactoryDB just hung and messages such as the below appeared in the MariaDB log files:- 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 908 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 4860 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0buf.cc line 4136 Last time write locked in file buf0buf.cc line 5363 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 1460 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.cc line 3568 Last time write locked in file buf0buf.cc line 5363 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 2448 has waited at ibuf0ibuf.cc line 2532 for 240.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1688 has waited at buf0flu.cc line 1246 for 232.00 seconds the semaphore: SX-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 908 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 4860 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0buf.cc line 4136 Last time write locked in file buf0buf.cc line 5363 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1460 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.cc line 3568 Last time write locked in file buf0buf.cc line 5363 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending reads 0, writes 0 ===================================== 2018-03-28 05:25:59 0x98c INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 64 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 37468 srv_active, 0 srv_shutdown, 56188 srv_idle srv_master_thread log flush and writes: 93655 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 626368 --Thread 2448 has waited at ibuf0ibuf.cc line 2532 for 249.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 1688 has waited at buf0flu.cc line 1246 for 241.00 seconds the semaphore: SX-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 250.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 --Thread 908 has waited at ibuf0ibuf.cc line 4578 for 250.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 4860 has waited at buf0buf.cc line 4136 for 250.00 seconds the semaphore: S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0buf.cc line 4136 Last time write locked in file buf0buf.cc line 5363 --Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 250.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 --Thread 1460 has waited at buf0buf.cc line 4136 for 250.00 seconds the semaphore: S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.cc line 3568 Last time write locked in file buf0buf.cc line 5363 OS WAIT ARRAY INFO: signal count 520921 RW-shared spins 0, rounds 798992, OS waits 390861 RW-excl spins 0, rounds 1141727, OS waits 14298 RW-sx spins 2663, rounds 33568, OS waits 503 Spin rounds per wait: 798992.00 RW-shared, 1141727.00 RW-excl, 12.61 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 137843677 Purge done for trx's n:o < 137843524 undo n:o < 0 state: running but idle History list length 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281475041773368, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041772808, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041772248, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041771688, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041771128, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041770568, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041769448, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041768888, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041768328, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041759368, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041755448, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041753768, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041753208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041739208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041738088, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041725208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041724088, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041767768, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041767208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041766648, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041766088, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041765528, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041764968, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041764408, not started |
[^Full-log-file.txt] Our Primary Live data factory at 05:25 started to hang
We also have a SLAVE; with 0 seconds delay to master; and this experienced EXACTLY the same issue; and this Slave is on a totally different Virtual machine and different backend. When the problem started 05:25; user traffic and actual database activity was extremely low. new connections were able to logon to the database ok; but any activity against the INNODB database - datafactoryDB just hung and messages such as the below appeared in the MariaDB log files:- 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 908 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 4860 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0buf.cc line 4136 Last time write locked in file buf0buf.cc line 5363 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Warning] InnoDB: A long semaphore wait: --Thread 1460 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.cc line 3568 Last time write locked in file buf0buf.cc line 5363 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 2448 has waited at ibuf0ibuf.cc line 2532 for 240.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1688 has waited at buf0flu.cc line 1246 for 232.00 seconds the semaphore: SX-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 908 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 4860 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0buf.cc line 4136 Last time write locked in file buf0buf.cc line 5363 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 2018-03-28 5:25:50 2440 [Note] InnoDB: A semaphore wait: --Thread 1460 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore: S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.cc line 3568 Last time write locked in file buf0buf.cc line 5363 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending reads 0, writes 0 ===================================== 2018-03-28 05:25:59 0x98c INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 64 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 37468 srv_active, 0 srv_shutdown, 56188 srv_idle srv_master_thread log flush and writes: 93655 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 626368 --Thread 2448 has waited at ibuf0ibuf.cc line 2532 for 249.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 1688 has waited at buf0flu.cc line 1246 for 241.00 seconds the semaphore: SX-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 1564 has waited at ibuf0ibuf.cc line 4394 for 250.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 --Thread 908 has waited at ibuf0ibuf.cc line 4578 for 250.00 seconds the semaphore: S-lock on RW-latch at 0431E114 created in file buf0buf.cc line 1471 a writer (thread id 1460) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file ibuf0ibuf.cc line 4578 Last time write locked in file mtr0mtr.ic line 147 --Thread 4860 has waited at buf0buf.cc line 4136 for 250.00 seconds the semaphore: S-lock on RW-latch at 04280A54 created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0buf.cc line 4136 Last time write locked in file buf0buf.cc line 5363 --Thread 1480 has waited at ibuf0ibuf.cc line 4394 for 250.00 seconds the semaphore: Mutex at 01F93E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2 --Thread 1460 has waited at buf0buf.cc line 4136 for 250.00 seconds the semaphore: S-lock on RW-latch at 0CB3065C created in file buf0buf.cc line 1471 a writer (thread id 0) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.cc line 3568 Last time write locked in file buf0buf.cc line 5363 OS WAIT ARRAY INFO: signal count 520921 RW-shared spins 0, rounds 798992, OS waits 390861 RW-excl spins 0, rounds 1141727, OS waits 14298 RW-sx spins 2663, rounds 33568, OS waits 503 Spin rounds per wait: 798992.00 RW-shared, 1141727.00 RW-excl, 12.61 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 137843677 Purge done for trx's n:o < 137843524 undo n:o < 0 state: running but idle History list length 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281475041773368, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041772808, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041772248, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041771688, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041771128, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041770568, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041769448, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041768888, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041768328, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041759368, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041755448, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041753768, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041753208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041739208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041738088, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041725208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041724088, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041767768, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041767208, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041766648, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041766088, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041765528, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041764968, not started 0 lock struct(s), heap size 1080, 0 row lock(s) ---TRANSACTION 281475041764408, not started |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Link |
This issue relates to |
Further to the above; I have been able to re-produce the issue on our Pre-production environment; in that
if do a delete from a table with only 2.5m records (partitioned or non-partitioned tables suffer the issue) and this delete takes a while; and during this delete if we have user traffic "inserting records" in this table as this delete is running; we see the above errors; it completely corrupts this INNODB Table. Definition of the table is:-
CREATE TABLE `rjis_server_enq_perf` (
`QUERY_START` datetime NOT NULL,
`QUERY_TYPE` double(6,0) NOT NULL,
`QUERY_END` datetime NOT NULL,
`SERVER_NODE_NAME` varchar(40) COLLATE latin1_bin NOT NULL,
`CLIENT_NODE_NAME` varchar(40) COLLATE latin1_bin NOT NULL,
`TOTAL_IJQ_TIME` double(8,0) NOT NULL,
`ACCOMODATION_TIME` double(8,0) NOT NULL,
`ENRG_TIME` double(8,0) NOT NULL,
`TIMETABLE_TIME` double(8,0) NOT NULL,
`SUPPLEMENTS_TIME` double(8,0) NOT NULL,
`RESTRICTIONS_TIME` double(8,0) NOT NULL,
`FARES_TIME` double(8,0) NOT NULL,
`EXTERNAL_TIME` double(8,0) DEFAULT NULL,
`USER_ID` varchar(8) COLLATE latin1_bin DEFAULT NULL,
`PARTITION_NUMBER` double(2,0) DEFAULT NULL,
`BULLETINS_TIME` double(8,0) DEFAULT NULL,
`EASEMENTS_TIME` double(8,0) DEFAULT NULL,
`ACCOMMODATION_TIME_INTERNAL` double(8,0) DEFAULT NULL,
`NRS_TIMEOUT` double(1,0) DEFAULT NULL,
`HTTP_STATUS` double(10,0) DEFAULT NULL,
`NRS_RES` double(3,0) DEFAULT NULL,
`JETT_CALLS` double(4,0) DEFAULT NULL,
`NUM_JOURNEYS` double(4,0) DEFAULT NULL,
`NRS_CALLS` double(4,0) DEFAULT NULL,
`TRANSLATED_USER_ID` varchar(8) COLLATE latin1_bin DEFAULT NULL,
KEY `RSEP_IDX_TYPE` (`QUERY_START`,`QUERY_END`,`QUERY_TYPE`),
KEY `RSEP_IDX_SERVER` (`SERVER_NODE_NAME`,`QUERY_START`,`QUERY_END`,`QUERY_TYPE`),
KEY `RSEP_RQT_FK` (`QUERY_TYPE`),
CONSTRAINT `RSEP_RQT_FK` FOREIGN KEY (`QUERY_TYPE`) REFERENCES `rjis_query_type` (`QUERY_TYPE`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
Furthermore; if the table is inserting lots of data with importing from mysqldump .SQL file and users are inserting a few records as this is happening; we also see the above scenario;
I am thus finding MariaDB is not handling this at all well; how can I stop this happening by making any configuration changes to MariaDB my.ini; or is it fixed in later version of 10.2.11?
thank you for your help.
I am guessing I may need to change these settings to stop the above happening; any advice please?
innodb_max_purge_lag 0
innodb_max_purge_lag_delay 0
innodb_purge_batch_size 300
innodb_purge_rseg_truncate_frequency 128
innodb_purge_threads 4
relay_log_purge ON
Many thanks Alice Sherepa; do we have a proposed fixed date \ release ? - or any kinda of workaround that can help to mitigate the problem?
thank you.
slaterss can you take a process dump, if you're able to reproduce it?
e.g procdump mysqld.exe
with procdump from https://docs.microsoft.com/en-us/sysinternals/downloads/procdump
And then attach the dump here.
I see some FOREIGN KEY. I wonder if this could be related to MDEV-15199 or MDEV-14222.
If not, I would suggest SET GLOBAL innodb_change_buffering=none to disable the change buffer.
umoser has a customer who got a similar hang with change buffering on Windows, maybe 2 months ago. Was that one ever repeated in a controlled fashion?
HI Thank you all for your help.
Many thanks Vladislav Vaintroub; I was only able to reproduce it in our pre-production environment; which has to be up and running most of the day also; so I can't get any more downtime on that DB either given the impact of many hours already today for both production and pre-prod environments. I will see if I can reproduce it in our labs away from any users.
Marko - many thanks; I will set the innodb_change_buffering=none; and let you know how I get on.
slaterss,still, if you can try to provide a dump of any hang of that kind of a hang, this would immensely helpful.
THanks Vladislav will try for sure. I see others have had similar issues and I am hoping with all these reports this problem gets fixed soon.
| Labels | need_feedback |
| Attachment | mysqld.exe_180406_035217.zip [ 45442 ] |
Hi
I have been able to re-produce the issue in our test environment.
*as requested find attached a procdump of mysqld.exe *
the errors reported in the mariadb log file are the same
2018-04-06 3:47:00 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:00 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:00 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 241.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2056 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
2018-04-06 3:47:00 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2088 has waited at buf0flu.cc line 1246 for 241.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 241.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 2676 has waited at ibuf0ibuf.cc line 2720 for 240.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 241.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 241.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 2056 has waited at buf0buf.cc line 4136 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 2088 has waited at buf0flu.cc line 1246 for 241.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 2680 has waited at ibuf0ibuf.cc line 4578 for 239.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 3780 has waited at ibuf0ibuf.cc line 3460 for 123.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:00 2668 [Note] InnoDB: A semaphore wait:
--Thread 3792 has waited at dict0dict.cc line 372 for 32.00 seconds the semaphore:
S-lock on RW-latch at 15213D10 created in file dict0dict.cc line 296
a writer (thread id 2680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.cc line 372
Last time write locked in file dict0dict.cc line 375
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending reads 0, writes 0=====================================
2018-04-06 03:47:04 0xa70 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 954 srv_active, 0 srv_shutdown, 57987 srv_idle
srv_master_thread log flush and writes: 58940
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28629
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 245.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 2676 has waited at ibuf0ibuf.cc line 2720 for 244.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 245.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 245.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 245.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 2056 has waited at buf0buf.cc line 4136 for 245.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
--Thread 2088 has waited at buf0flu.cc line 1246 for 245.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 2680 has waited at ibuf0ibuf.cc line 4578 for 243.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 3780 has waited at ibuf0ibuf.cc line 3460 for 127.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
--Thread 3792 has waited at dict0dict.cc line 372 for 36.00 seconds the semaphore:
S-lock on RW-latch at 15213D10 created in file dict0dict.cc line 296
a writer (thread id 2680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.cc line 372
Last time write locked in file dict0dict.cc line 375
OS WAIT ARRAY INFO: signal count 28757
RW-shared spins 0, rounds 49602, OS waits 22173
RW-excl spins 0, rounds 57330, OS waits 1202
RW-sx spins 2624, rounds 8685, OS waits 154
Spin rounds per wait: 49602.00 RW-shared, 57330.00 RW-excl, 3.31 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1196017
Purge done for trx's n:o < 1196016 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281475039826344, not started returning various info to MariaDB
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475039825784, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475039825224, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475039823544, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 281475039822984, not started
0 lock struct(s), heap size 1080, 0 row lock(s)
---TRANSACTION 1196016, ACTIVE 127 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 31, locked 31
1 lock struct(s), heap size 1080, 0 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 0, query id 3780 10.208.1.120 rjis
/* */ INSERT INTO RJIS_SERVER_ENQ_PERF (QUERY_TYPE, QUERY_START, QUERY_END, SERVER_NODE_NAME, CLIENT_NODE_NAME, USER_ID, TOTAL_IJQ_TIME, ACCOMODATION_TIME, ENRG_TIME, TIMETABLE_TIME, SUPPLEMENTS_TIME, RESTRICTIONS_TIME, FARES_TIME, EXTERNAL_TIME, BULLETINS_TIME, EASEMENTS_TIME, ACCOMMODATION_TIME_INTERNAL, NRS_TIMEOUT, HTTP_STATUS, NRS_RES, JETT_CALLS, NUM_JOURNEYS, NRS_CALLS, TRANSLATED_USER_ID) VALUES (61,'2018-04-06 03:44:45','2018-04-06 03:44:45','AWVM2','JQI','ZZMON01',15,0,0,15,0,0,0,0,0,0,0,0,0,0,0,0,0,null)
---TRANSACTION 1196011, ACTIVE 258 sec updating or deleting, thread declared inside InnoDB 56
mysql tables in use 31, locked 31
6648 lock struct(s), heap size 597608, 793339 row lock(s), undo log entries 253931
MySQL thread id 344, OS thread handle 0, query id 3924 127.0.0.1 liverjis updating
delete from rjis_server_enq_perf
where query_start between '2018-03-19' and '2018-03-24'
and server_node_name in ('JPGUISX3','JPGUISX4','JPGUIT3','JJQISX2')
--------
FILE I/O
--------
I/O thread 0 state: complete io for buf page (insert buffer thread)
I/O thread 1 state: complete io for buf page (log thread)
I/O thread 2 state: complete io for buf page (read thread)
I/O thread 3 state: complete io for buf page (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
107870 OS file reads, 422913 OS file writes, 16709 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
InnoDB: ###### Diagnostic info printed to the standard error stream
2018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 272.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2676 has waited at ibuf0ibuf.cc line 2720 for 271.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 272.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 272.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 272.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2056 has waited at buf0buf.cc line 4136 for 272.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
2018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2088 has waited at buf0flu.cc line 1246 for 272.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2680 has waited at ibuf0ibuf.cc line 4578 for 270.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 272.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2676 has waited at ibuf0ibuf.cc line 2720 for 271.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 272.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 272.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 272.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2056 has waited at buf0buf.cc line 4136 for 272.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2088 has waited at buf0flu.cc line 1246 for 272.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2680 has waited at ibuf0ibuf.cc line 4578 for 270.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 3780 has waited at ibuf0ibuf.cc line 3460 for 154.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 3792 has waited at dict0dict.cc line 372 for 63.00 seconds the semaphore:
S-lock on RW-latch at 15213D10 created in file dict0dict.cc line 296
a writer (thread id 2680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.cc line 372
Last time write locked in file dict0dict.cc line 375
2018-04-06 3:47:31 2668 [Note] InnoDB: A semaphore wait:
--Thread 2672 has waited at ibuf0ibuf.cc line 4921 for 27.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending reads 0, writes 0
InnoDB: ###### Diagnostic info printed to the standard error stream
2018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 303.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2676 has waited at ibuf0ibuf.cc line 2720 for 302.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 303.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 303.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 303.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2056 has waited at buf0buf.cc line 4136 for 303.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
2018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2088 has waited at buf0flu.cc line 1246 for 303.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Warning] InnoDB: A long semaphore wait:
--Thread 2680 has waited at ibuf0ibuf.cc line 4578 for 301.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2060 has waited at ibuf0ibuf.cc line 4578 for 303.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2676 has waited at ibuf0ibuf.cc line 2720 for 302.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 1188 has waited at ibuf0ibuf.cc line 4394 for 303.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2052 has waited at ibuf0ibuf.cc line 4394 for 303.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 22018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 3924 has waited at ibuf0ibuf.cc line 3460 for 303.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2056 has waited at buf0buf.cc line 4136 for 303.00 seconds the semaphore:
S-lock on RW-latch at 0422722C created in file buf0buf.cc line 1471
a writer (thread id 0) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 986
Last time write locked in file buf0buf.cc line 5363
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2088 has waited at buf0flu.cc line 1246 for 303.00 seconds the semaphore:
SX-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2680 has waited at ibuf0ibuf.cc line 4578 for 301.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 3780 has waited at ibuf0ibuf.cc line 3460 for 185.00 seconds the semaphore:
S-lock on RW-latch at 04251D24 created in file buf0buf.cc line 1471
a writer (thread id 2056) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file ibuf0ibuf.cc line 4578
Last time write locked in file mtr0mtr.ic line 147
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 3792 has waited at dict0dict.cc line 372 for 94.00 seconds the semaphore:
S-lock on RW-latch at 15213D10 created in file dict0dict.cc line 296
a writer (thread id 2680) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file dict0dict.cc line 372
Last time write locked in file dict0dict.cc line 375
2018-04-06 3:48:02 2668 [Note] InnoDB: A semaphore wait:
--Thread 2672 has waited at ibuf0ibuf.cc line 4921 for 58.00 seconds the semaphore:
Mutex at 00DC3E20, Mutex IBUF created ibuf0ibuf.cc:516, lock var 2InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending reads 0, writes 0
InnoDB: ###### Diagnostic info printed to the standard error stream
to re-produce the issue, I had a 31 (by day) partition table - called rjis_server_enq_perf with only 14m rows in it.
I then had one sql session that was doing a delete as follows:-
delete from rjis_server_enq_perf
where query_start between '2018-03-19' and '2018-03-24'
and server_node_name in ('JPGUISX3','JPGUISX4','JPGUIT3','JJQISX2');
I attempted to insert a SINGLE row in another session; and this after 50 seconds just said lock wait exceeded; and then everything started to hang and I could not use the database.
then in one session was inserting a single row at a time in it;
even trying to open a new connection; or do anything; everything just hangs now; cannot shutdown the database. cannot do anything; so I had to kill the process mysqld.exe and trash the datafactorydb database.
please provide a fix asap; as this is a real showstopper for us.
| Priority | Critical [ 2 ] | Blocker [ 1 ] |
when I try and start the db afterwards; the DB starts, but I get the below errors in the log file and again I can't shutdown the database or do anything with the database.
2018-04-06 4:07:36 3328 [Note] Plugin 'FEEDBACK' is disabled.
2018-04-06 4:07:36 3328 [Note] Server socket created on IP: '::'.
2018-04-06 4:07:36 3328 [ERROR] mysqld.exe: Table '.\mysql\user' is marked as crashed and should be repaired
2018-04-06 4:07:36 3328 [ERROR] mysqld.exe: Table 'user' is marked as crashed and should be repaired
2018-04-06 4:07:36 3328 [Warning] Checking table: '.\mysql\user'
2018-04-06 4:07:36 2964 [Note] InnoDB: Buffer pool(s) load completed at 180406 4:07:36
2018-04-06 4:07:36 3328 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly
2018-04-06 4:07:36 3328 [ERROR] mysqld.exe: Table '.\mysql\db' is marked as crashed and should be repaired
2018-04-06 4:07:36 3328 [ERROR] mysqld.exe: Table 'db' is marked as crashed and should be repaired
2018-04-06 4:07:36 3328 [Warning] Checking table: '.\mysql\db'
2018-04-06 4:07:36 3328 [ERROR] mysql.db: 1 client is using or hasn't closed the table properly
2018-04-06 4:07:36 3328 [Warning] 'user' entry 'rjis@asjp2df' ignored in --skip-name-resolve mode.
2018-04-06 4:07:36 3328 [Warning] 'db' entry 'datafactorydb rjis@asjp2df' ignored in --skip-name-resolve mode.
2018-04-06 4:07:36 3328 [Note] Reading of all Master_info entries succeded
2018-04-06 4:07:36 3328 [Note] Added new Master_info '' to hash table
2018-04-06 4:07:36 3328 [Note] D:\MariaDB\bin\mysqld.exe: ready for connections.
Version: '10.2.11-MariaDB-log' socket: '' port: 3306 mariadb.org binary distribution
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 342018-04-06 4:08:31 3424 [Note] D:\MariaDB\bin\mysqld.exe (unknown): Normal shutdown
even attempting to start the db with innodb_force_recovery = 1 and other various values; the db is not working and shutting down just hangs; and everything seems to hang on the db;
I went with innodb_force_recovery=3; and then ran a mysqlcheck on -all-databases and it reported all ok and database seemed to work again now; and shutdown ok etc..
please provide a fix; as a delete from a small 600k records from a 14m row table causes this issue.
| Labels | need_feedback |
| Assignee | Vladislav Vaintroub [ wlad ] |
| Attachment | stacks.txt [ 45445 ] |
| Component/s | Platform Windows [ 10138 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Component/s | Server [ 13907 ] | |
| Fix Version/s | 10.2.15 [ 23006 ] | |
| Fix Version/s | 10.3.6 [ 23003 ] | |
| Summary | Primary and Slave MariaDB 10.2.11 W2k8 32bit DBs HANG with A long Seampore wait | Long semaphore wait : deadlock in Innodb IO code, caused by change buffering. |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
HI, great news this is fixed in 10.2.15; is it possible to get an early release of 10.2.15 for Windows 32bit please so I can release to our environments please?
many thanks
we do not provide nightly (or any prerelease) Windows builds to public. If you like to test it slaterss, and/or it is urgent, you'd need to find a place a´where we can upload the build. JIRA does not like very big attachments.
slaterss, or, you can build yourself, we provide extensive instructions here https://mariadb.com/kb/en/library/Building_MariaDB_on_Windows/
slaterss, you can get the latest 10.2 32-bit Windows builds from here: ftp://ftp.askmonty.org/public/mdev15707_fix
Hi, I have upgraded my test DB to 10.2.15; and I can happily report the semaphore wait \ db hanging issue no longer occurs with the same test scenario and data; many thanks for the super quick fix on this bug,
Cheers
| Link |
This issue is blocked by |
| Workflow | MariaDB v3 [ 86268 ] | MariaDB v4 [ 154042 ] |
PLEASE PLEASE PLEASE HELP
I love MariaDB but this is the first time we have had a problem that has totally got me stumped.
Full Log file attached from the primary.
Can you please advise what may of caused this; and how to mitigate against it please; as I am totally stuck.
tried restarted the database; problem persists.
I even issued the command drop database datafactorydb; it just hung for 10minutes and did nothing.
I could not recover the database easily; in the end given the time constraints I recreated the entire data base from scratch and reinstalled the MariaDB s/w from scratch also.
I am very worried though with this; as there i have no clue what caused this problem. - please please please please please help.
I checked with our hosting supplier; no issues on the ESXi hosts or clusters; and all looking ok other on the VMs except for these database issues.
for now I have set our slave to have a master_delay=10800 (3hrs) so to give us some breathing space in case it propagates again to the slave.