MySQL Bugs: #4056: Replicating lock type in LOAD DATA INFILE statements

Bug #4056	Replicating lock type in LOAD DATA INFILE statements
Submitted:	8 Jun 2004 13:40	Modified:	10 Jan 2013 13:26
Reporter:	Masaki Fujimoto	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S5 (Performance)
Version:	5.0.66a	OS:	Any (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I'm not sure why (perhaps it's not so important?), but currently a lock type
(e.g. CONCURRENT) in LOAD DATA INFILE statement is not logged into binlog file,
so that of course LOAD DATA INFILE is always executed without any lock options
at slave serves.

Maybe it's true that this is not important for most of users (because normally
we do not use LOAD DATA INFILE so often, and do not care about lock options, 
too), it's worth activating a lock option of LOAD DATA INFILE at slave 
servers for some occasion (and I believe it won't do any harm).

How to repeat:
lock type in the LOAD DATA statement is never logged into binlog:

mysql> CREATE TABLE t1 (col1 INT);
mysql> LOAD DATA CONCURRENT INFILE '/tmp/infile.txt' INTO TABLE t1;
mysql> \q

$ mysqlbinlog log/mysql-bin.001
-snip-
# at 140
# LOAD DATA INFILE '/tmp/infile.txt' INTO TABLE t1  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED
BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (col1);
# file_id: 1  block_len: 4
# at 224
#040608 20:38:03 server id 1  log_pos 224
#Exec_load: file_id=1
LOAD DATA LOCAL INFILE '/tmp/infile.txt-1-5' INTO TABLE t1  FIELDS TERMINATED BY '\t' ENCLOSED BY ''
ESCAPED BY '\\' LINES TERMINATED BY '\n' STARTING BY '' (col1);
# file_id: 1  block_len: 4

Suggested fix:
a patch at following url (against mysql-4.0.20) is a sample implementation of 
this feature.

http://eth.jp/mysql-load-data-lock-type.patch.txt

Hello,

your request makes sense. It would help when there are client threads on the slave; that would allow them to use the table while it's being loaded. It would however not help inside the replication thread, as replication serializes all modifications done on master (I mean that if N insertions were run concurrently on master with the LOAD DATA CONCURRENT, in the master's binary log they all end serialized, so slave does not execute them in parallel (TODO later)).
Now, it's impossible to add logging of the CONCURRENT keyword in MySQL 4.0 or 4.1. It will be done for sure in MySQL 5.0 (hopefully 5.0.1) (that's why I'm marking this "deferred"), where we are changing the format used to write LOAD DATA INFILE to the binary log.

Thanks for your good suggestion!
Guilhem

Hello, thank you for your positive response.

Actually I am not sure about one thing you mentioned, so let me ask you the detail
of your response...

As my previous patch (located at http://eth.jp/mysql-load-data-lock-type.patch.txt)
shows, I think adding CONCURRENT logging feature could be easily done
*without* modifying current binlog format (using free bit of "opt_flags"), although
it is not so elegant. So I wonder why you think it is impossible to add 
CONCURRENT logging support ot MySQL 4.0 or 4.1... (I'm running mysql server
with the patch quoted above on my linux boxes, and they are currently quite
stable).

Please tell me if I am missing somehting, and if not, I hope this feature is 
integrated into MySQL 4.x

Dear Masaki,
you are right, your patch introduces no incompatibility. Still, 4.x are in a too much frozen state and we have switched our development efforts to 5.0. Hence I am not allowed to add this small feature to 4.x (you know, Quality Assurance...).
I hope this is not coming as a major inconvenience to you.
I am however glad that you took such a deep look in the replication code :)

Lars, is this still not fixed?

5.0.66a is still affected, as far as I can see.

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.66a-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.25 sec)

mysql> create table t1 (col1 int);
Query OK, 0 rows affected (0.25 sec)

mysql> LOAD DATA CONCURRENT INFILE 'c:/tmp/work/infile.txt' INTO TABLE t1;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
+------+
| col1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.03 sec)

mysql> show master status\G
*************************** 1. row ***************************
            File: toshiba-user-bin.000007
        Position: 527
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog ..\data\toshiba-user-bin
.000007
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080930 19:27:43 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
66a-enterprise-gpl-nt-log created 080930 19:27:43 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
ROLLBACK/*!*/;
# at 98
#080930 21:58:02 server id 1  end_log_pos 174   Query   thread_id=4     exec_tim
e=1     error_code=0
use test/*!*/;
SET TIMESTAMP=1222801082/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
drop table t1
/*!*/;
# at 174
#080930 21:58:12 server id 1  end_log_pos 263   Query   thread_id=4     exec_tim
e=1     error_code=0
SET TIMESTAMP=1222801092/*!*/;
create table t1 (col1 int)
/*!*/;
# at 263
#080930 21:59:06 server id 1  end_log_pos 331   Query   thread_id=4     exec_tim
e=0     error_code=0
SET TIMESTAMP=1222801146/*!*/;
BEGIN
/*!*/;
# at 331
#080930 21:59:06 server id 1  end_log_pos 358
#Begin_load_query: file_id: 1  block_len: 4
# at 358
#080930 21:59:06 server id 1  end_log_pos 500   Execute_load_query      thread_i
d=4     exec_time=0     error_code=0
SET TIMESTAMP=1222801146/*!*/;
LOAD DATA LOCAL INFILE 'C:/DOCUME~1/SATELL~1/LOCALS~1/Temp/SQL_LOAD_MB-1-1' INTO
 TABLE t1
/*!*/;
# file_id: 1
# at 500
#080930 21:59:06 server id 1  end_log_pos 527   Xid = 21
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Fixed in 5.1.44 as a duplicate of bug#34628.