comprofiler-table reaches maximum database-fields

10 years 10 months ago #78961 by dpdk
USING CB 1.2 RC3

When building a large community with many user-registration I encountered a mayor structural flaw in Community Builder.

Because all CB-fields are added in one Table called "comprofiler". You get a serious restriction in the total amount of fields you can add!

I've reached that total amount of data in one Table, as you can see in the code-block below!


[code:1]moscomprofilerFields::«»store failed: CBSQLupgrader::changeColumn (ADD) of Table #__comprofiler Column cb_5c3communitynaam failed with SQL error: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs SQL=ALTER TABLE `jos_comprofiler`\n ADD `cb_5c3communitynaam` varchar(255) DEFAULT NULL Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs SQL=ALTER TABLE `jos_comprofiler`\n ADD `cb_5c3communitynaam` varchar(255) DEFAULT NULL[/code:1]

As it says, i've reached the maximum ammount of field-data a table can contain.

In order to let Community Builder evolve, we maybe need to change this data-structure dramaticly!
Because nothing seems to be joined, this reaches serious limitations.


Yes, I've reached the limit of Community Builder.
But this doesn't have to be a limitation of CB!


Perhaps there is a sollution to this:
You can let Community Builder build a new database-table for each new Tab that is created within the CB admin.
Because all information is then devided by tabs, and so, all data-fields containing this tab are stored in different database-tables, you minimize the possibility of reaching this database-entry-limit.

Please feel free to discus this!
And hopefully the development team will have a look at this important issue.

Best regards,
stefan @ dpdk

(NED)
10 years 10 months ago #78981 by beat
MySql has a limit of max-sizes of fields in a row of 64 kilobytes.

With varchar(255) types, this means maximum 256 fields of type text.

This is not a design flaw, it's a limit for that field type.

That said there is an easy cure in CB 1.2 RC3 ;) until we remove that limit in next CB version.

Open file:

components/com_comprofiler/plugin/user/cb.core.xml

search for:

[code:1]varchar(255)
[/code:1]
within field type 'text' and change that one to:

[code:1]text[/code:1]

then go to CB -> tools -> check CB database -> fix.

because text is outside the row in MySql, it uses only 4 bytes instead of 256, so you can now store 30'000 such fields instead of 256... Should be enough for some time.

Don't forget to do that change in each new version until we make the fields SQL typing available in a future version.

Beat - Community Builder Team Member

Before posting on forums: Read FAQ thoroughly -- Help us spend more time coding by helping others in this forum, many thanks :)
CB links: Our membership - CBSubs - Templates - Hosting - Forge - Send me a Private Message (PM) only for private/confidential info
The following user(s) said Thank You: Michael2014
Moderators: beatnantkrileon
Time to create page: 0.378 seconds
Facebook Twitter Google LinkedIn