Error 139 = Too big row (>= 16 M)

Anon

We have written our own email server software and during our testing of large messages we have run into a very odd error that we are finding very very little information on.

We get the error 139 from the Table Handler within our C code. What we do not understand is how can you have a field that can hold 2³² yet still have a row size of 16 MB. What is the magic field that we need to update in order to increase this limitation. At first we thought that it was just a problem with how we were inserting into the database so instead of inserting the message all at once, we break down large messages (over 1 MB in size) into smaller chunks and do an update so we do not run into the 16 MB net buffer limitation. But the error we get is related to the row size which unfortunately we have been unable to find much documentation on.

Thoughts....Anyone.....

[deleted]

"But the error we get is related to the row size"

what makes you think that, if the error you get relates to the net_buffer?

A few thoughts:
1: why on earth are you storing messages in a database (and why in the worst of them all: mysql?)
2: did you encrease the max-allowed-packet and net_buffer_length on the mysql server?

Anon

The reason why we are currently using a database for storing messages is quite simple. It gives us a variety of options for moving the data around to different servers, backups are quite simple and with the range of products that we have developed, it makes it for a nice easy to access data point. As for why we use MySQL. It is speedy for what we need and it is cheap as we grow the company. We have built in a database abstraction layer so that if/when we change to something else, it will be a relativly easy process.

The reason why we know it is not a net buffer error is the following. We are chunking the data so that our maximum query that we are inserting is under 2 MB. The Net buffer starts at 16k and grows dynamically to a size of 16MB which is the maximum query size that you can have. SO, we are using the concat function to get past that issue which works fine up to the point where you are trying to add more than 16 MB of data into the row. The chunking of the data makes the insert of large messages take a little longer since there are multiple updates(concats) but the majority of the queries (95%+) are under 1 MB which is our chunk point.

Max_allow_packet is set to 64MB, Net_buffer_length has been tweaked but due to the internal api with MySQL 3.23, you cannot get past the 16 MB row size.

We have started playing around with INNO_DB and 3.23.49. It appears taht we will be able to get past the 16 MB row limit but there is still the problem where you have to chunk the data to get it back out as well. We are also testing the 4.0.1 code which gets around this problem all together....according to the MySQL documentation.

[deleted]

About your reasons for using a database, let's just say I disagree. I think you should do some serious investigations into what exactly the pro's and con's are of storing the message bodies in a database.

There's no easy moving around of data, you always have to run queries, format the data, send the data, and run another query.
Backups are not easy, the only way to backup a database is to dump it's content, which is basically SELECT FROM , which generates one huge file that can only be restored in full, it's not possible to restore one email seperately (which is not what you want anyway, but your customers will want that)

"it makes it for a nice easy to access data point. .... it's speedy"

it's nice and speedy now, But mysql stores all it's blob data in the table itself, which means that if you have 1.5GB of email data, you'll have a 1,5GB datafile. MySQL does not like large datafiles and your speedy mysql will quickly slow to a crawl.

If you don't need to manipulate the email's content with queries, then quickly forget all about storing the email in the database itself. Instead, save it as a file on disk and use the database only to hold the 'header' data so you can still use SQL to find messages, but use the filesystem to get the content.

BTW, from what I've read about this 'problem' so far it seems that MySQL 4.0.1 has the same limit problem but at 64MB.

Anon

Thanks for the feedback. Here is another reason.....

The pop/web/wap interface/db/incoming mail/outgoing mail/you name your access point, are not on the same box. So, to have a big clustered environment, you would have to use NFS to get to the mail....which, is not pretty. But I appreacitate your input.

We are currently playing around with 4.0.1 and seeing if it can do what we need. 64 MB is not pefect but it is better.