1

Context : Wordpress 4.9.8–fr_FR, PHP version 7.0.22, libmysql - mysqlnd 5.0

Hello When trying to run the upgrade script from 5.0.1 to 5.4.1, I got this issue :

    Aug 31 13:51:25  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => 
        UPDATE civicrm_msg_template SET msg_text = '{assign var=\"greeting\" value=\"{contact.email_greeting}\"}{if $greeting}{$greeting},{/if}\n{if $receipt_text}\n{$receipt_text}\n{/if}\n{if $is_pay_later}\n\n===========================================================\n{$pay_later_receipt}\n===========================================================\n{else}\n\n{ts}Please print this receipt for your records.{/ts}\n{/if}\n\n{if $membership_assign && !$useForMember}\n===========================================================\n{ts}Membership Information{/ts}\n\n===========================================================\n{ts}Membership Type{/ts}: {$membership_name}\n{if $mem_start_date}{ts}Membership Start Date{/ts}: {$mem_start_date|crmDate}\n{/if}\n{if $mem_end_date}{ts}Membership End Date{/ts}: {$mem_end_date|crmDate}\n{/if}\n\n{/if}\n{if $amount}\n===========================================================\n{ts}Membership Fee{/ts}\n\n===========================================================\n{if !$useForMember && $membership_amount && $is_quick_config}\n{ts 1=$membership_name}%1 Membership{/ts}: {$membership_amount|crmMoney}\n{if $amount && !$is_separate_payment }\n{ts}Contribution Amount{/ts}: {$amount|crmMoney}\n-------------------------------------------\n{ts}Total{/ts}: {$amount+$membership_amount|crmMoney}\n{/if}\n{elseif !$useForMember && $lineItem and $priceSetID & !$is_quick_config}\n{foreach from=$lineItem item=value key=priceset}\n---------------------------------------------------------\n{capture assign=ts_item}{ts}Item{/ts}{/capture}\n{capture assign=ts_qty}{ts}Qty{/ts}{/capture}\n{capture assign=ts_each}{ts}Each{/ts}{/capture}\n{capture assign=ts_total}{ts}Total{/ts}{/capture}\n{$ts_item|string_format:\"%-30s\"} {$ts_qty|string_format:\"%5s\"} {$ts_each|string_format:\"%10s\"} {$ts_total|string_format:\"%10s\"}\n----------------------------------------------------------\n{foreach from=$value item=line}\n{$line.description|truncate:30:\"...\"|string_format:\"%-30s\"} {$line.qty|string_format:\"%5s\"} ... \n{/foreach}\n{/if}\n' WHERE id IN () [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 ')' at line 1]"]
)

Aug 31 13:51:25  [info] $backTrace = #0 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...")
#3 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...")
#4 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...", "DB_Error", TRUE)
#5 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, "\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...", "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...")
#9 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common->query("\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...")
#10 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...")
#11 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...")
#12 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1394): CRM_Core_DAO->query("\n        UPDATE civicrm_msg_template SET msg_text = '{assign var=\\"greeting...", TRUE)
#13 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Incremental/MessageTemplates.php(149): CRM_Core_DAO::executeQuery("\n        UPDATE civicrm_msg_template SET msg_text = %1 WHERE id IN ()", (Array:1))
#14 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Incremental/General.php(143): CRM_Upgrade_Incremental_MessageTemplates->updateTemplates()
#15 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Form.php(772): CRM_Upgrade_Incremental_General::updateMessageTemplate("<br />Les exemplaires par défaut des modèles de message répertoriés ci-dessou...", "5.4.alpha1")
#16 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php(111): CRM_Upgrade_Form->setPreUpgradeMessage("<br />Les exemplaires par défaut des modèles de message répertoriés ci-dessou...", "5.0.1", "5.4.1")
#17 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php(73): CRM_Upgrade_Page_Upgrade->runIntro()
#18 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Upgrade_Page_Upgrade->run((Array:2), NULL)
#19 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#20 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
#21 /var/www/html/civicrm/wp-content/plugins/civicrm/civicrm.php(1246): CRM_Core_Invoke::invoke((Array:2))
#22 /var/www/html/civicrm/wp-includes/class-wp-hook.php(286): CiviCRM_For_WordPress->invoke("")
#23 /var/www/html/civicrm/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters("", (Array:1))
#24 /var/www/html/civicrm/wp-includes/plugin.php(453): WP_Hook->do_action((Array:1))
#25 /var/www/html/civicrm/wp-admin/admin.php(224): do_action("toplevel_page_CiviCRM")
#26 {main}

I really don't know how to move forward and run the upgrade script. Any help will be kindly accepted Thanks

DB view, msg_template table UI view civicrm_option_value table

  • looks like the message template in your system is missing – Pradeep Nayak Aug 31 '18 at 12:12
  • Thanks for jumping in, how could I fix that ? Do you have a sql query that I could run ? – ITSupport ERELOS Aug 31 '18 at 12:24
2

This seems to be fixed in 5.6.0. If you have to upgrade to 5.5.1 or have problems so need to do it incrementally, at the suggestion of Aidan@Squiffle commenting out the UPDATE civicrm_msg_template SET msg_{$template['type']} command at the bottom of CiviCRM/CRM/Upgrade/Incremental/MessageTemplates.php seemed to work.

  • Thank you very much ! I managed upgrading by commenting the recommended lines in CiviCRM/CRM/Upgrade/Incremental/MessageTemplates.php – ITSupport ERELOS Dec 28 '18 at 14:33
  • Following the upgrade to 5.4.1, I upgraded to 5.8.2 but got the same issue than before -> so doesn't seem to be fixed by 5.6. I had to comment the same lines in MessageTemplates.php and the upgrade successfully moved forward. – ITSupport ERELOS Dec 28 '18 at 15:55
  • Glad it's now working. To dble check, are you saying that having fixed it for 5.4.1 to 5.6.0, you then had to correct it again when you upgraded to 5.8.2? Or just when you went 5.4.1 to 5.8.2? – Nic Dec 29 '18 at 13:28
  • I went directly to 5.8.2 from 5.4.1 :( did get the opportunity to check with 5.6 - Thanks again ! – ITSupport ERELOS Dec 30 '18 at 17:18
1

I'm sorry not to post in the comments but I do not have 50 points yet.

@Nic , can you explain your answer better? from version 5.4 exists the file MessageTemplates.php but (in WordPress) however does not update from 5.3.2 to 5.4

@Tellie , did you solve the problem?

  • Just seen this - sorry for the delay. If you've not fixed it yet - do you have the same problem upgrading to 5.6 or 5.7? If so then maybe commenting out that command should help? I've not tried on WP tho, sorry, just Joomla. – Nic Nov 29 '18 at 22:15
0

I guess your system has either missing 'Event Online Receipt' message template or the template is not reserved. Check by running below query if it returns result.

SELECT id FROM civicrm_msg_template WHERE workflow_id IN (SELECT MAX(id) as id FROM civicrm_option_value WHERE name = 'event_online_receipt') AND is_reserved = 1
  • Indeed no result return, how should i fix that ? Thanks ! – ITSupport ERELOS Aug 31 '18 at 13:10
  • This is one also return no result : SELECT id FROM civicrm_msg_template WHERE workflow_id IN (SELECT MAX(id) as id FROM civicrm_option_value WHERE name = 'event_offline_receipt') AND is_reserved = 1 ;(event_offline_receipt) – ITSupport ERELOS Aug 31 '18 at 13:44
  • Can you try running the query by running without is_reserved = 1 – Pradeep Nayak Aug 31 '18 at 13:47
  • SELECT id FROM civicrm_msg_template WHERE workflow_id IN (SELECT MAX(id) as id FROM civicrm_option_value WHERE name = 'event_online_receipt'); no result – ITSupport ERELOS Aug 31 '18 at 15:39
  • SELECT id FROM civicrm_msg_template WHERE workflow_id IN (SELECT MAX(id) as id FROM civicrm_option_value WHERE name = 'event_offline_receipt'); no result – ITSupport ERELOS Aug 31 '18 at 15:39
0

Have you ever found an fix for this issue? I got the exact same problem when upgrading from 5.3.2 to 5.6.0 or for that case any higher version then 5.3.2. I think it's a bug in the upgrader because I checked the civicrm_msg_template table to the original SQL schema and there are no differences.

  • Hi Tallie,Comments should be used if what you are posting is not an answer to the question being asked. You might want to delete your answer and post it as a comment to avoid down votes. – Tamar Meir - Cividesk Oct 5 '18 at 0:04
  • If StackExchange would allow me to, I get the notification 'You must have 50 reputation to comment'.... – Tallie Oct 9 '18 at 7:23

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.