| 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) | ||
[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!
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_serv er=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.