MySQL Bugs: #40672: 'Writing one row to the row-based binary log failed' and max_binlog_cache

Bug #40672	'Writing one row to the row-based binary log failed' and max_binlog_cache_size
Submitted:	12 Nov 2008 18:17	Modified:	30 May 2009 12:36
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1.29	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
when max_binlog_cache_size is too small, changes to tables fail with this uninformative error:

ERROR 1534 (HY000) at line 8: Writing one row to the row-based binary log failed

How to repeat:
start server with --log-bin
save attached data.txt and run the following sql:

flush logs;
drop table if exists b3;
create table b3(a text,b int)engine=myisam;
set global max_binlog_cache_size = 4096;
set binlog_format=row;
load data infile '/tmp/data.txt' into table b3;
update b3 set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'  limit 100;
update b3 set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasaaaaasaasaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'  limit 100;

data.txt

Attachment: data.txt (text/plain), 2.50 KiB.

filing this as a bug because it was hard to track down the cause.

Verified almost as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.29-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)

mysql> drop table if exists b3;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table b3(a text,b int)engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> set global max_binlog_cache_size = 4096;
Query OK, 0 rows affected (0.05 sec)

mysql> set binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile 'c:/tmp/data.txt' into table b3 fields terminated by ','
;
Query OK, 108 rows affected (0.05 sec)
Records: 108  Deleted: 0  Skipped: 0  Warnings: 0

mysql> update b3 set
    -> a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaa
    '> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaa'  limit
    -> 100;
Query OK, 100 rows affected (0.05 sec)
Rows matched: 100  Changed: 100  Warnings: 0

mysql> update b3 set
    -> a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaasaaaaa
saasaaaaaaaaaaaaa
    '> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaa'  limit
    -> 100;
ERROR 1534 (HY000): Writing one row to the row-based binary log failed

This is a duplicate of BUG#43929.

This error is also thrown on MySQL 5.6 when there's and issue with tmpdir on the OS level. In such a case you will get this when issuing "ls -la" over tmpdir:

ls: reading directory .: Input/output error
total 0

In my case tmpdir is a standalone disk, so it actually can be related to wrong cables or dying disk.