Bonjour Edwin,

I successfully set up a set of shared sites with user and community builder sharing.
When I try to show a CB profile (on any of the shared sites), I receive a MySQL Error :
Key 'aprm' doesn't exist in table '####_comprofiler_members' SQL=SELECT a.referenceid, a.memberid AS d1 FROM `####_comprofiler_members` AS a FORCE INDEX (aprm) WHERE a.referenceid = 532 AND a.accepted=1 AND a.pending=0 AND a.memberid = 533

My investigation reveals that VIEWS are unable to have own indicies, so there IS no index 'aprm' in the respective VIEW - therefore MySQL is correct with this error message.

Is there a better way to handle this "sharing issue" than manually edit the community builder script and remove the "FORCE INDEX" clause from these queries?

This clause is used ONLY for the #__comprofiler_members table (and only in one single script of the community builder component).

Kind regards
Ruediger

Based on what you describe, it seems this statement is written in CB somewhere and therefore the code in CB should be fixed to remove the "FORCE INDEX" statement.

Can you mention the CB version that you are using and if you have located the place in CB where this statement is wroten, this will be helpfull (file and line code).

Perhaps that you could also report this issue to CB to allow them fixing that directly in CB.

I don't see any other method than fix the CB source code as there is no alternative in MySQL VIEWS to make the "force index" statement valid.

Hello Edwin,

I am using CB 1.9.1 in this project and the script is comprofiler.class.php (see below). I commented the FORCE INDEX (several occurences) out for now and this fixes the issue for the moment.

I can get back to Community Builder developers, but I belief they "have their reasons" for using the FORCE INDEX clause in this case (it is about connections between CommunityBuilder users).

Maybe other JMS users find this topic helpful, if they share the community builder extenison across slave sites.

Kind regards
Ruediger

*****************************************************************************
...\cb_1_9_1-unzip1st\com_comprofiler\comprofiler.class.php (21 hits)
Line 4739: ."\n FROM `#__comprofiler_members` AS a /* SITS: FORCE INDEX (aprm)*/"
Line 4746: ."\n FROM `#__comprofiler_members` AS a /* SITS: FORCE INDEX (aprm)*/"
Line 4747: ."\n LEFT JOIN #__comprofiler_members AS b /* SITS: FORCE INDEX (pamr)*/ ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 "
Line 4757: ."\n FROM `#__comprofiler_members` AS a /* SITS: FORCE INDEX (aprm)*/"
Line 4758: ."\n LEFT JOIN #__comprofiler_members AS b /* SITS: FORCE INDEX (pamr)*/ ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 "
Line 4759: ."\n LEFT JOIN #__comprofiler_members AS c /* SITS: FORCE INDEX (pamr)*/ ON b.memberid=c.referenceid AND c.accepted=1 AND c.pending=0 "
Line 4770: ."\n FROM `#__comprofiler_members` AS a /* SITS: FORCE INDEX (aprm)*/"
Line 4771: ."\n LEFT JOIN #__comprofiler_members AS b /* SITS: FORCE INDEX (aprm)*/ ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 "
Line 4772: ."\n LEFT JOIN #__comprofiler_members AS c /* SITS: FORCE INDEX (pamr)*/ ON b.memberid=c.referenceid AND c.accepted=1 AND c.pending=0 "
Line 4773: ."\n LEFT JOIN #__comprofiler_members AS d /* SITS: FORCE INDEX (pamr)*/ ON c.memberid=d.referenceid AND d.accepted=1 AND d.pending=0 "
Line 4785: ."\n FROM `#__comprofiler_members` AS a /* SITS: FORCE INDEX (aprm)*/"
Line 4786: ."\n LEFT JOIN #__comprofiler_members AS b /* SITS: FORCE INDEX (aprm)*/ ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 "
Line 4787: ."\n LEFT JOIN #__comprofiler_members AS c /* SITS: FORCE INDEX (aprm)*/ ON b.memberid=c.referenceid AND c.accepted=1 AND c.pending=0 "
Line 4788: ."\n LEFT JOIN #__comprofiler_members AS d /* SITS: FORCE INDEX (pamr)*/ ON c.memberid=d.referenceid AND d.accepted=1 AND d.pending=0 "
Line 4789: ."\n LEFT JOIN #__comprofiler_members AS e /* SITS: FORCE INDEX (pamr)*/ ON d.memberid=e.referenceid AND e.accepted=1 AND e.pending=0 "
Line 4802: ."\n FROM `#__comprofiler_members` AS a /* SITS: FORCE INDEX (aprm)*/"
Line 4803: ."\n LEFT JOIN #__comprofiler_members AS b /* SITS: FORCE INDEX (aprm)*/ ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 "
Line 4804: ."\n LEFT JOIN #__comprofiler_members AS c /* SITS: FORCE INDEX (aprm)*/ ON b.memberid=c.referenceid AND c.accepted=1 AND c.pending=0 "
Line 4805: ."\n LEFT JOIN #__comprofiler_members AS d /* SITS: FORCE INDEX (pamr)*/ ON c.memberid=d.referenceid AND d.accepted=1 AND d.pending=0 "
Line 4806: ."\n LEFT JOIN #__comprofiler_members AS e /* SITS: FORCE INDEX (pamr)*/ ON d.memberid=e.referenceid AND e.accepted=1 AND e.pending=0 "
Line 4807: ."\n LEFT JOIN #__comprofiler_members AS f /* SITS: FORCE INDEX (pamr)*/ ON e.memberid=f.referenceid AND f.accepted=1 AND f.pending=0 "

The reason to use a FORCE INDEX is perhaps for performance and perhaps want to be sure that MySQL will use this index to perform the request. (Not let MySQL decide which index to use to speed-up the processing).

Did you reported the issue to CB team to allow them fixing the problem for everybody.