XF 2.0 Rebuilding Search Index Error

[dash]

I tried to rebuild the search index from the admin panel and get the following error:

An exception occurred: [XF\Db\Exception] MySQL statement prepare error [2006]: MySQL server has gone away in src/XF/Db/AbstractStatement.php on line 212

    XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 174
    XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 36
    XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 46
    XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 74
    XF\Db\AbstractAdapter->query() in src/XF/Session/DbStorage.php at line 57
    XF\Session\DbStorage->writeSession() in src/XF/Session/Session.php at line 215
    XF\Session\Session->save() in src/XF/Admin/App.php at line 84
    XF\Admin\App->complete() in src/XF/App.php at line 1883
    XF\App->run() in src/XF.php at line 328
    XF::runApp() in admin.php at line 13

Any way to correct this?

 

[Ozzy47]

This generally indicates MySQL server connectivity issues or timeouts.

Can generally be solved by changing wait_timeout and max_allowed_packet in my.cnf or similar.

 

[dash]

Just checked my.cnf. There is no mention of wait_timeout or max_allowed_packet. Should these be in my.cnf? What should they be set at?

 

[Ozzy47]

I would suggest these values.

wait_timeout = 28800
max_allowed_packet = 8M

 

[dash]

Added those parameters. Restarted Mysql and when I rebuilt the search index, the same error came up. Error comes up when rebuilding content types: All, Posts or Threads. Works fine for Pages, Profile Posts and Profile Post Comments.

 

[Ozzy47]

Hmmm, that should have worked. Hopefully someone comes along with a different idea.

 

[Mike]

Can you confirm what the max_allowed_packet actually shows up as?

show variables like 'max_allowed_packet';

The only other possibility is that there was a bug triggered within MySQL that caused the connection to be killed. That should likely show up in your MySQL log.

 

[dash]

Can you confirm what the max_allowed_packet actually shows up as?

show variables like 'max_allowed_packet';

The only other possibility is that there was a bug triggered within MySQL that caused the connection to be killed. That should likely show up in your MySQL log.

Hi...I see the following when I run that command.

+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

 

[Mike]

If you applied the commands mentioned in a previous post, then the max_allowed_packet should be higher -- that's 1MB still. Make sure you're applying it to the correct configuration file -- and the correct section of the file, likely under a "[mysqld]" section (and restarting MySQL).

 

[dash]

That worked! I must have originally put the commands in a different configuration file. Thanks!

 

[gxd]

@dash I got this error too. Can you guide me step by step the way you resolse this problem?
Thank you.

 

[dash]

Hi...you need to find the my.cnf file on your server (my server had more than 1...i changed the incorrect one first). The correct one for me was located at /etc/my.cnf but I imagine it could differ by server.

I edited the file and added:

wait_timeout = 28800
max_allowed_packet = 8M

then restarted the mysql server with the following command:

service mysqld restart

Note that my server is running CentOS so locations and restart command may be different.

 

[gerryvz]

I just did a successful migration from vB 4.2.4 to XF 2.1.0 last weekend (just upgraded last night to 2.1.1). Ubuntu 18.04, MySQL 5.7.25, php 7.2

I have XFES installed, but have the add-on disabled for now, until I get things set up. In the meantime, I figured I'd just use the built-in XF search.

Upon rebuilding the search index after install, I get the following error after around 125,000 of my ~180,000 posts have been indexed:

An exception occurred: [XF\Db\Exception] MySQL statement prepare error [2006]: MySQL server has gone away in src/XF/Db/AbstractStatement.php on line 217

1. XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
2. XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 39
3. XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 54
4. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
5. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1261
6. XF\Mvc\Entity\Finder->fetchOne() in src/XF/Repository/UpgradeCheck.php at line 39
7. XF\Repository\UpgradeCheck->getLatestUpgradeCheck() in src/XF/Admin/App.php at line 238
8. XF\Admin\App->renderPageHtml() in src/XF/App.php at line 1988
9. XF\App->renderPage() in src/XF/Admin/App.php at line 122
10. XF\Admin\App->renderPage() in src/XF/Mvc/Dispatcher.php at line 362
11. XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 53
12. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2177
13. XF\App->run() in src/XF.php at line 390
14. XF::runApp() in admin.php at line 13

Some searching has shown that there is perhaps a database timeout issue. I updated (per above) the my.conf file to lengthen the max timeout and packet size (and restarted MySQL from the CLI), but I continue to get the error.

The location of the mysql.conf file on my installation is: /etc/mysql/mysql.cnf

File contents (after I modified and added a line):

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
max_allowed_packet = 8M
wait_timeout = 28800
thread_stack = 128K
max_connections = 125
table_open_cache = 32M
key_buffer_size = 32M



Any thoughts on what could be causing the issue? Database corruption somehow?

Cheers,
Gerry

 

[gerryvz]

I overcame this issue successfully, at the suggestion of Jon at XenForo, by reviewing the mySQL error log. I attempted and failed a search index rebuild to ensure I had a fresh error in the log, before reviewing it.

I found the following errors, after two search engine rebuild attempts:

2019-03-16T14:15:47.639365Z 146336 [Note] Aborted connection 146336 to db: 'xenforo' user: '500eadmin' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)

2019-03-16T14:17:38.206541Z 146472 [Note] Aborted connection 146472 to db: 'xenforo' user: '500eadmin' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
(END)

That told me that my packet size was still too small, so I upped it from 8MB to 16MB in the mysql.cnf file, and re-attempted another search index rebuild.

Success!! Issue resolved.

So, hopefully this helps someone in the future.

Cheers,
Gerry