Bug #102036 crash (got signal 11) at TRIGGER BEFORE DELETE in 8.0.22, but works with 8.0.21
Submitted: 20 Dec 2020 10:39 Modified: 20 Dec 2020 14:19
Reporter: Franz Eisenk├Âlbl Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.0.22 OS:Linux (Kernel 4.12.14-lp151.28.87-default x86_64)
Assigned to: CPU Architecture:x86

[20 Dec 2020 10:39] Franz Eisenk├Âlbl
Description:
- No problem in 8.0.21 with the same DB and Data.
- Tested with 8.0.22 from mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz and also self compiled from source, both have the same problem.
- The problem occurs not immediately, but around the 120th delete, mixed with around 300 inserts, in a data import program.
- The problem occurs only in ~9 of 10 tests with the same data, so it seems to be a timing problem!
- Using a VM with only 1 core or 4 cores makes no difference.

2020-12-20T07:30:36.485194Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.22'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Comm
unity Server - GPL.
07:31:08 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x2ac0a8107b70
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 2ac09f3badb0 thread_stack 0x46000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x2019e9e]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x323) [0x101f1c3]
/lib64/libpthread.so.0(+0x132d0) [0x2ac04193e2d0]
/usr/local/mysql/bin/mysqld(sp_cursor::Query_fetch_into_spvars::send_data(THD*, mem_root_deque<Item*> const&)+0xfa) [0xe72ffa]
/usr/local/mysql/bin/mysqld(Materialized_cursor::fetch(unsigned long)+0x50) [0xe9b030]
/usr/local/mysql/bin/mysqld(sp_cursor::fetch(List<sp_variable>*)+0x3d) [0xe733fd]
/usr/local/mysql/bin/mysqld(sp_head::execute(THD*, bool)+0x597) [0xe65a37]
/usr/local/mysql/bin/mysqld(sp_head::execute_trigger(THD*, MYSQL_LEX_CSTRING const&, MYSQL_LEX_CSTRING const&, GRANT_INFO*)+0x27a) [0xe662fa]
/usr/local/mysql/bin/mysqld(Trigger::execute(THD*)+0xd3) [0xfed943]
/usr/local/mysql/bin/mysqld(Trigger_chain::execute_triggers(THD*)+0x18) [0xfeecb8]
/usr/local/mysql/bin/mysqld(Table_trigger_dispatcher::process_triggers(THD*, enum_trigger_event_type, enum_trigger_action_time_type, bool)+0x46) [0xfe8c06]
/usr/local/mysql/bin/mysqld(Sql_cmd_delete::delete_from_single_table(THD*)+0x10a3) [0xea2c93]
/usr/local/mysql/bin/mysqld(Sql_cmd_delete::execute_inner(THD*)+0xa5) [0xea35e5]
/usr/local/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x688) [0xf45118]
/usr/local/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0xab8) [0xeeb048]
/usr/local/mysql/bin/mysqld(mysql_parse(THD*, Parser_state*)+0x3f8) [0xeef7c8]
/usr/local/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x167b) [0xef12bb]
/usr/local/mysql/bin/mysqld(do_command(THD*)+0x174) [0xef2834]
/usr/local/mysql/bin/mysqld() [0x1010fd8]
/usr/local/mysql/bin/mysqld() [0x253d3cc]
/lib64/libpthread.so.0(+0x84f9) [0x2ac0419334f9]
/lib64/libc.so.6(clone+0x3f) [0x2ac043997fbf]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2ac0a80250c8): is an invalid pointer
Connection ID (thread ID): 92
Status: NOT_KILLED

This are the last lines of the query log (DELETE FROM `services` crashed):
 
2020-12-20T07:31:08.753475Z        90 Query     INSERT INTO `config_log` (`time`, `type`, `db_id`, `id`, `user`, `message`, `mod`)
    VALUES (CURRENT_TIMESTAMP, 'host', OLD.`host_db_id`, OLD.`host_name`, @USER, '', 'deleted')
2020-12-20T07:31:08.755009Z        91 Connect   ghi@localhost on abc_monitor_config using Socket
2020-12-20T07:31:08.755577Z        91 Query     SET @USER = 'abc-system', @TRIGGER_CHECKS = TRUE
2020-12-20T07:31:08.755651Z        91 Query     LOCK tables `hosts` WRITE
2020-12-20T07:31:08.756219Z        91 Query     SELECT `host_db_id`, `parents` from `hosts` WHERE parents LIKE '%Template_Device%'
2020-12-20T07:31:08.756376Z        91 Query     SELECT @@autocommit
2020-12-20T07:31:08.756441Z        91 Query     UNLOCK TABLES
2020-12-20T07:31:08.756625Z        92 Connect   ghi@localhost on abc_monitor_config using Socket
2020-12-20T07:31:08.757261Z        92 Query     SET @USER = 'abc-system', @TRIGGER_CHECKS = TRUE
2020-12-20T07:31:08.757360Z        92 Query     DELETE FROM `services`  WHERE `host_name` = 'Template_Device'

I think that some remains from session "90", which also used a trigger, writing partially to the same tables as the trigger in "92" only 5ms later, will cause the problem.

How to repeat:
To reproduce this problem is maybe a little bit difficult.
I would have to obfuscate the customers data in the DB and to obfuscate the customers data from the needed import and to upload the data dumps and upload the software (where I would need to collect the permissions to do so).

This is the BEFORE DELETE TRIGGER, where the crash occurs:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `service_delete` BEFORE DELETE ON `services` FOR EACH ROW customTrigger: BEGIN
    DECLARE var_service_db_id, var_contact_db_id, var_contactgroup_db_id, var_id, var_value VARCHAR(255);
    DECLARE var_serviceescalation_db_id, var_first_notification, var_last_notification, var_notification_interval, var_escalation_options VARCHAR(255);
    DECLARE var_dependent_service_db_id, var_inherits_parent, var_execution_failure_crit, var_notifications_failure_crit VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur1 CURSOR FOR SELECT `service_db_id`, `id`, `value`
    FROM `custom_service_attributes`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE cur2 CURSOR FOR SELECT `service_db_id`, `contact_db_id`
    FROM `service_contacts`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE cur3 CURSOR FOR SELECT `service_db_id`, `contactgroup_db_id`
    FROM `service_contactgroups`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE cur4 CURSOR FOR SELECT `serviceescalation_db_id`, `service_db_id`, `first_notification`, `last_notification`, `notification_interval`, `escalation_options`
    FROM `serviceescalations`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE cur5 CURSOR FOR SELECT `serviceescalation_db_id`, `service_db_id`, `contact_db_id`
    FROM `serviceescalation_contacts`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE cur6 CURSOR FOR SELECT `serviceescalation_db_id`, `service_db_id`, `contactgroup_db_id`
    FROM `serviceescalation_contactgroups`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE cur7 CURSOR FOR SELECT `service_db_id`, `dependent_service_db_id`, `inherits_parent`, `execution_failure_criteria`, `notification_failure_criteria`
    FROM `servicedependencies`
    WHERE `service_db_id` = OLD.`service_db_id`;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        IF (@TRIGGER_CHECKS = FALSE)
        THEN
                LEAVE customTrigger;
        END IF;

    OPEN cur1;

    the_loop: LOOP

        FETCH cur1 INTO var_service_db_id, var_id, var_value;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_custom_service_attributes` (`version`, `service_db_id`, `id`, `value`)
         VALUES (OLD.`version`, var_service_db_id, var_id, var_value);

    END LOOP the_loop;

    CLOSE cur1;

    SET done = FALSE;
    OPEN cur2;

    the_loop: LOOP

        FETCH cur2 INTO var_service_db_id, var_contact_db_id;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_service_contacts` (`version`, `service_db_id`, `contact_db_id`)
         VALUES (OLD.`version`, var_service_db_id, var_contact_db_id);

    END LOOP the_loop;

    CLOSE cur2;

    SET done = FALSE;
    OPEN cur3;

    the_loop: LOOP

        FETCH cur3 INTO var_service_db_id, var_contactgroup_db_id;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_service_contactgroups` (`version`, `service_db_id`, `contactgroup_db_id`)
         VALUES (OLD.`version`, var_service_db_id, var_contactgroup_db_id);

    END LOOP the_loop;

    CLOSE cur3;

    SET done = FALSE;
    OPEN cur4;

    the_loop: LOOP

        FETCH cur4 INTO var_serviceescalation_db_id, var_service_db_id, var_first_notification, var_last_notification, var_notification_interval, var_escalation_options;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_serviceescalations` (`version`, `serviceescalation_db_id`, `service_db_id`, `first_notification`, `last_notification`, `notification_interval`, `escalation_optio
ns`)
         VALUES (OLD.`version`, var_serviceescalation_db_id, var_service_db_id, var_first_notification, var_last_notification, var_notification_interval, var_escalation_options);

    END LOOP the_loop;

    CLOSE cur4;

    SET done = FALSE;
    OPEN cur5;

    the_loop: LOOP

        FETCH cur5 INTO var_serviceescalation_db_id, var_service_db_id, var_contact_db_id;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_serviceescalation_contacts` (`version`, `serviceescalation_db_id`, `service_db_id`, `contact_db_id`)
         VALUES (OLD.`version`, var_serviceescalation_db_id, var_service_db_id, var_contact_db_id);

    END LOOP the_loop;

    CLOSE cur5;

    SET done = FALSE;
    OPEN cur6;

    the_loop: LOOP

        FETCH cur6 INTO var_serviceescalation_db_id, var_service_db_id, var_contactgroup_db_id;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_serviceescalation_contactgroups` (`version`, `serviceescalation_db_id`, `service_db_id`, `contactgroup_db_id`)
         VALUES (OLD.`version`, var_serviceescalation_db_id, var_service_db_id, var_contactgroup_db_id);

    END LOOP the_loop;

    CLOSE cur6;

    SET done = FALSE;
    OPEN cur7;

    the_loop: LOOP

        FETCH cur7 INTO var_service_db_id, var_dependent_service_db_id, var_inherits_parent, var_execution_failure_crit, var_notifications_failure_crit;
        IF done THEN
          LEAVE the_loop;
        END IF;

        INSERT INTO `history_servicedependencies` (`version`, `service_db_id`, `dependent_service_db_id`, `inherits_parent`, `execution_failure_criteria`, `notification_failure_criteria`)
         VALUES (OLD.`version`, var_service_db_id, var_dependent_service_db_id, var_inherits_parent, var_execution_failure_crit, var_notifications_failure_crit);

    END LOOP the_loop;

    CLOSE cur7;

    INSERT INTO `history_services` (`service_db_id`, `service_id`, `host_name`, `service_description`, `display_name`, `comment`, `check_command`, `max_check_attempts`, `check_period`, `chec
k_interval`, `retry_interval`, `enable_notifications`, `enable_active_checks`, `enable_passive_checks`, `enable_event_handler`, `enable_flapping`, `enable_perfdata`, `event_command`, `flappi
ng_threshold`, `volatile`, `zone`, `command_endpoint`, `notes`, `notes_url`, `action_url`, `icon`, `active`, `user_name`, `update_time`, `version`, `notification_interval`, `notification_per
iod`, `notification_options`, `notifications_enabled`, `check_freshness`, `freshness_threshold`, `register`, `name`, `use`, `check_template_id`, `hostgroup_name`, `is_volatile`, `initial_sta
te`, `active_checks_enabled`, `passive_checks_enabled`, `obsess_over_service`, `event_handler`, `event_handler_enabled`, `low_flap_threshold`, `high_flap_threshold`, `flap_detection_enabled`
, `flap_detection_options`, `failure_prediction_enabled`, `process_perf_data`, `retain_status_information`, `retain_nonstatus_information`, `first_notification_delay`, `stalking_options`, `s
ervice_template_id`, `service_template_src`, `history_action`)
    VALUES                      (OLD.`service_db_id`,  OLD.`service_id`,  OLD.`host_name`,  OLD.`service_description`,  OLD.`display_name`,  OLD.`comment`,  OLD.`check_command`,  OLD.`max_ch
eck_attempts`,  OLD.`check_period`,  OLD.`check_interval`,  OLD.`retry_interval`,  OLD.`enable_notifications`,  OLD.`enable_active_checks`,  OLD.`enable_passive_checks`,  OLD.`enable_event_h
andler`,  OLD.`enable_flapping`,  OLD.`enable_perfdata`,  OLD.`event_command`,  OLD.`flapping_threshold`,  OLD.`volatile`,  OLD.`zone`,  OLD.`command_endpoint`,  OLD.`notes`,  OLD.`notes_url
`,  OLD.`action_url`,  OLD.`icon`,  OLD.`active`,  OLD.`user_name`,  OLD.`update_time`,  OLD.`version`,  OLD.`notification_interval`,  OLD.`notification_period`,  OLD.`notification_options`,
  OLD.`notifications_enabled`,  OLD.`check_freshness`,  OLD.`freshness_threshold`,  OLD.`register`,  OLD.`name`,  OLD.`use`,  OLD.`check_template_id`,  OLD.`hostgroup_name`,  OLD.`is_volatil
e`,  OLD.`initial_state`,  OLD.`active_checks_enabled`,  OLD.`passive_checks_enabled`,  OLD.`obsess_over_service`,  OLD.`event_handler`,  OLD.`event_handler_enabled`,  OLD.`low_flap_threshol
d`,  OLD.`high_flap_threshold`,  OLD.`flap_detection_enabled`,  OLD.`flap_detection_options`,  OLD.`failure_prediction_enabled`,  OLD.`process_perf_data`,  OLD.`retain_status_information`,
OLD.`retain_nonstatus_information`,  OLD.`first_notification_delay`,  OLD.`stalking_options`,  OLD.`service_template_id`,  OLD.`service_template_src`, 'DELETE');
    INSERT INTO `config_log` (`time`, `type`, `db_id`, `id`, `user`, `message`, `mod`)
    VALUES (CURRENT_TIMESTAMP, 'service', OLD.`service_db_id`, OLD.`service_id`, @USER, '', 'deleted');
END */;;
[20 Dec 2020 14:19] MySQL Verification Team
Thank you for your bug report.

The bug reported is a duplicate of the following  bug, belonging to this forum:

https://bugs.mysql.com/bug.php?id=101549

That bug, in itself, is a duplicate of an internally reported security bug, which is already fixed in the release 8.0.23. At this moment, nobody knows when will that release be published, but definitely during next several months.

We do not know of the workaround. Usually, it is about a stored routine that has a conditional jump which depends on the comparison of some strings or integers. You should try to change that as a workaround .......

In short, this bug is a duplicate one.

Since all these bugs, including this one, are security bugs, public can not access them.

Duplicate.
[21 Dec 2020 11:48] MySQL Verification Team
Sorry I pointed a wrong bug duplicate bug, please consider, which is an internal number bug:

Bug 32267749 - TRIGGERS CRASHING IN
SP_CURSOR::QUERY_FETCH_INTO_SPVARS::SEND_DATA