MySQL Bugs: #578: mysqlimport -l silently fails when binlog-ignore-db is set

Bug #578	mysqlimport -l silently fails when binlog-ignore-db is set
Submitted:	3 Jun 2003 19:21	Modified:	5 Jun 2003 6:49
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysqld Ver 4.0.12	OS:	Linux (Linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Running a mysqlimport with the -l switch fails when the database server has binlog-ignore-db turned on for the imported database.  Removing the -l makes the problem go away.

In both cases, mysqlimport reports a sucessful import along with the count of the records loaded, but when -l is specified, the load never seems to happen!

How to repeat:
1) Do a mysqldump -T of some database.  For the purpose of this example, let's say it has one table of type innodb called "company" (but the exact makeup of the db is largely irrelevant).

2) Add binlog-ignore-db=testDB to my.cnf and restart server.  (I suspect you also need to add "log-bin=log-bin" as well for this to have any effect).

3) Create a new database "testDB"

4) Load in a table definition into testDB: mysql testDB < company.sql

5) mysqlimport -l testDB company.txt

This will report the number of rows imported, but if you check (via the client) the table has no data.

6) mysqlimport testDB company.txt

This generates the exact same output as step 5, but the data is actually loaded.

We use innodb tables, so perhaps this is a requirement as well.

Suggested fix:
The import should work independently of the -l switch.

If the import does fail, it should report an error.

Indeed, I could repeat it (only with InnoDB tables). We are going to work on it
and let you know of our progress.

Found a shorter testcase : start mysqld without --log-bin (no need for binlog-ignore-db then) and do:
create table test.t(a int) type=innodb;
lock tables test.t write;
insert into test.t values(10);
exit

Then open another connection and do
select * from test.t; #nothing

Hi,

here is a patch for this bug. This patch will be compiled in MySQL 4.0.14 (whose release date is probably not before at least one month from here, as we planned to release 3.23.57 and 4.1.1 before). Until then, you probably can drop the '-l' option. When one does 'mysqlimport -l' on table T, this is equivalent to:
LOCK TABLES T WRITE;
LOAD DATA INFILE ... INTO TABLE T ... ;

As you are loading into an InnoDB table, I would say LOCK TABLES is not needed (because InnoDB has row-level locking).
So, while what you pointed out is truly our bug and will be fixed in 4.0.14, I think you can safely drop the '-l' option for the moment.
Another way, which keeps the table locking behaviour, is not use mysqlimport for the moment, and do this in a 'mysql' client instead:
LOCK TABLES T WRITE;
LOAD DATA INFILE ... INTO TABLE T ... ;
UNLOCK TABLES; #with this command the bug will not appear

--- 1.98/sql/handler.cc Wed May 14 01:27:23 2003
+++ 1.99/sql/handler.cc Wed Jun  4 17:58:30 2003
@@ -208,23 +208,45 @@
 }
 
 /*
-  This is used to commit or rollback a single statement depending
-  on the value of error
+  This is used to commit or rollback a single statement depending on the value
+  of error. If the autocommit is on, then we will commit or rollback the whole
+  transaction (= the statement). The autocommit mechanism built into handlers
+  is based on counting locks, but if the user has used LOCK TABLES then that
+  mechanism does not know to do the commit.
 */
 
 int ha_autocommit_or_rollback(THD *thd, int error)
 {
+  bool do_autocommit=FALSE;
+
   DBUG_ENTER("ha_autocommit_or_rollback");
 #ifdef USING_TRANSACTIONS
+
+  if (!(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)))
+    do_autocommit=TRUE;  /* We can commit or rollback the whole transaction */
+
   if (opt_using_transactions)
   {
     if (!error)
     {
-      if (ha_commit_stmt(thd))
-       error=1;
+      if (do_autocommit)
+      {
+       if (ha_commit(thd))
+         error=1;
+      }
+      else
+      {
+        if (ha_commit_stmt(thd))
+         error=1;
+      }
     }
     else
-      (void) ha_rollback_stmt(thd);
+    {
+      if (do_autocommit)
+       (void) ha_rollback(thd);
+      else
+        (void) ha_rollback_stmt(thd);
+    }
 
     thd->variables.tx_isolation=thd->session_tx_isolation;
   }

Very unfortunately, we just detected that the patch posted here can cause problems when using BDB (Berkeley DB) tables, so a better, more general patch will be posted here soon.

Hi,

The previous patch, which fixes the bug for InnoDB, is not suitable for Berkeley DB (BDB) tables (it is its only known problem). We have fixed it for BDB now, but this is a not-so-small new patch which needs time to be tested and validated internally. Once validated, it will be included in 4.0.14. Until then, it is advisable to either drop the '-l' mysqlimport option; or apply the previous patch (if you do not use BDB) as a temporary solution until 4.0.14 is released.

Regards,
Guilhem