Bug #86991 binlog corruption when tmpdir gets full
Submitted: 8 Jul 2017 23:10 Modified: 9 Jul 2017 3:03
Reporter: Marcelo Altmann (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.36, 5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[8 Jul 2017 23:10] Marcelo Altmann
Description:
MySQL writes a corrupted event on binary log if a statement present on a transaction requires a tmp bin logfile and the tmp partition gets full.
Transaction commits on master but fails on slave:

                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event '' at 4, the last event read from './mysqld-bin.000001' at 12826202, the last byte read from './mysqld-bin.000001' at 12826221.'
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table marcelo.users_applications; Can't find record in 'users_applications', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000001, end_log_pos 12826202

How to repeat:
Create a machine with a separate disk/fs to hold mysql tmp dir. Mount it and create a file big enough to leave the partition with only ~2M free:

Filesystem    Size  Used Avail Use% Mounted on
/dev/sdb1        18M   14M  2.3M  86% /mysqltmp

===== My.cnf =======
[mysqld]
log_bin
tmpdir  = /mysqltmp
binlog_cache_size = 4M
binlog_row_image                        = minimal
binlog_format                           = ROW
innodb_tmpdir  = /mysqltmp
server_id=1
===== My.cnf =======

On MySQL, create a new database and source the file contain the procedures to populate random data (attached on this bug):

===== Commands  =======
CREATE DATABASE marcelo;
USE marcelo;
SOURCE create_random_data.sql
CREATE TABLE `movies_catalog` (
  `movie_id` int(11) unsigned NOT NULL DEFAULT '0',
  `catalog_id` int(11) unsigned NOT NULL DEFAULT '0',
  `movie_is_new_release` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`movie_id`,`catalog_id`),
  KEY `fk_movie_id` (`movie_id`),
  KEY `fk_catalog_id` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

call populate('marcelo','movies_catalog',50000,'N');

# Problem
begin;
update movies_catalog set catalog_id=catalog_id+1;
call populate('marcelo','movies_catalog',10000,'N');
update movies_catalog set catalog_id=catalog_id+10;
update movies_catalog set catalog_id=catalog_id+10;
update movies_catalog set catalog_id=catalog_id+10;
update movies_catalog set catalog_id=catalog_id+10 limit 1;
update movies_catalog set catalog_id=catalog_id+10 limit 10;
update movies_catalog set catalog_id=catalog_id+10 limit 100;
commit;
# In order to reproduce it multiple times, delete 10K rows
delete from movies_catalog limit 10000;
#repeat the transaction

===== Commands  =======
[8 Jul 2017 23:10] Marcelo Altmann
create_random_data.sql file

Attachment: create_random_data.sql (application/octet-stream, text), 5.26 KiB.

[9 Jul 2017 3:03] Miguel Solorzano
Thank you for the bug report. Duplicate of https://bugs.mysql.com/bug.php?id=72457.