No such field fatal error when a open is received

[jmcclelland]

I'm getting this in my logs:

(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUES ( 15684 ,  20181116103642 )  [nativecode=1054 ** Unknown column 'j.start_date' in 'where clause']
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUES ( 15684 ,  20181116103642 )  [nativecode=1054 ** Unknown column 'j.start_date' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUES ( 15684 ,  20181116103642 )  [nativecode=1054 ** Unknown column 'j.start_date' in 'where clause']"]
)

Nov 16 10:36:42  [info] $backTrace = #0 /var/www/powerbase/sites/all/modules/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/powerbase/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/powerbase/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...")
#3 /var/www/powerbase/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...")
#4 /var/www/powerbase/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...", "DB_Error", TRUE)
#5 /var/www/powerbase/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#6 /var/www/powerbase/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, "INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...", "1054 ** Unknown column 'j.start_date' in 'where clause'")
#7 /var/www/powerbase/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/powerbase/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...")
#9 /var/www/powerbase/sites/all/modules/civicrm/packages/DB/DataObject.php(2443): DB_common->query("INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...")
#10 /var/www/powerbase/sites/all/modules/civicrm/packages/DB/DataObject.php(1068): DB_DataObject->_query("INSERT INTO civicrm_mailing_event_opened (event_queue_id , time_stamp ) VALUE...")
#11 /var/www/powerbase/sites/all/modules/civicrm/CRM/Core/DAO.php(548): DB_DataObject->insert()
#12 /var/www/powerbase/sites/all/modules/civicrm/CRM/Mailing/Event/BAO/Opened.php(61): CRM_Core_DAO->save()
#13 /var/www/powerbase/sites/all/modules/civicrm/extern/open.php(15): CRM_Mailing_Event_BAO_Opened::open("15684")
#14 {main}

It looks like the civicrm_mailing_event_bounce and civicrm_mailing_event_opened are missing a join on the civicrm_mailing_job. I'm not sure if the fix is as simple as adding that join or whether it was left off for some purpose. But... with this module installed, CiviCRM throws an exception whenever an opened event is processed that results in no open events being processed!

[jmcclelland]

A temporary work around is to uncheck the mail fields from the summary fields admin page.

[twomice]

Thanks for the report, Jamie. Which version of CiviCRM is this?

[jmcclelland]

Thanks for the follow up!

We're running 5.3.

jamie

[twomice]

Thanks @jmcclelland Code looks good; need to try it myself before merging.

[yosefromano]

@twomice if it is of any help, we tested this fix and it works well.

[twomice]

Thanks Yosef!

[twomice]

We've finally made time to test and merge the PR, so this is fixed as of ed52627075f0e3eb82efe511239de3a5a1297742 Thanks everyone!