1

I have created an event registration form, using a profile with a custom field. When I click Continue after filling in the form, I see Processing ... and then I get the confirmation screen, which looks good. But when I hit Continue I get:

Sorry but we are not able to provide this at the moment. DB Error: no such field Return to home page.

WordPress 4.7.4

CiviCRM 4.7.17

Full debug info is shown below. I get that civicrm_event.event_type_id is the problem but I'm not sure what to do about it.

I get the feeling this relates to the profile used with this event. In the profile, the event type is associated with the participant

profile

I'm filling in the event_type field from a URL variable. Not sure if that might be a problem.

If you want to try:

https://connectionpractice.org/test-webinar-with-affiliate-code/?affiliatecode=irthlingz1&eventtype=Introduction

My code for the URL variables

<script>
(function($) {
  // all JS code here
var l = document.location;
  var affiliatecode = l.search.match(/affiliatecode=([a-zA-Z0-9_]+)/)[1];
var eventtype = l.search.match(/eventtype=([a-zA-Z0-9_]+)/)[1];
   $( "input#custom_3").val(affiliatecode);
   $( "input#event_type").val(eventtype);
})(jQuery);
</script>

Debug info:

Database Error Code: Unknown column 'civicrm_event.event_type_id' in 'on clause', 1054
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-email`.id as `1-email_id`, `1-email`.email as `1-email`, `Billing-location_type`.id as `Billing-location_type_id`, `Billing-location_type`.name as `Billing-location_type`, `Billing-email`.id as `Billing-email_id`, `Billing-email`.email as `Billing-email`, civicrm_participant.id as participant_id, event_type.label as event_type, civicrm_participant.role_id as participant_role, civicrm_value_webinar_registration_3.id as civicrm_value_webinar_registration_3_id, civicrm_value_webinar_registration_3.affiliate_code_3 as custom_3  FROM civicrm_contact contact_a 
LEFT JOIN civicrm_email `1-email` ON contact_a.id = `1-email`.contact_id AND `1-email`.is_primary = 1  
LEFT JOIN civicrm_email `Billing-email` ON contact_a.id = `Billing-email`.contact_id AND `Billing-email`.location_type_id = 5  
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-email`.location_type_id = `1-location_type`.id ) )  
LEFT JOIN civicrm_location_type `Billing-location_type` ON (  ( `Billing-email`.location_type_id = `Billing-location_type`.id ) )  LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id  
LEFT JOIN civicrm_value_webinar_registration_3 ON civicrm_value_webinar_registration_3.entity_id = `civicrm_participant`.id  LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id )  LEFT JOIN civicrm_option_group option_group_participant_role ON (option_group_participant_role.name = 'participant_role') LEFT JOIN civicrm_option_value participant_role ON ((civicrm_participant.role_id = participant_role.value OR SUBSTRING_INDEX(role_id,'', 1) = participant_role.value)
                               AND option_group_participant_role.id = participant_role.option_group_id )  WHERE  ( contact_a.id = '2' )   GROUP BY contact_a.id LIMIT 0, 25  [nativecode=1054 ** Unknown column 'civicrm_event.event_type_id' in 'on clause']
    [type] => DB_Error
    [user_info] => SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-email`.id as `1-email_id`, `1-email`.email as `1-email`, `Billing-location_type`.id as `Billing-location_type_id`, `Billing-location_type`.name as `Billing-location_type`, `Billing-email`.id as `Billing-email_id`, `Billing-email`.email as `Billing-email`, civicrm_participant.id as participant_id, event_type.label as event_type, civicrm_participant.role_id as participant_role, civicrm_value_webinar_registration_3.id as civicrm_value_webinar_registration_3_id, civicrm_value_webinar_registration_3.affiliate_code_3 as custom_3  FROM civicrm_contact contact_a 
LEFT JOIN civicrm_email `1-email` ON contact_a.id = `1-email`.contact_id AND `1-email`.is_primary = 1  
LEFT JOIN civicrm_email `Billing-email` ON contact_a.id = `Billing-email`.contact_id AND `Billing-email`.location_type_id = 5  
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-email`.location_type_id = `1-location_type`.id ) )  
LEFT JOIN civicrm_location_type `Billing-location_type` ON (  ( `Billing-email`.location_type_id = `Billing-location_type`.id ) )  LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id  
LEFT JOIN civicrm_value_webinar_registration_3 ON civicrm_value_webinar_registration_3.entity_id = `civicrm_participant`.id  LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id )  LEFT JOIN civicrm_option_group option_group_participant_role ON (option_group_participant_role.name = 'participant_role') LEFT JOIN civicrm_option_value participant_role ON ((civicrm_participant.role_id = participant_role.value OR SUBSTRING_INDEX(role_id,'', 1) = participant_role.value)
                               AND option_group_participant_role.id = participant_role.option_group_id )  WHERE  ( contact_a.id = '2' )   GROUP BY contact_a.id LIMIT 0, 25  [nativecode=1054 ** Unknown column 'civicrm_event.event_type_id' in 'on clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type as `contact_sub_type`, contact_a.first_name as `first_name`, contact_a.middle_name as `middle_name`, contact_a.last_name as `last_name`, contact_a.household_name as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, `1-location_type`.id as `1-location_type_id`, `1-location_type`.name as `1-location_type`, `1-email`.id as `1-email_id`, `1-email`.email as `1-email`, `Billing-location_type`.id as `Billing-location_type_id`, `Billing-location_type`.name as `Billing-location_type`, `Billing-email`.id as `Billing-email_id`, `Billing-email`.email as `Billing-email`, civicrm_participant.id as participant_id, event_type.label as event_type, civicrm_participant.role_id as participant_role, civicrm_value_webinar_registration_3.id as civicrm_value_webinar_registration_3_id, civicrm_value_webinar_registration_3.affiliate_code_3 as custom_3  FROM civicrm_contact contact_a 
LEFT JOIN civicrm_email `1-email` ON contact_a.id = `1-email`.contact_id AND `1-email`.is_primary = 1  
LEFT JOIN civicrm_email `Billing-email` ON contact_a.id = `Billing-email`.contact_id AND `Billing-email`.location_type_id = 5  
LEFT JOIN civicrm_location_type `1-location_type` ON (  ( `1-email`.location_type_id = `1-location_type`.id ) )  
LEFT JOIN civicrm_location_type `Billing-location_type` ON (  ( `Billing-email`.location_type_id = `Billing-location_type`.id ) )  LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id  
LEFT JOIN civicrm_value_webinar_registration_3 ON civicrm_value_webinar_registration_3.entity_id = `civicrm_participant`.id  LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id )  LEFT JOIN civicrm_option_group option_group_participant_role ON (option_group_participant_role.name = 'participant_role') LEFT JOIN civicrm_option_value participant_role ON ((civicrm_participant.role_id = participant_role.value OR SUBSTRING_INDEX(role_id,'', 1) = participant_role.value)
                               AND option_group_participant_role.id = participant_role.option_group_id )  WHERE  ( contact_a.id = '2' )   GROUP BY contact_a.id LIMIT 0, 25  [nativecode=1054 ** Unknown column 'civicrm_event.event_type_id' in 'on clause']"]
)

phpmyadmin screenshot showing that the field is there:enter image description here

Backtrace, in case that might be helpful (site specific info replaced by mysite)

#0 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(187): CRM_Core_Error::backtrace()
#1 /mysite/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB.php(984): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#3 /mysite/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#4 /mysite/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...", "DB_Error", TRUE)
#5 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#6 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...", "1054 ** Unknown column 'civicrm_event.event_type_id' in 'on clause'")
#7 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#9 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2438): DB_common->query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#10 /mysite/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#11 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(354): DB_DataObject->query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#12 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1304): CRM_Core_DAO->query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...", TRUE)
#13 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4372): CRM_Core_DAO::executeQuery("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#14 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Contact.php(1639): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:11), (Array:0))
#15 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Contact.php(1972): CRM_Contact_BAO_Contact::getHierContactDetails("2", (Array:8))
#16 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Contact.php(1880): CRM_Contact_BAO_Contact::formatProfileContactParams((Array:24), (Array:8), "2", NULL, "Individual")
#17 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Event/Form/Registration/Confirm.php(1171): CRM_Contact_BAO_Contact::createProfileContact((Array:24), (Array:8), "2", (Array:0), NULL, "Individual", TRUE)
#18 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Event/Form/Registration/Confirm.php(510): CRM_Event_Form_Registration_Confirm::updateContactFields("2", (Array:24), (Array:8), Object(CRM_Event_Form_Registration_Confirm))
#19 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(453): CRM_Event_Form_Registration_Confirm->postProcess()
#20 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#21 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Event_Form_Registration_Confirm), "next", "Next")
#22 /mysite/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Event_Form_Registration_Confirm), "next")
#23 /mysite/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Event_Form_Registration_Confirm), "next")
#24 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")
#25 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(310): CRM_Core_Controller->run((Array:3), NULL)
#26 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:15))
#27 /mysite/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#28 /mysite/wp-content/plugins/civicrm/civicrm.php(1227): CRM_Core_Invoke::invoke((Array:3))
#29 /mysite/wp-content/plugins/civicrm/includes/civicrm.basepage.php(132): CiviCRM_For_WordPress->invoke()
#30 /mysite/wp-includes/class-wp-hook.php(298): CiviCRM_For_WordPress_Basepage->basepage_handler(Object(WP))
#31 /mysite/wp-includes/class-wp-hook.php(323): WP_Hook->apply_filters("", (Array:1))
#32 /mysite/wp-includes/plugin.php(515): WP_Hook->do_action((Array:1))
#33 /mysite/wp-includes/class-wp.php(746): do_action_ref_array("wp", (Array:1))
#34 /mysite/wp-includes/functions.php(955): WP->main("")
#35 /mysite/wp-blog-header.php(16): wp()
#36 /mysite/index.php(17): require("/mysite/wp-blog-header.php")
#37 {main}

Note that the Event Type field is a standard choice for Participants. That is not a custom field that I added. Below is a screenshot from a brand new install of CiviCRM. Perhaps this option should not be there?

enter image description here

Improve this question
6
  • i barely follow what is happening but Event Type is a field that belongs to the entity type Event, and you seem to be associating that with a Participant record. seems like a bad mix to me. on the other hand, i see you have 'event type' in your url, are you actually just trying to get that value and add it in to a custom field on participant?
    – petednz - fuzion ♦
    May 8, 2017 at 6:42
  • Yes, I'm trying to add that value to a field on participant, but it's not a custom field. I picked it from existing fields. The only custom field is Affiliate Code.
    – user1147171
    May 8, 2017 at 19:24
  • That is what I don't understand then. If you are adding data to a Participant, how do you think you can include a field pertaining to the Event via a Profile?
    – petednz - fuzion ♦
    May 8, 2017 at 19:49
  • Event Type (Participant) is a standard option when creating a new field in a profile. (See the last screenshot in my question, just added.) Perhaps it shouldn't be?
    – user1147171
    May 9, 2017 at 20:37
  • so i see. and has been for a long time. not sure what to suggest
    – petednz - fuzion ♦
    May 9, 2017 at 21:49

1 Answer 1

Reset to default
1

That column really really should exist. Do you have access to see the underlying database through phpmyadmin or similar? I would expect either you have logging turned on but there is a problem with the database table or you have an issue with your mysql server, possibly the disk is full, or some db admin has made an error and deleted the column

Improve this answer
2
  • The field is there in the database. See phpmyadmin screenshot added to the question. The MySQL database is allowed over a GB of storage and has used less than 16MB. Hmmm ...
    – user1147171
    May 7, 2017 at 22:41
  • My question with almost the same title got an answer that pointed me in the correct direction: the problem is probably not with the table but with the triggers, it is they that fail and generate the error message. Turns out that e.g. upgrading with logging turned on fails to activate triggers. The solution for me was to turn logging off and the error went away. Maybe this is similar?
    – thoni56
    Jun 9, 2017 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.