MySQL query stuck in preparing status

[Robert Gunther]

I have a MySQL server v. 5.6.26 running for years (upgraded from version to version over the years).

Yesterday the performance of the server came to a crawl.

I checked out the status of all executing queries and I found several that were just sitting their with a status of 'preparing'. The documentation page states preparing is 'This state occurs during query optimization.'

The query is a basic insert, no sub selects, no nothing. Just a basic insert something like this.

insert into example
(id, name, email)
values
(1,'joe','[e-mail])

There were maybe 6 queries like this running. They were consuming all the CPU on the server bringing the server to its knees.

I killed off the queries and the server came back to life.

The process that is running this query runs every 5 minutes. Sure enough 5 minutes later the query goes into 'preparing' status again.

I don't see anything in the logs, eventually it will complete but the entire server is basically dead if there are a few pending.

How do I debug this?