MySQL Bugs: #34628: LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log

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	CPU Architecture:	Any
Tags:	binary log, concurrent, LOAD DATA

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.

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.

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.

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

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

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!

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.

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.

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.

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.

LiBing, approved. Thanks for the work and addressing my comments!

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.

Pushed to mysql5.1-bugteam, merged to mysql-pe.

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)

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.

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)

Also documented fix in the 6.1.43 changelog.

Closed.

Typo in previous comment, should refer to 5.1.43.

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.

Amended text as suggested in 5.1, 5.5, 6.0 versions of the Manual. See http://lists.mysql.com/commits/99281.

Closed.

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)

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)

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)

No new changelog entries required. Closed.

Bug#4056 Replicating lock type in LOAD DATA INFILE statements

was marked as a duplicate.