我在MySQL中有一个触发器,每次被触发时都会导致MySQL服务器重新启动。这是在1天前进行版本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