MySQL Bugs: #50545: Single table UPDATE IGNORE crashes on join view in sql_safe

Bug #50545	Single table UPDATE IGNORE crashes on join view in sql_safe_updates mode.
Submitted:	22 Jan 2010 13:46	Modified:	18 Jun 2010 1:30
Reporter:	Martin Hansson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
Bug found while fixing Bug#49534. This is related but not the same bug.

How to repeat:
CREATE TABLE t1( a INT, KEY( a ) );
INSERT INTO t1 VALUES (0), (1);
CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
SET SESSION sql_safe_updates = 1;
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
UPDATE IGNORE v1 SET a = 1;
DROP TABLE t1;
DROP VIEW v1;

Suggested fix:
The fix for Bug#49534 was straightforward; just copy whatever was done for single table UPDATE to multi table UPDATE.

Thank you for the bug report. Which server version and source bzr tree was used to repeat this bug?. Thanks in advance.

Hi Miguel,
I believe it's present in every version. I used mysql-5.1-bugteam, though.

/Martin

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/102748

3369 Martin Hansson	2010-03-09
      Bug#50545: Single table UPDATE IGNORE crashes on join view in
                 sql_safe_updates mode
      
      The IGNORE flag was implemented with two quasi-global flags
      (globally available flags of globally available structures),
      one on the session level (LEX object) and one on the
      statement level (SELECT_LEX object). Supposedly this was a
      means of making the IGNORE flags apply to nested
      statements. There were several drawbacks to this design and
      there have been numerous bugs. The biggest problem is that
      there was only a boolean flag in the SELECT_LEX structure
      that caused an ad-hoc check in my_message_sql to issue a
      warning instead of an error. Unfortunately, this was not
      enough. Some commands want the error silenced completely,
      while others wanted no silencing at all. There is also an
      error that must never be silenced: the error caused by
      sql_safe_updates mode. 
      
      This bug was caused by a function issuing an error and
      assuming it not to be silenced while in fact it was. This
      leads to no status being set in the Diagnostics_area, which
      has an assertion to catch precisely this scenario.
      
      When at all possible, this fix brings the decision of the
      THD object (and by extension any error handlers) in harmony
      with functions' returned error status by giving authority to
      THD.
     @ sql/item_subselect.cc
        Added code to surround calls to subselect_engine::exec with
        push/pop of Ignore_handler. The 'subselect' test covers this.
     @ sql/mysqld.cc
        Removed the special hook in my_message_sql that reads
        quasi-global flag no_error.
     @ sql/sql_class.cc
        Implementation of new classes.
        Added assertion to catch circular lists (happens easily when
        Internal_error_handler's are stack allocated).
        
        mark_transaction_to_rollback(THD*, bool):
        This code is no longer needed. The Ignore_handler reads the
        is_fatal_sub_stmt_error flag and acts appropriately.
     @ sql/sql_class.h
        Declaration of new classes.
        Removal of ignore parameter of
        multi_delete::do_table_deletes(TABLE*, bool)
     @ sql/sql_delete.cc
        mysql_delete:
        Substituted Delete_ignore_handler for the no_error flag.
        
        multi_delete::send_data:
        It is safe to always call handler::print_error here since
        all of its code paths will cross error_handler_hook. We can
        preserve original behavior by checking if an error was
        indeed raised during this call.
        
        multi_delete::do_deletes(), ::do_table_deletes(TABLE*, bool)
        This code now defers to the THD interface whether to
        suppress errors or not, and honors its decision.
     @ sql/sql_insert.cc
        This is not covered by any test. We could probably use the
        THD::is_fatal_error here.
     @ sql/sql_prepare.cc
        We need not care about resetting global flags anymore.
     @ sql/sql_select.cc
        Instead of setting no_error flag in JOIN::optimize (and then
        having to care for unsetting it in an open-ended number of
        places) we surround the calls to JOIN::optimize and
        JOIN::exec with pushing and popping of an error handler in
        mysql_select. This has the effect that all calls to these
        methods will need to do the same (see item_subselect.cc).

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/102898

3384 Martin Hansson	2010-03-10
      Bug#50545: Single table UPDATE IGNORE crashes on 
      join view in sql_safe_updates mode. 
      
      This bug was unexpectedly fixed along with bug 
      number 49534. This patch contains only the test
      case.

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/102906

3384 Martin Hansson	2010-03-10
      Bug#50545: Single table UPDATE IGNORE crashes on 
      join view in sql_safe_updates mode. 
      
      This bug was unexpectedly fixed along with bug 
      number 49534. This patch contains only the test
      case.

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/102907

3391 Martin Hansson	2010-03-10
      Bug#50545: Single table UPDATE IGNORE crashes on 
      join view in sql_safe_updates mode. 
      
      This bug was unexpectedly fixed along with bug 
      number 49534. This patch contains only the test
      case.

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/102909

3962 Martin Hansson	2010-03-10 [merge]
      Merge of Bug#50545 (test case only)

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100312095153-t4rtoqc7p96lmxvh) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:martin.hansson@sun.com-20100310161005-12ofie048c0na7xr) (merge vers: 5.1.45) (pib:16)

Changes to test case. No changelog entry needed.

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)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

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)