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<Item*> const&, mem_root_deque<Item*> 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

Answer 1

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 [email protected]

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

Waiting for Percona release to test it