Smart Search not finding CB Profiles and Row Size too large

Hi guys,

I'm working on a site migration from J3 to J4 (4.4.12). I have two CB issues at the moment. 
1. I'm unable to get Smart Search to produce any CB results. Seems to work fine for everything else.
2. I'm getting a Row size too large error when saving CB fields.

Current CB version is 2.10.0. All plugins have been updated.
At the moment both the J3 live site and the new J4 site are still running on php7.4.33. The old site breaks when I update to 8.2 so I'm holding off until the new site is ready.
MySQL is 5.7.44 - I'm seeing a notice that CB will no longer be compatible. Looks like I need to update MySQL. I haven't done that before. Is it safe to go ahead and do that?

1. Smart Search Issue
I have the new cbusersearchhelper and cbusersearchfinder plugins installed/enabled and the old searchbot disabled. Debug and max errors don't show any issues.
If I try the filters in Smart Search - under Type I see it is searching for Contacts, Articles, Categories. Under Category I see the various Joomla categories. Under Author I see I users that have previously published an article (about 40 users). Don't see Profiles anywhere. When I run the Index in Smart Search I get about 260 results. No profiles. My site has over 5700 CB users (schools).

In the mean time...
I've set up a custom search field which works fine to search a single CB field instead of using Smart Search but it isn't ideal because the form I've created is only searching cb_name field. It becomes a problem if anyone searches for a school by anything that isn't part of their name. For example town or state.

I'm not expert in the code required to get it to search more than one field. Not sure if it's even possible?

This is my code....
<form action="xxxx/quick-search?cb_name__srmch=phrase&amp;cb_name=" method="post">
    <input type="hidden" name="listid" value="595">
    <input type="text" name="cb_name" value="" placeholder="Type any part of school name…">
    <button type="submit" class="button" span="" aria-hidden="true"><i class="icon-search icon-white"></i> Go</button>
</form>

If I could get that to search multiple fields at once I may be able to live without using Smart Search at all. Although I think Smart Search is still likely the best solution if I can get it to function.


2. Row Size too Large
I've seen this message before on the old site. Deleted a field or two and all was fine. On the new migrated one it doesn't. I've now deleted about 25 fields and it still has the issue. I do have about 160 published fields but the old site has over 185 and works fine.

Debug showing some errors like this....
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
xxxx/libraries/vendor/joomla/database/src/DatabaseDriver.php(675): Joomla\Database\Mysqli\MysqliStatement->execute() #1
xxxx/libraries/CBLib/CBLib/Database/Driver/CmsDatabaseDriver.php(326): Joomla\Database\DatabaseDriver->execute() #2
xxxx/libraries/CBLib/CBLib/Database/DatabaseUpgrade.php(1980): CBLib\Database\Driver\CmsDatabaseDriver->query() #3
xxxx/libraries/CBLib/CBLib/Database/DatabaseUpgrade.php(1384): CBLib\Database\DatabaseUpgrade->doQuery('ALTER TABLE `#_...') #4
xxxx/libraries/CBLib/CBLib/Database/DatabaseUpgrade.php(474): CBLib\Database\DatabaseUpgrade->changeCollation('#__comprofiler', 'utf8mb4_unicode...') #5
xxxx/libraries/CBLib/CBLib/Database/DatabaseUpgrade.php(254): CBLib\Database\DatabaseUpgrade->checkXmlTableDescription(Object(CBLib\Xml\SimpleXMLElement), 'cb_welcome', true, false, true, true) #6
....and a bunch more similar.

Hope you can help.

 

1. Smart Search Issue

Note CB User Search only works on Joomla 4 so if you've forced it to install on your Joomla 3 install it won't work there. Smart search works by indexing data and it does not index on install so you will need to navigate to Components > Smart Search > Index and let it run the indexer to index all your users.

I'm not expert in the code required to get it to search more than one field. Not sure if it's even possible?

It can index and subsequently search as many fields as you like. Within System > Manage > Plugins edit "Smart Search - Users" then under the Index tab you can configure the indexer to index as many fields as you want. Once you're done run indexing again in Components > Smart Search > Index. Note it will automatically index a user when they register or edit their profile so it's not necessary to manually index regularly, but only when changing settings for the indexer.

2. Row Size too Large
I've seen this message before on the old site. Deleted a field or two and all was fine. On the new migrated one it doesn't. I've now deleted about 25 fields and it still has the issue. I do have about 160 published fields but the old site has over 185 and works fine.

You've too many fields and have hit the limit for your database. This is usually due to new sites being on very conservative non-default MySQL settings. The result is you'll probably be limited to 100 fields. A properly configured server would have a limit of around 1000. This is a hosting issue though so there isn't much I can do to help you with this beyond providing the configuration, which can be found below.

www.joomlapolis.com/forum/153-professional-member-support/242484-installation-cb-connect-failed?start=12#318734

You can contact your host with this information and see if they'll fix the configuration. If you've access to MySQL configuration file you'll be able to adjust it yourself in my.ini file.

With CB 3.0 we'll be moving away from the _comprofiler table and be entirely storing fields in Joomla's user profiles so this won't be an issue in the future.

Thanks Kyle.
Didn't solve the issues but it might be getting closer to a solution.

Firstly, just for clarity, the code I gave you was referring to a custom search form module I made (not Smart Search) that just searches the cb_name field from a single CB list. At the moment I've just got that module at the top of every page because Smart Search won't work for me yet, where visitors can type a name and get directed to a CB list showing the users with a matching name. It works but it is not ideal because I need it to search multiple fields like Smart Search does. Unless there is a way to search multiple CB fields via a custom form, where a user can type either a name OR a state and get matches I want to remove my custom form module and replace it with a Smart Search one.

CB User Search is only installed on my J4 site. I've run the index many times. The issue is that it has been finding zero CB profiles. I have the CB plugin configured correctly to search several fields (it works similar to the old cbsearchbot plugin in J3 so that's not the problem). I've narrowed down the problem - if I go into CB User Manager, select a profile and then save it - Smart Search indexes that profile automatically. Now when I create a filter in Smart search it is showing Categories, Contacts, Articles, Profile. Yesterday it was only showing the first 3 of those. So by manually saving a CB profile the CB User Search plugin is now starting to work. But it is only working for the profiles that I have manually saved. I have 5700 profiles. I currently have only 3 being found by Smart Search. I just need it to index the other 5697 without me having to do them one by one.

I've tried the various CB Tools but that doesn't help. Everything seems to be fine except I am seeing a long list of issues that look like this when I run the fields database tool:
Table #__comprofiler Collation is utf8_general_ci instead of utf8mb4_unicode_ci
If I try to run the fix I get the row size too large issue.

I'm planning to get the database upgraded as you suggested. Do you think the database is the problem with Smart Search or is that just an issue with saving fields?

Sounds like a possible bug with the Finder plugin on Joomla 4. Will investigate further and see if I can find any issues on J4 with it not indexing properly.

Was able to confirm a bug with the indexer on Joomla 4. It is now fixed in now available 1.0.1 release of CB User Search. Once updated you'll be able to run the indexer like normally and it'll work its way through your users.