[MDEV-20074] Lost connection on update trigger Created: 2019-07-16  Updated: 2020-06-03  Resolved: 2019-10-17

Status: Closed
Project: MariaDB Server
Component/s: Sequences, Server
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.3.19, 10.4.9

Type: Bug Priority: Critical
Reporter: miracee Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 18.04 and Ubuntu 19.04

Issue Links:
Relates
relates to MDEV-22785 Crash with prepared statements and NE... Closed

 Description   

Test:

drop database if exists test1;
 
create database test1;
 
use test1;
 
 
 
create sequence seq_a_id increment by 1 start with 1;
 
create table p (p_id integer, p_name varchar(128), p_first_name varchar(128));
 
create table archiv (a_id integer default nextval(seq_a_id), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
 
 
 
insert into p values
 
(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
 
 
 
DELIMITER $$
 
 
 
CREATE TRIGGER tr_upd
 
BEFORE UPDATE on p
 
FOR EACH ROW
 
BEGIN
 
INSERT INTO archiv(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
 
END;
 
$$
 
DELIMITER ;


MariaDB [test1]> update p set p_first_name='Yunxi' where p_id=1;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query

It is also not working when using AFTER UPDATE.
And it is also not working on BEFORE/AFTER DELETE

Best Regards,

Susanne

 Comments   
Comment by miracee [ 2019-07-16 ]

Version 10.4.6

Comment by Elena Stepanova [ 2019-07-21 ]

Thanks for the report and test case.

10.4 9d6b601e

 
#3  <signal handler called>
 
#4  0x000055971b4a0de8 in Item_func_nextval::update_table (this=0x7f671c1a3900) at /data/src/10.4/sql/item_func.h:3428
 
#5  0x000055971b49b4c3 in Item_func_nextval::val_int (this=0x7f671c1a3900) at /data/src/10.4/sql/item_func.cc:6628
 
#6  0x000055971b42fafc in Item::save_int_in_field (this=0x7f671c1a3900, field=0x7f671c1a1cf8, no_conversions=false) at /data/src/10.4/sql/item.cc:6525
 
#7  0x000055971b2cf2b0 in Type_handler_int_result::Item_save_in_field (this=0x55971c758af8 <type_handler_longlong>, item=0x7f671c1a3900, field=0x7f671c1a1cf8, no_conversions=false) at /data/src/10.4/sql/sql_type.cc:3618
 
#8  0x000055971b42fba7 in Item::save_in_field (this=0x7f671c1a3900, field=0x7f671c1a1cf8, no_conversions=false) at /data/src/10.4/sql/item.cc:6535
 
#9  0x000055971b1f6490 in TABLE::update_default_fields (this=0x7f671c1a0c40, update_command=false, ignore_errors=false) at /data/src/10.4/sql/table.cc:8316
 
#10 0x000055971b039483 in fill_record (thd=0x7f671c000b00, table_arg=0x7f671c1a0c40, fields=..., values=..., ignore_errors=false, update=false) at /data/src/10.4/sql/sql_base.cc:8518
 
#11 0x000055971b03998c in fill_record_n_invoke_before_triggers (thd=0x7f671c000b00, table=0x7f671c1a0c40, fields=..., values=..., ignore_errors=false, event=TRG_EVENT_INSERT) at /data/src/10.4/sql/sql_base.cc:8652
 
#12 0x000055971b07f72d in mysql_insert (thd=0x7f671c000b00, table_list=0x7f671c077428, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /data/src/10.4/sql/sql_insert.cc:972
 
#13 0x000055971b0ca4f6 in mysql_execute_command (thd=0x7f671c000b00) at /data/src/10.4/sql/sql_parse.cc:4512
 
#14 0x000055971afe3b1f in sp_instr_stmt::exec_core (this=0x7f671c07af38, thd=0x7f671c000b00, nextp=0x7f6733056684) at /data/src/10.4/sql/sp_head.cc:3607
 
#15 0x000055971afe2e7c in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f671c07af80, thd=0x7f671c000b00, nextp=0x7f6733056684, open_tables=false, instr=0x7f671c07af38) at /data/src/10.4/sql/sp_head.cc:3335
 
#16 0x000055971afe36c4 in sp_instr_stmt::execute (this=0x7f671c07af38, thd=0x7f671c000b00, nextp=0x7f6733056684) at /data/src/10.4/sql/sp_head.cc:3513
 
#17 0x000055971afdd2e4 in sp_head::execute (this=0x7f671c076398, thd=0x7f671c000b00, merge_da_on_success=false) at /data/src/10.4/sql/sp_head.cc:1346
 
#18 0x000055971afde3e7 in sp_head::execute_trigger (this=0x7f671c076398, thd=0x7f671c000b00, db_name=0x7f671c06c088, table_name=0x7f671c06c098, grant_info=0x7f671c075c88) at /data/src/10.4/sql/sp_head.cc:1755
 
#19 0x000055971b1bf94e in Table_triggers_list::process_triggers (this=0x7f671c075708, thd=0x7f671c000b00, event=TRG_EVENT_UPDATE, time_type=TRG_ACTION_BEFORE, old_row_is_record1=true) at /data/src/10.4/sql/sql_trigger.cc:2202
 
#20 0x000055971b0399c5 in fill_record_n_invoke_before_triggers (thd=0x7f671c000b00, table=0x7f671c070540, fields=..., values=..., ignore_errors=false, event=TRG_EVENT_UPDATE) at /data/src/10.4/sql/sql_base.cc:8657
 
#21 0x000055971b1cec5d in mysql_update (thd=0x7f671c000b00, table_list=0x7f671c013210, fields=..., values=..., conds=0x7f671c013c60, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f6733057860, updated_return=0x7f6733057920) at /data/src/10.4/sql/sql_update.cc:975
 
#22 0x000055971b0c9d50 in mysql_execute_command (thd=0x7f671c000b00) at /data/src/10.4/sql/sql_parse.cc:4360
 
#23 0x000055971b0d63a7 in mysql_parse (thd=0x7f671c000b00, rawbuf=0x7f671c013108 "update p set p_first_name='Yunxi' where p_id=1", length=46, parser_state=0x7f6733058180, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7892
 
#24 0x000055971b0c2651 in dispatch_command (command=COM_QUERY, thd=0x7f671c000b00, packet=0x7f671c008311 "update p set p_first_name='Yunxi' where p_id=1", packet_length=46, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1827
 
#25 0x000055971b0c0e0f in do_command (thd=0x7f671c000b00) at /data/src/10.4/sql/sql_parse.cc:1360
 
#26 0x000055971b23a19a in do_handle_one_connection (connect=0x55971dfdb360) at /data/src/10.4/sql/sql_connect.cc:1404
 
#27 0x000055971b239ee9 in handle_one_connection (arg=0x55971dfdb360) at /data/src/10.4/sql/sql_connect.cc:1306
 
#28 0x000055971bb652ed in pfs_spawn_thread (arg=0x55971deff7f0) at /data/src/10.4/storage/perfschema/pfs.cc:1862
 
#29 0x00007f673ea234a4 in start_thread (arg=0x7f6733059700) at pthread_create.c:456
 
#30 0x00007f673cf6bd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Comment by Oleksandr Byelkin [ 2019-10-15 ] 

create sequence s1 increment by 1 start with 1;
 
create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
 
create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
 
 
 
insert into t1 values
 
(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
 
 
 
DELIMITER $$;
 
 
 
CREATE TRIGGER tr_upd
 
BEFORE UPDATE on t1
 
FOR EACH ROW
 
BEGIN
 
INSERT INTO t2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
 
END;
 
$$
 
DELIMITER ;$$
 
 
 
update t1 set p_first_name='Yunxi' where p_id=1;
 
 
 
drop sequence s1;
 
drop table t1,t2;

Comment by Oleksandr Byelkin [ 2019-10-16 ]

the default() code which address "next" table to find table referred in default expression, somehow already points on that "next", and so trying to get next to that "next", which is absent:

  void update_table()
 
  {
 
    if (!(table= table_list->table))
 
    {
 
      /*
 
        If nextval was used in DEFAULT then next_local points to
 
        the table_list used by to open the sequence table
 
      */
 
      table= table_list->next_local->table;
 
    }
 
  }

Comment by Oleksandr Byelkin [ 2019-10-16 ]

SP INSERT instruction has only one table (t2) in both local and global lists (no surprise that s1 is not opened)

Comment by Oleksandr Byelkin [ 2019-10-16 ]

internal_tables of the table t2 present, but is not opened

Comment by Oleksandr Byelkin [ 2019-10-16 ]

It is alway not the best idea to check current command, especially inside trigger:

if (table->internal_tables &&
 
      ((sql_command_flags[thd->lex->sql_command] & CF_INSERTS_DATA) ||
 
       thd->lex->default_used))

Comment by Oleksandr Byelkin [ 2019-10-16 ]

The check above made when we open tables for UPDATE, and have no idea what for the tables is (some for a trigger and INSERT inside it)

Generated at Thu Feb 08 08:56:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.