Description:
1、origin
For MySQL version 8.0.23, a timeout error is reported when the following SQL is executed. Through the slow log, find the SQL that reported the error. SQL is as follows:
SELECT * FROM t_dict WHERE FIND_IN_SET(id,(SELECT getDictChildrenNew(
(select id from t_dict where code = 'metadata_classify'
and root_code = 'metadata_classify' ),
(select code from t_dict where code = 'metadata_classify'
and root_code = 'metadata_classify' ),
(select root_code from t_dict where code = 'metadata_classify'
and root_code = 'metadata_classify' )
)))
order by dict_level,dict_order asc;
The structure of the table t_dict:
CREATE TABLE `t_dict` (
`id` decimal(10,0) NOT NULL ,
`parent_code` varchar(255) DEFAULT NULL ,
`parent_name` varchar(255) DEFAULT NULL ,
`code` varchar(255) DEFAULT NULL ,
`dict_name` varchar(255) DEFAULT NULL ,
`dict_value` varchar(255) DEFAULT NULL ,
`company_id` varchar(255) DEFAULT NULL ,
`status` tinyint DEFAULT NULL ,
`dict_sort` decimal(10,0) DEFAULT '0' ,
`deleted` tinyint DEFAULT NULL ,
`remark` varchar(255) DEFAULT NULL ,
`root_code` varchar(255) DEFAULT NULL ,
`enable_expand` tinyint DEFAULT '1' ,
`dict_level` bigint DEFAULT '1' ,
`level_code` varchar(255) DEFAULT NULL ,
`level_remark` varchar(255) DEFAULT NULL ,
`dict_order` decimal(10,0) DEFAULT NULL ,
`default_num` decimal(10,0) DEFAULT NULL ,
`dict_type` varchar(255) DEFAULT NULL ,
`create_date` datetime DEFAULT CURRENT_TIMESTAMP ,
`create_user` varchar(64) DEFAULT NULL ,
`create_ip` varchar(64) DEFAULT NULL ,
`update_date` datetime DEFAULT NULL ,
`update_user` varchar(64) DEFAULT NULL ,
`update_ip` varchar(64) DEFAULT NULL ,
`inner_code` varchar(255) DEFAULT NULL ,
`serial_num` varchar(500) DEFAULT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
This SQL uses a custom function getDictChildrenNew. The function content is as follows:
CREATE DEFINER=`user_name`@`%` FUNCTION `user_name`.`getDictChildrenNew`(iid varchar(255),icode varchar(255),ircode varchar(255)) RETURNS varchar(5000) CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTemp VARCHAR(5000);
DECLARE oTempChild VARCHAR(5000);
DECLARE oTempId VARCHAR(5000);
DECLARE oRootCode VARCHAR(5000);
SET oTemp = '';
SET oTempChild = icode;
SET oTempId = iid;
SET oRootCode = ircode;
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempId);
SELECT GROUP_CONCAT(ID) INTO oTempId
FROM t_dict
WHERE ROOT_CODE = oRootCode AND FIND_IN_SET(PARENT_CODE,oTempChild);
SELECT GROUP_CONCAT(CODE) INTO oTempChild
FROM t_dict
WHERE ROOT_CODE = oRootCode AND FIND_IN_SET(PARENT_CODE,oTempChild);
END WHILE;
RETURN oTemp;
END
2、Test phenomenon
In my environment, the SQL that takes the second input parameter of FIND_IN_SET alone only needs to be executed, and the results can be run normally, and it only takes 122ms
SELECT getDictChildrenNew(
(select id from t_dict where code = 'metadata_classify' #58
and root_code = 'metadata_classify' ),
(select code from t_dict where code = 'metadata_classify' #metadata_classify
and root_code = 'metadata_classify' ),
(select root_code from t_dict where code = 'metadata_classify' #metadata_classify
and root_code = 'metadata_classify' )
)
Or I can pass the value of this query as a string as the second parameter of FIND_IN_SET and execute it quickly. It only takes 79ms.
But if you execute the above complete SQL alone, there will be a timeout error.
The strange thing is that the same amount of data, table structure, operating system environment, this SQL has no problems on MySQL 8.0.18. Can calculate the result quickly and normally
How to repeat:
This exception occurs when the SQL in 1 above is executed in MySQL8.0.23. MySQL8.0.18 will not
Suggested fix:
Revert to be consistent with the MySQL8.0.18 version