Bug #49534 multitable IGNORE update with sql_safe_updates error causes debug assertion
Submitted: 8 Dec 2009 15:07 Modified: 18 Jun 2010 1:22
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.0.88-debug, 5.0.89-debug, 5.1.41-debug, 5.1.43-debug,5.5.99-m3-debug OS:Any
Assigned to: Martin Hansson
Triage: Triaged: D2 (Serious)

[8 Dec 2009 15:07] Shane Bester
Description:
Version: '5.1.41-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: 0, file .\protocol.cc, line 465

 mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2048]
 mysqld-debug.exe!raise()[winsig.c:597]
 mysqld-debug.exe!abort()[abort.c:78]
 mysqld-debug.exe!_wassert()[assert.c:212]
 mysqld-debug.exe!net_end_statement()[protocol.cc:465]
 mysqld-debug.exe!dispatch_command()[sql_parse.cc:1622]
 mysqld-debug.exe!do_command()[sql_parse.cc:872]
 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]
 kernel32.dll!BaseThreadStart()

there is a bug also in the release build.  It gives some 'unknown error':

mysql> update ignore t1,t1 a set t1.a=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1175
Message: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
*************************** 2. row ***************************
  Level: Error
   Code: 1105
Message: Unknown error
2 rows in set (0.00 sec)

So, please fix this unknown error too.
5.0.88-debug causes the client connection to hang, instead of server assertion.

How to repeat:
start debug server then run:

drop table if exists t1;
create table t1(a int,key(a))engine=myisam;
insert into t1 values (0),(1);
set session sql_safe_updates=1;
update ignore t1,t1 a set t1.a=1;
[8 Dec 2009 15:18] Valerii Kravchuk
Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> create table t1(a int,key(a))engine=myisam;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values (0),(1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set session sql_safe_updates=1;
Query OK, 0 rows affected (0.05 sec)

mysql> update ignore t1,t1 a set t1.a=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1175
Message: You are using safe update mode and you tried to update a table without
a WHERE that uses a KEY column
*************************** 2. row ***************************
  Level: Error
   Code: 1105
Message: Unknown error
2 rows in set (0.00 sec)

and yes, with 5.0.89-debug client also hangs.
[8 Dec 2009 15:21] Valerii Kravchuk
Assertion failure with recent 5.1.43-debug from bzr:

Version: '5.1.43-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: protocol.cc:465: void net_end_statement(THD*): Assertion `0' failed.
100315 21:48:02 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337727 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x8eef808
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x42828430 thread_stack 0x30000
/home2/openxs/dbs/5.1/libexec/mysqld(my_print_stacktrace+0x29)[0x86e1154]
/home2/openxs/dbs/5.1/libexec/mysqld(handle_segfault+0x253)[0x829332b]
[0xffffe420]
/lib/tls/libc.so.6(abort+0x1a5)[0x400e6b75]
/lib/tls/libc.so.6(__assert_fail+0x103)[0x400de903]
/home2/openxs/dbs/5.1/libexec/mysqld(_Z17net_end_statementP3THD+0x1a9)[0x8287dcb]
/home2/openxs/dbs/5.1/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1890)[0x82a3104]
/home2/openxs/dbs/5.1/libexec/mysqld(_Z10do_commandP3THD+0x260)[0x82a153a]
/home2/openxs/dbs/5.1/libexec/mysqld(handle_one_connection+0x14e)[0x829f970]
/lib/tls/libpthread.so.0[0x40031aa7]
/lib/tls/libc.so.6(__clone+0x5e)[0x40177c2e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8f45558 = update ignore t1,t1 a set t1.a=1
thd->thread_id=1
thd->killed=NOT_KILLED
[8 Dec 2009 16:46] Shane Bester
another testcase, whereby a warning is issued due to a column being out of range, but the debug assertion trips:

drop table if exists t1;
create table t1(b tinyint,a tinyint) engine=innodb;
insert into t1 values (0,0);
set session sql_mode='STRICT_ALL_TABLES';
update ignore (select 128 as col1) x, test.t1 set t1.a=x.col1;
[8 Dec 2009 19:48] Shane Bester
another assertion for 5.1.41-debug:

drop table if exists `t1`;
create table `t1`(`a` int,key(`a`))engine=mrg_myisam;
update ignore
(select
   min(`t1`.`a`) `a` from `t1`,`t1` `a`
) `x`,`t1` set t1.`a`=x.`a`
;

5.0.88 returns "unknown error".  Please make sure all these testcase get into a testsuite.
[20 Jan 2010 15:53] Martin Hansson
This bug has nothing to do with subqueries.
[28 Jan 2010 13:19] 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/98474

3327 Martin Hansson	2010-01-28
      Bug#49534: multitable IGNORE update with sql_safe_updates
      error causes debug assertion
      
      The IGNORE option of the multiple-table UPDATE command was
      not intented to suppress errors caused by the
      sql_safe_updates mode. This flag will raise an error if the
      execution of UPDATE does not use a key for row retrieval,
      and should continue do so regardless of the IGNORE option.
      
      However the implementation of IGNORE does not support
      exceptions; it always converts errors to warnings and cannot
      be extended. The Internal_error_handler interface offers the
      infrastructure to handle individual errors, making sure that
      the error raised by sql_safe_updates is not silenced.
      
      Fixed by implementing an Internal_error_handler and using it
      for UPDATE IGNORE commands.
      
      This fix also modifies THD::pop_internal_handler to return 
      the handler being popped, in order to detect improper use of
      the error handler stack.
[10 Feb 2010 14:37] 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/99833

3374 Martin Hansson	2010-02-10
      Bug#49534: multitable IGNORE update with sql_safe_updates
      error causes debug assertion
      
      The IGNORE option of the multiple-table UPDATE command was
      not intended to suppress errors caused by the
      sql_safe_updates mode. This flag will raise an error if the
      execution of UPDATE does not use a key for row retrieval,
      and should continue do so regardless of the IGNORE option.
      
      However the implementation of IGNORE does not support
      exceptions to the rule; it always converts errors to
      warnings and cannot be extended. The Internal_error_handler
      interface offers the infrastructure to handle individual
      errors, making sure that the error raised by
      sql_safe_updates is not silenced.
      
      Fixed by implementing an Internal_error_handler and using it
      for UPDATE IGNORE commands.
[11 Feb 2010 14:47] 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/99959

3894 Martin Hansson	2010-02-11 [merge]
      Merge of Bug#49534. Fix had to be somewhat tweaked since THD::main_da
      is private in 6.0. We have no choice but to pass through the 
      THD::raise_error needle-eye. And we would recurse inifinitely unless 
      we temporarily unset the IGNORE flag (called no_error for some reason)
[11 Feb 2010 14:56] 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/99961

3375 Martin Hansson	2010-02-11 [merge]
      Merge of Bug#49534
[13 Feb 2010 17:22] 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/100254

3905 Martin Hansson	2010-02-13
      Post-push fix for Bug#49534. It is not safe to use THD::raise_error() 
      inside an override of Internal_error_handler::handle_condition().
      The patch changes subsitutes this call with a call to 
      thd->stmt_da->set_error_status().
[15 Feb 2010 11: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/100347

3906 Martin Hansson	2010-02-15
      Post-push fix for Bug#49534. It is not safe to use THD::raise_error() 
      inside an override of Internal_error_handler::handle_condition().
      The patch changes subsitutes this call with a call to 
      thd->stmt_da->set_error_status().
[1 Mar 2010 8:42] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joerg@mysql.com-20100212173307-ph563zr4wmoklgwd) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:39] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:44] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[8 Apr 2010 17:58] Paul Dubois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

For debug builds, with sql_safe_updates enabled, a multiple-table 
UPDATE with the IGNORE modifier could raise an assertion.
[17 Jun 2010 12:02] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:44] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:29] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)