CiviCRM DB Error: syntax error during upgrade in Wordpress

Question

I have tried upgrading to several different versions (5.14.1 up to 5.28) and every time I run the database upgrade, I get a DB Error: syntax error message and can't figure out why.

Here is an extract from my log file.

Any help would be most appreciated.

Aug 08 09:06:01 [error] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle )

[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => DROP TRIGGER IF EXISTS civicrm_group_contact_05Feb20-2049_before_insert [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2049_before_insert' at line 1]
[type] => DB_Error
[user_info] => DROP TRIGGER IF EXISTS civicrm_group_contact_05Feb20-2049_before_insert [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2049_before_insert' at line 1]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="DROP TRIGGER IF EXISTS civicrm_group_contact_05Feb20-2049_before_insert [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2049_before_insert' at line 1]"]

)

Answer 1

My theory is you have a table called 'civicrm_group_contact_05Feb20-2049' in the database, maybe a backup whilst you were investigating an issue or something? If you drop this table prior to commencing the upgrade you'll probably have better luck.

Note that the reason its an issue is that CiviCRM will add audit log triggers to any table prefixed with 'civicrm'. So its trying to add/drop a trigger from that table and falling over. I'm not 100% sure on the reason, looks like trigger names dont support hyphens perhaps. In this case dropping or renaming the table (prefixing it with bkp_ or something) will stop the trigger drop/create failing.