CiviCRM Community Forums (archive)

When I enable logging I receive this error, and it stops the addition of logging triggers. So only 25% of the tables have logging triggers. How to I alter the trigger before it is added?

I tried disabling logging, fixing the trigger in phpmyadmin and then re-enabling it, but I get mysql error 1235, "This version of MySQL doesn't yet support multiple triggers with the same action time and event for one table"




Database Error Code: 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 ') AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THE' at line 1, 1064
Additional Details:
Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

[code => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => CREATE TRIGGER civicrm_events_in_carts_after_update after update ON civicrm_events_in_carts FOR EACH ROW BEGIN  IF ( () AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THEN INSERT INTO `civinbla_logs`.log_civicrm_events_in_carts (log_conn_id, log_user_id, log_action) VALUES (CONNECTION_ID(), @civicrm_user_id, 'update');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 ') AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THE' at line 1]
    [type] => DB_Error
    [user_info] => CREATE TRIGGER civicrm_events_in_carts_after_update after update ON civicrm_events_in_carts FOR EACH ROW BEGIN  IF ( () AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THEN INSERT INTO `civinbla_logs`.log_civicrm_events_in_carts (log_conn_id, log_user_id, log_action) VALUES (CONNECTION_ID(), @civicrm_user_id, 'update');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 ') AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THE' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TRIGGER civicrm_events_in_carts_after_update after update ON civicrm_events_in_carts FOR EACH ROW BEGIN  IF ( () AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THEN INSERT INTO `civinbla_logs`.log_civicrm_events_in_carts (log_conn_id, log_user_id, log_action) VALUES (CONNECTION_ID(), @civicrm_user_id, 'update');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 ') AND ( @civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0 ) ) THE' at line 1]"]
)


*I would have selected the error text as /code/ but I received the message "Sorry, you are not allowed to post external links." I assume this is a spam filter on new members?

Apologies, I forgot to mention that I am working with multiple languages. CiviCRM does not support logging and multiple languages.

I found the file /civicrm/CRM/Core/DAO.php which calls the functions rebuildTriggers() and createTriggers(). The first function, drops all triggers on the tables and the second replaces them with logging triggers. A simple edit allowed me to prevent the function from building the trigger causing the error, such that it would continue on and build it for the rest. A quick fix to allow me to continue. I looked through phpmyadmin and all of the triggers were created except those I excluded.

I ran into a new error, one that was present before, I just did not notice it. When logging is enabled, it is supposed to create a new _log table for each table in the database. However, this is not the case as some _log tables are missing. There are 208 tables in the database, but only 136 of them have a respective _log table.


EDIT: I am looking for the file that creates the triggers for multilingual support as well. I would like to alter the createTriggers() function such that the triggers support both logging and multilingualism.

Thanks!

I compared all of the triggers prior to and after enabling logging, not a single trigger was lost by enabling the feature! Now back to solving the missing _log tables and error with the triggers on the event_in_carts table.

EDIT:Database Tables without log_ counterpart

%_log
%_cache

I suspect that it is normal to not have a log_ table for these.

The interesting tables that are missing are those created due to multilingual support. When it is enabled with two languages, in this case English(Canada) and French(Canada), the following tables would exist.

dashboard
dashboard_en_CA
dashboard_fr_CA

However, the only log table created is log_dashboard_en_CA and the only table with logging triggers is dashboard, which writes to the nonexsistent log_dashboard table.

I have made the changes manually for now and it appears that I have fully functional logging. I will look into changing the files to automate it.