MySQL Server restarts after trigger execution

Question

I have a trigger in MySQL that is causing the MySQL server to restart each time it's triggered. This started happening after the version 8.0.22 update a day ago. My trigger is as follows:

CREATE TRIGGER max_client_invoice_before_insert
BEFORE INSERT
ON client_invoices FOR EACH ROW

BEGIN

DECLARE vMax int(11);

SELECT IFNULL(max(client_invoice_id),0) from client_invoices where client_operating_unit_id = NEW.client_operating_unit_id INTO vMax;

SET NEW.client_invoice_id = vMax+1;

END

Is there anything I am missing due to the recent update, or any better and efficient ways of achieving the same goal if I can't seem to find the cause of the server crashes?

I have also checked the logs and this is what I found:

06:03:02 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f08e4921bd0
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 = 7f09c8254c70 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x2194f3d]
/usr/sbin/mysqld(handle_fatal_signal+0x313) [0xff55f3]
/lib64/libpthread.so.0(+0xf630) [0x7f09d5633630]
/usr/sbin/mysqld(Item_splocal::this_item()+0x14) [0x111fce4]
/usr/sbin/mysqld(Item_sp_variable::val_int()+0x13) [0x111fb63]
/usr/sbin/mysqld(Item_func_plus::int_op()+0x1d) [0x11aafdd]
/usr/sbin/mysqld(Item_func_numhybrid::val_int()+0x191) [0x11ad541]
/usr/sbin/mysqld(Item::save_in_field_inner(Field*, bool)+0x125) [0x11259c5]
/usr/sbin/mysqld(Item::save_in_field(Field*, bool)+0x53) [0x113ef03]
/usr/sbin/mysqld(Item_trigger_field::set_value(THD*, sp_rcontext*, Item**)+0x76) [0x113f136]
/usr/sbin/mysqld(sp_instr_set_trigger_field::exec_core(THD*, unsigned int*)+0x90) [0xe38a80]
/usr/sbin/mysqld(sp_lex_instr::reset_lex_and_exec_core(THD*, unsigned int*, bool)+0x60c) [0xe39b1c]
/usr/sbin/mysqld(sp_lex_instr::validate_lex_and_execute_core(THD*, unsigned int*, bool)+0x9a) [0xe3a55a]
/usr/sbin/mysqld(sp_head::execute(THD*, bool)+0x5d3) [0xe311c3]
/usr/sbin/mysqld(sp_head::execute_trigger(THD*, MYSQL_LEX_CSTRING const&, MYSQL_LEX_CSTRING const&, GRANT_INFO*)+0x29d) [0xe31acd]
/usr/sbin/mysqld(Trigger::execute(THD*)+0x10c) [0xfc150c]
/usr/sbin/mysqld(Trigger_chain::execute_triggers(THD*)+0x18) [0xfc28b8]
/usr/sbin/mysqld(Table_trigger_dispatcher::process_triggers(THD*, enum_trigger_event_type, enum_trigger_action_time_type, bool)+0x46) [0xfbc4a6]
/usr/sbin/mysqld(fill_record_n_invoke_before_triggers(THD*, COPY_INFO*, mem_root_deque const&, mem_root_deque const&, TABLE*, enum_trigger_event_type, int, bool, bool*)+0x3f9) [0xe45ac9]
/usr/sbin/mysqld(Sql_cmd_insert_values::execute_inner(THD*)+0x454) [0x1352464]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x525) [0xf15695]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x9f0) [0xeb98d0]
/usr/sbin/mysqld(Prepared_statement::execute(String*, bool)+0x8f0) [0xee8160]
/usr/sbin/mysqld(Prepared_statement::execute_loop(String*, bool)+0x117) [0xeec5f7]
/usr/sbin/mysqld(mysqld_stmt_execute(THD*, Prepared_statement*, bool, unsigned long, PS_PARAM*)+0x181) [0xeecba1]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1712) [0xebfbf2]
/usr/sbin/mysqld(do_command(THD*)+0x19c) [0xec101c]
/usr/sbin/mysqld() [0xfe69e0]
/usr/sbin/mysqld() [0x272fc3e]
/lib64/libpthread.so.0(+0x7ea5) [0x7f09d562bea5]
/lib64/libc.so.6(clone+0x6d) [0x7f09d3a0e8dd]



Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f08e5343368): insert into `client_invoices` (`customer_id`, `invoice_date`, `sub_total`, `vat`, `total`, `client_operating_unit_id`, `client_invoice_id`, `invoiced`, `paid`, `created_by`, `updated_by`, `updated_at`, `created_at`) values (459, '2020-10-18 08:03:01', '24202.53', '0', '24202.53', 1, 0, 0, 0, 47, 47, '2020-10-20 08:03:02', '2020-10-20 08:03:02')
Connection ID (thread ID): 743
Status: NOT_KILLED



The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2020-10-20T06:03:04.667817Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-10-20T06:03:04.668382Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 59229
2020-10-20T06:03:04.685040Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-20T06:03:07.357601Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-20T06:03:08.537376Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-10-20T06:03:08.656148Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-10-20T06:03:08.656724Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-10-20T06:03:08.733111Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.

When I run SHOW TABLE client_invoices;, this is the result I get:

CREATE TABLE `client_invoices` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
 `client_invoice_id` bigint unsigned NOT NULL COMMENT 'Unique autoincrementing bigint for this client',
 `client_operating_unit_id` int unsigned NOT NULL,
 `customer_id` int unsigned NOT NULL,
 `invoice_status_id` int unsigned NOT NULL DEFAULT '1',
 `invoice_date` date NOT NULL,
 `sub_total` decimal(14,2) DEFAULT '0.00',
 `vat` decimal(14,2) DEFAULT '0.00',
 `total` decimal(14,2) DEFAULT '0.00',
 `invoiced` tinyint(1) NOT NULL COMMENT 'Invoice sent to customer',
 `paid` tinyint(1) NOT NULL COMMENT 'Invoice been paid',
 `exported` tinyint(1) NOT NULL DEFAULT '0',
 `automatic_invoice` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Invoice has been generated automatically',
 `comments` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `created_by` int unsigned NOT NULL DEFAULT '0',
 `updated_by` int unsigned NOT NULL DEFAULT '0',
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `invoice_id` (`client_operating_unit_id`,`client_invoice_id`),
 KEY `client_invoices_client_operating_unit_id_index` (`client_operating_unit_id`),
 KEY `client_invoices_customer_id_index` (`customer_id`),
 KEY `client_invoices_invoice_status_id_index` (`invoice_status_id`),
 KEY `client_invoices_invoice_date_index` (`invoice_date`),
 CONSTRAINT `client_invoices_client_operating_unit_id_foreign` FOREIGN KEY (`client_operating_unit_id`) REFERENCES `client_operating_units` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
 CONSTRAINT `client_invoices_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
 CONSTRAINT `client_invoices_invoice_status_id_foreign` FOREIGN KEY (`invoice_status_id`) REFERENCES `client_invoice_status` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=60975 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

user_1627522 · Answer

MySQL 8.0.23 released today (01/18/2021) should fix this issue:

Bug#32045681: Heap-use-after-free in triggers Item_splocal::this_item()

This problem requires several subsequent sessions to use the same trigger procedure, containing a local variable. Since Items are prepared only on first installment in the server, and the m_thd member of Item_splocal is initialized only on preparation, on second use m_thd is different from the current THD and a failure is provoked.

There are at least two ways to fix this problem. One is to assign m_thd when binding the procedure to the new session. However, the solution may be considered vulnerable and we may risk ending with the same problem. The strategy chosen here is to remove the m_thd member and use current_thd instead. The existing code warns about it, however accessing current_thd as a thread-local variable is sufficiently fast now, and the same principle is used several other places in the server. Still, passing a THD as context argument would be even better, but is far too intrusive for a bugfix.

Reviewed by: Dmitry Lenev Dmitry.Lenev@oracle.com

https://github.com/mysql/mysql-server/commit/aecc02f8c75beb0f5911b02b8364b4e2ba22a25a

Waiting for Percona release to test it

user_3604824 · Answer

I was able to track down the problem only to a update trigger (others seems to work just fine).

The strange thing is that this test update trigger works just fine:

CREATE DEFINER=`root`@`%` TRIGGER `nobreak_status_AFTER_UPDATE` AFTER UPDATE ON `nobreak_status` FOR EACH ROW BEGIN
    DECLARE old_values VARCHAR(2000);
    
    SET old_values := '';
    
    
    IF TRUE THEN
        INSERT INTO cybersecurity.nobreak_status_changelog (`date`, `user`, `type`, result, attribute) VALUES (NOW(), USER(), 'UPDATE', '', '');
    END IF;
END

while this one crashes the mysql server randomly:

CREATE DEFINER=`root`@`%` TRIGGER `nobreak_status_AFTER_UPDATE` AFTER UPDATE ON `nobreak_status` FOR EACH ROW BEGIN
    DECLARE old_values VARCHAR(2000);
    
    SET old_values := '';
    
    
    IF old_values = '' THEN
        INSERT INTO cybersecurity.nobreak_status_changelog (`date`, `user`, `type`, result, attribute) VALUES (NOW(), USER(), 'UPDATE', '', '');
    END IF;
END

UPDATE:

Return from SHOW CREATE TABLE cybersecurity.nobreak_status_changelog;

CREATE TABLE `nobreak_status_changelog` (
  `date` datetime NOT NULL,
  `user` varchar(100) NOT NULL,
  `type` varchar(45) NOT NULL,
  `result` varchar(1000) NOT NULL,
  `attribute` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

UPDATE 2: Just checked the server logs and these started to show up at least once a day since the update:

Oct 29 08:01:50 SERVER systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Oct 29 08:01:50 SERVER systemd[1]: mysql.service: Failed with result 'exit-code'.
Oct 29 08:01:50 SERVER systemd[1]: mysql.service: Service RestartSec=100ms expired, scheduling restart.
Oct 29 08:01:50 SERVER systemd[1]: mysql.service: Scheduled restart job, restart counter is at 12.
Oct 29 08:01:50 SERVER systemd[1]: Stopped MySQL Community Server.
Oct 29 08:01:50 SERVER systemd[1]: Starting MySQL Community Server...
Oct 29 08:01:55 SERVER systemd[1]: Started MySQL Community Server.

user_5978707 · Answer

Joined this bug-club after update to 8.0.22, which has a numerous security fixes and we had to upgrade. In my case the trigger is after delete:

CREATE TRIGGER `TRG_n2d_delete` AFTER DELETE ON `n2d` FOR EACH ROW BEGIN
    DECLARE vn_state tinyint(1);

    SELECT n_state FROM nets WHERE net_id=OLD.net_id INTO vn_state;
    IF (vn_state=0) THEN
        UPDATE dicts SET hits=hits-1 WHERE d_id=OLD.d_id;
        UPDATE nets SET hits=hits-1 WHERE net_id=OLD.net_id;
    END IF;
END

Looking at common denominator, it's like the issue is with accessing the local variables.

Edit: rewrote the trigger dropping the internal variable and see no crashes. My current code:

CREATE TRIGGER `TRG_n2d_delete` AFTER DELETE ON `n2d` FOR EACH ROW BEGIN
    IF ((SELECT n_state FROM nets WHERE net_id=OLD.net_id)=0) THEN
        UPDATE dicts SET hits=hits-1 WHERE d_id=OLD.d_id;
        UPDATE nets SET hits=hits-1 WHERE net_id=OLD.net_id;
    END IF;
END

user_1627522 · Answer

Same problem after upgrade 5.7 to 8.0.22-13 (Percona Server). Bug was reported in https://jira.percona.com/browse/PS-7477

I tried without success:

Turn off replication (disable slave)
Recreate trigger
Recreate table
Remove INSERTs from trigger

@RealEnder suggested that the problem could be in the use of local variables inside trigger, so I removed all variables from trigger and no crashes since it.

I still need to find alternatives for not using variables in the trigger, it is not a solution, but a workaround until they fix the bug.

Update1: 24 hours w/o crash

Update2: 11 days w/o crash

user_3030592 · Answer

I updated my trigger to the following and that has stopped the MySQL server crashes (its been close to 36 hours now without a restart):

CREATE TRIGGER max_client_invoice_before_insert
                        BEFORE INSERT
                           ON client_invoices FOR EACH ROW
                        BEGIN
                           DECLARE vMax int(11);
                           SELECT IF(COUNT(client_invoice_id) > 0, MAX(client_invoice_id) + 1, 1) from client_invoices where client_operating_unit_id = NEW.client_operating_unit_id INTO vMax;
                           SET NEW.client_invoice_id = vMax;
                        END

The only difference was removing

SELECT IFNULL(max(client_invoice_id),0)

and replacing it with

SELECT IF(COUNT(client_invoice_id) > 0, MAX(client_invoice_id) + 1, 1)

user_658393 · Answer

One of my triggers that often causes a crash

CREATE TRIGGER `items_insert`
    AFTER INSERT
        ON `items` FOR EACH ROW
    BEGIN
        DECLARE rowcount INT;
        SET rowcount = (SELECT COUNT(*) from entity_log WHERE global_ref=NEW.global_ref LIMIT 1);
        IF (rowcount=0 AND LEFT(NEW.global_ref, 2)="ZZ") THEN
            INSERT INTO entity_log (global_ref, entity_type, server_updated_at) values (NEW.global_ref, "IL", UTC_TIMESTAMP());
        END IF;
    END

The table

CREATE TABLE `entity_log` (
  `entity_id` int NOT NULL,
  `global_ref` varchar(30) COLLATE utf8mb4_general_ci NOT NULL,
  `entity_type` varchar(2) COLLATE utf8mb4_general_ci NOT NULL,
  `subentity_type` char(2) COLLATE utf8mb4_general_ci NOT NULL,
  `server_updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Unsure how to resolve this one as it is very simple

My MySQL bug report is referenced at https://bugs.mysql.com/bug.php?id=101342 but MySQL made it private, like other report(s) mentioned in the comments, for security reasons

user_1032291 · Answer

same problem here. In my case it looks like it's happening on triggers where I have this select:

SET vTipoEvento = '';
SELECT TipoEvento
  INTO vTipoEvento
  FROM evexcl
 WHERE eclid = OLD.IdEsito;

which is pretty simple. I don't even know how to rewrite it.... Problems begun with mysql 8.0.22 (ubuntu 20.04.2)

I have lots of triggers and in my case it keeps on rebooting

user_2239479 · Answer

you can try change how to call query.

The bug's name is: Prepared statements involving stored programs could cause heap-use-after-free memory problems.

So I tried to change how to call statement and I have changed from prepare to single query. It should help, because query is not execuded as "prepared statement'. I don't know if help now, but I hope it will help for some weeks until Percona 8.0.23 will be available.

MySQL Server restarts after trigger execution

8 Answers