触发器执行后 MySQL 服务器重启

Question

我在 MySQL 中有一个触发器，它导致 MySQL 服务器在每次触发时重新启动。这在一天前 8.0.22 版更新后开始发生。我的触发器如下:

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

由于最近的更新，我是否遗漏了什么，或者如果我似乎无法找到服务器崩溃的原因，有什么更好更有效的方法可以实现相同的目标？
我还检查了日志，这就是我发现的:

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.

当我跑 SHOW TABLE client_invoices; ，这是我得到的结果:

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 (01/18/2021) 应该可以解决这个问题:

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

This problem requires several subsequent sessions to use the sametrigger procedure, containing a local variable. Since Items areprepared only on first installment in the server, and the m_thd memberof Item_splocal is initialized only on preparation, on second usem_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 assignm_thd when binding the procedure to the new session. However, thesolution may be considered vulnerable and we may risk ending withthe same problem. The strategy chosen here is to remove the m_thdmember and use current_thd instead. The existing code warns about it,however accessing current_thd as a thread-local variable issufficiently fast now, and the same principle is used several otherplaces in the server. Still, passing a THD as context argument wouldbe 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
等待 Percona 发布来测试它