mysqlmysql-event

Why is MySQL Event Scheduler Stuck Opening Tables?


I'm using MySQL 8.0.21 From the MySQL Community Installer on Windows 10 updated to version2004 and for some reason if I create a event in the event scheduler, which calls a procedure once every second (regardless of what that SP actually does, I'll explain my test case) - my CPU maxes out and when I look at the active connections in MySQL Workbench, it stacks up a ton of worker threads which stall on the "Opening Tables" state. My PC freezes, I have to edit the event to be disabled, stop the MySQL process in Task Manager and Start the service again.

TEST CASE

During setup of a brand new server, I used all default settings, except I enabled the general log and I use the new 8.0+ mysql_sha2_password encryption (although I ALTER USER to mysql_native_password for phpmyadmin so that might revert it, I'm honestly not sure)

Also, if I enable the event, or create it, it'll run without issue, it's important to note that the issue begins when the event scheduler is left on, and the event is left enabled, then the server is restarted from the services in task manager. Immediately the CPU jacks up to max and active connections show threads stacking up without completing. Any clues are appreciated, I find no actual errors nor do I have any idea where to begin debugging anymore. I've tried skipping grant tables (but obviously that's not optimal, and didn't work).

TestEvent TestProc

I did find a hint when reviewing the MySQL 8.0+ docs

"If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking. " from MySQL Reference Link

However, when analyzing there does not appear to be any locks, nor should I need to implement such manually just for this test case (or my actual program)


UPDATE


Up to this point, albeit a rather niche bug, I do believe that is exactly what this is, and I have posted it on MySQL bug forum. BUG#: 100449 Reference post is here: DBA Stack Exchange - DDeathlonger


Solution

  • The answer actually has turned out to be a bug which is reproducible - Bug#: 100449