Server gone away error

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
User avatar
Noxwizard
Support Team Leader
Support Team Leader
Posts: 10456
Joined: Mon Jun 27, 2005 8:41 pm
Location: Texas, USA
Name: Patrick Webster
Contact:

Re: Server gone away error

Post by Noxwizard »

lochness wrote:
Mon Nov 09, 2020 8:11 am
I got a reply to the first of my bug reports, the one I filed after mariadb crashed.
I would advise you not to use MariaDB 10.5.7 due to this bug. Basically, any InnoDB data file may become corrupted if one of the InnoDB tables contains an indexed virtual column, or a table defines a unique index on a {{BLOB}} or {{TEXT}} column. Please use 10.5.6 until 10.5.8 is available, or be prepared to reinitialize your database again.
Unless you have an extension which added or changed an index or added a new table, phpBB by default does not use BLOB or TEXT fields as part of an index and does not make use of virtual columns.
[Support Template] - [Read Before Posting] - [phpBB Knowledge Base]
Do not contact me for private support, please share the question in our forums.
sakm
Registered User
Posts: 652
Joined: Sun Jan 21, 2007 8:14 pm
Location: Hull, uk
Name: Stu
Contact:

Re: Server gone away error

Post by sakm »

lochness wrote:
Mon Nov 09, 2020 8:11 am
I got a reply to the first of my bug reports, the one I filed after mariadb crashed.
Does this help in anyway?

https://jira.mariadb.org/browse/MDEV-24 ... ent-171838

https://jira.mariadb.org/browse/MDEV-24117

They are claiming the issue is fixed but I am reluctant to try it at the moment


Over to you :lol:
danielgblack
Registered User
Posts: 4
Joined: Wed Nov 11, 2020 9:40 pm

Re: Server gone away error

Post by danielgblack »

MariaDB 10.5.8, 10.4.17, 10.3.27, and 10.2.36 is now available that corrects the excessive memory usage by long list of IN parameters that is presumed to be the cause of this error.

Apologies from the MariaDB community.

We wish you a happy upgrade and continued stability.
sakm
Registered User
Posts: 652
Joined: Sun Jan 21, 2007 8:14 pm
Location: Hull, uk
Name: Stu
Contact:

Re: Server gone away error

Post by sakm »

danielgblack wrote:
Wed Nov 11, 2020 9:45 pm
MariaDB 10.5.8, 10.4.17, 10.3.27, and 10.2.36 is now available that corrects the excessive memory usage by long list of IN parameters that is presumed to be the cause of this error.

Apologies from the MariaDB community.

We wish you a happy upgrade and continued stability.
I will speak to my host about trying it again and see how we get on!

I will know instantly if it works or not but won't be trying it until at least the weekend
HB
Registered User
Posts: 153
Joined: Mon May 16, 2005 9:30 pm
Contact:

Re: Server gone away error

Post by HB »

sakm wrote:
Wed Nov 04, 2020 6:52 pm
Hi all I keep getting server gone away errors since today but it only seems to be on some certain topics and forums
I've been seeing the same problem; I'm also running MariaDB 10.5.7 and just updated to 10.5.8. That said, while debugging, I noticed that some phpBB query statements have the potential to pass in huge queries. For example, in include/functions.php:

Code: Select all

		// Mark all post/quote notifications read for this user in this forum
		$topic_ids = array();
		$sql = 'SELECT topic_id
			FROM ' . TOPICS_TABLE . '
			WHERE ' . $db->sql_in_set('forum_id', $forum_id);
		$result = $db->sql_query($sql);
		while ($row = $db->sql_fetchrow($result))
		{
			$topic_ids[] = $row['topic_id'];
		}
		$db->sql_freeresult($result);

		$phpbb_notifications->mark_notifications_by_parent(array(
			'notification.type.quote',
			'notification.type.bookmark',
			'notification.type.post',
			'notification.type.approve_post',
			'notification.type.forum',
		), $topic_ids, $user->data['user_id'], $post_time);
Following the code above, you'll find that it generates a DELETE with an unbounded number of topic IDs in phpbb/notification/method/email.php:

Code: Select all

	public function mark_notifications_by_parent($notification_type_id, $item_parent_id, $user_id, $time = false, $mark_read = true)
	{
		$sql = 'DELETE FROM ' . $this->notification_emails_table . '
			WHERE ' . ($notification_type_id !== false ? $this->db->sql_in_set('notification_type_id', $notification_type_id) : '1=1') .
			($user_id !== false ? ' AND ' . $this->db->sql_in_set('user_id', $user_id) : '') .
			($item_parent_id !== false ? ' AND ' . $this->db->sql_in_set('item_parent_id', $item_parent_id, false, true) : '');
		$this->db->sql_query($sql);
	}
This would work fine in a small forum, but what if a forum contained 10s of thousands of topics? I saw the same errors in mark_notifications you reported and suspect they have the same cause. The phpBB code should check for the topic array count and if it's > some threshold, break the DELETE calls into chucks so the database isn't passed a multi-megabyte query statement.
Dan Kehn
lochness
Registered User
Posts: 84
Joined: Tue Aug 07, 2007 12:04 pm

Re: Server gone away error

Post by lochness »

sakm wrote:
Wed Nov 11, 2020 9:44 pm
Over to you :lol:
I’ll wait for the weekend as well :lol:

Anyway, thanks HB for confirming the upgrade is working as intended :D
danielgblack
Registered User
Posts: 4
Joined: Wed Nov 11, 2020 9:40 pm

Re: Server gone away error

Post by danielgblack »

HB wrote:
Wed Nov 11, 2020 11:48 pm
sakm wrote:
Wed Nov 04, 2020 6:52 pm
Hi all I keep getting server gone away errors since today but it only seems to be on some certain topics and forums
I've been seeing the same problem; I'm also running MariaDB 10.5.7 and just updated to 10.5.8. That said, while debugging, I noticed that some phpBB query statements have the potential to pass in huge queries.
I noticed some of these too in MariaDB issue MDEV-24163.

It looks like some analysis of the slow queries might identify some better indexes.

I did get a bit lost in the retrieval and delete operations being separate but a way to combine these might be:

* DELETE ... RETURNING (maybe a bit MariaDB specific for code base)
* A temporary table containing a list of primary keys that is used as a JOIN in the delete step.
* More batching as suggested
lochness
Registered User
Posts: 84
Joined: Tue Aug 07, 2007 12:04 pm

Re: Server gone away error

Post by lochness »

danielgblack wrote:
Mon Nov 16, 2020 12:18 am
HB wrote:
Wed Nov 11, 2020 11:48 pm
sakm wrote:
Wed Nov 04, 2020 6:52 pm
Hi all I keep getting server gone away errors since today but it only seems to be on some certain topics and forums
I've been seeing the same problem; I'm also running MariaDB 10.5.7 and just updated to 10.5.8. That said, while debugging, I noticed that some phpBB query statements have the potential to pass in huge queries.
I noticed some of these too in MariaDB issue MDEV-24163.

It looks like some analysis of the slow queries might identify some better indexes.

I did get a bit lost in the retrieval and delete operations being separate but a way to combine these might be:

* DELETE ... RETURNING (maybe a bit MariaDB specific for code base)
* A temporary table containing a list of primary keys that is used as a JOIN in the delete step.
* More batching as suggested
The new mariadb version is causing issues again.
User avatar
P_I
Registered User
Posts: 1565
Joined: Tue Mar 01, 2011 8:35 pm
Location: Western Canada
Contact:

Re: Server gone away error

Post by P_I »

lochness wrote:
Sat Feb 27, 2021 12:22 pm
The new mariadb version is causing issues again.
See viewtopic.php?f=661&t=2585816
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
lochness
Registered User
Posts: 84
Joined: Tue Aug 07, 2007 12:04 pm

Re: Server gone away error

Post by lochness »

Thank you very much :)
Post Reply

Return to “[3.3.x] Support Forum”