1

I'm running CiviCRM LTS (4.6.31) on Drupal 7.56 and a MySQL DB.

i built a custom profile to simplify Contactcreation. Each time i add my custom tags to that form i get the DB Error: syntax error when saving a new contact. It'sstil created though. Contactcreation using der builtin form is working fine (even when setting tags)

This is the error from the drupla log:

$Fatal error details = ARRAY ( [callback] => ARRAY ( [0] => crm_core_error [1] => handle ) [code] => -2 [message] => db error: syntax error [MODE] => 16 [debug_info] =>
                              SELECT    contact_a.id                                           AS contact_id,
                                        contact_a.gender_id                                    AS `gender_id`,
                                        contact_a.first_name                                   AS `first_name`,
                                        contact_a.last_name                                    AS `last_name`,
                                        civicrm_note.id                                        AS note_id,
                                        civicrm_note.note                                      AS `note`,
                                        `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`,
                                        `1-phone`.id                                           AS `1-phone_id`,
                                        IF ( `1-phone`.is_primary = 1, `1-phone`.phone, NULL ) AS `1-phone`,
                                        `1-address`.id                                         AS `1-address_id`,
                                        `1-address`.state_province_id                          AS `1-state_province_id`,
                                        `1-address`.street_address                             AS `1-street_address`,
                                        `1-address`.postal_code                                AS `1-postal_code`,
                                        `1-address`.city                                       AS `1-city`,
                                        `website--url`.id                                      AS `website--url_id`,
                                        `website--url`.url                                     AS `website--url`,
                                        `website--url`.website_type_id                         AS `website--website_type_id`
                              FROM      civicrm_contact contact_a
                              left join civicrm_address `1-address`
                              ON        (
                                                  `1-address`.contact_id = contact_a.id
                                                  AND `1-address`.is_primary = 1 )
                              left join civicrm_email `1-email`
                              ON        contact_a.id = `1-email`.contact_id
                                        AND `1-email`.is_primary = 1
                              left join civicrm_phone `1-phone`
                              ON        contact_a.id = `1-phone`.contact_id
                              left join civicrm_location_type `1-location_type`
                              ON        ( (
                                                            `1-email`.location_type_id = `1-location_type`.id )
                                                  OR (
                                                            `1-phone`.location_type_id = `1-location_type`.id )
                                                  OR (
                                                            `1-address`.location_type_id = `1-location_type`.id ) )
                              left join civicrm_note
                              ON        (
                                                  civicrm_note.entity_table = 'civicrm_contact'
                                                  AND contact_a.id = civicrm_note.entity_id )
                              left join civicrm_website `website--url`
                              ON        (
                                                  `website--url`.contact_id = contact_a.id
                                                  AND `website--url`.website_type_id = )
                              WHERE     (
                                                  contact_a.id = '2968' )
                                        AND (
                                                  contact_a.is_deleted = 0 )
                              ORDER BY  `contact_a`.`sort_name` ASC,
                                        `contact_a`.`id` [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 ') WHERE ( contact_a.id = '2968' ) AND (contact_a.is_deleted = 0) ORDER BY ' AT line 7] [TYPE] => db_error [user_info] =>
                            SELECT    contact_a.id                                           AS contact_id,
                                      contact_a.gender_id                                    AS `gender_id`,
                                      contact_a.first_name                                   AS `first_name`,
                                      contact_a.last_name                                    AS `last_name`,
                                      civicrm_note.id                                        AS note_id,
                                      civicrm_note.note                                      AS `note`,
                                      `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`,
                                      `1-phone`.id                                           AS `1-phone_id`,
                                      IF ( `1-phone`.is_primary = 1, `1-phone`.phone, NULL ) AS `1-phone`,
                                      `1-address`.id                                         AS `1-address_id`,
                                      `1-address`.state_province_id                          AS `1-state_province_id`,
                                      `1-address`.street_address                             AS `1-street_address`,
                                      `1-address`.postal_code                                AS `1-postal_code`,
                                      `1-address`.city                                       AS `1-city`,
                                      `website--url`.id                                      AS `website--url_id`,
                                      `website--url`.url                                     AS `website--url`,
                                      `website--url`.website_type_id                         AS `website--website_type_id`
                            FROM      civicrm_contact contact_a
                            left join civicrm_address `1-address`
                            ON        (
                                                `1-address`.contact_id = contact_a.id
                                                AND `1-address`.is_primary = 1 )
                            left join civicrm_email `1-email`
                            ON        contact_a.id = `1-email`.contact_id
                                      AND `1-email`.is_primary = 1
                            left join civicrm_phone `1-phone`
                            ON        contact_a.id = `1-phone`.contact_id
                            left join civicrm_location_type `1-location_type`
                            ON        ( (
                                                          `1-email`.location_type_id = `1-location_type`.id )
                                                OR (
                                                          `1-phone`.location_type_id = `1-location_type`.id )
                                                OR (
                                                          `1-address`.location_type_id = `1-location_type`.id ) )
                            left join civicrm_note
                            ON        (
                                                civicrm_note.entity_table = 'civicrm_contact'
                                                AND contact_a.id = civicrm_note.entity_id )
                            left join civicrm_website `website--url`
                            ON        (
                                                `website--url`.contact_id = contact_a.id
                                                AND `website--url`.website_type_id = )
                            WHERE     (
                                                contact_a.id = '2968' )
                                      AND (
                                                contact_a.is_deleted = 0 )
                            ORDER BY  `contact_a`.`sort_name` ASC,
                                      `contact_a`.`id` [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 ') WHERE ( contact_a.id = '2968' ) AND (contact_a.is_deleted = 0) ORDER BY ' AT line 7] [to_string] => [db_error: MESSAGE = "DB Error: syntax error" code = -2 MODE = callback callback = crm_core_error::handle prefix = "" info = "SELECT contact_a.id as contact_id, contact_a.gender_id as `gender_id`, contact_a.first_name as `first_name`, contact_a.last_name as `last_name`, civicrm_note.id as note_id, civicrm_note.note as `note`, `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`, `1 - phone`.id as `1 - phone_id`, IF (`1 - phone`.is_primary = 1, `1 - phone`.phone, NULL) as `1 - phone`, `1 - address`.id as `1 - address_id`, `1 - address`.state_province_id as `1 - state_province_id`, `1 - address`.street_address as `1 - street_address`, `1 - address`.postal_code as `1 - postal_code`, `1 - address`.city as `1 - city`, `website--url`.id as `website--url_id`, `website--url`.url as `website--url`, `website--url`.website_type_id as `website--website_type_id` FROM civicrm_contact contact_a LEFT JOIN civicrm_address `1-address` ON (`1-address`.contact_id = contact_a.id AND `1-address`.is_primary = 1) LEFT JOIN civicrm_email `1-emai] )

Any ideas how to fix this?

Thx

0

You should test against the latest release (4.7.24) - it is likely fixed there

1

This seems to be related to an incorrect website join in the query. Is there any website field in your custom profile?

If yes, you can try disabling/deleting it and check if you still find an issue.

You can also raise a bug in JIRA with some info of replicating the same.

1
  • thx, disabling the website field fixed the error!
    – Moe
    Sep 10 '17 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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