Database limitation bug#4946

Hello CB,

I have a similar if not the same issue as this post 8 months back: www.joomlapolis.com/forum/153-professional-member-support/227677-4946-error-1118-error-upgrading-from-cb-19-to-cb-204?start=6

My database's are set to InnoDB. I sent my server people the following message asking for assistance:

You could change to Barracuda then set the row format to compressed. This further reduces the size of a row and effectively increases its limits. This error can also be caused by innodb_log_file_size being too small, but it's not always the cause. The page size limit can be increased with innodb_page_size, but that can be dangerous and should be done with caution. (As per Kyle's instructions on the post link above). They still haven't replied since Saturday.

I only had 2 large paragraphs in some text editor fields. When I trimmed them down fields in profile edit would save again. I was getting that error message like so: #1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT_DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

Initially I thought it was a JCE editor issue, then I came across the post linked and relised it's database limitation.

All I can do is wait for the bug fix? forge.joomlapolis.com/issues/4946

By the way I do have word limits on these fields and there wasn't a huge amount of text just 2 standard lorem ipsum paragraphs.

Glad I realised this to be honest and praying for the bug fix to come in 2.0.11. Should I leave everything as is and wait for bug fix or try and sort with server aswell per Kyles instructions above?

Kind regards

There is no way for us to really fix this. It's a database limitation. It's not about the number of paragraphs but about the character count. In my tests it took a very large block of text to trigger this, but having a large amount of fields can reduce the amount of space available as well. Reducing the number of fields you have could also help, but aside from that I don't have anything else to suggest.

Hi Kyle, thanks. Bit worried now, especially when you guys are unable to fix it. I dont think I have overdone the fields. I'll assess further though and see what I can do, no harm in trimming.

This really throws a spanner in the works actually. I'm highly reliant on a smooth operation for my users - or why would they bother using my service. Imagine filling everything in - going to update and you get a 404 error with work lost, it annoyed the hell out of me and I was only pasting in some dummy text.

The highly critical editor text areas seem to work ok now - when used as I had intended: with a long list of individual links. Some others can barely take a standard paragraph though. I took out character limits for all, that seemed to help, as felt when saving with more than the limit - it may affect it. Might put them back in for remaining affected fields and be really stingy with them. Can also warn users to save each field/tab as they go along I suppose.

Is it CB database limitation or my server?could you tell me. (as, if it's cb, looks like I'm in trouble, otherwise can always upgrade server/database if needed/possible).

If it is CB, is it a case that each individual user has a database limitation or there is a general database limitation. Or each field has a limitation per user?


Sorry to come back at you with the issue, just need some intel (anything at all) in order to attempt to fix it.

Thanks

Also, is it a case of removing fields that are not in use or fields in use?or both?

Having further tested, it seems if I take characters off one field, I can add more characters to a field that previously couldn't take anything additional (talking going from 1 small paragraph to 2 and vice versa). (this is with other fields fully filled). I probably only need about 30% more space given in total for it to be perfect.

It seems to me that there is a limitation per user, in amount of total characters..

This greatly affects my use and desired outcome - of being able to let users freely build their own profiles (with certain limitations of course). Can see many annoyed users contacting me adding to workload and disatisfaction. If I'm not misjudging the situation, in my opinion this is a massive limitation to CB as a whole and subsequently makes my desired service look amaturish as it stands.

My server guys said I need a VPS service for any customisation (which may not even helpanyway). Intended to go that route anyway but later on in the project, after traction is gained.

Anyway, maybe your further intel can help me make some space, as that seems to be the problem.

Kind regards

You may have too many varchar columns. We changed a lot of fields column types to text so their size contribution is just their pointer. First create a backup of your database. Next navigate to CB > Tools and run "Check Community Builder User Fields Database with strict column types" tool in the "Check Community Builder User Fields Database" description. This will try to convert them from varchar to text and may improve the row storage a bit more.

This is a limitation of the database storage. It's not a limitation set by CB. There's really not a lot we can do about it. A 2 table system where the fields are independent of the main table, but that brings its own set of issues (performance, complete rewrite of storage behavior, etc..) so that's a potential solution for the distant future if we can make it efficient enough. For now I don't have much to suggest beyond the topic you linked and my replies here.