This is an automated archive.

The original was posted on /r/mysql by /u/jbostoen on 2023-08-07 15:36:37+00:00.

---

Since a couple of days, I have “lock wait timeout exceeded” when running certain UPDATE statements; and even an INSERT one (for a brand new table). It seemed to have started happening since upgrading from (probably) MySQL 8.0.31 to 8.0.34.

I’ve googled a lot already, and ran queries such as SHOW FULL PROCESSLIST. Only the event scheduler and a task related to compressing GTID table seemed suspicious, I’ve managed to disable it as I don’t think they’re needed in my case.

Also tried stuff like this, but no results.

heck MySQL open tables

SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions

SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;

Check lock dependency - what blocks what

SELECT * FROM information_schema.innodb_locks;

I’m not sure about the engine status; is this perhaps worrying?

---

TRANSACTIONS

Trx id counter 30988819 Purge done for trx’s n:o < 30988811 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: —TRANSACTION 283593565281880, not started 0 lock struct(s), heap size 1128, 0 row lock(s) —TRANSACTION 283593565281104, not started 0 lock struct(s), heap size 1128, 0 row lock(s) —TRANSACTION 283593565280328, not started 0 lock struct(s), heap size 1128, 0 row lock(s) —TRANSACTION 283593565279552, not started 0 lock struct(s), heap size 1128, 0 row lock(s) —TRANSACTION 283593565278000, not started 0 lock struct(s), heap size 1128, 0 row lock(s) —TRANSACTION 283593565277224, not started 1 lock struct(s), heap size 1128, 1 row lock(s)

Also, since the upgrade, I noticed this kind of error a lot in my error log.

2023-08-07T15:25:40.344099Z 24 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

It’s strange it’s still appearing: when I run

use mysql;

select plugin from user

it shows caching_sha2_password for each user. Not sure if it’s related.