MySQL Bugs: #15537: Binlog file bigger than max_binlog

Bug #15537	Binlog file bigger than max_binlog_size limit.
Submitted:	6 Dec 2005 18:58	Modified:	13 Dec 2005 17:16
Reporter:	Kayra Otaner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Linux (RHEL4)
Assigned to:		CPU Architecture:	Any

Description:
I tried to upload 5G sql dump into InnoDB table space with autocommit=0 set. After about 1 hour I started getting this error :

# Error: 1197 SQLSTATE: HY000 (ER_TRANS_CACHE_FULL)
Message: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

Which is fine. I understand that max_binlog_cache_size is set to 4G and I tried to insert more than that. I observed that this transaction is also logged into binary logs and although binary log size is set to 1G, one binary log is over 4G. When I list files under /var/lib/mysql/ here is what I see (only binlogs are shown below) :

-rw-rw----   1 mysql mysql 4.3G Dec  6 17:51 yellowfin-bin.000001
-rw-rw----   1 mysql mysql 1.1G Dec  6 18:16 yellowfin-bin.000002
-rw-rw----   1 mysql mysql 1.1G Dec  6 18:26 yellowfin-bin.000003
-rw-rw----   1 mysql mysql 1.1G Dec  6 18:41 yellowfin-bin.000004

As of now, I don't have slave setup, so not much worried about binlogs. But binlog file size being bigger than its limit seemed weird. Here are my binlog settings :

mysql> show variables like '%binlog%';
+--------------------------------+------------+
| Variable_name                  | Value      |
+--------------------------------+------------+
| binlog_cache_size              | 10485760   |
| innodb_locks_unsafe_for_binlog | OFF        |
| max_binlog_cache_size          | 4294967295 |
| max_binlog_size                | 1073741824 |
| sync_binlog                    | 0          |
+--------------------------------+------------+
5 rows in set (0.01 sec)

How to repeat:
Try importing millions of rows into a transactional engine with autocommit=0 so that max_binlog_cache fills. Then check binlog (first enable binlogging) see if any of the recent binlogs is bigger then max_binlog_size value.

Thank you for a problem report. Please, send the error log for the last several days (just to be sure).

Hello Kayra,
As said in
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
" A binary log may become larger than max_binlog_size if you are using large transactions: A transaction is written to the binary log in one piece, never split between binary logs."
Which is what you are experiencing on your machine.

I'm sorry, it is not a bug. I've thought that 2 things are wrong :
- Binlog size being bigger then its limits : Now I see that it is already documented.
- Although query failed due to binlog cache limit is reached it is still logged in binlog :  I've noticed that there was another instance of a batch upload process running and it was logged in, not the one failed. 

I apologize for jumping to this bug report page quickly. You can close this ticket (or bug request).

Thanks

Kayra Otaner