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

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

​x
 
`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;

 
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;

 
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;

 
alter table object_mastermodify 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.

Answer

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> ;

 
mysql> kill <processlist_id> ; ​

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

Got help from below SO answers:

Advertisement

Answer