Page MenuHomePhabricator

Fix database query error in CiviCRM (phpQueryObject.php on line 1040: "Array and string offset access syntax with curly braces is deprecated")
Closed, ResolvedPublic

Description

Opening a new CiviCRM mailing list with a Mosaico template causes a crash in CiviCRM.

  • Mailing -> New Mailing
  • Proceed to mailing template (I am using Mosaico) and try to send a Test.

Here the console log:

[Thu Mar 03 10:12:24.788770 2022] [proxy_fcgi:error] [pid 293678] [client 46.234.221.246:47640] AH01071: Got error 'PHP message: PHP Deprecated:  Array and string offset access syntax with curly braces is deprecated in /var/www/crm/testing/crmdev.wikimedia.it/vendor/electrolinux/phpquery/phpQuery/phpQuery/phpQueryObject.php on line 1040PHP message: PHP Deprecated:  Array and string offset access syntax with curly braces is deprecated in /var/www/crm/testing/crmdev.wikimedia.it/vendor/electrolinux/phpquery/phpQuery/phpQuery/phpQueryObject.php on line 1055', referer: 
https://crmdev.wikimedia.it/civicrm/a/

It seems caused by event chain:

  1. a warning raised by a minor problem in the library called phpQueryObject.php
  2. the warning is then elevated as error
  3. that error is then triggering a rollback
  4. that rollback is then causing an exception (nested bug? SAVEPOINT civi_0 does not exist)

Upstream bug:

https://lab.civicrm.org/dev/core/-/issues/2198

Workaround:

https://lab.civicrm.org/dev/core/-/issues/2198#note_59381

Suggested fix:

https://lab.civicrm.org/dev/core/-/issues/2198#note_74562

Details

Due Date
Apr 26 2022, 10:00 PM
Other Assignee
valerio.bozzolan

Event Timeline

valerio.bozzolan created this task.

ho applicato la patch anche nella versione aggiornata 5.47.3 commentando la riga 223 del file ./vendor/civicrm/civicrm-core/Civi/API/Subscriber/DynamicFKAuthorization.php

@valerio.bozzolan
sono diversi che stanno segnando questa issue su https://lab.civicrm.org/dev/core/-/issues/2198
Ma finora nessuno ha trovato la causa e la soluzione.
A me sembra che dipende da Mariadb perché ho un'installazione similare su Drupa e mysql 7 e non c'è questo problema.

L'errore è questo:

"ROLLBACK TO SAVEPOINT civi_0 [nativecode=1305 ** SAVEPOINT civi_0 does not exist]"

E questo è il log delle query:

May 02 08:51:59  [debug] $Query = SAVEPOINT civi_0

May 02 08:51:59  [info]  QUERY DONE IN 0.000124  seconds. No further information is available for this type of query

May 02 08:51:59  [debug] $Query =
    SELECT *
    FROM civicrm_data_processor_output o
    INNER JOIN civicrm_data_processor p ON o.data_processor_id = p.id
    WHERE p.is_active = 1
    AND (LOWER(api_entity) = LOWER('Mailing') OR LOWER(api_entity) = LOWER('mailing'))
    AND (
      LOWER(api_action) = LOWER('get') OR LOWER(api_count_action) = LOWER('get')
      OR LOWER(api_action) = LOWER('Get') OR LOWER(api_count_action) = LOWER('Get')
    )

May 02 08:51:59  [info]  QUERY DONE IN 0.000403  seconds. Result is 0 rows by 19 columns.

May 02 08:51:59  [debug] $Query = SELECT path, data, UNIX_TIMESTAMP(expired_date) as expires FROM civicrm_cache WHERE group_name = "contactTypes" AND path = "all_it_IT"

May 02 08:51:59  [info]  QUERY DONE IN 0.000389  seconds. Result is 1 rows by 3 columns.

May 02 08:51:59  [debug] $Query =
SELECT  v.label as label ,v.value as value, v.grouping as `grouping`
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'cg_extend_objects'
  AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviCampaign")) )  ORDER BY v.weight


May 02 08:51:59  [info]  QUERY DONE IN 0.000570  seconds. Result is 2 rows by 3 columns.

May 02 08:51:59  [debug] $Query = SELECT path, data, UNIX_TIMESTAMP(expired_date) as expires FROM civicrm_cache WHERE group_name = "contact-20fields" AND path = "custom importableFields Mailing_0_0_0_0__0__0__0_0__1_7acce319"

May 02 08:51:59  [info]  QUERY DONE IN 0.000336  seconds. Result is 0 rows by 3 columns.

May 02 08:51:59  [debug] $Query = SELECT id FROM civicrm_custom_field WHERE html_type = "RichTextEditor"

May 02 08:51:59  [info]  QUERY DONE IN 0.000300  seconds. Result is 0 rows by 1 columns.

May 02 08:51:59  [debug] $Query = SHOW TABLE STATUS LIKE 'civicrm_contact'

May 02 08:51:59  [info]  QUERY DONE IN 0.000681  seconds. Result is 1 rows by 20 columns.

May 02 08:51:59  [debug] $Query = SELECT a.id as `id`, a.domain_id as `domain_id`, a.header_id as `header_id`, a.footer_id as `footer_id`, a.reply_id as `reply_id`, a.unsubscribe_id as `unsubscribe_id`, a.resubscribe_id as `resubscribe_id`, a.optout_id as `optout_id`, a.name as `name`, a.mailing_type as `mailing_type`, a.from_name as `from_name`, a.from_email as `from_email`, a.replyto_email as `replyto_email`, a.template_type as `template_type`, a.template_options as `template_options`, a.subject as `subject`, a.body_text as `body_text`, a.body_html as `body_html`, a.url_tracking as `url_tracking`, a.forward_replies as `forward_replies`, a.auto_responder as `auto_responder`, a.open_tracking as `open_tracking`, a.is_completed as `is_completed`, a.msg_template_id as `msg_template_id`, a.override_verp as `override_verp`, a.created_id as `created_id`, a.created_date as `created_date`, a.modified_date as `modified_date`, a.scheduled_id as `scheduled_id`, a.scheduled_date as `scheduled_date`, a.approver_id as `approver_id`, a.approval_date as `approval_date`, a.approval_status_id as `approval_status_id`, a.approval_note as `approval_note`, a.is_archived as `is_archived`, a.visibility as `visibility`, a.campaign_id as `campaign_id`, a.dedupe_email as `dedupe_email`, a.sms_provider_id as `sms_provider_id`, a.hash as `hash`, a.location_type_id as `location_type_id`, a.email_selection_method as `email_selection_method`, a.language as `language`
FROM civicrm_mailing a
WHERE (a.id = "13")
LIMIT 25
OFFSET 0


May 02 08:51:59  [info]  QUERY DONE IN 0.000417  seconds. Result is 1 rows by 43 columns.

May 02 08:51:59  [debug] $Query = ROLLBACK TO SAVEPOINT civi_0

May 02 08:51:59  [debug] $Query = COMMIT

May 02 08:51:59  [debug] $Query = /*!40101 SET NAMES utf8 */

May 02 08:51:59  [debug] $Query = SELECT id, name, value, domain_id, contact_id, is_domain, component_id, created_date, created_id
FROM civicrm_setting
WHERE (domain_id = 1) AND (is_domain = 1)


May 02 08:51:59  [info]  QUERY DONE IN 0.000763  seconds. Result is 153 rows by 9 columns.

Hai idee?
Potremmo provare ad installare temporaneamente mysql7 in un container e vedere se il problema si risolve...altro non saprei perché per altri tipi di indagine ci si mette molto tempo.

Inizialmente dicevi che l'errore non era questo...ma io non l'ho trovato. Se invece hai traccia di questo, possiamo fare una patch manuale sul componente esterno e vedere se va.

Da quanto ho capito sembra che viene creata una transazione con un certo nome, e a metà di una transazione di MySQL emerge un warning di PHP (causato dalla recente versione di PHP), ma CiviCRM eleva quel warning ad eccezione, questo fa scatenare una rollback di MySQL, ma quella rollback sembra non essere stata creata con quel nome (causato secondo me da un bug di programmazione di CiviCRM).

A monte di tutto questo c'è sicuramente un bug nella libreria phpquery con PHP recente, che è stato risolto due anni fa (https://github.com/electrolinux/phpquery/pull/17) però CiviCRM usa una versione vecchia di quella libreria del 2013 (https://github.com/electrolinux/phpquery/commit/6cb8afcfe8cd4ce45f2f8c27d561383037c27a3a):

https://lab.civicrm.org/dev/core/-/blob/master/composer.lock

"name": "electrolinux/phpquery",
"version": "0.9.6",
"source": {
    "type": "git",
    "url": "https://github.com/electrolinux/phpquery.git",
    "reference": "6cb8afcfe8cd4ce45f2f8c27d561383037c27a3a"
},

In breve suggerirei di usare Composer per aggiornare electrolinux/phpquery all'ultima versione e vedere cosa succede. Posso provare a farlo prossima settimana.

Non credo che fare un downgrade di MySQL possa cambiare questo problema. Fare un downgrade di PHP invece potrebbe, però lo sconsiglierei. Meglio aggiornare electrolinux/phpquery.

grazie @valerio.bozzolan , provo io ad aggiornare electrolinux/phpquery e ti faccio sapere.

valerio.bozzolan renamed this task from Correggere errore DB in CiviCRM (phpQueryObject.php) to Fix database query error in CiviCRM (phpQueryObject.php on line 1040: "Array and string offset access syntax with curly braces is deprecated").May 17 2022, 8:44 AM
valerio.bozzolan closed this task as Resolved.
valerio.bozzolan updated the task description. (Show Details)
valerio.bozzolan awarded a token.