Bug #51650 crash with user variables and triggers

• Submitted: 2 Mar 2010 17:15
• Modified: 19 Jun 2010 0:21
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.0.22, 5.0.30, 5.0.90, 5.1.30, 5.1.45, 5.5.99-m3
• OS: Any
• Assigned to: Davi Arnaut
• CPU Architecture: Any
• Tags: crash, valgrind

[2 Mar 2010 17:15] Shane Bester

Description:
5.1.44 stack trace:

mysqld-debug.exe!Item_field::replace_equal_field()[item.cc:4679]
mysqld-debug.exe!Item::transform()[item.cc:542]
mysqld-debug.exe!Item_func::transform()[item_func.cc:275]
mysqld-debug.exe!substitute_for_best_equal_field()[sql_select.cc:8284]
mysqld-debug.exe!substitute_for_best_equal_field()[sql_select.cc:8246]
mysqld-debug.exe!JOIN::optimize()[sql_select.cc:1066]
mysqld-debug.exe!mysql_select()[sql_select.cc:2445]
mysqld-debug.exe!handle_select()[sql_select.cc:269]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3237]
mysqld-debug.exe!sp_instr_stmt::exec_core()[sp_head.cc:2928]
mysqld-debug.exe!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:2749]
mysqld-debug.exe!sp_instr_stmt::execute()[sp_head.cc:2871]
mysqld-debug.exe!sp_head::execute()[sp_head.cc:1255]
mysqld-debug.exe!sp_head::execute_trigger()[sp_head.cc:1558]
mysqld-debug.exe!Table_triggers_list::process_triggers()[sql_trigger.cc:2013]
mysqld-debug.exe!write_record()[sql_insert.cc:1620]
mysqld-debug.exe!mysql_insert()[sql_insert.cc:835]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3183]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5975]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1235]
mysqld-debug.exe!do_command()[sql_parse.cc:874]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
testcase to be provided soon

[2 Mar 2010 19:05] Valeriy Kravchuk

Verified just as described with test case from customer.

[2 Mar 2010 19:05] MySQL Verification Team

run mysqld under valgrind if a crash does not happen, as a corrupt pointer might not always cause a crash:

 Invalid read of size 4
at: Item_field::replace_equal_field(unsigned char*) (item_cmpfunc.h:1593)
by: Item_func::transform(item_func.cc:275)
<cut>

[2 Mar 2010 20:15] MySQL Verification Team

public testcase. paste into mysql client:

drop table if exists `t1`;
create table `t1` (`b` varchar(50) not null) 
engine=myisam default charset=latin1;

drop table if exists `t2`;
create table `t2` (`a` varchar(10) not null default '') 
engine=myisam default charset=latin1;

delimiter ;;
create trigger `on_i_to_t2` after insert on `t2`
for each row begin
   select 1 from t1 c where
   (@uservar is null or @uservar != c.b) and c.b=new.a
   limit 1 into @a;
end ;;

delimiter ;

set @uservar=1;
insert ignore into `t2` values();
\r
insert ignore into `t1` set `b`='666';
insert ignore into `t2` set `a`='111';
set @uservar=1;
insert ignore into `t2` set `a`='666';

[3 Mar 2010 7:40] MySQL Verification Team

not a regression. see the "Version" list that I tested.

[8 Mar 2010 8:07] MySQL Verification Team

a workaround could be to disable the table cache by setting table_open_cache=0

[9 Mar 2010 15:36] Davi Arnaut

It seems that equality propagation (a destructive modification) is not being properly undone at the end of the stored program (eg trigger), leading to problems upon re-execution. Patch will follow shortly.

[9 Mar 2010 17:18] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102762

3379 Davi Arnaut	2010-03-09
      Bug#51650: crash with user variables and triggers
      
      The problem was that bits of the destructive equality propagation
      optimization weren't being undone after the execution of a stored
      program. Modifications to the parse tree that are based on transient
      properties must be undone to enable the re-execution of stored
      programs.
      
      The solution is to cleanup any references to predicates generated
      by the equality propagation during the execution of a stored program.
     @ mysql-test/r/trigger.result
        Add test case result for Bug#51650.
     @ mysql-test/t/trigger.test
        Add test case for Bug#51650.
     @ sql/item.cc
        Remove reference to a equality predicate.

[9 Mar 2010 21:55] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102784

3381 Davi Arnaut	2010-03-09
      Bug#51650: crash with user variables and triggers
      
      The problem was that bits of the destructive equality propagation
      optimization weren't being undone after the execution of a stored
      program. Modifications to the parse tree that are based on transient
      properties must be undone to enable the re-execution of stored
      programs.
      
      The solution is to cleanup any references to predicates generated
      by the equality propagation during the execution of a stored program.
     @ mysql-test/r/trigger.result
        Add test case result for Bug#51650.
     @ mysql-test/t/trigger.test
        Add test case for Bug#51650.
     @ sql/item.cc
        Remove reference to a equality predicate.

[9 Mar 2010 21:55] Davi Arnaut

Queued to mysql-5.1-bugteam

[26 Mar 2010 8:21] Bugs System

Pushed into 5.5.4-m3 (revid:[e-mail]-20100326080914-2pz8ns984e0spu03) (version source revid:[e-mail]-20100312095153-t4rtoqc7p96lmxvh) (merge vers: 5.5.3-m2) (pib:16)

[26 Mar 2010 8:25] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100326081116-m3v4l34yhr43mtsv) (version source revid:[e-mail]-20100325072612-4sds00ix8ajo1e84) (pib:16)

[26 Mar 2010 8:30] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100326081944-qja07qklw1p2w7jb) (version source revid:[e-mail]-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

[6 Apr 2010 8:00] Bugs System

Pushed into 5.1.46 (revid:[e-mail]-20100405111026-7kz1p8qlzglqgfmu) (version source revid:[e-mail]-20100309215508-9lyhyjyy3uifj0u5) (merge vers: 5.1.45) (pib:16)

[18 Apr 2010 22:25] Paul DuBois

Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

        A problem with equality propagation optimization for prepared
        statements and stored procedures caused a server crash upon
        re-execution of the prepared statement or stored procedure.

See also Bug#8115, Bug#8849.

[17 Jun 2010 11:52] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:30] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:18] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[25 Feb 2011 4:28] Valeriy Kravchuk

Bug #60242 was marked as a duplicate of this one.