[MDEV-13170] Database service (MySQL) stops after update with trigger - Jira

Alice Sherepa added a comment - 2017-06-26 15:51

Reproduced on 10.2.7-debug Ubuntu 16.04

database dump from https://jira.mariadb.org/browse/MDEV-13107

simplified trigger:

delimiter //

CREATE  TRIGGER `pl_aurtrg` AFTER UPDATE ON `pl` FOR EACH ROW begin

drop temporary table if exists trg_u;

create temporary table trg_u as

WITH l AS

	SELECT a.*,

	Max(if(c.col_id=i.col_id,i.col_id,null))  over (PARTITION BY a.db_time, a.au_nr) bcol_id,

	Max(if(c.col_id=i.col_id,i.new_val,null)) over (PARTITION BY a.db_time, a.au_nr)  bcol_val

	 FROM

(SELECT d1 db_time, id1 au_nr, id2 tab_id FROM gttn) a

join au_idata i ON (a.db_time=i.db_time AND a.au_nr=i.au_nr)

 join tab_col c ON (a.tab_id=c.tab_id AND i.col_id=c.col_id ))

SELECT 1;

  END;//

 delimiter ;

Alice Sherepa added a comment - 2017-06-26 15:51 Reproduced on 10.2.7-debug Ubuntu 16.04 database dump from https://jira.mariadb.org/browse/MDEV-13107 simplified trigger: delimiter // CREATE TRIGGER `pl_aurtrg` AFTER UPDATE ON `pl` FOR EACH ROW begin drop temporary table if exists trg_u; create temporary table trg_u as WITH l AS ( SELECT a.*, Max ( if (c.col_id=i.col_id,i.col_id, null )) over (PARTITION BY a.db_time, a.au_nr) bcol_id, Max ( if (c.col_id=i.col_id,i.new_val, null )) over (PARTITION BY a.db_time, a.au_nr) bcol_val FROM ( SELECT d1 db_time, id1 au_nr, id2 tab_id FROM gttn) a join au_idata i ON (a.db_time=i.db_time AND a.au_nr=i.au_nr) join tab_col c ON (a.tab_id=c.tab_id AND i.col_id=c.col_id )) SELECT 1; END ;// delimiter ;

Elena Stepanova added a comment - 2017-06-26 17:04

balta, alice, thanks for the report and the simplified trigger.

MTR test case:

CREATE TABLE t1 (i1 INT, i2 INT, i3 INT);

INSERT INTO t1 VALUES (1,2,3),(4,5,6); # Unimportant, can be an empty table

CREATE PROCEDURE pr()

  SELECT a.*,

    MAX(a.i1) OVER (PARTITION BY a.i3) f1,

    MAX(a.i2) OVER (PARTITION BY a.i3) f2

  FROM (SELECT * FROM t1) a

CALL pr();

CALL pr();

Reproducible with MyISAM and InnoDB. Also reproducible with prepared statements and triggers (as in the original report).

10.2 0d69d313a11fb03f31d71282f622b25d0a4764b8
#3 <signal handler called>
#4 0x0000563b9e2df08e in find_field_in_tables (thd=0x7f43a4000b00, item=0x7f43a40378a0, first_table=0x7f43a4036c38, last_table=0x0, ref=0x7f43bac2eb98, report_error=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /data/src/10.2/sql/sql_base.cc:5815
#5 0x0000563b9e3c068a in find_order_in_list (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, order=0x7f43a408ade8, fields=..., all_fields=..., is_group_field=true, from_window_spec=true) at /data/src/10.2/sql/sql_select.cc:22131
#6 0x0000563b9e3c0bb4 in setup_group (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, fields=..., all_fields=..., order=0x7f43a408ade8, hidden_group_fields=0x7f43bac2ed1f, from_window_spec=true) at /data/src/10.2/sql/sql_select.cc:22302
#7 0x0000563b9e5081bb in setup_windows (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /data/src/10.2/sql/sql_window.cc:210
#8 0x0000563b9e3875d1 in setup_without_group (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, leaves=..., fields=..., all_fields=..., conds=0x7f43a403d2c8, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7f43a403d1a7, reserved=0x7f43a4035074) at /data/src/10.2/sql/sql_select.cc:662
#9 0x0000563b9e387e16 in JOIN::prepare (this=0x7f43a403cec8, tables_init=0x7f43a4036c38, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f43a4034dc8, unit_arg=0x7f43a4034690) at /data/src/10.2/sql/sql_select.cc:817
#10 0x0000563b9e391f9f in mysql_select (thd=0x7f43a4000b00, tables=0x7f43a4036c38, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7f43a403cea8, unit=0x7f43a4034690, select_lex=0x7f43a4034dc8) at /data/src/10.2/sql/sql_select.cc:3646
#11 0x0000563b9e3869d6 in handle_select (thd=0x7f43a4000b00, lex=0x7f43a40345c8, result=0x7f43a403cea8, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:373
#12 0x0000563b9e352946 in execute_sqlcom_select (thd=0x7f43a4000b00, all_tables=0x7f43a4036c38) at /data/src/10.2/sql/sql_parse.cc:6438
#13 0x0000563b9e348985 in mysql_execute_command (thd=0x7f43a4000b00) at /data/src/10.2/sql/sql_parse.cc:3453
#14 0x0000563b9e7341e0 in sp_instr_stmt::exec_core (this=0x7f43a408baa0, thd=0x7f43a4000b00, nextp=0x7f43bac301c4) at /data/src/10.2/sql/sp_head.cc:3235
#15 0x0000563b9e733850 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f43a408bae0, thd=0x7f43a4000b00, nextp=0x7f43bac301c4, open_tables=false, instr=0x7f43a408baa0) at /data/src/10.2/sql/sp_head.cc:2998
#16 0x0000563b9e733e8b in sp_instr_stmt::execute (this=0x7f43a408baa0, thd=0x7f43a4000b00, nextp=0x7f43bac301c4) at /data/src/10.2/sql/sp_head.cc:3151
#17 0x0000563b9e72f0cc in sp_head::execute (this=0x7f43a4089b78, thd=0x7f43a4000b00, merge_da_on_success=true) at /data/src/10.2/sql/sp_head.cc:1324
#18 0x0000563b9e731157 in sp_head::execute_procedure (this=0x7f43a4089b78, thd=0x7f43a4000b00, args=0x7f43a40052f0) at /data/src/10.2/sql/sp_head.cc:2113
#19 0x0000563b9e34707d in do_execute_sp (thd=0x7f43a4000b00, sp=0x7f43a4089b78) at /data/src/10.2/sql/sql_parse.cc:2885
#20 0x0000563b9e350120 in mysql_execute_command (thd=0x7f43a4000b00) at /data/src/10.2/sql/sql_parse.cc:5794
#21 0x0000563b9e356318 in mysql_parse (thd=0x7f43a4000b00, rawbuf=0x7f43a4012368 "CALL pr()", length=9, parser_state=0x7f43bac31200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7875
#22 0x0000563b9e3443f1 in dispatch_command (command=COM_QUERY, thd=0x7f43a4000b00, packet=0x7f43a40087c1 "CALL pr()", packet_length=9, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1817
#23 0x0000563b9e342d32 in do_command (thd=0x7f43a4000b00) at /data/src/10.2/sql/sql_parse.cc:1362
#24 0x0000563b9e48e3ad in do_handle_one_connection (connect=0x563ba178c340) at /data/src/10.2/sql/sql_connect.cc:1354
#25 0x0000563b9e48e13a in handle_one_connection (arg=0x563ba178c340) at /data/src/10.2/sql/sql_connect.cc:1260
#26 0x0000563b9e8a8d78 in pfs_spawn_thread (arg=0x563ba17c4bb0) at /data/src/10.2/storage/perfschema/pfs.cc:1862
#27 0x00007f43c68a5494 in start_thread (arg=0x7f43bac32700) at pthread_create.c:333
#28 0x00007f43c4a1d93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Elena Stepanova added a comment - 2017-06-26 17:04 balta , alice , thanks for the report and the simplified trigger. MTR test case: CREATE TABLE t1 (i1 INT , i2 INT , i3 INT ); INSERT INTO t1 VALUES (1,2,3),(4,5,6); # Unimportant, can be an empty table CREATE PROCEDURE pr() SELECT a.*, MAX (a.i1) OVER (PARTITION BY a.i3) f1, MAX (a.i2) OVER (PARTITION BY a.i3) f2 FROM ( SELECT * FROM t1) a ; CALL pr(); CALL pr(); Reproducible with MyISAM and InnoDB. Also reproducible with prepared statements and triggers (as in the original report). 10.2 0d69d313a11fb03f31d71282f622b25d0a4764b8 #3 <signal handler called> #4 0x0000563b9e2df08e in find_field_in_tables (thd=0x7f43a4000b00, item=0x7f43a40378a0, first_table=0x7f43a4036c38, last_table=0x0, ref=0x7f43bac2eb98, report_error=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /data/src/10.2/sql/sql_base.cc:5815 #5 0x0000563b9e3c068a in find_order_in_list (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, order=0x7f43a408ade8, fields=..., all_fields=..., is_group_field=true, from_window_spec=true) at /data/src/10.2/sql/sql_select.cc:22131 #6 0x0000563b9e3c0bb4 in setup_group (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, fields=..., all_fields=..., order=0x7f43a408ade8, hidden_group_fields=0x7f43bac2ed1f, from_window_spec=true) at /data/src/10.2/sql/sql_select.cc:22302 #7 0x0000563b9e5081bb in setup_windows (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /data/src/10.2/sql/sql_window.cc:210 #8 0x0000563b9e3875d1 in setup_without_group (thd=0x7f43a4000b00, ref_pointer_array=..., tables=0x7f43a4036c38, leaves=..., fields=..., all_fields=..., conds=0x7f43a403d2c8, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7f43a403d1a7, reserved=0x7f43a4035074) at /data/src/10.2/sql/sql_select.cc:662 #9 0x0000563b9e387e16 in JOIN::prepare (this=0x7f43a403cec8, tables_init=0x7f43a4036c38, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f43a4034dc8, unit_arg=0x7f43a4034690) at /data/src/10.2/sql/sql_select.cc:817 #10 0x0000563b9e391f9f in mysql_select (thd=0x7f43a4000b00, tables=0x7f43a4036c38, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7f43a403cea8, unit=0x7f43a4034690, select_lex=0x7f43a4034dc8) at /data/src/10.2/sql/sql_select.cc:3646 #11 0x0000563b9e3869d6 in handle_select (thd=0x7f43a4000b00, lex=0x7f43a40345c8, result=0x7f43a403cea8, setup_tables_done_option=0) at /data/src/10.2/sql/sql_select.cc:373 #12 0x0000563b9e352946 in execute_sqlcom_select (thd=0x7f43a4000b00, all_tables=0x7f43a4036c38) at /data/src/10.2/sql/sql_parse.cc:6438 #13 0x0000563b9e348985 in mysql_execute_command (thd=0x7f43a4000b00) at /data/src/10.2/sql/sql_parse.cc:3453 #14 0x0000563b9e7341e0 in sp_instr_stmt::exec_core (this=0x7f43a408baa0, thd=0x7f43a4000b00, nextp=0x7f43bac301c4) at /data/src/10.2/sql/sp_head.cc:3235 #15 0x0000563b9e733850 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7f43a408bae0, thd=0x7f43a4000b00, nextp=0x7f43bac301c4, open_tables=false, instr=0x7f43a408baa0) at /data/src/10.2/sql/sp_head.cc:2998 #16 0x0000563b9e733e8b in sp_instr_stmt::execute (this=0x7f43a408baa0, thd=0x7f43a4000b00, nextp=0x7f43bac301c4) at /data/src/10.2/sql/sp_head.cc:3151 #17 0x0000563b9e72f0cc in sp_head::execute (this=0x7f43a4089b78, thd=0x7f43a4000b00, merge_da_on_success=true) at /data/src/10.2/sql/sp_head.cc:1324 #18 0x0000563b9e731157 in sp_head::execute_procedure (this=0x7f43a4089b78, thd=0x7f43a4000b00, args=0x7f43a40052f0) at /data/src/10.2/sql/sp_head.cc:2113 #19 0x0000563b9e34707d in do_execute_sp (thd=0x7f43a4000b00, sp=0x7f43a4089b78) at /data/src/10.2/sql/sql_parse.cc:2885 #20 0x0000563b9e350120 in mysql_execute_command (thd=0x7f43a4000b00) at /data/src/10.2/sql/sql_parse.cc:5794 #21 0x0000563b9e356318 in mysql_parse (thd=0x7f43a4000b00, rawbuf=0x7f43a4012368 "CALL pr()", length=9, parser_state=0x7f43bac31200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7875 #22 0x0000563b9e3443f1 in dispatch_command (command=COM_QUERY, thd=0x7f43a4000b00, packet=0x7f43a40087c1 "CALL pr()", packet_length=9, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1817 #23 0x0000563b9e342d32 in do_command (thd=0x7f43a4000b00) at /data/src/10.2/sql/sql_parse.cc:1362 #24 0x0000563b9e48e3ad in do_handle_one_connection (connect=0x563ba178c340) at /data/src/10.2/sql/sql_connect.cc:1354 #25 0x0000563b9e48e13a in handle_one_connection (arg=0x563ba178c340) at /data/src/10.2/sql/sql_connect.cc:1260 #26 0x0000563b9e8a8d78 in pfs_spawn_thread (arg=0x563ba17c4bb0) at /data/src/10.2/storage/perfschema/pfs.cc:1862 #27 0x00007f43c68a5494 in start_thread (arg=0x7f43bac32700) at pthread_create.c:333 #28 0x00007f43c4a1d93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Alice Sherepa added a comment - 2018-05-23 10:49

Fixed by Vicentiu Ciorbaru in a1e589be9b (10.2.9)

Alice Sherepa added a comment - 2018-05-23 10:49 Fixed by Vicentiu Ciorbaru in a1e589be9b (10.2.9)

Tadas Balaišis added a comment - 2018-05-23 11:11

I'm a bit confused about fix version. You wrote that it is fixed in 10.2.9.
I've just downloaded brand new mariadb-10.2.15-winx64.msi. It shows (version()) 10.3.4-MariaDB. And it still has this bug. When I'll be able to get fixed version (aprox.)? And what version number it would be shown?

Tadas Balaišis added a comment - 2018-05-23 11:11 I'm a bit confused about fix version. You wrote that it is fixed in 10.2.9. I've just downloaded brand new mariadb-10.2.15-winx64.msi. It shows (version()) 10.3.4-MariaDB. And it still has this bug. When I'll be able to get fixed version (aprox.)? And what version number it would be shown?

Vladislav Vaintroub added a comment - 2018-05-23 15:13

10.2.15 does not show version 10.3.4-MariaDB. I downloaded and installed the MSI, to check.
the package name,and SELECT VERSION() are derived from the same source in source repository, VERSION file.

maybe you're confused because you have couple of instances of MariaDB on your box?

Vladislav Vaintroub added a comment - 2018-05-23 15:13 10.2.15 does not show version 10.3.4-MariaDB. I downloaded and installed the MSI, to check. the package name,and SELECT VERSION() are derived from the same source in source repository, VERSION file. maybe you're confused because you have couple of instances of MariaDB on your box?

Elena Stepanova added a comment - 2018-05-23 15:15

Run select @@basedir on the same instance where you're getting 10.3.4-MariaDB, it will show where it runs from.

Elena Stepanova added a comment - 2018-05-23 15:15 Run select @@basedir on the same instance where you're getting 10.3.4-MariaDB , it will show where it runs from.

Tadas Balaišis added a comment - 2018-05-23 17:16

Thank you. You were right. In February I installed mariadb-10.3.4-winx64.msi then later mariadb-10.2.14-winx64.msi and today mariadb-10.2.15-winx64.msi. I see that 10.2 and 10.3 are in different folders. And I see that my service is still driven by 10.3 binaries. It was easy to be mistaken. I install 10.2xxx and I believe that's what is running on from this moment.
So the truth is that the bug was also on 10.3.4.

Tadas Balaišis added a comment - 2018-05-23 17:16 Thank you. You were right. In February I installed mariadb-10.3.4-winx64.msi then later mariadb-10.2.14-winx64.msi and today mariadb-10.2.15-winx64.msi. I see that 10.2 and 10.3 are in different folders. And I see that my service is still driven by 10.3 binaries. It was easy to be mistaken. I install 10.2xxx and I believe that's what is running on from this moment. So the truth is that the bug was also on 10.3.4.

Tadas Balaišis added a comment - 2018-05-23 17:25

Just took the latest 10.3 candidate (mariadb-10.3.6-winx64.msi). I checked the version() first. It's 10.3.6-MariaDB. And the bug is reproduced either on it. I hope you'll fix it on 10.3 as well.

Tadas Balaišis added a comment - 2018-05-23 17:25 Just took the latest 10.3 candidate (mariadb-10.3.6-winx64.msi). I checked the version() first. It's 10.3.6-MariaDB. And the bug is reproduced either on it. I hope you'll fix it on 10.3 as well.

Alice Sherepa added a comment - 2018-05-24 11:22 - edited

balta, Thanks for your help!
You were right, patch resolved the simplified testcase, but the original bug is still presented in 10.2, 10.3.

CREATE TABLE t1 (t1_id int, point_id int, ml_id int) ;

INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);

CREATE TABLE t2 (db_time datetime, au_nr int, col_id int, new_val int);

CREATE TABLE t3 (tab_id int, col_id int, col_bp int DEFAULT NULL);

CREATE TABLE t4 (id1 int, id2 int, d1 int);

delimiter //;

CREATE  TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin

CREATE OR REPLACE TEMPORARY TABLE trg_u AS

SELECT 1 FROM

  (SELECT a.*,

          Max(t2.col_id) over (PARTITION BY a.d1),

          Max(t2.new_val) over (PARTITION BY a.d1)

   FROM

     (SELECT d1,id1,id2 FROM t4) a

   JOIN t2 ON (a.d1=t2.db_time)

   JOIN t3 ON (a.id2=t3.tab_id)) l;

 END;//

 delimiter ;//

--error 1062

update t1 set ml_id=8884 where point_id=1;

update t1 set ml_id=8884 where point_id=1;

 52df8040264cc6f322

Thread 1 (Thread 0x7f13b548c700 (LWP 31379)):

#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

#1  0x000055ee551b6d9a in my_write_core (sig=sig@entry=11) at /home/alice/git/10.2/mysys/stacktrace.c:477

#2  0x000055ee54cf67b4 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305

#3  <signal handler called>

#4  find_field_in_tables (thd=thd@entry=0x7f13a40009a8, item=item@entry=0x7f13a4091728, first_table=first_table@entry=0x7f13a408e190, last_table=last_table@entry=0x0, ref=ref@entry=0x7f13b54875e8, report_error=report_error@entry=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /home/alice/git/10.2/sql/sql_base.cc:5846

#5  0x000055ee54b9ef36 in find_order_in_list (thd=thd@entry=0x7f13a40009a8, ref_pointer_array=..., tables=tables@entry=0x7f13a408e190, order=order@entry=0x7f13a40b6100, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22363

#6  0x000055ee54bb555a in setup_group (thd=thd@entry=0x7f13a40009a8, ref_pointer_array=..., tables=tables@entry=0x7f13a408e190, fields=..., all_fields=..., order=0x7f13a40b6100, hidden_group_fields=0x7f13b548772f, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22537

#7  0x000055ee54c860d1 in setup_windows (thd=thd@entry=0x7f13a40009a8, ref_pointer_array=..., tables=tables@entry=0x7f13a408e190, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /home/alice/git/10.2/sql/sql_window.cc:208

#8  0x000055ee54bb7772 in setup_without_group (reserved=0x7f13a40b3724, hidden_group_fields=0x7f13a402e767, win_funcs=..., win_specs=..., group=0x0, order=<optimized out>, conds=0x7f13a402e888, all_fields=..., fields=..., leaves=..., tables=0x7f13a408e190, ref_pointer_array=..., thd=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_select.cc:664

#9  JOIN::prepare (this=this@entry=0x7f13a402e480, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f13a40b3460, unit_arg=0x7f13a40b50d0) at /home/alice/git/10.2/sql/sql_select.cc:819

#10 0x000055ee54c020fb in st_select_lex_unit::prepare (this=this@entry=0x7f13a40b50d0, thd_arg=thd_arg@entry=0x7f13a40009a8, sel_result=<optimized out>, additional_options=additional_options@entry=0) at /home/alice/git/10.2/sql/sql_union.cc:585

#11 0x000055ee54b53268 in mysql_derived_prepare (thd=0x7f13a40009a8, lex=0x7f13a40b3a50, derived=0x7f13a4090960) at /home/alice/git/10.2/sql/sql_derived.cc:770

#12 0x000055ee54b53dc7 in mysql_handle_single_derived (lex=lex@entry=0x7f13a40b3a50, derived=derived@entry=0x7f13a4090960, phases=phases@entry=2) at /home/alice/git/10.2/sql/sql_derived.cc:197

#13 0x000055ee54c190a8 in TABLE_LIST::handle_derived (this=this@entry=0x7f13a4090960, lex=lex@entry=0x7f13a40b3a50, phases=phases@entry=2) at /home/alice/git/10.2/sql/table.cc:7837

#14 0x000055ee54b6a9ee in st_select_lex::handle_derived (this=this@entry=0x7f13a40b4250, lex=0x7f13a40b3a50, phases=phases@entry=2) at /home/alice/git/10.2/sql/sql_lex.cc:3917

#15 0x000055ee54bb710a in JOIN::prepare (this=0x7f13a402de58, tables_init=<optimized out>, wild_num=0, conds_init=<optimized out>, og_num=0, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f13a40b4250, unit_arg=0x7f13a40b3b18) at /home/alice/git/10.2/sql/sql_select.cc:716

#16 0x000055ee54bc4767 in mysql_select (thd=thd@entry=0x7f13a40009a8, tables=0x7f13a4090960, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2415921920, result=0x7f13a402dd60, unit=0x7f13a40b3b18, select_lex=0x7f13a40b4250) at /home/alice/git/10.2/sql/sql_select.cc:3758

#17 0x000055ee54bc48de in handle_select (thd=thd@entry=0x7f13a40009a8, lex=lex@entry=0x7f13a40b3a50, result=result@entry=0x7f13a402dd60, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/alice/git/10.2/sql/sql_select.cc:376

#18 0x000055ee54b7bc9a in mysql_execute_command (thd=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:3952

#19 0x000055ee54df75e4 in sp_instr_stmt::exec_core (this=0x7f13a40900d0, thd=<optimized out>, nextp=0x7f13b5489274) at /home/alice/git/10.2/sql/sp_head.cc:3244

#20 0x000055ee54dfd303 in sp_lex_keeper::reset_lex_and_exec_core (this=this@entry=0x7f13a4090110, thd=thd@entry=0x7f13a40009a8, nextp=nextp@entry=0x7f13b5489274, open_tables=open_tables@entry=false, instr=instr@entry=0x7f13a40900d0) at /home/alice/git/10.2/sql/sp_head.cc:3007

#21 0x000055ee54dfd914 in sp_instr_stmt::execute (this=0x7f13a40900d0, thd=0x7f13a40009a8, nextp=0x7f13b5489274) at /home/alice/git/10.2/sql/sp_head.cc:3160

#22 0x000055ee54dfa13b in sp_head::execute (this=this@entry=0x7f13a40b1a20, thd=thd@entry=0x7f13a40009a8, merge_da_on_success=merge_da_on_success@entry=false) at /home/alice/git/10.2/sql/sp_head.cc:1326

#23 0x000055ee54dfa650 in sp_head::execute_trigger (this=0x7f13a40b1a20, thd=thd@entry=0x7f13a40009a8, db_name=<optimized out>, table_name=<optimized out>, grant_info=grant_info@entry=0x7f13a40a18c0) at /home/alice/git/10.2/sql/sp_head.cc:1655

#24 0x000055ee54bfa7dc in Table_triggers_list::process_triggers (this=0x7f13a40a1440, thd=thd@entry=0x7f13a40009a8, event=event@entry=TRG_EVENT_UPDATE, time_type=time_type@entry=TRG_ACTION_AFTER, old_row_is_record1=old_row_is_record1@entry=true) at /home/alice/git/10.2/sql/sql_trigger.cc:2189

#25 0x000055ee54c06fd1 in mysql_update (thd=thd@entry=0x7f13a40009a8, table_list=0x7f13a400f190, fields=..., values=..., conds=0x7f13a400fb10, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551614, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f13b5489ce0, updated_return=0x7f13b5489d90) at /home/alice/git/10.2/sql/sql_update.cc:838

#26 0x000055ee54b75c04 in mysql_execute_command (thd=thd@entry=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:4289

#27 0x000055ee54b7cddd in mysql_parse (thd=0x7f13a40009a8, rawbuf=<optimized out>, length=41, parser_state=0x7f13b548b240, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /home/alice/git/10.2/sql/sql_parse.cc:7941

#28 0x000055ee54b7f976 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f13a40009a8, packet=packet@entry=0x7f13a4006ce9 "update t1 set ml_id=8884 where point_id=1", packet_length=packet_length@entry=41, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /home/alice/git/10.2/sql/sql_parse.cc:1820

#29 0x000055ee54b8024f in do_command (thd=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:1374

#30 0x000055ee54c3a734 in do_handle_one_connection (connect=connect@entry=0x55ee57a7e3a8) at /home/alice/git/10.2/sql/sql_connect.cc:1335

#31 0x000055ee54c3a8a4 in handle_one_connection (arg=arg@entry=0x55ee57a7e3a8) at /home/alice/git/10.2/sql/sql_connect.cc:1241

#32 0x000055ee54e78b84 in pfs_spawn_thread (arg=0x55ee57a32dd8) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862

#33 0x00007f13bbdf86ba in start_thread (arg=0x7f13b548c700) at pthread_create.c:333

#34 0x00007f13bb28d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Alice Sherepa added a comment - 2018-05-24 11:22 - edited balta , Thanks for your help! You were right, patch resolved the simplified testcase, but the original bug is still presented in 10.2, 10.3. CREATE TABLE t1 (t1_id int , point_id int , ml_id int ) ; INSERT INTO t1 VALUES (1,1,8884),(2,1,8885); CREATE TABLE t2 (db_time datetime, au_nr int , col_id int , new_val int ); CREATE TABLE t3 (tab_id int , col_id int , col_bp int DEFAULT NULL ); CREATE TABLE t4 (id1 int , id2 int , d1 int ); delimiter //; CREATE TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin CREATE OR REPLACE TEMPORARY TABLE trg_u AS SELECT 1 FROM ( SELECT a.*, Max (t2.col_id) over (PARTITION BY a.d1), Max (t2.new_val) over (PARTITION BY a.d1) FROM ( SELECT d1,id1,id2 FROM t4) a JOIN t2 ON (a.d1=t2.db_time) JOIN t3 ON (a.id2=t3.tab_id)) l; END ;// delimiter ;// --error 1062 update t1 set ml_id=8884 where point_id=1; update t1 set ml_id=8884 where point_id=1; 52df8040264cc6f322 Thread 1 (Thread 0x7f13b548c700 (LWP 31379)): #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62 #1 0x000055ee551b6d9a in my_write_core (sig=sig@entry=11) at /home/alice/git/10.2/mysys/stacktrace.c:477 #2 0x000055ee54cf67b4 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305 #3 <signal handler called> #4 find_field_in_tables (thd=thd@entry=0x7f13a40009a8, item=item@entry=0x7f13a4091728, first_table=first_table@entry=0x7f13a408e190, last_table=last_table@entry=0x0, ref=ref@entry=0x7f13b54875e8, report_error=report_error@entry=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /home/alice/git/10.2/sql/sql_base.cc:5846 #5 0x000055ee54b9ef36 in find_order_in_list (thd=thd@entry=0x7f13a40009a8, ref_pointer_array=..., tables=tables@entry=0x7f13a408e190, order=order@entry=0x7f13a40b6100, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22363 #6 0x000055ee54bb555a in setup_group (thd=thd@entry=0x7f13a40009a8, ref_pointer_array=..., tables=tables@entry=0x7f13a408e190, fields=..., all_fields=..., order=0x7f13a40b6100, hidden_group_fields=0x7f13b548772f, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22537 #7 0x000055ee54c860d1 in setup_windows (thd=thd@entry=0x7f13a40009a8, ref_pointer_array=..., tables=tables@entry=0x7f13a408e190, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /home/alice/git/10.2/sql/sql_window.cc:208 #8 0x000055ee54bb7772 in setup_without_group (reserved=0x7f13a40b3724, hidden_group_fields=0x7f13a402e767, win_funcs=..., win_specs=..., group=0x0, order=<optimized out>, conds=0x7f13a402e888, all_fields=..., fields=..., leaves=..., tables=0x7f13a408e190, ref_pointer_array=..., thd=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_select.cc:664 #9 JOIN::prepare (this=this@entry=0x7f13a402e480, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f13a40b3460, unit_arg=0x7f13a40b50d0) at /home/alice/git/10.2/sql/sql_select.cc:819 #10 0x000055ee54c020fb in st_select_lex_unit::prepare (this=this@entry=0x7f13a40b50d0, thd_arg=thd_arg@entry=0x7f13a40009a8, sel_result=<optimized out>, additional_options=additional_options@entry=0) at /home/alice/git/10.2/sql/sql_union.cc:585 #11 0x000055ee54b53268 in mysql_derived_prepare (thd=0x7f13a40009a8, lex=0x7f13a40b3a50, derived=0x7f13a4090960) at /home/alice/git/10.2/sql/sql_derived.cc:770 #12 0x000055ee54b53dc7 in mysql_handle_single_derived (lex=lex@entry=0x7f13a40b3a50, derived=derived@entry=0x7f13a4090960, phases=phases@entry=2) at /home/alice/git/10.2/sql/sql_derived.cc:197 #13 0x000055ee54c190a8 in TABLE_LIST::handle_derived (this=this@entry=0x7f13a4090960, lex=lex@entry=0x7f13a40b3a50, phases=phases@entry=2) at /home/alice/git/10.2/sql/table.cc:7837 #14 0x000055ee54b6a9ee in st_select_lex::handle_derived (this=this@entry=0x7f13a40b4250, lex=0x7f13a40b3a50, phases=phases@entry=2) at /home/alice/git/10.2/sql/sql_lex.cc:3917 #15 0x000055ee54bb710a in JOIN::prepare (this=0x7f13a402de58, tables_init=<optimized out>, wild_num=0, conds_init=<optimized out>, og_num=0, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f13a40b4250, unit_arg=0x7f13a40b3b18) at /home/alice/git/10.2/sql/sql_select.cc:716 #16 0x000055ee54bc4767 in mysql_select (thd=thd@entry=0x7f13a40009a8, tables=0x7f13a4090960, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2415921920, result=0x7f13a402dd60, unit=0x7f13a40b3b18, select_lex=0x7f13a40b4250) at /home/alice/git/10.2/sql/sql_select.cc:3758 #17 0x000055ee54bc48de in handle_select (thd=thd@entry=0x7f13a40009a8, lex=lex@entry=0x7f13a40b3a50, result=result@entry=0x7f13a402dd60, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/alice/git/10.2/sql/sql_select.cc:376 #18 0x000055ee54b7bc9a in mysql_execute_command (thd=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:3952 #19 0x000055ee54df75e4 in sp_instr_stmt::exec_core (this=0x7f13a40900d0, thd=<optimized out>, nextp=0x7f13b5489274) at /home/alice/git/10.2/sql/sp_head.cc:3244 #20 0x000055ee54dfd303 in sp_lex_keeper::reset_lex_and_exec_core (this=this@entry=0x7f13a4090110, thd=thd@entry=0x7f13a40009a8, nextp=nextp@entry=0x7f13b5489274, open_tables=open_tables@entry=false, instr=instr@entry=0x7f13a40900d0) at /home/alice/git/10.2/sql/sp_head.cc:3007 #21 0x000055ee54dfd914 in sp_instr_stmt::execute (this=0x7f13a40900d0, thd=0x7f13a40009a8, nextp=0x7f13b5489274) at /home/alice/git/10.2/sql/sp_head.cc:3160 #22 0x000055ee54dfa13b in sp_head::execute (this=this@entry=0x7f13a40b1a20, thd=thd@entry=0x7f13a40009a8, merge_da_on_success=merge_da_on_success@entry=false) at /home/alice/git/10.2/sql/sp_head.cc:1326 #23 0x000055ee54dfa650 in sp_head::execute_trigger (this=0x7f13a40b1a20, thd=thd@entry=0x7f13a40009a8, db_name=<optimized out>, table_name=<optimized out>, grant_info=grant_info@entry=0x7f13a40a18c0) at /home/alice/git/10.2/sql/sp_head.cc:1655 #24 0x000055ee54bfa7dc in Table_triggers_list::process_triggers (this=0x7f13a40a1440, thd=thd@entry=0x7f13a40009a8, event=event@entry=TRG_EVENT_UPDATE, time_type=time_type@entry=TRG_ACTION_AFTER, old_row_is_record1=old_row_is_record1@entry=true) at /home/alice/git/10.2/sql/sql_trigger.cc:2189 #25 0x000055ee54c06fd1 in mysql_update (thd=thd@entry=0x7f13a40009a8, table_list=0x7f13a400f190, fields=..., values=..., conds=0x7f13a400fb10, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551614, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f13b5489ce0, updated_return=0x7f13b5489d90) at /home/alice/git/10.2/sql/sql_update.cc:838 #26 0x000055ee54b75c04 in mysql_execute_command (thd=thd@entry=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:4289 #27 0x000055ee54b7cddd in mysql_parse (thd=0x7f13a40009a8, rawbuf=<optimized out>, length=41, parser_state=0x7f13b548b240, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /home/alice/git/10.2/sql/sql_parse.cc:7941 #28 0x000055ee54b7f976 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f13a40009a8, packet=packet@entry=0x7f13a4006ce9 "update t1 set ml_id=8884 where point_id=1", packet_length=packet_length@entry=41, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /home/alice/git/10.2/sql/sql_parse.cc:1820 #29 0x000055ee54b8024f in do_command (thd=0x7f13a40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:1374 #30 0x000055ee54c3a734 in do_handle_one_connection (connect=connect@entry=0x55ee57a7e3a8) at /home/alice/git/10.2/sql/sql_connect.cc:1335 #31 0x000055ee54c3a8a4 in handle_one_connection (arg=arg@entry=0x55ee57a7e3a8) at /home/alice/git/10.2/sql/sql_connect.cc:1241 #32 0x000055ee54e78b84 in pfs_spawn_thread (arg=0x55ee57a32dd8) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862 #33 0x00007f13bbdf86ba in start_thread (arg=0x7f13b548c700) at pthread_create.c:333 #34 0x00007f13bb28d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Alice Sherepa added a comment - 2018-05-24 11:22

variation using CTE

CREATE TABLE t1 ( t1_id int, point_id int, ml_id int,  UNIQUE KEY t1_ml_u (ml_id,point_id)) ;

INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);

CREATE TABLE t2 ( db_time datetime, au_nr int, col_id int, new_val int);

CREATE TABLE t4 (id1 int, id2 int, d1 int);

delimiter //;

CREATE  TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin

CREATE OR REPLACE TEMPORARY TABLE trg_u AS

WITH l AS

  (SELECT a.*,

          Max(t2.col_id) over (PARTITION BY a.d1),

          Max(t2.new_val) over (PARTITION BY a.d1)

   FROM

   (SELECT d1 , id1, id2  FROM t4) a

   JOIN t2  ON (a.d1=t2.db_time AND a.id1=t2.au_nr))

SELECT 1;

 END;//

 delimiter ;//

--error 1062

update t1 set ml_id=8884 where point_id=1;

update t1 set ml_id=8884 where point_id=1;

 52df8040264cc6f322

Thread 1 (Thread 0x7f30d427b700 (LWP 24517)):

#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

#1  0x00005618ff0d9d9a in my_write_core (sig=sig@entry=11) at /home/alice/git/10.2/mysys/stacktrace.c:477

#2  0x00005618fec197b4 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305

#3  <signal handler called>

#4  find_field_in_tables (thd=thd@entry=0x7f30c40009a8, item=item@entry=0x7f30c4092a50, first_table=first_table@entry=0x7f30c408e190, last_table=last_table@entry=0x0, ref=ref@entry=0x7f30d4276688, report_error=report_error@entry=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /home/alice/git/10.2/sql/sql_base.cc:5846

#5  0x00005618feac1f36 in find_order_in_list (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, order=order@entry=0x7f30c40b60e8, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22363

#6  0x00005618fead855a in setup_group (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, fields=..., all_fields=..., order=0x7f30c40b60e8, hidden_group_fields=0x7f30d42767cf, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22537

#7  0x00005618feba90d1 in setup_windows (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /home/alice/git/10.2/sql/sql_window.cc:208

#8  0x00005618feada772 in setup_without_group (reserved=0x7f30c40b370c, hidden_group_fields=0x7f30c402c64f, win_funcs=..., win_specs=..., group=0x0, order=<optimized out>, conds=0x7f30c402c770, all_fields=..., fields=..., leaves=..., tables=0x7f30c408e190, ref_pointer_array=..., thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_select.cc:664

#9  JOIN::prepare (this=this@entry=0x7f30c402c368, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f30c40b3448, unit_arg=0x7f30c40b50d0) at /home/alice/git/10.2/sql/sql_select.cc:819

#10 0x00005618feb250fb in st_select_lex_unit::prepare (this=0x7f30c40b50d0, thd_arg=thd_arg@entry=0x7f30c40009a8, sel_result=sel_result@entry=0x0, additional_options=additional_options@entry=0) at /home/alice/git/10.2/sql/sql_union.cc:585

#11 0x00005618febaf55c in With_element::prepare_unreferenced (this=this@entry=0x7f30c408fbf8, thd=thd@entry=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_cte.cc:970

#12 0x00005618febaf5e3 in With_clause::prepare_unreferenced_elements (this=<optimized out>, thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_cte.cc:717

#13 0x00005618feada974 in JOIN::prepare (this=0x7f30c402be10, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f30c40b4250, unit_arg=0x7f30c40b3b18) at /home/alice/git/10.2/sql/sql_select.cc:893

#14 0x00005618feae7767 in mysql_select (thd=thd@entry=0x7f30c40009a8, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2415921920, result=0x7f30c402bd18, unit=0x7f30c40b3b18, select_lex=0x7f30c40b4250) at /home/alice/git/10.2/sql/sql_select.cc:3758

#15 0x00005618feae78de in handle_select (thd=thd@entry=0x7f30c40009a8, lex=lex@entry=0x7f30c40b3a50, result=result@entry=0x7f30c402bd18, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/alice/git/10.2/sql/sql_select.cc:376

#16 0x00005618fea9ec9a in mysql_execute_command (thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:3952

#17 0x00005618fed1a5e4 in sp_instr_stmt::exec_core (this=0x7f30c408fe30, thd=<optimized out>, nextp=0x7f30d4278274) at /home/alice/git/10.2/sql/sp_head.cc:3244

#18 0x00005618fed20303 in sp_lex_keeper::reset_lex_and_exec_core (this=this@entry=0x7f30c408fe70, thd=thd@entry=0x7f30c40009a8, nextp=nextp@entry=0x7f30d4278274, open_tables=open_tables@entry=false, instr=instr@entry=0x7f30c408fe30) at /home/alice/git/10.2/sql/sp_head.cc:3007

#19 0x00005618fed20914 in sp_instr_stmt::execute (this=0x7f30c408fe30, thd=0x7f30c40009a8, nextp=0x7f30d4278274) at /home/alice/git/10.2/sql/sp_head.cc:3160

#20 0x00005618fed1d13b in sp_head::execute (this=this@entry=0x7f30c40b1a20, thd=thd@entry=0x7f30c40009a8, merge_da_on_success=merge_da_on_success@entry=false) at /home/alice/git/10.2/sql/sp_head.cc:1326

#21 0x00005618fed1d650 in sp_head::execute_trigger (this=0x7f30c40b1a20, thd=thd@entry=0x7f30c40009a8, db_name=<optimized out>, table_name=<optimized out>, grant_info=grant_info@entry=0x7f30c40a3188) at /home/alice/git/10.2/sql/sp_head.cc:1655

#22 0x00005618feb1d7dc in Table_triggers_list::process_triggers (this=0x7f30c40a2bb0, thd=thd@entry=0x7f30c40009a8, event=event@entry=TRG_EVENT_UPDATE, time_type=time_type@entry=TRG_ACTION_AFTER, old_row_is_record1=old_row_is_record1@entry=true) at /home/alice/git/10.2/sql/sql_trigger.cc:2189

#23 0x00005618feb29fd1 in mysql_update (thd=thd@entry=0x7f30c40009a8, table_list=0x7f30c400f190, fields=..., values=..., conds=0x7f30c400fb10, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f30d4278ce0, updated_return=0x7f30d4278d90) at /home/alice/git/10.2/sql/sql_update.cc:838

#24 0x00005618fea98c04 in mysql_execute_command (thd=thd@entry=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:4289

#25 0x00005618fea9fddd in mysql_parse (thd=0x7f30c40009a8, rawbuf=<optimized out>, length=41, parser_state=0x7f30d427a240, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /home/alice/git/10.2/sql/sql_parse.cc:7941

#26 0x00005618feaa2976 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f30c40009a8, packet=packet@entry=0x7f30c4006ce9 "update t1 set ml_id=8884 where point_id=1", packet_length=packet_length@entry=41, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /home/alice/git/10.2/sql/sql_parse.cc:1820

#27 0x00005618feaa324f in do_command (thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:1374

#28 0x00005618feb5d734 in do_handle_one_connection (connect=connect@entry=0x5619010593a8) at /home/alice/git/10.2/sql/sql_connect.cc:1335

#29 0x00005618feb5d8a4 in handle_one_connection (arg=arg@entry=0x5619010593a8) at /home/alice/git/10.2/sql/sql_connect.cc:1241

#30 0x00005618fed9bb84 in pfs_spawn_thread (arg=0x56190100ddd8) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862

#31 0x00007f30dabe76ba in start_thread (arg=0x7f30d427b700) at pthread_create.c:333

#32 0x00007f30da07c41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Alice Sherepa added a comment - 2018-05-24 11:22 variation using CTE CREATE TABLE t1 ( t1_id int , point_id int , ml_id int , UNIQUE KEY t1_ml_u (ml_id,point_id)) ; INSERT INTO t1 VALUES (1,1,8884),(2,1,8885); CREATE TABLE t2 ( db_time datetime, au_nr int , col_id int , new_val int ); CREATE TABLE t4 (id1 int , id2 int , d1 int ); delimiter //; CREATE TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin CREATE OR REPLACE TEMPORARY TABLE trg_u AS WITH l AS ( SELECT a.*, Max (t2.col_id) over (PARTITION BY a.d1), Max (t2.new_val) over (PARTITION BY a.d1) FROM ( SELECT d1 , id1, id2 FROM t4) a JOIN t2 ON (a.d1=t2.db_time AND a.id1=t2.au_nr)) SELECT 1; END ;// delimiter ;// --error 1062 update t1 set ml_id=8884 where point_id=1; update t1 set ml_id=8884 where point_id=1; 52df8040264cc6f322 Thread 1 (Thread 0x7f30d427b700 (LWP 24517)): #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62 #1 0x00005618ff0d9d9a in my_write_core (sig=sig@entry=11) at /home/alice/git/10.2/mysys/stacktrace.c:477 #2 0x00005618fec197b4 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305 #3 <signal handler called> #4 find_field_in_tables (thd=thd@entry=0x7f30c40009a8, item=item@entry=0x7f30c4092a50, first_table=first_table@entry=0x7f30c408e190, last_table=last_table@entry=0x0, ref=ref@entry=0x7f30d4276688, report_error=report_error@entry=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /home/alice/git/10.2/sql/sql_base.cc:5846 #5 0x00005618feac1f36 in find_order_in_list (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, order=order@entry=0x7f30c40b60e8, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22363 #6 0x00005618fead855a in setup_group (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, fields=..., all_fields=..., order=0x7f30c40b60e8, hidden_group_fields=0x7f30d42767cf, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22537 #7 0x00005618feba90d1 in setup_windows (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /home/alice/git/10.2/sql/sql_window.cc:208 #8 0x00005618feada772 in setup_without_group (reserved=0x7f30c40b370c, hidden_group_fields=0x7f30c402c64f, win_funcs=..., win_specs=..., group=0x0, order=<optimized out>, conds=0x7f30c402c770, all_fields=..., fields=..., leaves=..., tables=0x7f30c408e190, ref_pointer_array=..., thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_select.cc:664 #9 JOIN::prepare (this=this@entry=0x7f30c402c368, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f30c40b3448, unit_arg=0x7f30c40b50d0) at /home/alice/git/10.2/sql/sql_select.cc:819 #10 0x00005618feb250fb in st_select_lex_unit::prepare (this=0x7f30c40b50d0, thd_arg=thd_arg@entry=0x7f30c40009a8, sel_result=sel_result@entry=0x0, additional_options=additional_options@entry=0) at /home/alice/git/10.2/sql/sql_union.cc:585 #11 0x00005618febaf55c in With_element::prepare_unreferenced (this=this@entry=0x7f30c408fbf8, thd=thd@entry=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_cte.cc:970 #12 0x00005618febaf5e3 in With_clause::prepare_unreferenced_elements (this=<optimized out>, thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_cte.cc:717 #13 0x00005618feada974 in JOIN::prepare (this=0x7f30c402be10, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f30c40b4250, unit_arg=0x7f30c40b3b18) at /home/alice/git/10.2/sql/sql_select.cc:893 #14 0x00005618feae7767 in mysql_select (thd=thd@entry=0x7f30c40009a8, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2415921920, result=0x7f30c402bd18, unit=0x7f30c40b3b18, select_lex=0x7f30c40b4250) at /home/alice/git/10.2/sql/sql_select.cc:3758 #15 0x00005618feae78de in handle_select (thd=thd@entry=0x7f30c40009a8, lex=lex@entry=0x7f30c40b3a50, result=result@entry=0x7f30c402bd18, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/alice/git/10.2/sql/sql_select.cc:376 #16 0x00005618fea9ec9a in mysql_execute_command (thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:3952 #17 0x00005618fed1a5e4 in sp_instr_stmt::exec_core (this=0x7f30c408fe30, thd=<optimized out>, nextp=0x7f30d4278274) at /home/alice/git/10.2/sql/sp_head.cc:3244 #18 0x00005618fed20303 in sp_lex_keeper::reset_lex_and_exec_core (this=this@entry=0x7f30c408fe70, thd=thd@entry=0x7f30c40009a8, nextp=nextp@entry=0x7f30d4278274, open_tables=open_tables@entry=false, instr=instr@entry=0x7f30c408fe30) at /home/alice/git/10.2/sql/sp_head.cc:3007 #19 0x00005618fed20914 in sp_instr_stmt::execute (this=0x7f30c408fe30, thd=0x7f30c40009a8, nextp=0x7f30d4278274) at /home/alice/git/10.2/sql/sp_head.cc:3160 #20 0x00005618fed1d13b in sp_head::execute (this=this@entry=0x7f30c40b1a20, thd=thd@entry=0x7f30c40009a8, merge_da_on_success=merge_da_on_success@entry=false) at /home/alice/git/10.2/sql/sp_head.cc:1326 #21 0x00005618fed1d650 in sp_head::execute_trigger (this=0x7f30c40b1a20, thd=thd@entry=0x7f30c40009a8, db_name=<optimized out>, table_name=<optimized out>, grant_info=grant_info@entry=0x7f30c40a3188) at /home/alice/git/10.2/sql/sp_head.cc:1655 #22 0x00005618feb1d7dc in Table_triggers_list::process_triggers (this=0x7f30c40a2bb0, thd=thd@entry=0x7f30c40009a8, event=event@entry=TRG_EVENT_UPDATE, time_type=time_type@entry=TRG_ACTION_AFTER, old_row_is_record1=old_row_is_record1@entry=true) at /home/alice/git/10.2/sql/sql_trigger.cc:2189 #23 0x00005618feb29fd1 in mysql_update (thd=thd@entry=0x7f30c40009a8, table_list=0x7f30c400f190, fields=..., values=..., conds=0x7f30c400fb10, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f30d4278ce0, updated_return=0x7f30d4278d90) at /home/alice/git/10.2/sql/sql_update.cc:838 #24 0x00005618fea98c04 in mysql_execute_command (thd=thd@entry=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:4289 #25 0x00005618fea9fddd in mysql_parse (thd=0x7f30c40009a8, rawbuf=<optimized out>, length=41, parser_state=0x7f30d427a240, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /home/alice/git/10.2/sql/sql_parse.cc:7941 #26 0x00005618feaa2976 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f30c40009a8, packet=packet@entry=0x7f30c4006ce9 "update t1 set ml_id=8884 where point_id=1", packet_length=packet_length@entry=41, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /home/alice/git/10.2/sql/sql_parse.cc:1820 #27 0x00005618feaa324f in do_command (thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:1374 #28 0x00005618feb5d734 in do_handle_one_connection (connect=connect@entry=0x5619010593a8) at /home/alice/git/10.2/sql/sql_connect.cc:1335 #29 0x00005618feb5d8a4 in handle_one_connection (arg=arg@entry=0x5619010593a8) at /home/alice/git/10.2/sql/sql_connect.cc:1241 #30 0x00005618fed9bb84 in pfs_spawn_thread (arg=0x56190100ddd8) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862 #31 0x00007f30dabe76ba in start_thread (arg=0x7f30d427b700) at pthread_create.c:333 #32 0x00007f30da07c41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Tadas Balaišis added a comment - 2018-05-24 11:42

Thank you alice. This issue reminds me the other issue MDEV-12135 (created by me). If there would be statement triggers functionality, I'd use that complex queries at statement level trigger, which is not so mutation complicated.

Tadas Balaišis added a comment - 2018-05-24 11:42 Thank you alice . This issue reminds me the other issue MDEV-12135 (created by me). If there would be statement triggers functionality, I'd use that complex queries at statement level trigger, which is not so mutation complicated.

Varun Gupta (Inactive) added a comment - 2018-07-27 19:13

A simple test case that crashes with views and prepared statement

CREATE TABLE t1 (i INT, a char);

INSERT INTO t1 VALUES (1, 'a'),(2, 'b');

create view v1 as select * from t1;

PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1";

execute stmt;

Varun Gupta (Inactive) added a comment - 2018-07-27 19:13 A simple test case that crashes with views and prepared statement CREATE TABLE t1 (i INT , a char ); INSERT INTO t1 VALUES (1, 'a' ),(2, 'b' ); create view v1 as select * from t1; PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1" ; execute stmt;

Varun Gupta (Inactive) added a comment - 2018-11-14 07:41

Patch
http://lists.askmonty.org/pipermail/commits/2018-November/013091.html

Varun Gupta (Inactive) added a comment - 2018-11-14 07:41 Patch http://lists.askmonty.org/pipermail/commits/2018-November/013091.html

Oleksandr Byelkin added a comment - 2018-11-14 08:04

OK to push

Oleksandr Byelkin added a comment - 2018-11-14 08:04 OK to push

MariaDB Server

Database service (MySQL) stops after update with trigger

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration