Bug #30435 loading large LOAD DATA INFILE breaks slave with read_buffer_size set on master
Submitted: 15 Aug 2007 13:33 Modified: 12 Feb 2008 20:41
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.46 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any
Tags: bfsm_2007_10_18, regression

[15 Aug 2007 13:33] Morgan Tocker 
Description:
This appears to be related to bug #15937, which was apparently fixed.  The slave doesn't process the text file at all, and stops the IO thread:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: 
                Master_Host: localhost
                Master_User: test
                Master_Port: 3307
              Connect_Retry: 60
            Master_Log_File: host2-bin.000001
        Read_Master_Log_Pos: 312
             Relay_Log_File: host2-relay-bin.000002
              Relay_Log_Pos: 449
      Relay_Master_Log_File: host2-bin.000001
           Slave_IO_Running: No
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 312
            Relay_Log_Space: 449
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

How to repeat:
#!/bin/sh

# kill the servers from the previous run.
mysqladmin -S /tmp/slave.sock shutdown;
mysqladmin -S /tmp/master.sock shutdown;

# kill the data directories from the previous run.

rm -rf /tmp/master/*
rm -rf /tmp/slave/*

# start the servers
# you may want to change this line.

cd /Users/morgo/bin/mysql-enterprise-gpl-5.0.46*;

# note: remove the line " --read_buffer_size=2M \" and the testcase passes!

./bin/mysqld_safe --no-defaults \
 --port=3307 \
 --socket=/tmp/master.sock \
 --read_buffer_size=2M \
 --log-bin \
 --server-id=1 \
 --datadir=/tmp/master \
 --skip-grant &

./bin/mysqld_safe --no-defaults \
 --port=3308 \
 --socket=/tmp/slave.sock \
 --server-id=2 \
 --log-bin \
 --datadir=/tmp/slave \
 --skip-grant &

# give the servers a chance to start.
sleep 10;

# start replication on the slave.

mysql -S /tmp/slave.sock -e "CHANGE MASTER TO MASTER_HOST='localhost', MASTER_PORT=3307;"
mysql -S /tmp/slave.sock -e "START SLAVE;"

# create the database on the master, and create load_table

mysql -S /tmp/master.sock -e "CREATE DATABASE test";
mysql -S /tmp/master.sock test -e "CREATE TABLE load_table (id int not null primary key auto_increment);";

# load 1 million records into the master.
# can be generated with:
# shell> for i in `seq 1 1000000`; do echo $i >> /tmp/1000k.txt; done;

mysql -S /tmp/master.sock test -e "load data infile '/tmp/1000k.txt' into table load_table";

sleep 3

mysql -S /tmp/slave.sock test -e "SELECT count(*) from load_table";
[16 Aug 2007 11:12] Axel Schwenke 
I vote for *not* using read_buffer_size as chunk size for reading data file to LOAD DATA INFILE. This should be a separate variable or maybe automatically chosen dependent on if binlogging is enabled and what max_allowed_packet is set.

On slave side this is even easier, as the binlog contains information how big the chunks are.
[16 Aug 2007 19:28] Morgan Tocker 
Sinisa asked me to check if this is a regression.  It is in some sense, because with the earlier 4.1 behavior of LOAD DATA INFILE I believe this worked fine.

However, I have also verified this in 5.0.18, so it's not a recent regression.
[19 Dec 2007 19:18] Andrei Elkin 
The current issue relates indeed to previous bugs that dealt with max_allowed_packet. It appeared that this bug's read_buffer_size variable actually has constraint not to be greater than the former bug#15937, bug#19402 max_allowed_packet.
[20 Dec 2007 18:06] 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/40293

ChangeSet@1.2560, 2007-12-20 20:05:44+02:00, aelkin@koti.dsl.inet.fi +5 -0
  BUG#30435 loading large LOAD DATA INFILE breaks slave with 
            read_buffer_size set on master
  BUG#33413 show binlog events fails if binlog has event size of close
            to max_allowed_packet
  
  
  The size of Append_block replication event was determined solely by
  read_buffer_size whereas the rest of replication code deals with
  max_allowed_packet.
  When the former parameter was set to larger than the latter there were
  two artifacts: the master could not read events from binlog;
  show master events did not show.
  
  Fixed with 
  - fragmenting the used io-cached buffer into pieces each size of less
    than max_allowed_packet (bug#30435)
  - incrementing show-binlog-events handling thread's max_allowed_packet
    with the max estimated for the replication header size
[20 Dec 2007 21:32] 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/40307

ChangeSet@1.2560, 2007-12-20 23:31:27+02:00, aelkin@koti.dsl.inet.fi +7 -0
  BUG#30435 loading large LOAD DATA INFILE breaks slave with 
            read_buffer_size set on master
  BUG#33413 show binlog events fails if binlog has event size of close
            to max_allowed_packet
  
  
  The size of Append_block replication event was determined solely by
  read_buffer_size whereas the rest of replication code deals with
  max_allowed_packet.
  When the former parameter was set to larger than the latter there were
  two artifacts: the master could not read events from binlog;
  show master events did not show.
  
  Fixed with 
  - fragmenting the used io-cached buffer into pieces each size of less
    than max_allowed_packet (bug#30435)
  - incrementing show-binlog-events handling thread's max_allowed_packet
    with the max estimated for the replication header size
[21 Dec 2007 11: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/40329

ChangeSet@1.2560, 2007-12-21 13:40:01+02:00, aelkin@koti.dsl.inet.fi +7 -0
  BUG#30435 loading large LOAD DATA INFILE breaks slave with 
            read_buffer_size set on master
  BUG#33413 show binlog events fails if binlog has event size of close
            to max_allowed_packet
  
  
  The size of Append_block replication event was determined solely by
  read_buffer_size whereas the rest of replication code deals with
  max_allowed_packet.
  When the former parameter was set to larger than the latter there were
  two artifacts: the master could not read events from binlog;
  show master events did not show.
  
  Fixed with 
  - fragmenting the used io-cached buffer into pieces each size of less
    than max_allowed_packet (bug#30435)
  - incrementing show-binlog-events handling thread's max_allowed_packet
    with the max estimated for the replication header size
[21 Dec 2007 11:42] 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/40330

ChangeSet@1.2560, 2007-12-21 13:42:02+02:00, aelkin@koti.dsl.inet.fi +6 -0
  BUG#30435 loading large LOAD DATA INFILE breaks slave with 
            read_buffer_size set on master
  BUG#33413 show binlog events fails if binlog has event size of close
            to max_allowed_packet
  
  
  The size of Append_block replication event was determined solely by
  read_buffer_size whereas the rest of replication code deals with
  max_allowed_packet.
  When the former parameter was set to larger than the latter there were
  two artifacts: the master could not read events from binlog;
  show master events did not show.
  
  Fixed with 
  - fragmenting the used io-cached buffer into pieces each size of less
    than max_allowed_packet (bug#30435)
  - incrementing show-binlog-events handling thread's max_allowed_packet
    with the max estimated for the replication header size
[21 Dec 2007 20:35] 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/40357

ChangeSet@1.2560, 2007-12-21 22:34:43+02:00, aelkin@koti.dsl.inet.fi +6 -0
  BUG#30435 loading large LOAD DATA INFILE breaks slave with 
            read_buffer_size set on master
  BUG#33413 show binlog events fails if binlog has event size of close
            to max_allowed_packet
  
  
  The size of Append_block replication event was determined solely by
  read_buffer_size whereas the rest of replication code deals with
  max_allowed_packet.
  When the former parameter was set to larger than the latter there were
  two artifacts: the master could not read events from binlog;
  show master events did not show.
  
  Fixed with 
  - fragmenting the used io-cached buffer into pieces each size of less
    than max_allowed_packet (bug#30435)
  - incrementing show-binlog-events handling thread's max_allowed_packet
    with the max estimated for the replication header size
[22 Dec 2007 18:00] 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/40376

ChangeSet@1.2563, 2007-12-22 19:59:47+02:00, aelkin@koti.dsl.inet.fi +2 -0
  bug#30435
  
  refining the test after it failed on pb.
[23 Dec 2007 9:19] 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/40380

ChangeSet@1.2564, 2007-12-23 11:18:46+02:00, aelkin@koti.dsl.inet.fi +4 -0
  bug#30435
  
  changes due to non-determinism in value of read_buffer_size.
[5 Feb 2008 12:45] Bugs System 
Pushed into 5.0.56
[5 Feb 2008 13:04] Bugs System 
Pushed into 5.1.24-rc
[5 Feb 2008 13:08] Bugs System 
Pushed into 6.0.5-alpha
[12 Feb 2008 20:41] Paul DuBois 
Noted in 5.0.56, 5.1.24, 6.0.5 changelogs.

Replication of LOAD DATA INFILE could fail when read_buffer_size was
larger than max_allowed_packet.
[6 Mar 2008 9:05] Jon Stephens 
Also documented for 5.1.23-ndb-6.2.14.
[30 Mar 2008 18:58] Jon Stephens 
Also documented for 5.1.23-ndb-6.3.11.