Fixed Upgrade taking many hours (phrase import)

[ZuluView]

So far it's been running for 4 hours and I'm at "Importing... Master data (Phrases: 4.4%)" and looks to be taking around an hour for each %.

Is this something others have experienced?

Did the upgrade via the button in the admin panel.

 

[Mike]

If I’m correct, your server just updated its MariaDB version didn’t it? I think there might be a regression in this version with a particular query that is used by the phrase system here (which has a very large list of phrase names in an IN clause).

I’m not sure if it’s possible to temporarily roll back the MariaDB change, to help confirm this. I did workaround this for another customer a recently, though at the time it appeared to be server-specific. We will likely need to change our approach for now, but if this is a performance regression in MariaDB, we will likely need to gather a reduced test case to report to them.

I’m going to move this to bugs for now. If you’re stuck with this and can’t/aren’t comfortable with server changes, let me know and I’ll see if I can pull a quick and dirty fix.

 

[Mike]

Note that this may manifest as a “server has gone away” error as well. It isn’t totally consistent.

 

[phucvandinh]

I did have a similar situation with MariaDB 10.4.16 and PHP 7.4.12. Reverse back to 10.4.15 does solve the issue.

 

[Mike]

Quick and dirty fix should be as follows.

In src/XF/AddOn/DataType/Phrase.php, find:

$existing = $this->finder()->where('title', $ids)->where('language_id', 0)->keyedBy('title')->fetch();

Replace with:

//$existing = $this->finder()->where('title', $ids)->where('language_id', 0)->keyedBy('title')->fetch();

---

In the same file, then find: (13 lines below)

$entity = $existing[$id] ?? $this->create();

Replace with:

$existingPhrase = $this->em->findOne('XF:phrase', [
   'title' => $entry['title'],
   'language_id' => 0
]);

$entity = $existingPhrase ?? $this->create();

 

[Mike]

This issue is being tracked in MariaDB here:

[MDEV-24117] Memory management problem in statistics state for queries that have a large field IN (values) part - Jira

jira.mariadb.org

 

[djbaxter]

Yes this whole MariaDB thing is a mess. Still waiting for a new FIXED version to be released.

 

[XF Bug Bot]

Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.2).

Change log:

Break the phrase import query into chunks to avoid a MariaDB performance regression.

There may be a delay before changes are rolled out to the XenForo Community.

 

[djbaxter]

Just to clarify, my comment about fixing the MariaDB mess was aimed at MariaDB, NOT Xenforo.

 

[wedgar]

Just to clarify, my comment about fixing the MariaDB mess was aimed at MariaDB, NOT Xenforo.

Figured as much...

 

[antonino]

Ιn my case and in the second part of your solution i see this in line 69...

$entity = isset($existing[$id]) ? $existing[$id] : $this->create();

Do i have to make the same changes you suggest?

 

[Jared26]

Quick and dirty fix should be as follows.

In src/XF/AddOn/DataType/Phrase.php, find:

$existing = $this->finder()->where('title', $ids)->where('language_id', 0)->keyedBy('title')->fetch();

Replace with:

//$existing = $this->finder()->where('title', $ids)->where('language_id', 0)->keyedBy('title')->fetch();

---

In the same file, then find: (13 lines below)

$entity = $existing[$id] ?? $this->create();

Replace with:

$existingPhrase = $this->em->findOne('XF:phrase', [
   'title' => $entry['title'],
   'language_id' => 0
]);

$entity = $existingPhrase ?? $this->create();

Just wanted to thank you and say that helped me out of a tough spot.

 

[Mike]

Ιn my case and in the second part of your solution i see this in line 69...

$entity = isset($existing[$id]) ? $existing[$id] : $this->create();

Do i have to make the same changes you suggest?

Replacing that line with the one I mentioned should be fine (provided you're running PHP7, which you likely would be).

The changes I listed are based on the 2.2 version. The line you're showing would be from 2.1.

 

[Mike]

Oh, just to follow up, this is also fixed in today's emergency MariaDB releases:

Emergency Release of MariaDB 10.5.8, 10.4.17, 10.3.27, and 10.2.36 is now available - MariaDB.org

Why do we release MariaDB again only a week after the 10.5.7, 10.4.16, etc? What's the emergency?

mariadb.org

So updating to the latest version (of whatever branch you're using) should also resolve this.

 

[Wildcat Media]

10.3.26 killed my XenForo and WordPress sites a few days ago when I ran my upgrades--I was able to roll back to 10.3.25 to get them back online. I saw their dismissive note abut it in the emergency release:

But regardless of where the bug is, from the user point of view it’s MariaDB upgrade that broke their applications. And they cannot always move to PHP 7.3 or wait for Oracle to fix connectors.

It was broken in PHP 7.4 also--I was already running PHP 7.3 so that shouldn't have been affected, but it was, and I tried upgrading to 7.4 nonetheless. No change. Just dropping this here in case anyone encounters this in the near future.

 

[djbaxter]

cPanel is automatically rolling out MariaDB 10.3.27 which fixes the problem if your settings allow for auto updates. Mine was updated Tuesday night (3 days ago now).