MySQL Bugs: #30295: Error 139 from storage engine

Bug #30295	Error 139 from storage engine
Submitted:	8 Aug 2007 8:11	Modified:	6 Sep 2007 20:15
Reporter:	Mohammad Ashraful Anam	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any (Linux, Windows)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any
Tags:	error 139, innodb

Description:

Here is my data structure

CREATE TABLE `bioinfo` (
`pin` varchar(17) NOT NULL default '',
`photo` longblob,
`photo_original` longblob,
`photostatus` tinyint(1) NOT NULL default '0',
`signature` longblob,
`signaturestatus` tinyint(1) NOT NULL default '0',
`lfingert` longblob,
`lfingerstatust` tinyint(1) NOT NULL default '0',
`lfingeri` longblob,
`lfingerstatusi` tinyint(1) NOT NULL default '0',
`rfingeri` longblob,
`rfingerstatusi` tinyint(1) NOT NULL default '0',
`rfingert` longblob,
`rfingerstatust` tinyint(1) NOT NULL default '0',
`lfingertemplatet` blob,
`lfingertemplatei` blob,
`rfingertemplatet` blob,
`rfingertemplatei` blob,
`lfingerbarcodeitext` longtext NOT NULL,
PRIMARY KEY (`pin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have a row with pin='19442695047100023' and about 22K each in the 7 longblob fields and around 450bytes in 4 blob fields.

As you can see almost all fields are blobs and there should not be any restriction on any size limit of 8000 for INNODB. But when I try the following query it give 139 error:

UPDATE bioinfo SET lfingerbarcodeitext='TkZSI+QCFvQBJgL0AfQBAAD/YwABAAb/dAZxYqATBGD4dUAPCF8GeeAWDlh6mQAXDVnknWAFIFhazSAIH1jfIoEFDFhaPSEJFGROSeEJGGPoVkELcWHQ5eEDE16oBkItHVvfJsIFClguLqItEV/ydYIMCVZ0jWINCFMslkIqGFssquInHmIQviIgC1fs2SIPBVGq3QIsFF8h8SIsF1+u/eIpFWWpBoMvI2ZrCiMPB1D6RaMUDF8eRkMqCWDtSmMLBU93ykMUBlrwzgMGElugzsMnA1Ny/YMHCl8t/mMpEFkcCqQiB1TxDsQJB2MwNiQvC2mxRmQtDW+eZuQlBli0gkQxFV7yjuQQClFQzoQIBln/0sQRDVy91mQwHmpm2qQOIVZl3sQMQlvh4qQKJF848UQtEGdI9gQDDFWn9gQqB25QCkUKBWevEWUsCmswPQUiEGm+SaUwDm8uVgUkDWihaQUgC2klekUeBl+CjqUWElzgnoUNDWOyqUUjDWspsmUmEWaztqUvF2tZvYUOD2TRyaUFCW4nyqUgB2WsycUiE2ms4WUuCWcv5iUqB3E48YUlEGfT9sUGCGnoBkYQGmK+FYYkDGJSImYGCWmwIaYhBGeqLsYoBmenMQYfBmdgNmYSEVUoOkYgAmlXagYDCGklauYuB1nUbeYGCmircmYnA1qxeoYqCF3TfQYOBWPWlmYEBGpWnoYMBGOlvgYmCWCp6uYgA2Qn6gYnCGasAUcjBWUoBockA2TbGscJDGBBGucVImTZRecPBmCoVmcpAmBdWucKFF9ZZscUG2AkeQcYJFikfmcnBWOjgeccEFSnlscfCl3TnWcNEF/anQcRCF0asgcdDFVftgcPClvdzscEAmTnzgcWH1zg0mcHBmLb2ocBCWZf5WcJBmXpAugRD2CfBqgkCVSYDogcC1onGoghFFU2TqgfDmJeXkgNDVvjZggGC2QAAAAABAEHAKgnF////wAEAAA=' WHERE pin='19442695047100023'

MySQL said:
#1030 - Got error 139 from storage engine

But if you notice my table structure, you will see that I have only 1 varchar and 1 text column and their total combined size is less than 2000 bytes which is well below the 8000 byte limit. And if I read and understood correctly this limitation is not applicable for Blob fields which contain most of my data.

How to repeat:
Create table:

CREATE TABLE `bioinfo` (
`pin` varchar(17) NOT NULL default '',
`photo` longblob,
`photo_original` longblob,
`photostatus` tinyint(1) NOT NULL default '0',
`signature` longblob,
`signaturestatus` tinyint(1) NOT NULL default '0',
`lfingert` longblob,
`lfingerstatust` tinyint(1) NOT NULL default '0',
`lfingeri` longblob,
`lfingerstatusi` tinyint(1) NOT NULL default '0',
`rfingeri` longblob,
`rfingerstatusi` tinyint(1) NOT NULL default '0',
`rfingert` longblob,
`rfingerstatust` tinyint(1) NOT NULL default '0',
`lfingertemplatet` blob,
`lfingertemplatei` blob,
`rfingertemplatet` blob,
`rfingertemplatei` blob,
`lfingerbarcodeitext` longtext NOT NULL,
PRIMARY KEY (`pin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert one row with the following query:

INSERT INTO bioinfo SELECT '123',
REPEAT('A', 3000), REPEAT('A', 3000), 1,
REPEAT('A', 3000), 1, 
REPEAT('A', 3000),1,
REPEAT('A', 3000),1,
REPEAT('A', 3000),1,
REPEAT('A', 3000),1,
REPEAT('A', 450), REPEAT('A', 450), REPEAT('A', 450), REPEAT('A', 450),''

Then update with query:

UPDATE bioinfo SET lfingerbarcodeitext='TkZSI+QCFvQBJgL0AfQBAAD/YwABAAb/dAZxYqATBGD4dUAPCF8GeeAWDlh6mQAXDVnknWAFIFhazSAIH1jfIoEFDFhaPSEJFGROSeEJGGPoVkELcWHQ5eEDE16oBkItHVvfJsIFClguLqItEV/ydYIMCVZ0jWINCFMslkIqGFssquInHmIQviIgC1fs2SIPBVGq3QIsFF8h8SIsF1+u/eIpFWWpBoMvI2ZrCiMPB1D6RaMUDF8eRkMqCWDtSmMLBU93ykMUBlrwzgMGElugzsMnA1Ny/YMHCl8t/mMpEFkcCqQiB1TxDsQJB2MwNiQvC2mxRmQtDW+eZuQlBli0gkQxFV7yjuQQClFQzoQIBln/0sQRDVy91mQwHmpm2qQOIVZl3sQMQlvh4qQKJF848UQtEGdI9gQDDFWn9gQqB25QCkUKBWevEWUsCmswPQUiEGm+SaUwDm8uVgUkDWihaQUgC2klekUeBl+CjqUWElzgnoUNDWOyqUUjDWspsmUmEWaztqUvF2tZvYUOD2TRyaUFCW4nyqUgB2WsycUiE2ms4WUuCWcv5iUqB3E48YUlEGfT9sUGCGnoBkYQGmK+FYYkDGJSImYGCWmwIaYhBGeqLsYoBmenMQYfBmdgNmYSEVUoOkYgAmlXagYDCGklauYuB1nUbeYGCmircmYnA1qxeoYqCF3TfQYOBWPWlmYEBGpWnoYMBGOlvgYmCWCp6uYgA2Qn6gYnCGasAUcjBWUoBockA2TbGscJDGBBGucVImTZRecPBmCoVmcpAmBdWucKFF9ZZscUG2AkeQcYJFikfmcnBWOjgeccEFSnlscfCl3TnWcNEF/anQcRCF0asgcdDFVftgcPClvdzscEAmTnzgcWH1zg0mcHBmLb2ocBCWZf5WcJBmXpAugRD2CfBqgkCVSYDogcC1onGoghFFU2TqgfDmJeXkgNDVvjZggGC2QAAAAABAEHAKgnF////wAEAAA=' WHERE pin='123'

Thank you for the report.

Seems to be duplicate of bug #25945

Actually it is not a duplicate. They were using 10 text fields which was crossing the 8000 byte InnoDB limit. My table has only 1 varchar(17) and 1 text field and those content are well within the 8000 byte InnoDB limit. According to the manual, the blobs (where most of my data are) are saved in another page and are not part of that 8000 byte limit

Thank you for the feedback.

Marked as verified.

InnoDB stores a prefix of also BLOBs to the row itself. Therefore, this is not a bug.

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

"length, including also BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 768 bytes of a VARCHAR, BLOB, or TEXT column in the row, and the rest into separate pages."

In an upcoming InnoDB version (probably 5.2), only indexed BLOB etc. columns will have their prefix stored locally. Thus, error number 139 will no be as common as it is now.

any solution to this problem !!

any idea to solve this problem now !!

Isaac sam,

I shifted to MyISAM to avoid this issue although I would have preferred InnoDB engine. I was getting too much problem and performance issue in InnoDB. In any case this is not a bug. What the manual fails to explain properly (although it does mention it but somehow it is not easy to comprehend) and was cleared to me by Heikki Tuuri is that when it says in the manual that InnoDB stores the
first 768 bytes of a VARCHAR, BLOB, or TEXT column in the row it means that if you have 5 blob fields (and they are filled), those fields are eating up 768X5=3840 bytes (the rest bytes are save in another page) of the row leaving you 8000-3840=4160 bytes maximum for the other fields which becomes filled easily and you get the storage error. So either, you divided the table so that it never reaches 8000 byte row limit even with the 768 bytes in the blob fields or you have to switch to MyISAM. In my case I did not, I divided the tables and also shifted to MyISAM as the performance was wayyyyy better. I hope this helps.

Thanks alot Mohammad 
but MyISAM did not have referential integrity right ?

also is it possible somehow to change row limit in InnoDB ?

Thanks again

The way to ovecome the 8000 byte row length limitation is to split the table in several tables. You could split the left and right finger information to separate tables.