Match Now gives a database error.

[vingle]

Have submitted a request for, and offer of, help – both records appear in the dbse with the correct custom fields. But when I click Match Now get the following error (am using Mutual Aid 1.1-alpha1, OCM 1.7-beta3 , Google Mapping/Geocoding):

CiviCRM_API3_Exception: "Matching failed: DB Error: unknown error"

#0 /Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/default/files/civicrm/ext/de.systopia.mutualaid-1.1-alpha1/CRM/Mutualaid/Page/MatchNow.php(26): civicrm_api3("MutualAid", "match", (Array:1))
#1 /Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/Invoke.php(268): CRM_Mutualaid_Page_MatchNow->run((Array:3), NULL)
#2 /Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:13))
#3 /Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:3))
#4 /Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/drupal/civicrm.module(456): CRM_Core_Invoke::invoke((Array:3))
#5 /Applications/MAMP/htdocs/drupal-7.69-civicrm/includes/menu.inc(527): civicrm_invoke("mutualaid", "matchnow")
#6 /Applications/MAMP/htdocs/drupal-7.69-civicrm/index.php(21): menu_execute_active_handler()
#7 {main}

Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
Matching failed: DB Error: unknown error

[vingle]

(NB - I get the same via a job: "Finished execution of Mutual Aid with result: Failure, Error message: Matching failed: DB Error: unknown error")

[bjendres]

Yes, I had my suspicions, and I found this in the log:

               [nativecode=1055 ** Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drk_civicrm.address.geo_code_1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]

I'll fix that...

[bjendres]

@vingle try 1.1-alpha2 (https://github.com/systopia/de.systopia.mutualaid/releases/tag/1.1-alpha2)

[vingle]

@bjendres that didn't seem to fix it – after install I got the error below in-screen, and 'match now' gives the same error as before.

Notice: Undefined index: option_type in CRM_Core_BAO_CustomField::prepareCreate() (line 1978 of /Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/BAO/CustomField.php).

[bjendres]

@vingle: that's weird. Could you maybe get the full error message from the CiviCR log? It should be around here:

/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/default/files/civicrm/ConfigAndLog/CiviCRM.[jibberish].log

[vingle]

It's

[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => 
        SELECT
          contact_id                               AS contact_id,
          max_distance                             AS max_distance,
          max_spots                                AS max_spots,
          open_spots                               AS open_spots,
          ((min_longitude +  max_longitude) / 2.0) AS longitude,
          ((min_latitude + max_latitude) / 2.0)    AS latitude,
          CONCAT(IF(offers_help_1, '1,', ''), IF(offers_help_5, '5,', ''))                          AS offers_help
        FROM civicrm_tmp_e_dflt_3b4e702d2ea6f45cf494d00cafc686bd
        WHERE ((offers_help_1) OR (offers_help_3))
          AND min_longitude <= -0.1097186 
          AND max_longitude >= -0.1097186 
          AND min_latitude  <= 51.5682303 
          AND max_latitude  >= 51.5682303 
          AND open_spots > 0
       [nativecode=1054 ** Unknown column 'offers_help_3' in 'where clause']
[type] => DB_Error
[user_info] => 
        SELECT
          contact_id                               AS contact_id,
          max_distance                             AS max_distance,
          max_spots                                AS max_spots,
          open_spots                               AS open_spots,
          ((min_longitude +  max_longitude) / 2.0) AS longitude,
          ((min_latitude + max_latitude) / 2.0)    AS latitude,
          CONCAT(IF(offers_help_1, '1,', ''), IF(offers_help_5, '5,', ''))                          AS offers_help
        FROM civicrm_tmp_e_dflt_3b4e702d2ea6f45cf494d00cafc686bd
        WHERE ((offers_help_1) OR (offers_help_3))
          AND min_longitude <= -0.1097186 
          AND max_longitude >= -0.1097186 
          AND min_latitude  <= 51.5682303 
          AND max_latitude  >= 51.5682303 
          AND open_spots > 0
       [nativecode=1054 ** Unknown column 'offers_help_3' in 'where clause']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="
        SELECT
          contact_id                               AS contact_id,
          max_distance                             AS max_distance,
          max_spots                                AS max_spots,
          open_spots                               AS open_spots,
          ((min_longitude +  max_longitude) / 2.0) AS longitude,
          ((min_latitude + max_latitude) / 2.0)    AS latitude,
          CONCAT(IF(offers_help_1, '1,', ''), IF(offers_help_5, '5,', ''))                          AS offers_help
        FROM civicrm_tmp_e_dflt_3b4e702d2ea6f45cf494d00cafc686bd
        WHERE ((offers_help_1) OR (offers_help_3))
          AND min_longitude <= -0.1097186 
          AND max_longitude >= -0.1097186 
          AND min_latitude  <= 51.5682303 
          AND max_latitude  >= 51.5682303 
          AND open_spots > 0
       [nativecode=1054 ** Unknown column 'offers_help_3' in 'where clause']"]

)

and

Mar 30 11:59:42  [error] 
$Fatal Error Details = array(3) {
  ["message"]=>
  string(40) "Matching failed: DB Error: no such field"
  ["code"]=>
  NULL
  ["exception"]=>
  object(CiviCRM_API3_Exception)#1858 (8) {
    ["extraParams":"CiviCRM_API3_Exception":private]=>
    array(3) {
      ["is_error"]=>
      int(1)
      ["error_message"]=>
      string(40) "Matching failed: DB Error: no such field"
      ["error_code"]=>
      string(9) "undefined"
    }
    ["message":protected]=>
    string(40) "Matching failed: DB Error: no such field"
    ["string":"Exception":private]=>
    string(0) ""
    ["code":protected]=>
    int(0)
    ["file":protected]=>
    string(83) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/api/api.php"
    ["line":protected]=>
    int(138)
    ["trace":"Exception":private]=>
    array(7) {
      [0]=>
      array(4) {
        ["file"]=>
        string(142) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/default/files/civicrm/ext/de.systopia.mutualaid-1.1-alpha2/CRM/Mutualaid/Page/MatchNow.php"
        ["line"]=>
        int(26)
        ["function"]=>
        string(12) "civicrm_api3"
        ["args"]=>
        array(3) {
          [0]=>
          string(9) "MutualAid"
          [1]=>
          string(5) "match"
          [2]=>
          array(1) {
            ["version"]=>
            int(3)
          }
        }
      }
      [1]=>
      array(6) {
        ["file"]=>
        string(91) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/Invoke.php"
        ["line"]=>
        int(268)
        ["function"]=>
        string(3) "run"
        ["class"]=>
        string(27) "CRM_Mutualaid_Page_MatchNow"
        ["type"]=>
        string(2) "->"
        ["args"]=>
        array(2) {
          [0]=>
          array(3) {
            [0]=>
            string(7) "civicrm"
            [1]=>
            string(9) "mutualaid"
            [2]=>
            string(8) "matchnow"
          }
          [1]=>
          NULL
        }
      }
      [2]=>
      array(6) {
        ["file"]=>
        string(91) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/Invoke.php"
        ["line"]=>
        int(68)
        ["function"]=>
        string(7) "runItem"
        ["class"]=>
        string(15) "CRM_Core_Invoke"
        ["type"]=>
        string(2) "::"
        ["args"]=>
        array(1) {
          [0]=>
          &array(13) {
            ["id"]=>
            string(3) "449"
            ["domain_id"]=>
            string(1) "1"
            ["path"]=>
            string(26) "civicrm/mutualaid/matchnow"
            ["title"]=>
            string(34) "Match MutualAid requests to offers"
            ["access_callback"]=>
            array(2) {
              [0]=>
              string(19) "CRM_Core_Permission"
              [1]=>
              string(9) "checkMenu"
            }
            ["access_arguments"]=>
            array(2) {
              [0]=>
              array(1) {
                [0]=>
                string(14) "access CiviCRM"
              }
              [1]=>
              string(3) "and"
            }
            ["page_callback"]=>
            string(27) "CRM_Mutualaid_Page_MatchNow"
            ["breadcrumb"]=>
            array(1) {
              [0]=>
              array(2) {
                ["title"]=>
                string(7) "CiviCRM"
                ["url"]=>
                string(36) "/drupal-7.69-civicrm/civicrm?reset=1"
              }
            }
            ["is_ssl"]=>
            string(1) "0"
            ["weight"]=>
            string(1) "1"
            ["type"]=>
            string(1) "1"
            ["page_type"]=>
            string(1) "0"
            ["page_arguments"]=>
            bool(false)
          }
        }
      }
      [3]=>
      array(6) {
        ["file"]=>
        string(91) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/CRM/Core/Invoke.php"
        ["line"]=>
        int(36)
        ["function"]=>
        string(7) "_invoke"
        ["class"]=>
        string(15) "CRM_Core_Invoke"
        ["type"]=>
        string(2) "::"
        ["args"]=>
        array(1) {
          [0]=>
          array(3) {
            [0]=>
            string(7) "civicrm"
            [1]=>
            string(9) "mutualaid"
            [2]=>
            string(8) "matchnow"
          }
        }
      }
      [4]=>
      array(6) {
        ["file"]=>
        string(93) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/sites/all/modules/civicrm/drupal/civicrm.module"
        ["line"]=>
        int(456)
        ["function"]=>
        string(6) "invoke"
        ["class"]=>
        string(15) "CRM_Core_Invoke"
        ["type"]=>
        string(2) "::"
        ["args"]=>
        array(1) {
          [0]=>
          array(3) {
            [0]=>
            string(7) "civicrm"
            [1]=>
            string(9) "mutualaid"
            [2]=>
            string(8) "matchnow"
          }
        }
      }
      [5]=>
      array(4) {
        ["file"]=>
        string(63) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/includes/menu.inc"
        ["line"]=>
        int(527)
        ["function"]=>
        string(14) "civicrm_invoke"
        ["args"]=>
        array(2) {
          [0]=>
          string(9) "mutualaid"
          [1]=>
          string(8) "matchnow"
        }
      }
      [6]=>
      array(4) {
        ["file"]=>
        string(55) "/Applications/MAMP/htdocs/drupal-7.69-civicrm/index.php"
        ["line"]=>
        int(21)
        ["function"]=>
        string(27) "menu_execute_active_handler"
        ["args"]=>
        array(0) {
        }
      }
    }
    ["previous":"Exception":private]=>
    NULL
  }
}
`

[vingle]

OK, I've tried a completely clean Drupal & Civi install (there were a few custom fields/profiles in the dbse on that install which maybe had similar names?) and it seems to work.

[bjendres]

there were a few custom fields/profiles in the dbse on that install which maybe had similar names?

That sounds weird, but if you can reproduce that again, please create another ticket...

@vingle does that mean it's working and you're happy now? Should we close the ticket...?

[vingle]

@bjendres - it's working in a clean install so I guess? The error Unknown column 'offers_help_3' in 'where clause' - doesn't mean too much for me but if you think that's likely not down to a common user-config issue, and more likely something specific to my setup - then definitely.

Or if you can suggest specific tables/fields that this error might be referencing I can compare them between my two installs to try and debug further.

[bjendres]

The error Unknown column 'offers_help_3' in 'where clause' - doesn't mean too much for me but if you think that's likely not down to a common user-config issue

You're right, that is weird. I my opinion, this could only happen if somebody changed the enabled help types while the matcher was running... @vingle maybe open a new ticket for that after all...?

[bjendres]

@vingle maybe open a new ticket for that after all...?

See #11