Howdy, I'm having issues with database calls freez...

# exposed

[Miclebrick]

Howdy, I'm having issues with database calls freezing on socketRead0, I got a SHOW PROCESSLIST & SHOW ENGINE INNODB STATUS output via CLI this time: https://gist.github.com/Miclebrick/8622e4542a7f246bb1f3a8dcda951ba7 Can anyone help me out?

[Miclebrick]

Seems like there's a lot of "waiting for table metadata lock"?

[tapac]

Your engine status looks strange, because there is no any information about all that waiting transactions.
Are you sure that both queries were executed simulationally?

[Miclebrick]

Do you mean simultaneously? I ran those queries manually, but the issue was happening the whole time

[Miclebrick]

processListDump.txt

processListDump.txt

[Miclebrick]

innoDbStatusDump.txt

innoDbStatusDump.txt

[Miclebrick]

These were generated by the application when it happened, at the same exact second

[Miclebrick]

Could manually calling commit() cause this?

[Miclebrick]

https://gist.github.com/Miclebrick/7c57f3bd2cd69691868623693cf26352 i set wait_timeout to something shorter, now i get this error instead of it breaking everything

[tapac]

Looks like you run SHOW ENGINE INNODB STATUS under test user while most of queries made by root user.
Could you show result of show variables like 'max_connections' ?

[Miclebrick]

Max connections was at 151, but I actually got an out of connections issue so I increased it to 300. Sorry for the late reply

[Miclebrick]

@tapac i'm still having these issues 😞 any other tips to try and find out more?

[tapac]

1. Could you show new SHOW PROCESSLIST output?
2. Could you connect as root user and make SHOW ENGINE INNODB STATUS
3. Do you use only test user connections in your app or also root users?

[Miclebrick]

1. https://gist.github.com/Miclebrick/65dee9e13276c31bc7da9f307e939cc4
2. https://gist.github.com/Miclebrick/256e4110ecf3f26931a016efadfe6e82

[Miclebrick]

3. test is used by the test server, root is used by the production server

[Miclebrick]

https://gist.github.com/Miclebrick/256e4110ecf3f26931a016efadfe6e82#file-gistfile1-txt-L294
transaction here is active for 200+ seconds, that can't be normal

[tapac]

There are two transactions with 460+ seconds, witch could also be the cause, but could you tell me how much records do you have in plan_users/plan_servers/plan_ping tables and do you have an index on uuid fields?

[Miclebrick]

uh, I don't know anything about that plugins data, it's not one of mine, its this: https://github.com/Rsl1122/Plan-PlayerAnalytics

do you think it could be the cause? And how do I get that information?

[tapac]

You may call SHOW CREATE TABLE _table_name_ to see full table creation script with all indices/keys.
Could you tell me,
INSERT INTO plan_ping (user_id, server_id, date, min_ping, max_ping, avg_ping) VALUES ((SELECT plan_users.id FROM plan_users WHERE (uuid='aced95a8-422a-483c-99ed-611759f0dcad') LIMIT 1), (SELECT plan_servers.id FROM plan_servers WHERE (plan_servers.uuid='7db4c4e5-248e-37a8-a3e1-1d6f88840218') LIMIT 1), 1542424198514, 86, 87, 86.0)
is written on Exposed?

[Miclebrick]

Plan tables: https://gist.github.com/Miclebrick/eeab6e38bbe2c50b184000a7a9372564

Might be worth noting the Plan is on a different database ("plan" instead of "minecraft"). Despite that, do you think it could somehow be Plan causing the issue?

[Miclebrick]

And, the query you quoted, is not made by Exposed, it is written by Plan which does not use Kotlin or Exposed (assuming I understood the question)

[Miclebrick]

It seems to pretty consistently be hanging on inserts to a certain few tables

[Miclebrick]

I know this is probably a terrible solution, but I'm pretty desperate here; Will this work? https://gist.github.com/Miclebrick/0ec8182d176bee258ed0e81e184b03a8