MySQL Internals Manual  /  Prepared Statement and Stored Routine Re-Execution  /  Execution of a Prepared Statement

17.3 Execution of a Prepared Statement

In order to call mysql_execute_command (the function that executes a statement) for a prepared statement and not damage its parse tree, we backup and restore the active Query_arena of THD.

  • We don't want the garbage created during execution to be left in the permanent arena of the statement. To ensure that, every statement is executed in the runtime arena of THD. In other words, the arena which was active when mysql_stmt_execute was called is used as the runtime arena of the statement during its execution.

  • Before calling mysql_stmt_execute, we allocate thd->query with parameter markers ('?') replaced with their values: the new query is allocated in the runtime arena. We'll need this query for general, binary, error and slow logs.

  • The execution plan created at prepare stage is not saved (see Section 17.2, “Preparation of a Prepared Statement”), and at execute we simply create a new set of JOINs and then prepare and optimize it. During the first execution of the prepared statement the server may perform non-destructive transformations of statement's parsed tree: normally that would belong to a separate step executed at statement prepare, but once again, this haven't been done in 4.1 or 5.0. Such transformations absolutely must use the permanent arena of the prepared statement (saved in thd->stmt_arena). Whenever we need to perform a permanent transformation, we first call THD::activate_stmt_arena_if_needed to make the permanent arena active, transform the tree, and restore the runtime arena. To avoid double transformations in such cases, we track current state of the parsed tree in Query_arena::state.

    This state may be one of the following:

    • INITIALIZED — we're in statement PREPARE.

    • INITIALIZED_FOR_SP — we're in first execution of a stored procedure statement.

    • PREPARED — we're in first execution of a prepared statement.

    • EXECUTED — we're in a subsequent execution of a prepared statement or a stored procedure statement.

    • CONVENTIONAL_EXECUTION — we're executing a pre-4.1 query.

    One can use helper methods of Query_arena to check this state (is_conventional_execution(), is_stmt_prepare(), is_stmt_execute(), is_stmt_prepare_or_first_sp_execute()).

    Additionally, st_select_lex_unit::first_execution contains a flag for the state of each subquery in a complex statement. A separate variable is needed because not all subqueries may get executed during the first execution of a statement.

  • Some optimizations damage the parsed tree, for example, replace leaves and subtrees of items with other items or leave item objects cluttered with runtime data. To allow re-execution of a prepared statement the following mechanisms are currently employed:

    1. A hierarchy of Item::cleanup() and st_select_lex::cleanup() methods to restore the parsed tree to the condition of right-after-parse. These cleanups are called in Prepared_statement::cleanup_stmt() after the statement has been executed.

    2. In order to roll back destructive transformations of the parsed tree, every replacement of one item with another is registered in THD::change_list by using THD::change_item_tree(). In the end of execution all such changes are rolled back in reverse order.


      if (!(fld= new Item_field(from_field)))
      goto error;
      thd->change_item_tree(reference, fld);

      If a transformation is a non-destructive, it should not be registered, but performed only once in the permanent memory root. Additionally, be careful to not supply a pointer to stack as the first argument of change_item_tree(); that will lead to stack corruption when a tree is restored.

    3. AND /OR subtrees of WHERE and ON clauses are created anew for each execution. It was easier to implement in 4.1, and the approach with change record list used in (b) could not have been used for AND/OR transformations, because these transformations not only replace one item with another, but also can remove a complete subtree. Leafs of AND/OR subtrees are not copied by this mechanism because currently they are not damaged by the transformation. For details, see Item::copy_andor_structure().

    4. No other mechanism exists in the server at the moment to allow re-execution. If the code that you're adding transforms the parsed tree, you must use one of the mechanisms described above, or propose and implement a better approach.

  • When execution is done, we rollback the damage of the parsed tree.