Server gone away error

[Noxwizard]

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.

[sakm]

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

[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]

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]

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.

[lochness]

Over to you

I’ll wait for the weekend as well

Anyway, thanks HB for confirming the upgrade is working as intended

[danielgblack]

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]

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.

[P_I]

The new mariadb version is causing issues again.

See viewtopic.php?f=661&t=2585816

[lochness]

Thank you very much

[TomaGo]

Hello HB,

I think i have an issue (slow posting) beacause of the bad optimization described in your message on my large forum.
viewtopic.php?f=661&t=2604271

Do you have a solution to optimize this querry when posting ?

Thanks !

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.

[HB]

I think i have an issue (slow posting) because of the bad optimization described in your message on my large forum.

You could try pruning entries from the notifications table. I wrote some general housekeeping code and it includes this:

Code: Select all

    $sql = "DELETE FROM " . NOTIFICATIONS_TABLE . " WHERE notification_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))";
    $db->sql_query($sql);

You can change 180 to however many days you want to keep around notifications. If this doesn't help, then I suggest starting a new topic as your question isn't about the "server gone" problem of this thread.

[TomaGo]

Thanks, but even with an empty notification table it takes 10/12 second to post a message in big subforums.

My dedicade post is open here : viewtopic.php?f=661&t=2604271