Select query on MYSQL table taking long time and getting timed out

Question

I have a mysql table with 2 million rows, when I'm running any select query on the table it's taking long time to execute and ultimately it does not return any result.

I have tried running select query from both Mysql Workbench and terminal, it's the same issue happening.

Below is the table:

`object_master` 
  `key` varchar(300) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `bucket` varchar(255) DEFAULT NULL,
  `object_name` varchar(300) DEFAULT NULL,
  `object_type` varchar(50) DEFAULT NULL,
  `last_modified_date` datetime DEFAULT NULL,
  `last_accessed_date` datetime DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `p_object` varchar(300) DEFAULT NULL,
  `record_insert_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `record_update_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`key`)
ENGINE=InnoDB DEFAULT CHARSET=latin1

And below is the select query i'm running :

select `key` from object_master;

even with a limit 1 is also taking long time and not returning a result, its getting timed out :

select `key` from object_master limit 1;

Could anyone tell me what can be the real reason here?

Also I would like to mention: before I was running these select queries, there was an alter table statement executed on this table which got timed out after 10 minutes and table remained un-altered.

Following is the alter statement:

alter table object_master
modify column object_name varchar(1096) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

Note: Using MYSQL version 5.7.24 and Mysql running on Linux Docker container.

Is it just that table? Does a `SELECT 1 FROM dual;` return a row? Could be anything from an unreleased lock on your table to a network issue — marcothesane, Nov 18 '20 at 11:03
```key``` is reserved word of ```mysql``` https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-K ... you can't name any column by key. — ash, Nov 18 '20 at 11:03
@ash: You *can* if you put ticks around it, as the OP did. But you're right, that you probably *shouldn't*, as it just makes handling it harder than it needs to be. — sticky bit, Nov 18 '20 at 11:22
@all: I resolved this. There was Java batch program which was executing a query on the same table for long time and was holding a lock on the table. I found this through "processlist" table of information_schema. Had to kill the long running query through terminal. Then it released the lock on that table and all got resolved. — the.realist.guy, Nov 18 '20 at 16:39

score 0 · Accepted Answer · answered Nov 18 '20 at 16:45

So I got this resolved:

There was Java batch program which was executing a query on the same table for long time and was holding a lock on the table. I found this through "processlist" table of information_schema.

Had to kill the long running query through terminal:

mysql> kill <processlist_id> ;

Then it released the lock on that table and all got resolved.

Got help from below SO answers:

Select query on MYSQL table taking long time and getting timed out

1 Answers1