Bug #35877 Update .. WHERE with function, constraint violation, crash
Submitted: 7 Apr 2008 12:11 Modified: 12 Nov 2009 20:54
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[7 Apr 2008 12:11] Matthias Leich
Description:
Slightly edited protocol:
SET SESSION sql_mode = '';
CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)
ENGINE = MyISAM;
CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)
ENGINE = MyISAM;
INSERT INTO t1_aux VALUES (1,1);
COMMIT;
CREATE FUNCTION f1_two_inserts() returns INTEGER
BEGIN
INSERT INTO t1_not_null SET f1 = 10, f2 = NULL;
RETURN 1;
END//
UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts();
ERROR HY000: Lost connection to MySQL server during query

1. Using InnoDB instead of MyISAM does not prevent
   the crash.
2. The crash does not happen in case of
   mysql-5.0-main ChangeSet@1.2610, 2008-04-01

My environment:
- mysql-5.1-main ChangeSet@1.2561, 2008-04-01
  BUILD/compile-pentium-debug-max
- mysql-6.0-main ChangeSet@1.2626, 2008-04-03
  BUILD/compile-pentium-debug-max
- Intel Core2Duo (64 Bit)
- SuSE Linux 10.3 (64 Bit)

How to repeat:
Please use the attached testscript.
[7 Apr 2008 12:15] Matthias Leich
testscript

Attachment: ml002.test (application/octet-stream, text), 993 bytes.

[7 Apr 2008 12:16] Matthias Leich
Stack back trace

Attachment: stacktrace (application/octet-stream, text), 4.05 KiB.

[7 Apr 2008 12:45] Matthias Leich
IMHO using a function within a WHERE qualification of
an UPDATE + the function itself modifies data is a very
rare case. The only case I can in the moment imagine
is that somebody has a DWH application. The updated table
is used for collecting some temporary results ("used on the
way to get the final report") and the function is
- much more complicated
- has to serve two purposes
  - bring some aggregate tables up to date
  - process aggregate tables for getting the return value
--> I4
I also guess that many users could find a different
solution - the function within the WHERE qualification-
does not modify data - which fits to their application
needs. --> W3
[9 Apr 2008 20:50] Konstantin Osipov
Backtrace:
(gdb) bt
#0  0xffffe410 in __kernel_vsyscall ()
#1  0xb7d56875 in raise () from /lib/tls/i686/cmov/libc.so.6
#2  0xb7d58201 in abort () from /lib/tls/i686/cmov/libc.so.6
#3  0xb7d4fb6e in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
#4  0x08246815 in Diagnostics_area::set_ok_status (this=0x8dd6678, 
    thd=0x8dd58e0, affected_rows_arg=39560943763462, last_insert_id_arg=0, 
    message_arg=0xb50de1f8 "Rows matched: 0  Changed: 0  Warnings: 0")
    at sql_class.cc:397
#5  0x082fa9da in mysql_update (thd=0x8dd58e0, table_list=0x8e217d0, 
    fields=@0x8dd6cdc, values=@0x8dd6f28, conds=0x8e1a410, order_num=0, 
    order=0x0, limit=13046331721333608305, handle_duplicates=DUP_ERROR, 
    ignore=false) at sql_class.h:2146
#6  0x0826e719 in mysql_execute_command (thd=0x8dd58e0) at sql_parse.cc:2827
#7  0x0827479c in mysql_parse (thd=0x8dd58e0, 
    inBuf=0x8e207b0 "UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()", 
    length=52, found_semicolon=0xb50deeec) at sql_parse.cc:5617
#8  0x08275b49 in dispatch_command (command=COM_QUERY, thd=0x8dd58e0, 
    packet=0x8e0e0f1 "UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()", 
    packet_length=52) at sql_parse.cc:1121
#9  0x08276cf2 in do_command (thd=0x8dd58e0) at sql_parse.cc:781
#10 0x08265098 in handle_one_connection (arg=0x8dd58e0) at sql_connect.cc:1106
#11 0xb7eeb46b in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#12 0xb7dff6de in clone () from /lib/tls/i686/cmov/libc.so.6
[9 Apr 2008 20:51] Konstantin Osipov
The assert was added as part of Bug#12713
[8 Aug 2008 17:08] Evgeny Potemkin
Analysis of this bug:
The f1_two_inserts function tries to insert NULL into a not null column and fails.
This error isn't correctly propagated to the caller statement and causes
failed assertion at setting return code of whole statement for the second time.
See also bug#27563.
[15 May 2009 9:49] 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/74172

2777 Jon Olav Hauglid	2009-05-15
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
      
      Unable to reproduce crash with current version of mysql-6.0-runtime.
      Test case for MyISAM/InnoDB based on the bug rapport added to sp.test.
[27 May 2009 14:23] 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/75058

2781 Jon Olav Hauglid	2009-05-27
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
      
      Unable to reproduce crash with current version of mysql-6.0-runtime.
      Test case for MyISAM/InnoDB based on the bug rapport added to sp.test.
[29 Jul 2009 8:28] 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/79502

2833 Jon Olav Hauglid	2009-07-29
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
            
      Unable to reproduce crash with current version of mysql-azalea.
      Test case for MyISAM/InnoDB based on the bug rapport added to 
      sp_trans.test.
[29 Jul 2009 8:31] Jon Olav Hauglid
Pushed to azalea-bugfixing.
[4 Aug 2009 19:51] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:iggy@mysql.com-20090731204544-7nio1afvg0dmzs7g) (merge vers: 5.4.4-alpha) (pib:11)
[6 Aug 2009 23:56] Paul Dubois
Bug no longer present apparently; changes to test case only. No changelog entry needed.
[12 Oct 2009 11:41] 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/86554

2910 Jon Olav Hauglid	2009-10-12
      Bug #35877 Update .. WHERE with function, constraint violation, crash 
      
      Unable to reproduce crash with current version of the 5.5.0 codebase.
      Test case for MyISAM/InnoDB based on the bug rapport added to 
      sp_trans.test.
      
      Backport of revno: 2617.65.9.
[12 Oct 2009 11:43] Jon Olav Hauglid
Pushed to mysql-next-mr-runtime (version 5.5.0).
[3 Nov 2009 7:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 15:39] Paul Dubois
Changes to test case only. No changelog entry needed.
[12 Nov 2009 8:20] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 20:54] Paul Dubois
Changes to test case only. No changelog entry needed.