DB Error when creating profile

[Aktifit]

I have to work on a Joomla Website with CiviCRM, I upgraded CiviCRM from 4.1.2 to 4.2.1 (I get an error updating the DB on higher versions...). Now when I try to create a profile, I get a a DB Error: syntax error. I know next to nothing about MySQL so I'm kind of lost here, any help would be appreciated.
Error Details:

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1, 1064
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

    code] => -2
   

[message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1]
    [type] => DB_Error
    [user_info] => INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO dedupe (id1, id2, weight) SELECT 0 id1, 0 id2, 0 weight LIMIT 0 GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight)' at line 1]"]
)

[dkiasimon]

We've had the same issue. We're running Civicrm on Drupal but in this case it probably doesn't matter. MySQL doesn't like the GROUP BY statement when it's not really selecting anything.
I don't really know how to make a proper patchfile, but here's what I did:

1. Open CRM/Dedupe/BAO/RuleGroup.php

2. Look for this block

if ($this->params && !$this->noRules) {
      $tempTableQuery = "CREATE TEMPORARY TABLE dedupe (id1 int, weight int, UNIQUE UI_id1 (id1)) ENGINE=MyISAM";
      $insertClause   = "INSERT INTO dedupe (id1, weight)";
      $groupByClause  = "GROUP BY id1";
      $dupeCopyJoin   = " JOIN dedupe_copy ON dedupe_copy.id1 = t1.column WHERE ";
    }
    else {
      $tempTableQuery = "CREATE TEMPORARY TABLE dedupe (id1 int, id2 int, weight int, UNIQUE UI_id1_id2 (id1, id2)) ENGINE=MyISAM";
      $insertClause   = "INSERT INTO dedupe (id1, id2, weight)";
      $groupByClause  = "GROUP BY id1, id2";
      $dupeCopyJoin   = " JOIN dedupe_copy ON dedupe_copy.id1 = t1.column AND dedupe_copy.id2 = t2.column WHERE ";
    }

3. Put this in right after the block:

if(isset($this->noRules) && $this->noRules) {
        $groupByClause = "";
}

That should fix it.

[Donald Lobo]

i think the below is a band aid and does not address the root cause

the question is, why is that select statement generated looking so odd (i.e. not using any civi tables etc). seems like civi is not getting a handle on the right dedupe rules etc. i would probably investigate that path first

lobo

[Aktifit]

Thanks for the replies, we actually fixed the problem now. Seems like updating CiviCRM may duplicate some of the dedupe rules, at least that was the case on our webpage, so we simply deleted them manually from the dedupe table. Now everything works fine .