Match Now gives a database error. #8

vingle · 2020-03-29T18:35:51Z

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

The text was updated successfully, but these errors were encountered:

vingle · 2020-03-30T01:42:34Z

(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 · 2020-03-30T06:48:14Z

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 · 2020-03-30T08:01:44Z

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

vingle · 2020-03-30T10:33:37Z

@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 · 2020-03-30T10:57:08Z

@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 · 2020-03-30T11:01:35Z

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 · 2020-03-30T11:37:26Z

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 · 2020-03-30T12:47:58Z

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 · 2020-03-30T12:59:14Z

@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 · 2020-03-30T13:02:34Z

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 · 2020-03-30T13:26:40Z

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

See #11

bjendres added a commit that referenced this issue Mar 30, 2020

[#8] 'fixed' issues with ONLY_FULL_GROUP_BY

715d93c

bjendres added a commit that referenced this issue Mar 30, 2020

[#8] 'fixed' issues with ONLY_FULL_GROUP_BY

5cf7206

bjendres mentioned this issue Mar 30, 2020

Unknown column 'offers_help_3' in 'where clause' #11

Closed

bjendres closed this as completed Mar 30, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Match Now gives a database error. #8

Match Now gives a database error. #8

vingle commented Mar 29, 2020

vingle commented Mar 30, 2020

bjendres commented Mar 30, 2020

bjendres commented Mar 30, 2020 •

edited

Loading

vingle commented Mar 30, 2020 •

edited

Loading

bjendres commented Mar 30, 2020

vingle commented Mar 30, 2020 •

edited

Loading

vingle commented Mar 30, 2020

bjendres commented Mar 30, 2020

vingle commented Mar 30, 2020

bjendres commented Mar 30, 2020 •

edited

Loading

bjendres commented Mar 30, 2020

Match Now gives a database error. #8

Match Now gives a database error. #8

Comments

vingle commented Mar 29, 2020

vingle commented Mar 30, 2020

bjendres commented Mar 30, 2020

bjendres commented Mar 30, 2020 • edited Loading

vingle commented Mar 30, 2020 • edited Loading

bjendres commented Mar 30, 2020

vingle commented Mar 30, 2020 • edited Loading

vingle commented Mar 30, 2020

bjendres commented Mar 30, 2020

vingle commented Mar 30, 2020

bjendres commented Mar 30, 2020 • edited Loading

bjendres commented Mar 30, 2020

bjendres commented Mar 30, 2020 •

edited

Loading

vingle commented Mar 30, 2020 •

edited

Loading

vingle commented Mar 30, 2020 •

edited

Loading

bjendres commented Mar 30, 2020 •

edited

Loading