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 18:11	Modified:	9 Mar 2008 18:36
Reporter:	Partha Dutta
Status:	Verified
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Libing Song	Target Version:	5.1+
Tags:	LOAD DATA, concurrent, binary log
Triage:	Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

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!