MySQL Bugs: #22496: Create InnoDB settings to prevent blob fields from exceeding 16K page size

Bug #22496	Create InnoDB settings to prevent blob fields from exceeding 16K page size
Submitted:	19 Sep 2006 21:00	Modified:	9 May 2007 10:11
Reporter:	Matthew Mastrangelo	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.24	OS:	Any (All)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	error 139, innodb, page size

Description:
Currently, there is a known InnoDB limitation that prevents storage of more than 10 blobs each containing at least 768 bytes of data in a single row. If this is attempted, the following error occurs:

Error No. 1030, Got error 139 from storage engine

The reason for this, according to the documentation, is because the database page size is 16K, and each row must be less than half of this - around 8000 bytes. The 10 blob maximum exists because the first 768 bytes of each blob is stored in the primary database page: 768 * 11 > 8000.

How to repeat:
Create an InnoDB table with 11 blob fields. Attempt to store a value greater than 768 bytes in each.

Suggested fix:
Create two new properties in the my.ini file:

1) innodb_page_size - this would allow the page size to be modified without having to change constants in the source code and recompile.

2) innodb_max_blob_prefix_length - this would allow the 768 byte prefix size to be adjusted. A caveat to changing this setting would be that blob indexing may no longer function. In many cases, this is an acceptable side effect.

Either of the above settings alone would help resolve this issue.

Matthew,

Marko will reduce the locally stored BLOB/TEXT length in MySQL-5.2 so that a non-indexed BLOB/TEXT column does not need local storage at all. That allow one to have tens of BLOB columns in a row if they are not indexed. I hope that is a good enough fix for this feature request.

Regards,

Heikki

Heikki,

Thanks, that solution sounds good. Until then, is there a workaround aside from restructuring my data model, or recompiling the source code with a larger page size? Right now, this issue is forcing me to use MyISAM for tables with > 10 blobs. Unfortunately, this sacrifices ACID functionality, so I'm going to have to figure something else out before 5.2 is released.

Matt

Matthew,

Someone reported a bug in 4.1 or 5.0 with 64k page size. We have not tested InnoDB with other than the default 16k page size since that time. So, be careful when compiling InnoDB with a bigger page size (at least run some tests with UNIV_DEBUG switched on).

You could create separate tables for the BLOB columns (one table for each 10 BLOBs). You should use a short primary key in the main table, e.g., an AUTOINCREMENT column.

I have now implemented completely external storage of BLOB columns in an internal InnoDB repository. It is yet unclear which source tree it will be merged to, MySQL 5.1 or 5.2.

This bug will be fixed together with Bug #27646 (compressed InnoDB tablespaces).

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)