Ok, here is the answer to this issue (until CB 2.0 comes out):
Step 0. As usual, BACKUP your site first.
Open file:
components/com_comprofiler/plugin/user/plug_cbcore/cb.core.xml
Then, depending on the field type of which you have the most of in your CB profile, you can change their formal type as follows:
For text fields: around line 252: change:
[code:1] <field type="text" label="Text Field">
<data name="" nametype="namesuffix" type="sql:varchar(255)" null="true" default="NULL" strict="false" />
<params>
[/code:1]
To e.g., if you have text fields no longer than 150 characters (UTF8 in database reserves 3 bytes per character):
[code:1] <field type="text" label="Text Field">
<data name="" nametype="namesuffix" type="sql:varchar(150)" null="true" default="NULL" strict="true" />
<params>
[/code:1]
Notice to switch strict to ="true" in that line, otherwise the CB database tool won't look at the type correspondence.
You can also change type="sql:varchar(255)" to type="sql:mediumtext" (64k fields but using only 4 bytes in the row) but notice that you will slow a bit all SQL queries as it's an extra disk seek per text field if not in database cache.
Then save file, and go to Tools -> Check CB fields data storage database.
Similarily for single drop-downs if you have a lot of them, you can adjust to your longest drop-down text:
around line 156:
[code:1] <field type="select" label="Drop Down (Single Select)">
<data name="" nametype="namesuffix" type="sql:varchar(255)" null="true" default="NULL" strict="false" />
<params>
</params>
</field>
[/code:1]
could be changed (if e.g. your maximum drop-down text-size is 100 chars) to:
[code:1] <field type="select" label="Drop Down (Single Select)">
<data name="" nametype="namesuffix" type="sql:varchar(100)" null="true" default="NULL" strict="true" />
<params>
</params>
</field>
[/code:1]
Then again CB Tools -> Check Community Builder User Fields Database and fix according to the change made.
Of course backup your changed cb.core.xml file, as that will be overwritten at each CB update, and redo your changes until we make that parameter settable per field in upcoming CB 2.0.
MySQL has a 64 kilobytes limit per row, but for varchar it reserves the maximum size, and for UTF 8 chars, as they take 3 bytes maximum, it again reserves maximum, thus 765 bytes per varchar(255), thus those errors ! Text is stored separately from the row, which then has only a 4-bytes index in it, so it doesn't take space in the row almost, but requires an extra access to fetch the data.
Post edited by: beat, at: 2010/05/12 09:39