Query causing CPU Spike to 100% after MYSQL upgrade

[Duncan Muturi]

Greetings!

We recently upgraded the kernel from Ubuntu 4.4.0-92-generic to 4.4.0-103-generic. Together with that, mysql got an upgrade from mysql "5.7.19" to "5.7.20".

Current setup:

mysql Ver 14.14 Distrib 5.7.20

Server:
x86_64, Ubuntu 4.4.0-103-generic
# of CPUs -> 2


Issue:
The query below no longer works (worked OK before the upgrade) and causes the CPU to spike to 100%:

mysql>
mysql> select distinct cast(`subtype_values`.`language_override` as char(500) charset latin1) AS `doc_type`,cast(`cffir`.`value` as char(400) charset latin1) AS `doc_type_code` from (((((`col_xxxyyyzzztracking` `cct` join `col_flexicontent_items_tmp` `cfit`) join `col_flexicontent_fields_item_relations` `cffir`) join `col_flexicontent_fields` `cff`) join `col_flexicontent_types` `cft`) join `aabb_ws_doc_subtype_mapping` `subtype_values`) where ((1 = 1) and (`cfit`.`type_id` = `cft`.`id`) and (`cffir`.`item_id` = `cct`.`item_id`) and (`cffir`.`field_id` = `cff`.`id`) and (convert(`cffir`.`value` using utf8mb4) = `subtype_values`.`subtype_value`) and (`cft`.`name` = ('Document - Product, Processing and Application')));


After the upgrade, this query doesn't complete,and the CPU spikes to 100%.
Temporary fix we have is by replacing the "= ('Document - Product, Processing and Application'" with "like ('Document%Product, Processing and Application'", as below:

mysql>
mysql> select distinct cast(`subtype_values`.`language_override` as char(500) charset latin1) AS `doc_type`,cast(`cffir`.`value` as char(400) charset latin1) AS `doc_type_code` from (((((`col_xxxyyyzzztracking` `cct` join `col_flexicontent_items_tmp` `cfit`) join `col_flexicontent_fields_item_relations` `cffir`) join `col_flexicontent_fields` `cff`) join `col_flexicontent_types` `cft`) join `aabb_ws_doc_subtype_mapping` `subtype_values`) where ((1 = 1) and (`cfit`.`type_id` = `cft`.`id`) and (`cffir`.`item_id` = `cct`.`item_id`) and (`cffir`.`field_id` = `cff`.`id`) and (convert(`cffir`.`value` using utf8mb4) = `subtype_values`.`subtype_value`) and (`cft`.`name` like ('Document%Product, Processing and Application')));
+-----------------------------------+-----------------+
| doc_type | doc_type_code |
+-----------------------------------+-----------------+
| General Information | ProductDocType2 |
| Product Brochure | ProductDocType5 |
| Published Posters | ProductDocType6 |
| Product Process Parameters | ProductDocType3 |
| Published White Papers / Articles | ProductDocType7 |
| Product Application Data | ProductDocType1 |
| Product Process Parameters | ProductDocType4 |
+-----------------------------------+-----------------+
7 rows in set (2.70 sec)

mysql>


Any ideas as to why the "="/ "equal" operator fails to work as before, while the "like" operator works?