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

[3 Feb 2007 13:20] Guilhem Bichot
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().
[26 Feb 2007 12:08] Guilhem Bichot
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)
[26 Feb 2007 19:35] Bugs System
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.
[26 Feb 2007 19:36] Guilhem Bichot
pushed to 4.1-rpl team tree. Shall be merged to 5.0-rpl and 5.1-rpl by the replication team.
[27 Feb 2007 17:15] Chuck Bell
I've reviewed the patch and tested it in 5.1. It works fine. Approved.
[8 Mar 2007 8:33] Andrei Elkin
pushed to 4.1.23,5.0.38,5.1.17-beta
[15 Mar 2007 15:20] Paul Dubois
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.