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