| Bug #34628 | LOAD DATA CONCURRENT INFILE drops CONCURRENT in binary log | ||
|---|---|---|---|
| Submitted: | 17 Feb 2008 18:11 | Modified: | 15 Dec 8:36 |
| Reporter: | Partha Dutta | ||
| Status: | Patch queued | ||
| 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) | ||
[18 Feb 2008 8: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 14: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.
[21 Feb 2008 0: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 18: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 18: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 15: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 16: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 4: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 13: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 17:04]
Andrei Elkin
LiBing, approved. Thanks for the work and addressing my comments!
[15 Dec 6: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 8:36]
Libing Song
Pushed to mysql5.1-bugteam, merged to mysql-pe.
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.