• Konstantin Osipov's avatar
    Apply and review: · bf9c1b73
    Konstantin Osipov authored
    3655 Jon Olav Hauglid   2009-10-19
    Bug #30977 Concurrent statement using stored function and DROP FUNCTION 
               breaks SBR
    Bug #48246 assert in close_thread_table
    
    Implement a fix for:
    Bug #41804 purge stored procedure cache causes mysterious hang for many
               minutes
    Bug #49972 Crash in prepared statements
    
    The problem was that concurrent execution of DML statements that
    use stored functions and DDL statements that drop/modify the same
    function might result in incorrect binary log in statement (and
    mixed) mode and therefore break replication.
    
    This patch fixes the problem by introducing metadata locking for
    stored procedures and functions. This is similar to what is done
    in Bug#25144 for views. Procedures and functions now are
    locked using metadata locks until the transaction is either
    committed or rolled back. This prevents other statements from
    modifying the procedure/function while it is being executed. This
    provides commit ordering - guaranteeing serializability across
    multiple transactions and thus fixes the reported binlog problem.
    
    Note that we do not take locks for top-level CALLs. This means
    that procedures called directly are not protected from changes by
    simultaneous DDL operations so they are executed at the state they
    had at the time of the CALL. By not taking locks for top-level
    CALLs, we still allow transactions to be started inside
    procedures.
    
    This patch also changes stored procedure cache invalidation.
    Upon a change of cache version, we no longer invalidate the entire
    cache, but only those routines which we use, only when a statement
    is executed that uses them.
    
    This patch also changes the logic of prepared statement validation.
    A stored procedure used by a prepared statement is now validated
    only once a metadata lock has been acquired. A version mismatch
    causes a flush of the obsolete routine from the cache and
    statement reprepare.
    Incompatible changes:
    1) ER_LOCK_DEADLOCK is reported for a transaction trying to access
       a procedure/function that is locked by a DDL operation in
       another connection.
    
    2) Procedure/function DDL operations are now prohibited in LOCK
       TABLES mode as exclusive locks must be taken all at once and
       LOCK TABLES provides no way to specifiy procedures/functions to
       be locked.
    
    Test cases have been added to sp-lock.test and rpl_sp.test.
    
    Work on this bug has very much been a team effort and this patch
    includes and is based on contributions from Davi Arnaut, Dmitry
    Lenev, Magne Mæhre and Konstantin Osipov.
    
    
    mysql-test/r/ps_ddl.result:
      Update results (Bug#30977).
    mysql-test/r/ps_ddl1.result:
      Update results (Bug#30977).
    mysql-test/r/sp-error.result:
      Update results (Bug#30977).
    mysql-test/r/sp-lock.result:
      Update results (Bug#30977).
    mysql-test/suite/rpl/r/rpl_sp.result:
      Update results (Bug#30977).
    mysql-test/suite/rpl/t/rpl_sp.test:
      Add a test case for Bug#30977.
    mysql-test/t/ps_ddl.test:
      Update comments. We no longer re-prepare a prepared statement
      when a stored procedure used in top-level CALL is changed.
    mysql-test/t/ps_ddl1.test:
      Modifying stored procedure p1 no longer invalidates prepared
      statement "call p1" -- we can re-use the prepared statement
      without invalidation.
    mysql-test/t/sp-error.test:
      Use a constant for an error value.
    mysql-test/t/sp-lock.test:
      Add test coverage for Bug#30977.
    sql/lock.cc:
      Implement lock_routine_name() - a way to acquire an 
      exclusive metadata lock (ex- name-lock) on 
      stored procedure/function.
    sql/sp.cc:
      Change semantics of sp_cache_routine() -- now it has an option
      to make sure that the routine that is cached is up to date (has
      the latest sp cache version).
      
      Add sp_cache_invalidate() to sp_drop_routine(), where it was
      missing (a bug!).
      
      Acquire metadata locks for SP DDL (ALTER/CREATE/DROP). This is
      the core of the fix for Bug#30977.
      
      Since caching and cache invalidation scheme was changed, make 
      sure we don't invalidate the SP cache in the middle of a stored
      routine execution. At the same time, make sure we don't access
      stale data due to lack of invalidation. 
      For that, change ALTER FUNCTION/PROCEDURE to not use the cache,
      and SHOW PROCEDURE CODE/SHOW CREATE PROCEDURE/FUNCTION to always
      read an up to date version of the routine from the cache.
    sql/sp.h:
      Add a helper wrapper around sp_cache_routine().
    sql/sp_cache.cc:
      Implement new sp_cache_version() and sp_cache_flush_obsolete().
      Now we flush stale routines individually, rather than all at once.
    sql/sp_cache.h:
      Update signatures of sp_cache_version() and sp_cache_flush_obsolete().
    sql/sp_head.cc:
      Add a default initialization of sp_head::m_sp_cache_version.
      Remove a redundant sp_head::create().
    sql/sp_head.h:
      Add m_sp_cache_version to sp_head class - we now 
      keep track of every routine in the stored procedure cache, rather than
      of the entire cache.
    sql/sql_base.cc:
      Implement prelocking for stored routines. Validate stored
      routines after they were locked.
      Flush obsolete routines upon next access, one by one, not all at once
      (Bug#41804).
      Style fixes.
    sql/sql_class.h:
      Rename a Open_table_context method.
    sql/sql_parse.cc:
      Make sure stored procedures DDL commits the active transaction 
      (issues an implicit commit before and after).
      Remove sp_head::create(), a pure redundancy.
      Move the semantical check during alter routine inside sp_update_routine() code in order to:
      - avoid using SP cache during update, it may be obsolete.
      - speed up and simplify the update procedure.
      
      Remove sp_cache_flush_obsolete() calls, we no longer flush the entire
      cache, ever, stale routines are flushed before next use, one at a time.
    sql/sql_prepare.cc:
      Move routine metadata validation to open_and_process_routine().
      Fix Bug#49972 (don't swap flags at reprepare).
      Reset Sroutine_hash_entries in reinit_stmt_before_use().
      Remove SP cache invalidation, it's now done by open_tables().
    sql/sql_show.cc:
      Fix a warning: remove an unused label.
    sql/sql_table.cc:
      Reset mdl_request.ticket for tickets acquired for routines inlined
      through a view, in CHECK TABLE statement, to satisfy an MDL assert.
    sql/sql_update.cc:
      Move the cleanup of "translation items" to close_tables_for_reopen(),
      since it's needed in all cases when we back off, not just
      the back-off in multi-update. This fixes a bug when the server
      would crash on attempt to back off when opening tables
      for a statement that uses information_schema tables.
    bf9c1b73
rpl_sp.result 30.3 KB