I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state.
Scenario to reproduce:
- OS: CentOS 6.5
- MySQL: 5.6
- Engine: MyISAM (with at least one BLOB/LONGTEXT column)
- mysql config 'myisam_repair_threads' = 2
I have a table with 40MB of data and 1500 records. With myisam_repair_threads set to 1, OPTIMIZE completes within a second. Settings myisam_repair_threads to 2 takes forever to complete. After killing the query, it remains in the 'killed' state (at moment of writing already 3 hours). Seems like this is a bug in mysql.
However, the big question: How can I force to kill this query? It currently holds a lock on the meta data of the table, so some processes can't execute because they are waiting for this lock.
Any advice is appreciated!