XF 2.2 SQL Error by hourly clean up

[Michaelb24179]

since the update to xenforo 2.2 i have the following error when i start hourly clean up

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

    XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
    XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 39
    XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 54
    XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
    XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1346
    XF\Mvc\Entity\Finder->fetchOne() in src/XF/Repository/UpgradeCheck.php at line 33
    XF\Repository\UpgradeCheck->getLatestUpgradeCheck() in src/XF/Admin/App.php at line 239
    XF\Admin\App->renderPageHtml() in src/XF/App.php at line 2105
    XF\App->renderPage() in src/XF/Admin/App.php at line 123
    XF\Admin\App->renderPage() in src/XF/Mvc/Dispatcher.php at line 402
    XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 58
    XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2300
    XF\App->run() in src/XF.php at line 464
    XF::runApp() in admin.php at line 13

I rebuilt Xenforo and also the rebuild caches. All addons have been deactivated. I optimized my SQL database, it didn't help. I had to set my PHP memory_limit very high so that I didn't get a blank page.

 

[Mike]

This error isn't actually from the hourly clean up, unless you're running the job manually (which should be unnecessary).

It seems likely that your host has a very low wait_timeout setting in MySQL. The default here is 28800 (seconds). I've seen it as low as 10 on occasion. This is very low, as it will silently drop connections in the time it may take to complete other actions, such as generating an image thumbnail or executing a network connection. If this value is indeed very low, it should be increased by your host. We'd probably not recommend a value below 60.

If the value isn't low, then something is actively terminating your MySQL connection and you may need to contact your host about this.

 

[Michaelb24179]

I have a very large server. The settings have been set as follows for a very long time

# optimization for mysqld
# ~ key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
# ~ 256M + ( 2M + 2M ) * 500 = 2256M
[mysqld]

max_connections        = 500
thread_concurrency      = 16
thread_cache_size       = 32

key_buffer_size         = 3000M     # 3500M
read_buffer_size        = 8M
sort_buffer_size        = 8M
join_buffer_size        = 16M

query_cache_size        = 1024M
query_cache_limit       = 16M
query_cache_type     = 1

open_files_limit        = 4000
table_definition_cache  = 4000
table_open_cache        = 7000

max_heap_table_size     = 1000M
tmp_table_size          = 1000M

innodb_buffer_pool_size = 15G         # 2G
innodb_log_file_size = 2000M        # Neu
innodb_buffer_pool_instances = 15    # 2
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 1000
innodb_thread_concurrency = 16       
innodb_open_files = 8192        # Neu
innodb_file_per_table    = 1        # Neu

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 5
log_queries_not_using_indexes = 0

interactive_timeout = 120
wait_timeout = 120

performance_schema = on
skip-name-resolve = 1

I noticed the error where users activated the CronJob. PHP was at the limit. I had to adjust in PHP. Then the database error came

 

[Michaelb24179]

I now have a lot of mistakes again. I do not start a cron job manually. It's still the hourly cleaning. I set the PHP memory limit to 2048M.

    ErrorException: Fatal Error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 40679328 bytes) src/XF/Db/AbstractAdapter.php:496

    Generiert von: Unbekanntes Konto 17. Oktober 2020 um 16:10

Stack-Trace

#0 [internal function]: XF::handleFatalError()
#1 {main}

Status der Anfrage

array(4) {
  ["url"] => string(8) "/job.php"
  ["referrer"] => string(42) "https://www.url.com/forums/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

At the same time I get this log

# Time: 201017 14:10:49
# User@Host: sql1[sql1] @ localhost []
# Thread_id: 1656174  Schema: xenforo  QC_hit: No
# Query_time: 6.440630  Lock_time: 0.000017  Rows_sent: 2542437  Rows_examined: 2542437
# Rows_affected: 0
SET timestamp=1602936649;
SELECT `xf_user_alert`.`alert_id`, `xf_user_alert`.`alerted_user_id`, `xf_user_alert`.`view_date`, `xf_user_alert`.`read_date`
            FROM `xf_user_alert`
           
            WHERE (`xf_user_alert`.`view_date` = 0) AND (`xf_user_alert`.`event_date` < 1600344642);
# Time: 201017 15:10:51
# User@Host: sql1[sql1] @ localhost []
# Thread_id: 1695237  Schema: xenforo  QC_hit: No
# Query_time: 6.673417  Lock_time: 0.000018  Rows_sent: 2542489  Rows_examined: 2542489
# Rows_affected: 0
SET timestamp=1602940251;
SELECT `xf_user_alert`.`alert_id`, `xf_user_alert`.`alerted_user_id`, `xf_user_alert`.`view_date`, `xf_user_alert`.`read_date`
            FROM `xf_user_alert`
           
            WHERE (`xf_user_alert`.`view_date` = 0) AND (`xf_user_alert`.`event_date` < 1600348244);
# Time: 201017 16:10:53
# User@Host: sql1[sql1] @ localhost []
# Thread_id: 1735850  Schema: xenforo  QC_hit: No
# Query_time: 6.546049  Lock_time: 0.000018  Rows_sent: 2542458  Rows_examined: 2542458
# Rows_affected: 0
SET timestamp=1602943853;
SELECT `xf_user_alert`.`alert_id`, `xf_user_alert`.`alerted_user_id`, `xf_user_alert`.`view_date`, `xf_user_alert`.`read_date`
            FROM `xf_user_alert`
           
            WHERE (`xf_user_alert`.`view_date` = 0) AND (`xf_user_alert`.`event_date` < 1600351847);

 

[Michaelb24179]

I found the mistake.
I send out alert users twice a month. I don't know why, but Xenforo doesn't delete the messages from the User_Alert table. The database got bigger and bigger. In the end it was too much.

Why are the user alerts not deleted from the system? The system should delete all notices after 7 days.