InnoDB max row size limit & error 139

[whitestar73]

It seems I hit a row limitation of sorts while testing a table setup with a web page.

 

I have an innodb table setup so that each data row corresponds with a userID. The UID being the PRI KEY for the table. Following that are 38 rows of TEXT type that I wanted to hold article length size amounts of text for each column. The idea is for the user to write essays on 38 topics, so this table layout seemed best. The site will have the potential for -millions- of users, so I didn't want to store text entries as 1 text entry per row at a time. Even though I know the max number row limit is 4.29 billion rows, I wanted to make a table design that ensured I didn't hit that limit sooner than later, and 1 row per user seemed optimal since the number of topics will certainly be under the max column limit of 3300 +/-

 

When running tests, I was copy/pasting essentially a wikipedia page of text to test how all 38 fields look on output. At that time, I got the error 139 from the database (oh yes, as a side note, the HTML form contains textareas for every topic essay, so the "edit" page loads all 38 essays for editing, then resubmits all 38 - I have a reason for that, but that's neither here nor there). I got the error 139 at first when I filled all textareas and tried to submit, but then I tried filling a couple at a time, and that worked. But, ultimately, I got to a point where I can't enter any more without the error 139. Now, the HTML form actually works fine with the database as long as I have only a little bit of text in each field.

 

When I researched error 139, I came across some row limit of 8000 characters which I think is utterly silly. I should be able to create a table with the max number of columns and fill them with whatever I feel like... at least in theory, but 8000 characters???!!! 8000 / 38 columns gives me a little over 200 characters for an essay which obviously won't cut it 

 

My research has yielded suggestions ranging from changing the table storage engine to MyISAM to changing the column data type to something like MEDIUMTEXT or MEDIUMBLOB to get around this. TEXT by itself is capable of holding 64k right? So why would there be such a ridiculous row limit of 8000 characters? I was reading that the database will store a certain amount of data in the row itself, then store anything over that somewhere else. Is that right?

 

Does anyone know what REALLY would work for the column data type in this instance? If it's as easy as switching to MEDIUMTEXT, then that's plenty easy enough. But, do I need to be concerned about anything else? I really didn't think I'd be running into this problem.

 

Thanks much for any help!

[fenway]

Read this and this "bug".  Nothing magical here.

[whitestar73]

Thanks fenway,

 

It looks like I've got some redesigning to do. I'm pretty new to all of this, but I think I'll get it worked out. I found another site that did a "when to use" innodb vs myisam engines, and it looks like I've got to stick with innodb.

 

This was the site that ran the comparison:

http://www.anchor.com.au/hosting/dedicated/MySqlStorageEngines#head-a3f9b0aaaf45665edfeeda164c22f56eedb85f9a

And, from what they're saying is that innodb should be used with sites that have high volume of users.

 

Of course, there also PostgreSQL. I've tried working with that a little bit, but found that MySQL is easier to work with for my skill level at the moment.

 

I just wasn't expecting to run into this kind of a problem in my design. I mean, I'm just dealing with text for Pete's sake LOL! And I didn't think the database would have ANY issue with it at all. But... that, as they say, is that... back to the drawing board.

[fenway]

Well, you rejected the original solution, which was simply to have one row per TEXT record.... mysql will have no issue handling this.