Bug #36258 Maria: INSERT SELECT sends ok to client before committing
Submitted: 22 Apr 2008 13:58 Modified: 19 May 2008 12:16
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Maria storage engine Severity:S2 (Serious)
Version:5.1-maria OS:Any
Assigned to: CPU Architecture:Any

[22 Apr 2008 13:58] Guilhem Bichot 
Description:
Konstantin said his patch for BUG#12713 would fix this; this bug report is here for us to check that after merging 5.1, including the fix for BUG#12713, the problem indeed does not exist anymore.

When running INSERT INTO t SELECT 1, select_insert::send_eof() call ::send_ok(), and only later is the table 't' unlocked, here:

        if (first_table->lock_type ==  TL_WRITE_CONCURRENT_INSERT &&
            thd->lock)
        {
          /* INSERT ... SELECT should invalidate only the very first table */
          TABLE_LIST *save_table= first_table->next_local;
          first_table->next_local= 0;
          mysql_unlock_tables(thd, thd->lock);
in sql_parse.cc.
And this unlock causes the commit.
So we have a case of OK sent to client before the statement is committed (durable), this is not ACID.

How to repeat:
Run mysqld in gdb, breakpoint in ma_commit(); run INSERT SELECT above; client gets ok; when breakpoint is hit, kill -9 mysqld. After Recovery, INSERT SELECT has been rolled back, though client received ok.
[19 May 2008 12:16] Guilhem Bichot 
fix has indeed been merged, bug scenario cannot be reproduced.
It is this changeset which fixed the bug:
ChangeSet@1.2476.772.1, 2007-12-12 18:21:01+03:00, kostja@bodhi.(none) +51 -0
  Bug#12713 "Error in a stored function called from a SELECT doesn't
  cause ROLLBACK of statement", part 1. Review fixes.

  Do not send OK/EOF packets to the client until we reached the end of
  the current statement.
  This is a consolidation, to keep the functionality that is shared by all
  SQL statements in one place in the server.
  Currently this functionality includes:
  - close_thread_tables()
  - log_slow_statement().

  After this patch and the subsequent patch for Bug#12713, it shall also include
:
  - ha_autocommit_or_rollback()
  - net_end_statement()
  - query_cache_end_of_result().

  In future it may also include:
  - mysql_reset_thd_for_next_command().