I just wanted to throw this oddity out here, should you encounter this yourself. I'm talking about the rather cryptic MySQL error 139.

First, my situation. I'm using MySQL to power JungleDragon. As you know, one can chose the storage engine per table but usually I go for InnoDB, which allows for foreign keys and some other trickery. In this case, I was creating a new table called "specie". It's a bit of an odd table. It has a few regular fields, but it also needs to store very large text blocks that roughly vary in size between 3 and 5K. In characters that is. Since I'm using UTF-8, the byte size storage is likely double, so between 6 and 10K. Each text block is a seperate column and there's 32 of them. As column data type I use MediumText, which allows up to 64KB of data to be stored.

You could question this table design, but for now, let's put that aside. Given that I've selected data types that allow for plenty of room, I did not expect problems when inserting records into this table. Yet I've hit error 139.

Upon further investigation, I've found that this error occurs when the maximum row size limit is exceeded. This limit is 8K per row. That sounds a bit silly given that we can chose column data types that far exceed that number. As you may know, MySQL doesn't store TEXT or BLOB data inside the rows, it stores them seperately.

Well, not really. MySQL stores the first 768 bytes of each TEXT or BLOB column inside the row, even though the actual TEXT far exceeds that size. So in my case, 32 TEXT columns x 768 bytes = 24576 bytes, which is far beyond the 8000 bytes limit. The solution, other than partitioning the table? Switching the storage engine of the table to MyISAM, which allows for a limit of 64KB.

So, the solution is simple, if you can live with the limitations of MyISAM (I can in this case). The reason I am writing this is to point out that several sources on the web are wrong in stating that the 8000 limit is a MySQL limit. In reality, it's a storage engine limit. 

RATE THIS CONTENT (OPTIONAL)

	Was this document useful to you?
	Rating:	Awesome  Good  Average  Poor  Useless

CREATE A NEW COMMENT

	Name:
	Email:
	Website:
	Content:	HTML is not allowed. Hyperlinks will automatically be converted.