• Jorgen Loland's avatar
    BUG#11762751: UPDATE STATEMENT THROWS AN ERROR, BUT STILL · d1d16687
    Jorgen Loland authored
                  UPDATES THE TABLE ENTRIES (formerly 55385)
    BUG#11764529: MULTI UPDATE+INNODB REPORTS ER_KEY_NOT_FOUND 
                  IF A TABLE IS UPDATED TWICE (formerly 57373)
                
    If multiple-table update updates a row through two aliases and
    the first update physically moves the row, the second update will
    fail to locate the row. This results in different errors
    depending on storage engine:
      * MyISAM: Got error 134 from storage engine
      * InnoDB: Can't find record in 'tbl'
    None of these errors accurately describe the problem. 
          
    Furthermore, since MyISAM is non-transactional, the update
    executed first will be performed while the second will not.
    In addition, for two equal multiple-table update statements,
    one could succeed and the other fail based on whether or not
    the record actually moved or not. This was inconsistent.
          
    Two update operations may physically move a row:
      1) Update of a column in a clustered primary key
      2) Update of a column used to calculate which partition the 
         row belongs to
               
    BUG#11764529 is about case 1) above, BUG#11762751 was about case 2).
          
    The fix for these bugs is to return with an error if multiple-table 
    update is about to:
      a) Update a table through multiple aliases, and
      b) Perform an update that may physically more the row 
         in at least one of these aliases
        
    This avoids 
      * partial updates as described for MyISAM above,
      * provides the same error message that describes the actual problem
        for all SEs
      * inconsistent behavior where a statement fails or succeeds based on
        e.g. the partitioning algorithm of the table.
    
    mysql-test/r/multi_update.result:
      Add test for bug#57373
    mysql-test/r/multi_update_innodb.result:
      Add test for bug#57373
    mysql-test/r/partition.result:
      Add test for bug#55385
    mysql-test/t/multi_update.test:
      Add test for bug#57373
    mysql-test/t/multi_update_innodb.test:
      Add test for bug#57373
    mysql-test/t/partition.test:
      Add test for bug#55385
    sql/handler.cc:
      Translate handler error HA_ERR_RECORD_DELETED to server error
    sql/share/errmsg-utf8.txt:
      New error message for multi-table update where the same table is updated multiple times.
    sql/sql_update.cc:
      Add function unsafe_key_update()
    d1d16687
errmsg-utf8.txt 451 KB