Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-20443

SQL syntax error creating logging triggers if column name is reserved word

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7.18, 4.6.27
    • Fix Version/s: 4.7.23, 4.6.32
    • Component/s: Core CiviCRM
    • Labels:
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      If the Civi database has a table with a column name that is a MySQL reserved word, then enabling advanced logging leads to a SQL syntax error when creating triggers. Core Civi tables do not provoke the problem but some extensions do, e.g. [CiviREBUX|https://civicrm.org/extensions/civirebux-native-report-building-extension. |https://civicrm.org/extensions/civirebux-native-report-building-extension].]If logging is already enabled, then the error occurs when trying to enable the extension - see https://civicrm.stackexchange.com/questions/15375/has-anyone-gotten-civirebux-working .

      SQL error:

      CREATE TRIGGER civicrm_civirebux_configuration_after_insert after insert ON civicrm_civirebux_configuration FOR EACH ROW BEGINĀ  IF ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) THEN INSERT INTO log_civicrm_civirebux_configuration (id, name, renderer, aggregator, vals, rows, cols, time, desc, type, log_conn_id, log_user_id, log_action) VALUES ( NEW.id, NEW.name, NEW.renderer, NEW.aggregator, NEW.vals, NEW.rows, NEW.cols, NEW.time, NEW.desc, NEW.type, COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17)), @civicrm_user_id, 'insert'); END IF; END [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 'desc, type, log_conn_id, log_user_id, log_action) VALUES ( NEW.id, NEW.name, NEW' at line 1]

      The error occurs in this case due to a column named `desc` in table `civicrm_civirebux_configuration`.

      I have successfully tested a fix on 4.6.27 . Will test against dmaster & create a PR.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              davej Dave Jenkins
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: