Description:
Query failing with "ERROR 1030 (HY000): Got error 1 from storage engine" when max_execution_time is set to a lower value via select statement.
How to repeat:
[1]Created a table with the following structure:
CREATE TABLE `cd2` (
`col1` bigint(20) NOT NULL AUTO_INCREMENT,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` datetime NOT NULL,
`col5` datetime NOT NULL,
`col6` int(11) DEFAULT '0',
`col7` datetime DEFAULT NULL,
`col8` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col9` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col10` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col11` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col12` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col13` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col14` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col15` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col16` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col17` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col18` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`col19` int(11) DEFAULT NULL,
`col20` int(11) DEFAULT NULL,
`col21` int(11) DEFAULT NULL,
`col22` decimal(3,2) DEFAULT '0.00',
`col23` tinyint(1) DEFAULT NULL,
`col24` tinyint(1) DEFAULT '0',
`col25` tinyint(1) DEFAULT '0',
`col26` tinyint(1) DEFAULT '0',
`col27` tinyint(1) DEFAULT '0',
`col28` mediumtext COLLATE utf8mb4_unicode_ci,
`col29` json DEFAULT NULL,
`col30` json DEFAULT NULL,
`col31` json DEFAULT NULL,
`col32` json DEFAULT NULL,
`col33` json DEFAULT NULL,
`col34` json DEFAULT NULL,
`col35` json DEFAULT NULL,
`col36` json DEFAULT NULL,
`col37` json DEFAULT NULL,
`col38` json DEFAULT NULL,
`col39` json DEFAULT NULL,
`col40` json DEFAULT NULL,
`col41` json DEFAULT NULL,
`col42` json DEFAULT NULL,
`col43` json DEFAULT NULL,
`col44` json DEFAULT NULL,
`col45` json DEFAULT NULL,
`col46` json DEFAULT NULL,
`col47` json DEFAULT NULL,
`col48` json DEFAULT NULL,
`col49` json DEFAULT NULL,
`col50` json DEFAULT NULL,
`col51` json DEFAULT NULL,
`col52` json DEFAULT NULL,
`col53` json DEFAULT NULL,
`col54` json DEFAULT NULL,
`col55` json DEFAULT NULL,
`col56` json DEFAULT NULL,
`col57` json DEFAULT NULL,
`s_1` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_3` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_4` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_5` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_6` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_7` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_8` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_9` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`s_10` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`d_id` int(11) DEFAULT NULL,
`d2_id` int(11) DEFAULT NULL,
`d3_id` int(11) DEFAULT NULL,
`d4_id` int(11) DEFAULT NULL,
`d5_id` int(11) DEFAULT NULL,
`d6_id` int(11) DEFAULT NULL,
`d7_id` int(11) DEFAULT NULL,
`d8_id` int(11) DEFAULT NULL,
`d9_id` int(11) DEFAULT NULL,
`d10_id` int(11) DEFAULT NULL,
PRIMARY KEY (`col1`),
UNIQUE KEY `index_c_i_on_col2_and_col3` (`col2`,`col3`),
UNIQUE KEY `index_ca_col12_and_col3` (`col12`,`col3`),
UNIQUE KEY `index_on_col13_and_col3` (`col13`,`col3`),
KEY `index_con_col2` (`col2`),
KEY `index_on_col3_and_col6_and_col19` (`col3`,`col6`,`col19`),
KEY `index_on_col3` (`col3`),
KEY `index_on_col12` (`col12`),
KEY `index_on_col18` (`col18`),
KEY `index_on_col8` (`col8`),
KEY `index_on_col23` (`col23`),
KEY `index_on_col17` (`col17`),
KEY `index_on_col14` (`col14`),
KEY `index_on_col15` (`col15`),
KEY `index_on_col16` (`col16`),
KEY `index_on_col9` (`col9`),
KEY `index_on_col19` (`col19`),
KEY `index_on_col24` (`col24`),
KEY `index_on_col11` (`col11`),
KEY `index_on_col21` (`col21`),
KEY `index_on_col20` (`col20`),
KEY `index_on_s_1` (`s_1`,`col3`),
KEY `index_on_string_2` (`s_2`,`col3`),
KEY `index_on_string_3` (`s_3`,`col3`),
KEY `index_on_string_4` (`s_4`,`col3`),
KEY `index_on_string_5` (`s_5`,`col3`),
KEY `index_on_string_6` (`s_6`,`col3`),
KEY `index_on_string_7` (`s_7`,`col3`),
KEY `index_on_string_8` (`s_8`,`col3`),
KEY `index_on_string_9` (`s_9`,`col3`),
KEY `index_on_string_10` (`s_10`,`col3`),
KEY `index_on_d_id` (`d_id`,`col3`),
KEY `index_on_d2_id` (`d2_id`,`col3`),
KEY `index_on_d3_id` (`d3_id`,`col3`),
KEY `index_on_d4_id` (`d4_id`,`col3`),
KEY `index_on_d5_id` (`d5_id`,`col3`),
KEY `index_on_d6_id` (`d6_id`,`col3`),
KEY `index_on_d7_id` (`d7_id`,`col3`),
KEY `index_on_d8_id` (`d8_id`,`col3`),
KEY `index_on_d9_id` (`d9_id`,`col3`),
KEY `index_on_d10_id` (`d10_id`,`col3`),
FULLTEXT KEY `index_on_col28` (`col28`)
) ENGINE=InnoDB AUTO_INCREMENT=10471007 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
[2]Loaded data:
mysql> select count(*) from cd2;
+----------+
| count(*) |
+----------+
| 8633292 |
+----------+
[3]The below query fails:
mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ col1 from cd2 where col11 like 'Neal%' or col12 like 'Neal%';
ERROR 1030 (HY000): Got error 1 from storage engine
[4]The query doesn't fail if we do not set MAX_EXECUTION_TIME(1) in the select statement.
[5]Ideally, the query should have failed with "ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded"
We get this error when we remove either of the conditions in the where clause.
mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ col1 from cd2 where col11 like 'Neal%';
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
Tested the same on the following versions:
> 5.7.12
> 5.7.32
> 5.7.28
