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

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  =======

create_random_data.sql file

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

Thank you for the bug report. Duplicate of https://bugs.mysql.com/bug.php?id=72457.

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.