0

I am running a node server with mysql8.
This query keeps popping up and freezing.
SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM my_table
Even though this query have MAX_EXECUTION_TIME mentioned in it, It keeps executing well past this limit (basically it never stops). The longest I have seen it running was around 20-30 days and even after that it didn't stopped I had restarted the server.

Main issue is I can't even kill this query. Even after killing it, It keeps executing and never stops.
I can't even restart mysql. After trying to restart the mysql. It just shuts down and never starts back.
I had to reboot the server (which is totally unacceptable).

SHOW OPEN TABLES;
Shows that this table is in use. It doesn't effect Reading, updating or inserting of data in the table. But as soon as I try to alter this table or any other table which has any reference from or to this table. It freezes whole node server as deadlock of queries occur and every query keeps waiting for the first query to end, which as already stated, never does.

This query in question isn't there in any of my node js code.
But is being executed by the user (mysql user) which my node server uses, I also use this user with adminer.

This query always shows up with this specific table. Below is the sample output of show create table my_table; (table name and column name is changed)

CREATE TABLE `my_table` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`col2` int NOT NULL,
`col3` longtext COLLATE utf8_unicode_ci,
`col4` longtext COLLATE utf8_unicode_ci,
`col5` mediumtext COLLATE utf8_unicode_ci,
`col5` int DEFAULT NULL COMMENT 'in seconds',
`col6` tinyint(1) NOT NULL DEFAULT '0',
`col7` int DEFAULT NULL,
`col8` int DEFAULT NULL,
`col9` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`col9` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `test_level` (`col7`),
KEY `col9` (`col9`),
KEY `col6` (`col6`),
KEY `col5` (`col5`),
KEY `col2` (`col2`),
CONSTRAINT `my_table_ibfk_1` FOREIGN KEY (`col7`) REFERENCES `table_1` (`id`),
CONSTRAINT `my_table_ibfk_2` FOREIGN KEY (`col2`) REFERENCES `table_2` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=4078 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

No of rows in this table is around 4000.

exact mysql-version 8.0.19

vaibhav3027
  • 51
  • 1
  • 6
  • How do you invoke the select query in your question? Cli? A gui client? From an application? Did you check if the optimiser hint was sent to the server? – Shadow Mar 30 '21 at 06:33
  • maybe add output of `show create table yourtablename`? – ysth Mar 30 '21 at 07:02
  • @Shadow I didn't invoke that query. I saw it listed in the output of `SHOW PROCESSLIST;`. This query is probably getting invoked through node server or adminer. Main issue is not that this query comes up. It is that it doesn't go away and keeps the table in use until I restart the server (VM instance) – vaibhav3027 Mar 31 '21 at 07:13
  • @ysth added the output of `show create table` in the question. – vaibhav3027 Mar 31 '21 at 07:14
  • I wonder if something is wrong with the table in question that causes the count query to stall. Perhaps a repair table or dropping then recreating the table may help. To be honest, I think you should ask this question on the DBA site, not here on SO as this is not really a programming problem, you need people with deep knowledge of mysql internals and those are mostly on the DBA site, not here. – Shadow Mar 31 '21 at 08:26

0 Answers0