Can I reset a shared MySQL instance? Tables are locked
Had a user circumvent a circular dependency protection but it wasn't caught on the back-end and I believe it resulted in infinite DB calls. I started getting "Error: Error writing file '/mysql_data/tmp/MLfd=197' (OS errno 28 - No space left on device)"
After a while, that error ceased (maybe the temp dir was auto-cleared?) and now it seems that all our tables are locked and processes are stacking up and not being cleared out.
Is there a way to reset MySQL or clear these pending processes and unlock the tables?
The event scheduler says it's waiting on an empty queue and most of the processes are "waiting for handler"
2 Replies
2023-06-22T18:57:14.993228+00:00 app[web.1]: code: 'ER_LOCK_WAIT_TIMEOUT',
2023-06-22T18:57:14.993235+00:00 app[web.1]: errno: 1205,
2023-06-22T18:57:14.993236+00:00 app[web.1]: sqlState: 'HY000',
2023-06-22T18:57:14.993236+00:00 app[web.1]: sqlMessage: 'Lock wait timeout exceeded; try restarting transaction',
and
2023-06-22T18:27:35.022842+00:00 app[web.1]: Error: Deadlock found when trying to get lock; try restarting transaction
2023-06-22T18:27:35.022843+00:00 app[web.1]: at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:728:17)
2023-06-22T18:27:35.022843+00:00 app[web.1]: at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
2023-06-22T18:27:35.022844+00:00 app[web.1]: at PoolConnection.handlePacket (/app/node_modules/mysql2/lib/connection.js:488:32)
2023-06-22T18:27:35.022844+00:00 app[web.1]: at PacketParser.onPacket (/app/node_modules/mysql2/lib/connection.js:94:12)
2023-06-22T18:27:35.022846+00:00 app[web.1]: at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
2023-06-22T18:27:35.022847+00:00 app[web.1]: at TLSSocket.<anonymous> (/app/node_modules/mysql2/lib/connection.js:387:25)
2023-06-22T18:27:35.022847+00:00 app[web.1]: at TLSSocket.emit (node:events:513:28)
2023-06-22T18:27:35.022848+00:00 app[web.1]: at addChunk (node:internal/streams/readable:324:12)
2023-06-22T18:27:35.022848+00:00 app[web.1]: at readableAddChunk (node:internal/streams/readable:297:9)
2023-06-22T18:27:35.022848+00:00 app[web.1]: at Readable.push (node:internal/streams/readable:234:10) {
2023-06-22T18:27:35.022849+00:00 app[web.1]: code: 'ER_LOCK_DEADLOCK',</anonymous>
kazouma
Linode Staff
It looks like you opened a ticket about this issue and we've escalated it so our Database devs can take a look at it. From the error message you shared, it indeed looks like you are out of disk space.For unmanaged databases, you can use this guide to help you clear your temp files.
-- Koffi