Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3(EOL), 10.4(EOL)
    • 10.3.19, 10.4.9
    • Sequences, Server
    • None
    • Ubuntu 18.04 and Ubuntu 19.04

    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

      Attachments

        Issue Links

          Activity

            miracee miracee added a comment -

            Version 10.4.6

            miracee miracee added a comment - Version 10.4.6

            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
            

            elenst Elena Stepanova added a comment - 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

            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;
            

            sanja Oleksandr Byelkin added a comment - 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;

            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;
                }
              }
            

            sanja Oleksandr Byelkin added a comment - 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; } }
            sanja Oleksandr Byelkin added a comment - - edited

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

            sanja Oleksandr Byelkin added a comment - - edited SP INSERT instruction has only one table (t2) in both local and global lists (no surprise that s1 is not opened)

            internal_tables of the table t2 present, but is not opened

            sanja Oleksandr Byelkin added a comment - internal_tables of the table t2 present, but is not opened

            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))
            

            sanja Oleksandr Byelkin added a comment - 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))

            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)

            sanja Oleksandr Byelkin added a comment - 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)

            People

              sanja Oleksandr Byelkin
              miracee miracee
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.