1. 06 Mar, 2007 3 commits
    • malff/marcsql@weblab.(none)'s avatar
      Manual merge · 82c1c023
      malff/marcsql@weblab.(none) authored
      82c1c023
    • malff/marcsql@weblab.(none)'s avatar
      Merge malff@bk-internal.mysql.com:/home/bk/mysql-5.0-runtime · 9f0b0df9
      malff/marcsql@weblab.(none) authored
      into  weblab.(none):/home/marcsql/TREE/mysql-5.0-8407_b
      9f0b0df9
    • malff/marcsql@weblab.(none)'s avatar
      Bug#8407 (Stored functions/triggers ignore exception handler) · b216d959
      malff/marcsql@weblab.(none) authored
      Bug 18914 (Calling certain SPs from triggers fail)
      Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
      Bug 21825 (Incorrect message error deleting records in a table with a
        trigger for inserting)
      Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency
        error)
      Bug 25345 (Cursors from Functions)
      
      
      This fix resolves a long standing issue originally reported with bug 8407,
      which affect the behavior of Stored Procedures, Stored Functions and Trigger
      in many different ways, causing symptoms reported by all the bugs listed.
      In all cases, the root cause of the problem traces back to 8407 and how the
      server locks tables involved with sub statements.
      
      Prior to this fix, the implementation of stored routines would:
      - compute the transitive closure of all the tables referenced by a top level
      statement
      - open and lock all the tables involved
      - execute the top level statement
      "transitive closure of tables" means collecting:
      - all the tables,
      - all the stored functions,
      - all the views,
      - all the table triggers
      - all the stored procedures
      involved, and recursively inspect these objects definition to find more
      references to more objects, until the list of every object referenced does
      not grow any more.
      This mechanism is known as "pre-locking" tables before execution.
      The motivation for locking all the tables (possibly) used at once is to
      prevent dead locks.
      
      One problem with this approach is that, if the execution path the code
      really takes during runtime does not use a given table, and if the table is
      missing, the server would not execute the statement.
      This in particular has a major impact on triggers, since a missing table
      referenced by an update/delete trigger would prevent an insert trigger to run.
      
      Another problem is that stored routines might define SQL exception handlers
      to deal with missing tables, but the server implementation would never give
      user code a chance to execute this logic, since the routine is never
      executed when a missing table cause the pre-locking code to fail.
      
      With this fix, the internal implementation of the pre-locking code has been
      relaxed of some constraints, so that failure to open a table does not
      necessarily prevent execution of a stored routine.
      
      In particular, the pre-locking mechanism is now behaving as follows:
      
      1) the first step, to compute the transitive closure of all the tables
      possibly referenced by a statement, is unchanged.
      
      2) the next step, which is to open all the tables involved, only attempts
      to open the tables added by the pre-locking code, but silently fails without
      reporting any error or invoking any exception handler is the table is not
      present. This is achieved by trapping internal errors with
      Prelock_error_handler
      
      3) the locking step only locks tables that were successfully opened.
      
      4) when executing sub statements, the list of tables used by each statements
      is evaluated as before. The tables needed by the sub statement are expected
      to be already opened and locked. Statement referencing tables that were not
      opened in step 2) will fail to find the table in the open list, and only at
      this point will execution of the user code fail.
      
      5) when a runtime exception is raised at 4), the instruction continuation
      destination (the next instruction to execute in case of SQL continue
      handlers) is evaluated.
      This is achieved with sp_instr::exec_open_and_lock_tables()
      
      6) if a user exception handler is present in the stored routine, that
      handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be
      trapped by stored routines. If no handler exists, then the runtime execution
      will fail as expected.
      
      With all these changes, a side effect is that view security is impacted, in
      two different ways.
      
      First, a view defined as "select stored_function()", where the stored
      function references a table that may not exist, is considered valid.
      The rationale is that, because the stored function might trap exceptions
      during execution and still return a valid result, there is no way to decide
      when the view is created if a missing table really cause the view to be invalid.
      
      Secondly, testing for existence of tables is now done later during
      execution. View security, which consist of trapping errors and return a
      generic ER_VIEW_INVALID (to prevent disclosing information) was only
      implemented at very specific phases covering *opening* tables, but not
      covering the runtime execution. Because of this existing limitation,
      errors that were previously trapped and converted into ER_VIEW_INVALID are
      not trapped, causing table names to be reported to the user.
      This change is exposing an existing problem, which is independent and will
      be resolved separately.
      b216d959
  2. 05 Mar, 2007 1 commit
  3. 02 Mar, 2007 4 commits
  4. 01 Mar, 2007 16 commits
  5. 28 Feb, 2007 9 commits
  6. 27 Feb, 2007 6 commits
    • rafal@quant.(none)'s avatar
      Merge quant.(none):/ext/mysql/bkroot/mysql-5.0-rpl · 235ca912
      rafal@quant.(none) authored
      into  quant.(none):/ext/mysql/bk/mysql-5.0-bug25306
      235ca912
    • lars/lthalmann@mysql.com/dl145j.mysql.com's avatar
      Merge mysql.com:/nfsdisk1/lars/MERGE/mysql-4.1-merge · 6889569f
      into  mysql.com:/nfsdisk1/lars/MERGE/mysql-5.0-merge
      6889569f
    • lars/lthalmann@mysql.com/dl145h.mysql.com's avatar
      Merge mysql.com:/nfsdisk1/lars/bkroot/mysql-5.0-rpl · f872d8ff
      into  mysql.com:/nfsdisk1/lars/MERGE/mysql-5.0-merge
      f872d8ff
    • cbell/Chuck@mysql_cab_desk.'s avatar
      BUG#20141 "User-defined variables are not replicated properly for · d13c3b94
      cbell/Chuck@mysql_cab_desk. authored
                SF/Triggers in SBR mode."
      BUG#14914 "SP: Uses of session variables in routines are not always replicated"
      BUG#25167 "Dupl. usage of user-variables in trigger/function is not replicated
                correctly"
      
      This patch corrects a minor error in the previous patch for BUG#20141. This patch
      corrects an errant code change to sp_head.cc. The comments for the first patch follow:
      
      User-defined variables used inside of stored functions/triggers in
      statements which did not update tables directly were not replicated.
      We also had problems with replication of user-defined variables which
      were used in triggers (or stored functions called from table-updating
      statements) more than once.
      
      This patch addresses the first issue by enabling logging of all
      references to user-defined variables in triggers/stored functions
      and not only references from table-updating statements.
      
      The second issue stemmed from the fact that for user-defined
      variables used from triggers or stored functions called from
      table-updating statements we were writing binlog events for each
      reference instead of only one event for the first reference.
      This problem is already solved for stored functions called from
      non-updating statements with help of "event unioning" mechanism.
      So the patch simply extends this mechanism to the case affected.
      It also fixes small problem in this mechanism which caused wrong
      logging of references to user-variables in cases when non-updating
      statement called several stored functions which used the same
      variable and some of these function calls were omitted from binlog
      as they were not updating any tables.
      d13c3b94
    • bar@mysql.com's avatar
      Merge abarkov@bk-internal.mysql.com:/home/bk/mysql-5.0-rpl · 75177d9c
      bar@mysql.com authored
      into  mysql.com:/home/bar/mysql-5.0.b24478
      75177d9c
    • gluh@mysql.com/eagle.(none)'s avatar
      removed unused variable · b3cb8466
      gluh@mysql.com/eagle.(none) authored
      b3cb8466
  7. 26 Feb, 2007 1 commit