• unknown's avatar
    Implement WL#2661 "Prepared Statements: Dynamic SQL in Stored Procedures". · c910050a
    unknown authored
    The idea of the patch is to separate statement processing logic,
    such as parsing, validation of the parsed tree, execution and cleanup, 
    from global query processing logic, such as logging, resetting
    priorities of a thread, resetting stored procedure cache, resetting
    thread count of errors and warnings.
    This makes PREPARE and EXECUTE behave similarly to the rest of SQL
    statements and allows their use in stored procedures.
    This patch contains a change in behaviour:
    until recently for each SQL prepared statement command, 2 queries
    were written to the general log, e.g.
    [Query]   prepare stmt from @stmt_text;
    [Prepare] select * from t1 <-- contents of @stmt_text
    The chagne was necessary to prevent [Prepare] commands from being written
    to the general log when executing a stored procedure with Dynamic SQL.
    We should consider whether the old behavior is preferrable and probably
    restore it.
    This patch refixes Bug#7115, Bug#10975 (partially), Bug#10605 (various bugs
    in Dynamic SQL reported before it was disabled).
    
    
    mysql-test/r/not_embedded_server.result:
      Since we don't want to log Dynamic SQL in stored procedures,
      now the general log gets only one log entry per SQL statement.
    mysql-test/r/sp-error.result:
      - remove obsolete tests
      - a better error message for the case when a stored procedure that
      returns a result set is called from a function
    mysql-test/r/trigger.result:
      - a better error message for the case when a stored procedure that
      returns a result set is called from a trigger
    mysql-test/t/sp-error.test:
      - a better error message for the case when a stored procedure that
        returns a result set is called from a function.
      - move the comment to its place (end of file).
    mysql-test/t/trigger.test:
      - a better error message for the case when a stored procedure that
      returns a result set is called from a trigger
    sql/item_func.cc:
      - we need to pass sql_command explicitly to get_var_with_binlog, because
      when creating a query for SQL prepared statement thd->lex->sql_command
      points at SQLCOM_EXECUTE, which is not listed in the list of update
      queries.
    sql/log_event.h:
      - remove an extra copy of the previous sentence
    sql/mysql_priv.h:
      - fix declarations of sql_prepare.cc API
    sql/share/errmsg.txt:
      - a new error message, when one attempts to execute a prepared statement
      which is currently being executed (this can happen only in Dynamic SQL
      at the moment).
    sql/sp_head.cc:
      - extend sp_multi_results_command to return different flags for a
      command (and rename it)
      - add support for SQLCOM_PREPARE,SQLCOM_EXECUTE, SQLCOM_DEALLOCATE
        to sp_get_flags_for_command
      - replace multiple boolean sp_head members with uint m_flags
      - a fix for a crash when user variables are used in a stored procedure
        and binlog is on. A temporary fix for Bug#12637 "SP crashes the server 
       if it has update query with user var & binlog is enabled", which actually
       stands for stored functions: now instead of a crash we break
       replication if a user variable is used in a stored function which 
       is executed in prelocked mode.
    sql/sp_head.h:
      - replace multiple boolean flags of sp_head with uint m_flags;
      - add flag CONTAINS_DYNAMIC_SQL
      - use this flag to error if a stored procedure with Dynamic SQL is
        called from a function or trigger.
    sql/sql_class.cc:
      - Statement_map::insert should not delete a statement if it exists,
        now it's done externally to be able to handle the case when the
        statement being deleted is in use.
      - remove extra code (free_list is already reset in free_items)
    sql/sql_lex.cc:
      - add lex->stmt_prepare_mode; we can't rely on thd->command any more,
        because we don't reset it any more (Dynamic SQL requirement is that
        PS are as little intrusive as possible).
    sql/sql_lex.h:
      - declare bool LEX::stmt_prepare_mode
    sql/sql_parse.cc:
      - move prepared statement code to sql_prepare.cc
      - change declarations (refactored code)
      - better error message when one attempts to use Dynamic SQL or a 
        stored procedure that returns a result set in a function or trigger.
    sql/sql_prepare.cc:
      - major refactoring to ensure PREPARE/EXECUTE commands do not reset global THD
        state and allow their use in stored procedures.
      - add Prepared_statement::flags and use it to ensure no recursive execution
        of a prepared statement is possible
      - better comments
    sql/sql_yacc.yy:
      - enable PREPARE/EXECUTE/DEALLOCATE in stored procedures
      - produce an error message on attempt to use PREPARE/EXECUTE/DEALLOCATE
        in a stored function or trigger
    mysql-test/r/sp-dynamic.result:
      - sp-dynamic.test results
    mysql-test/t/sp-dynamic.test:
      - a new test for PREPARE/EXECUTE/DEALLOCATE in stored procedures.
    c910050a
sp-error.test 21.1 KB