MySQL Bugs: #10206: InnoDB: Transaction requiring Max_BinLog_Cache

Bug #10206	InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback.
Submitted:	27 Apr 2005 15:16	Modified:	8 Aug 2009 0:30
Reporter:	Disha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.4, 5.0, 5.1 bzr	OS:	Any (Any)
Assigned to:	Satya B	CPU Architecture:	Any

Description:
Transaction that requires more than 4 GB of cache never commit. It always rolls-back because it runs out of cache.

How to repeat:
1) Create a text file to populate 5 million records to a table which is will create a table with size more than 4 GB. Following is a table used.

create table tb4 (
f176 numeric (0) unsigned not null DEFAULT 9, 
f177 numeric (64) unsigned not null DEFAULT 9, 
f178 numeric (0) zerofill not null DEFAULT 9, 
f179 numeric (64) zerofill not null DEFAULT 9, 
f180 numeric (0) unsigned zerofill not null DEFAULT 9, 
f181 numeric (64) unsigned zerofill not null DEFAULT 9, 
f182 numeric (0,0) not null DEFAULT 9, 
f183 numeric (63,63) not null DEFAULT 9, 
f184 numeric (0,0) unsigned not null DEFAULT 9, 
f185 numeric (63,63) unsigned not null DEFAULT 9, 
f186 numeric (0,0) zerofill not null DEFAULT 9, 
f187 numeric (63,63) zerofill not null DEFAULT 9, 
f188 numeric (0,0) unsigned zerofill not null DEFAULT 9, 
f189 numeric (63,63) unsigned zerofill not null DEFAULT 9, 
f190 real not null DEFAULT 88.8, 
f191 real unsigned not null DEFAULT 88.8, 
f192 real zerofill not null DEFAULT 88.8, 
f193 real unsigned zerofill not null DEFAULT 88.8, 
f194 double not null DEFAULT 55.5, 
f195 double unsigned not null DEFAULT 55.5, 
f196 double zerofill not null DEFAULT 55.5, 
f197 double unsigned zerofill not null DEFAULT 55.5, 
f198 float, 
f199 float unsigned, 
f200 float zerofill, 
f201 float unsigned zerofill, 
f202 float(0), 
f203 float(23), 
f204 float(0) unsigned, 
f205 float(23) unsigned, 
f206 float(0) zerofill, 
f207 float(23) zerofill, 
f208 float(0) unsigned zerofill, 
f209 float(23) unsigned zerofill, 
f210 float(24), 
f211 float(53), 
f212 float(24) unsigned, 
f213 float(53) unsigned, 
f214 float(24) zerofill, 
f215 float(53) zerofill, 
f216 float(24) unsigned zerofill, 
f217 float(53) unsigned zerofill, 
f218 date, 
f219 time, 
f220 datetime, 
f221 timestamp, 
f222 year, 
f223 year(3), 
f224 year(4), 
f225 enum("1enum","2enum"), 
f226 set("1set","2set"),
f235 char(0) unicode,
f236 char(90),
f237 char(255) ascii,
f238 varchar(0),
f239 varchar(20000) binary,
f240 varchar(2000) unicode,
f241 char(100) unicode
) engine = Innodb;

2) Execute transaction on this table to upload 5 million records.

Load data local INFILE 'c:\\data.txt' INTO table tb4;

ACTUAL RESULT
This transaction rolls back and never completes.

EXPECTED RESULT
This should not roll back when appending to InnoDB database with data.

Updated synopsis

Disha,

are you using a 32-bit computer?

Looks like the maximum binlog transaction cache size is 4 GB in a 32-bit computer. You should split your imports into smaller pieces. A rollback of 4 GB of data can take days if something goes wrong.

Regards,

Heikki

mysqld.cc:
" 
{"max_binlog_cache_size", OPT_MAX_BINLOG_CACHE_SIZE,
   "Can be used to restrict the total size used to cache a multi-transaction qu\
ery.",
   (gptr*) &max_binlog_cache_size, (gptr*) &max_binlog_cache_size, 0,
   GET_ULONG, REQUIRED_ARG, ~0L, IO_SIZE, ~0L, 0, IO_SIZE, 0},
"

We have tried this on both 32 bit as well as 64 bit machines.

For transactions like LOAD FILE, this will not work.

There should be a way to set or disable 'max_binlog_cache_size', so that user can complete certain transactions that cannot be split.

It's a known limitation of the current implementation.
I'm afraid it's too late to change it in 5.0.
It can only be done in 5.1 or later

More on 64bit in Bug#31066

Our manual still says at http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#option_mysqld_max_binl...:

# max_binlog_cache_size
...
Default 	4294967295
Range 	4096-4294967295

If a multiple-statement transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error. The minimum value is 4096, the maximum and default values are 4GB.

So problem still exists in current version.

Changing category since, AFAICT, this is not a replication bug.

further changing the category as it is not verified that it is
InnoDB specific bug. Have you tried it with Falcon?

Michael,

this is known server limitation related to max_binlog_cache_size variable and have nothing to do with particular storage engine.

This effectively limits transactions (with all storage engines) to 4G of changes on the following platforms:

All 32-bit systems
64-bit Windows 

With row-based binary logging this will be hit more frequently.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74188

2891 Satya B	2009-05-15
      Fix for BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB
                          always rollsback.
      
      The global variable max_binlog_cache_size cannot be set more than 4GB on
      32 bit systems, limiting transactions of all storage engines to 4G of changes.
      
      The problem is max_binlog_cache_size is declared as ulong which is 4 bytes
      on 32 bit and 8 bytes on 64 bit machines.
      
      Fixed by using ulonglong for max_binlog_cache_size which is 8bytes on 32 
      and 64 bit machines.The range for max_binlog_cache_size on 32 bit and 64 bit
      systems is 4096-18446744073709547520 bytes.
      modified:
        mysql-test/r/variables.result
        mysql-test/t/variables.test
        sql/mysql_priv.h
        sql/mysqld.cc
        sql/set_var.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75459

2922 Satya B	2009-06-02
      Followup Fix for BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB 
                                   always rollsback.
      
      There is failure on pushbuild machines which are using old compilers complaining about
      ULLONG_MAX declaration. Changing this to ULONGLONG_MAX to solve the problem.
      modified:
        sql/mysqld.cc

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:satya.bn@sun.com-20090602123747-oyil0e7fkwgfez2f) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:satya.bn@sun.com-20090602125800-u2s8q8z3597lpyc4) (merge vers: 6.0.12-alpha) (pib:11)

Noted in 5.1.36, 5.4.4 changelogs.

The maximum value for max_binlog_cache_size has been increased from
2^32 − 1 to 2^64 − 1, which enables transactions 4GB and larger to be
performed when binary logging is enabled.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

This fix now has been pushed into 5.4.2.