MySQL Bugs: #1611: More than fifteen text fields cause 'error 139' with InnoDB

Bug #1611	More than fifteen text fields cause 'error 139' with InnoDB
Submitted:	20 Oct 2003 14:57	Modified:	21 Oct 2003 9:12
Reporter:	Christian Warden	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.15	OS:	Linux (Linux/x86)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
If an InnoDB table has more than 15 text/blob fields, attempting to insert returns an error 139.

This was acknowledged by Heikki Tuuri here:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=bg62j0%2425hq%241%40FreeB...

but it's not clear if this is going to be fixed.  This worked as of 4.0.13, so it breaks backwards compatibility.

How to repeat:
mysql> create table many_text(a text, b text, c text, d text, e text, f text, g text, h text, i text, j text, k text, l text, m text, n text, o text, p text) type=innodb;

mysql> insert into many_text set a = repeat('.', 512);

mysql> update many_text set b=a,c=a,d=a,e=a,f=e,g=a,h=a,i=a,j=a,k=a,l=a,m=a,n=a,o=a;

mysql> update many_text set b=a,c=a,d=a,e=a,f=e,g=a,h=a,i=a,j=a,k=a,l=a,m=a,n=a,o=a,p=a;
ERROR 1030: Got error 139 from table handler

Hi!

http://www.innodb.com/ibman.html#InnoDB_restrictions

"
The maximum row length, except BLOB and TEXT columns, is slightly less than half of a database page, that is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns are allowed to be < 4 GB, and the total row length, including also BLOB and TEXT columns, must be < 4 GB. Of a BLOB and TEXT column InnoDB stores the first 512 bytes in the row, and the rest to separate pages. 
"

Regards,

Heikki

perror should be updated to return an appropriate description.

It would also be helpful to note in the documentation as of which version of MySQL this limit was introduced, and perhaps adding a sentence to the section of the documentation referenced along the lines of, "Thus, there is a practical limit of a maximum of 15 TEXT and BLOB fields in a table."  This would hopefully make the relevent section of the documentation easier to find using a search engine when searching for "maximum blob fields innodb", for example.

Objective: obtain wider max row width

Test: Changed to 64k database page size and rebuilt (using 4.0.17 sources):
univ.i: #define UNIV_PAGE_SIZE (8 * 8192)
univ.i: #define UNIV_PAGE_SIZE_SHIFT 16

Results:
- The max table width is in fact widened
- During update queries, however, mysql aborts with the following dump when the larger buffer limit is reached

031222 17:14:17 InnoDB: Page checksum 950908634, prior-to-4.0.14-form checksum 107936872
InnoDB: stored checksum 950908634, prior-to-4.0.14-form stored checksum 107936872
InnoDB: Page lsn 0 96051070, low 4 bytes of lsn at page end 96051070
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 0 15
031222 17:14:17 InnoDB: Apparent corruption of an index page n:o 273 in space 0
InnoDB: to be written to data file. We intentionally crash server
InnoDB: to prevent corrupt data from ending up in data
InnoDB: files.
031222 17:14:17 InnoDB: Assertion failure in thread 16384 in file buf0flu.c line 247
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to mysql@lists.mysql.com
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=1044480
max_used_connections=0
max_connections=100
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 466543 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x83b4650
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Bogus stack limit or frame pointer, fp=0xbfffe778, stack_bottom=0x705f656c, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x69665f61 is invalid pointer
thd->thread_id=138104520

I'm having that error also in a table with "only" 13 text field... is there a way to correct this issue?

thanks!

Matteo,

you might compile InnoDB with a 64 kB pages size. Though I recommend changing your table schema. Having 13 long columns in one row is not very common...

Regards,

Heikki

I just stumbled upon this bug when searching for a rarely issued error message.

Current versions of InnoDB can check for the "too large record" condition already at CREATE TABLE time, and also during the execution of DML statements. It looks like the checks were missing in 4.0.15, and the page was corrupted, but the corruption was caught right before it was going to be written to a file.

For what it is worth, the innodb_file_format=barracuda in the InnoDB Plugin for MySQL 5.1 (ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED) allow long string fields to be stored entirely off-page. The limit for 16k page size should then become over 350 columns. For each off-page column, we would only store a 20-byte pointer in the record.