Bug #34628 LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log
Submitted: 17 Feb 2008 17:11 Modified: 15 Mar 2010 4:39
Reporter: Partha Dutta Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.22 OS:Any
Assigned to: Libing Song
Tags: binary log, concurrent, LOAD DATA
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[17 Feb 2008 17:11] Partha Dutta
Description:
Issuing a LOAD DATA CONCURRENT LOCAL INFILE command only writes LOAD DATA LOCAL INFILE ito the binary log.  As a result, if replication is on, queries on slaves will block the replication SQL thread.

How to repeat:
Have binary logging enabled on the database server, and use this test:

mysql> create table a (a int);
Query OK, 0 rows affected (0.04 sec)

mysql> show create table a;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> \! cat a
1
2
3
4
mysql> load data concurrent local infile 'a' into table a;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

From a shell command prompt:

PowerBookDutta:/usr/local/mysql/data pdutta$ sudo mysqlbinlog /usr/local/mysql/data/binlog.000001
Password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#8217 12:1:29 server id 1  end_log_pos 106      Start: binlog v 4, server v 5.1.22-rc-log created 8217 12:1:29 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 106
#8217 12:2:31 server id 1  end_log_pos 191      Query   thread_id=3     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1203267751/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
create table a (a int)/*!*/;
# at 191
#8217 12:3:23 server id 1  end_log_pos 222 
#Begin_load_query: file_id: 1  block_len: 8
# at 222
#8217 12:3:23 server id 1  end_log_pos 348      Execute_load_query      thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1203267803/*!*/;
load data LOCAL INFILE '/var/tmp/SQL_LOAD_MB-1-3' INTO table a/*!*/;
# file_id: 1 
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

You can see that the LOAD DATA command has not been written with the CONCURRENT modifier.

I have not tested this on 5.0 but I imagine that it would be the same.

Suggested fix:
Add the CONCURRENT identifier to the binary log.
[18 Feb 2008 7:56] todiff wu
Hi Partha,

I try to reproduce the situation you reported, but master and slave work well after issue statement  LOAD DATA CONCURRENT LOCAL INFILE in MySQl 5.0. Mysql does log the statement into binlog as changing to “LOAD DATA LOCAL INFILE….”, but it is a normal result. please refer to the following page:
http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html

If you still get a error from slave for using LOAD DATA CONCURRENT LOCAL INFILE in your server, please upload the error information for us to analyze it.
[20 Feb 2008 13:40] Partha Dutta
My point is that if using a LOAD DATA CONCURRENT LOCAL INFILE on a server, then why is it writing LOCAL DATA LOCAL INFILE instead of LOAD DATA CONCURRENT LOCAL INFILE? Even with "concurrent_insert = 2" set in the my.cnf the entry in the binlog to me is incorrect, and should have the CONCURRENT option set.  Even if you replay the binary log onto the same server, the LOAD DATA statement will block all selects, which is not desirable at all.
[20 Feb 2008 23:07] Sveta Smirnova
Thank you for the feature request.

Please alco read about replication formats at http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html
[9 Mar 2008 17:36] Sveta Smirnova
After discussion changed severity and suggested fix as follows:

1. In MIXED mode log LOAD DATA CONCURRENT in ROW format.
2. Update documentation.

Workaround: use --binlog-format=row
[10 Apr 2008 16:01] Jon Stephens
After discussing this bug with Adam and Sveta, I've noted the following in the Manual's descriptions of LOAD DATA INFILE and replication issues with regard to this statement (for the 5.1 online Manual, these are located at http://dev.mysql.com/doc/refman/5.1/en/load-data.html and http://dev.mysql.com/doc/refman/5.1/en/replication-features-load-data.html, respectively):

In 4.1/5.0: CONCURRENT is not replicated.

In 5.1/6.0: CONCURRENT is not replicated using SBR, but is replicated by RBR. 

Docs changeset is here - http://lists.mysql.com/commits/45206 - changes should appear online within 24 hours.

When this bug is fixed, I'll add a note to the 5.1/6.0 versions of the Manual (in addition to the usual changelog entry) that MIXED mode did not select RBR for CONCURRENT prior to the fix, but does so following the fix.

Question: Does anyone know if this is also an issue for LOAD XML in 6.0? If so we should make sure that CONCURRENT's replication behaviour is the same for this statement as it is for LOAD DATA.

Thanks!
[28 Nov 2009 14:36] 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/91995

3202 Li-Bing.Song@sun.com	2009-11-28
      Bug #34628  	LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log
      
      'LOAD DATA CONCURRENT [LOCAL] INFILE ...' statment only is binlogged as
      'LOAD DATA [LOCAL] INFILE ...'.  As a result, if replication is on, 
      queries on slaves will block the replication SQL thread.
      
      This patch write code to write 'CONCURRENT' into the log event if 'CONCURRENT' option
      is in the original statement.
[28 Nov 2009 15:03] 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/92000

3202 Li-Bing.Song@sun.com	2009-11-28
      Bug #34628  	LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log
      
      'LOAD DATA CONCURRENT [LOCAL] INFILE ...' statment only is binlogged as
      'LOAD DATA [LOCAL] INFILE ...' in SBR and MBR.  As a result, if replication is on, 
      queries on slaves will block the replication SQL thread.
      
      This patch write code to write 'CONCURRENT' into the log event if 'CONCURRENT' option
      is in the original statement in SBR and MBR.
[30 Nov 2009 3:25] 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/92017

3202 Li-Bing.Song@sun.com	2009-11-30
      Bug #34628  	LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log
      
      'LOAD DATA CONCURRENT [LOCAL] INFILE ...' statment only is binlogged as
      'LOAD DATA [LOCAL] INFILE ...' in SBR and MBR.  As a result, if replication is on, 
      queries on slaves will be blocked by the replication SQL thread.
      
      This patch write code to write 'CONCURRENT' into the log event if 'CONCURRENT' option
      is in the original statement in SBR and MBR.
[10 Dec 2009 12:41] 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/93497

3202 Li-Bing.Song@sun.com	2009-12-10
      Bug #34628  	LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log
      
      'LOAD DATA CONCURRENT [LOCAL] INFILE ...' statment only is binlogged as
      'LOAD DATA [LOCAL] INFILE ...' in SBR and MBR.  As a result, if replication is on, 
      queries on slaves will be blocked by the replication SQL thread.
      
      This patch write code to write 'CONCURRENT' into the log event if 'CONCURRENT' option
      is in the original statement in SBR and MBR.
[11 Dec 2009 16:04] Andrei Elkin
LiBing, approved. Thanks for the work and addressing my comments!
[15 Dec 2009 5:14] 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/94027

3271 Li-Bing.Song@sun.com	2009-12-15
      Bug #34628  	LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log
      
      'LOAD DATA CONCURRENT [LOCAL] INFILE ...' statment only is binlogged as
      'LOAD DATA [LOCAL] INFILE ...' in SBR and MBR.  As a result, if replication is on, 
      queries on slaves will be blocked by the replication SQL thread.
      
      This patch write code to write 'CONCURRENT' into the log event if 'CONCURRENT' option
      is in the original statement in SBR and MBR.
[15 Dec 2009 7:36] Libing Song
Pushed to mysql5.1-bugteam, merged to mysql-pe.
[19 Dec 2009 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:33] Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091216134707-o96eqw0u2ynvo9gm) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[12 Jan 2010 23:10] Jon Stephens
Documented bugfix in the 5.5.1 and 6.0.14 changelogs as follows:

        When using the STATEMENT or MIXED logging format, the LOAD DATA
        CONCURRENT [LOCAL] INFILE statement was logged as LOAD DATA
        [LOCAL] INFILE. As a result, when using replication with either
        of these logging modes, queries on the slaves were blocked by
        the replication SQL thread.

Set status as NDI, waiting for merge to 5.1 tree.
[15 Jan 2010 9:00] Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:li-bing.song@sun.com-20091215051414-j5lrr1cx7bjjio5t) (merge vers: 5.1.42) (pib:16)
[18 Jan 2010 13:12] Jon Stephens
Also documented fix in the 6.1.43 changelog.

Closed.
[19 Jan 2010 18:46] Jon Stephens
Typo in previous comment, should refer to 5.1.43.
[4 Feb 2010 4:55] Libing Song
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
The reference manual still says "CONCURRENT is not replicated when using statement-based replication;"
But after this bug has been fixed, 'CONCURRENT' is always binlogged no matter
what mode is being used.
[4 Feb 2010 14:15] Jon Stephens
Amended text as suggested in 5.1, 5.5, 6.0 versions of the Manual. See http://lists.mysql.com/commits/99281.

Closed.
[12 Mar 2010 14:14] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:30] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:46] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 4:39] Jon Stephens
No new changelog entries required. Closed.
[10 Jan 2013 13:27] Erlend Dahl
Bug#4056 Replicating lock type in LOAD DATA INFILE statements

was marked as a duplicate.