MySQL Bugs: #26050: LOAD DATA INFILE breaks ACID

Bug #26050	LOAD DATA INFILE breaks ACID
Submitted:	3 Feb 2007 13:20	Modified:	15 Mar 2007 15:20
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0,4.1,5.0,5.1-bk	OS:	Linux (linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
LOAD DATA INFILE sends ok to the client before finishing writing to the binlog and before committing into the engine.
Normally, when a transactional table (InnoDB) is modified, when the client receives "ok" it implies that the modifications are committed and durable (that's ACID).
So, this breaks ACID.
Please, verify with older versions.

How to repeat:
For example start mysqld in a debugger, put a breakpoint after send_ok() in mysql_load.cc.
create table ld(a varchar(100)) engine=innodb;
show create table ld;
load data infile '~/xorg.conf' into table ld;
Client receives ok so commit should have been done. Do "kill -9" on mysqld.
Restart mysqld, observe that "ld" is empty.

Suggested fix:
move send_ok() down to after binlog writes and ha_autocommit_or_rollback().

auto-linking of the patch didn't work, so here it is:
ChangeSet@1.2394, 2007-02-08 17:32:49+01:00, guilhem@gbichot3.local +1 -0
  Fix for BUG#26050 "LOAD DATA INFILE breaks ACID"; the ok must be sent
  to the client only after the binlog write and engine commit.
  No testcase for this bug, as to reproduce it, we need to "kill -9" mysqld,
  which we cannot do in the testsuite. But, I tested by hand.

  sql/sql_load.cc@1.105, 2007-02-08 17:32:47+01:00, guilhem@gbichot3.local +3 -1
    D in ACID means that once the client got the ok from the server, the data
    is durable on disk. Implies that the ok must be sent after the binlog write
    and after the engine commit, not before.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	guilhem
# Host:	gbichot3.local
# Root:	/home/mysql_src/mysql-5.0-rpl-24432

--- 1.104/sql/sql_load.cc	2007-02-08 17:32:53 +01:00
+++ 1.105/sql/sql_load.cc	2007-02-08 17:32:53 +01:00
@@ -454,7 +454,6 @@ bool mysql_load(THD *thd,sql_exchange *e
   }
   sprintf(name, ER(ER_LOAD_INFO), (ulong) info.records, (ulong) info.deleted,
 	  (ulong) (info.records - info.copied), (ulong) thd->cuted_fields);
-  send_ok(thd,info.copied+info.deleted,0L,name);
 
   if (!transactional_table)
     thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
@@ -474,6 +473,9 @@ bool mysql_load(THD *thd,sql_exchange *e
 #endif /*!EMBEDDED_LIBRARY*/
   if (transactional_table)
     error=ha_autocommit_or_rollback(thd,error);
+
+  /* ok to client sent only after binlog write and engine commit */
+  send_ok(thd, info.copied + info.deleted, 0L, name);
 
 err:
   if (thd->lock)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20600

ChangeSet@1.2609, 2007-02-26 20:35:28+01:00, gbichot@dl145h.mysql.com +1 -0
  Fix for BUG#26050 "LOAD DATA INFILE breaks ACID"; the ok must be sent
  to the client only after the binlog write and engine commit.
  No testcase for this bug, as to reproduce it, we need to "kill -9" mysqld,
  which we cannot do in the testsuite. But, I tested by hand.

pushed to 4.1-rpl team tree. Shall be merged to 5.0-rpl and 5.1-rpl by the replication team.

I've reviewed the patch and tested it in 5.1. It works fine. Approved.

pushed to 4.1.23,5.0.38,5.1.17-beta

Noted in 4.1.23, 5.0.38, 5.1.17 changelogs.

LOAD DATA INFILE sent an okay to the client before writing the binary
log and committing the changes to the table had finished, thus
violating ACID requirements.