Recep Baltaş
Well-known member
I believe it's dead :/
Thread Tagging Improvements 1.0.8 Patch Level 1
- Thread starter mazzly
- Start date
Digital Doctor
Well-known member
have him fix the issue ?
Both are lazy programmers, 021 and mazzly
Digital Doctor
Well-known member
$$ just pay for it if you want it.
021 doesn't care about bugs and mazzly, you know he doesn't care about anything about add-ons. So, it shouldn't cost money to fix bugs, am I right?
Digital Doctor
Well-known member
Mazzly provided free coding for this addon but you think "he doesn't care".
That's a weird take, bro.
Q: Should you expect him to keep donating his time to this addon ?
A: Probably not !
That's a weird take, bro.
Q: Should you expect him to keep donating his time to this addon ?
A: Probably not !
Recep Baltaş
Well-known member
I am encountering a blocking error in the server error logs that is causing the job queue to get stuck. It appears that the collectWordsCron task is attempting to save an excessive amount of data into the xf_job table, exceeding the column limit.
The Error:
Stack Trace:
Analysis:
I inspected the problematic row in the xf_job table. The job class is MaZ\PotentialTagHelper:RebuildPotentialTagWords.
The execute_data column for this specific job has grown to 13.9 MiB, which is too large for the standard MEDIUMBLOB column type in XenForo (and generally too large for a job state payload).
The Error:
Code:
XF\Db\Exception: MySQL query error [1406]: Data too long for column 'execute_data' at row 1 src/XF/Db/AbstractStatement.php:230
Code:
UPDATE `xf_job` SET `execute_data` = ?, `trigger_date` = ?, `last_run_date` = ? WHERE job_id = ?<br>------------<br>#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1406, '22001')<br>#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1406, '22001')<br>#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()<br>#3 src/XF/Db/AbstractAdapter.php(323): XF\Db\AbstractAdapter->query('UPDATE `xf_job...', Array)<br>#4 src/XF/Job/Manager.php(226): XF\Db\AbstractAdapter->update('xf_job', Array, 'job_id = ?', 18133810)<br>I inspected the problematic row in the xf_job table. The job class is MaZ\PotentialTagHelper:RebuildPotentialTagWords.
The execute_data column for this specific job has grown to 13.9 MiB, which is too large for the standard MEDIUMBLOB column type in XenForo (and generally too large for a job state payload).
- Job ID: 18133810
- Unique Key: collectWordsCron
- Execute Class: MaZ\PotentialTagHelper:RebuildPotentialTagWords
- Execute Data Size: ~13.9 MiB (BLOB)
Recep Baltaş
Well-known member
The Fix Explanation
The error
Originally, the add-on collected all words from thousands of threads into a single PHP array (
I have refactored
Changes Made:
1. Flush Buffer: Instead of holding words in memory indefinitely, the script now writes to the database and clears memory whenever the word count in RAM reaches 2,000.
2. Early Filtering: Word length and stop word checks are now done inside the loop before writing to the DB, reducing load.
3. Start-up Cleanup: The logic to clear the table has been moved to the start of the job rather than the end to ensure a clean slate.
The Fixed Code
Replace the content of
The error
MySQL query error [1406]: Data too long for column 'execute_data' occurs because of a fundamental architectural issue in how the add-on handles data.Originally, the add-on collected all words from thousands of threads into a single PHP array (
word_count) and tried to carry this massive array between job steps via the database. Since your forum is large, this array grew to \~13.9 MB, exceeding the XenForo execute_data column limit.I have refactored
src/addons/MaZ/PotentialTagHelper/Job/RebuildPotentialTagWords.php to switch from a "Keep All -\> Write Later" logic to a "Process Batch -\> Write to DB -\> Flush Memory" logic.Changes Made:
1. Flush Buffer: Instead of holding words in memory indefinitely, the script now writes to the database and clears memory whenever the word count in RAM reaches 2,000.
2. Early Filtering: Word length and stop word checks are now done inside the loop before writing to the DB, reducing load.
3. Start-up Cleanup: The logic to clear the table has been moved to the start of the job rather than the end to ensure a clean slate.
The Fixed Code
Replace the content of
src/addons/MaZ/PotentialTagHelper/Job/RebuildPotentialTagWords.php with this:
PHP:
\<?php
namespace MaZ\\PotentialTagHelper\\Job;
use MaZ\\PotentialTagHelper\\PTH;
use XF\\Job\\AbstractRebuildJob;
use XF\\Job\\JobResult;
class RebuildPotentialTagWords extends AbstractRebuildJob
{
protected $stopWords = null;
```
// Maximum number of words to keep in memory before writing to the database.
// This number is small enough not to exceed the BLOB limit (error 1406), but large enough for performance.
protected $bufferSize = 2000;
protected $defaultData = [
'batch' => 1000, // Batch size reduced for more frequent checks
'start' => ['thread_id' => 0, 'title' => ''],
'threads_remaining' => 0,
'word_count' => array(),
'is_reset' => false // Checks if the table has been reset
];
public function calculateOptimalBatch($expected, $done, $start, $maxTime, $maxBatch = null)
{
return parent::calculateOptimalBatch($expected, $done, $start, $maxTime, \XF::options()->pth_max_batch_size);
}
protected function getNextIds($thread, $batch)
{
$threadFinder = \XF::finder('XF:Thread');
$limit_forums = \XF::options()->pth_forums;
if ($limit_forums && $limit_forums[0] !== "0") {
$threadFinder->where('node_id', $limit_forums);
}
$threadFinder->limit($batch)
->where('thread_id', '>', $thread['thread_id'])
->where('discussion_state', 'visible')
->where('discussion_type', '!=', 'redirect')
->order('thread_id', 'ASC');
return $threadFinder->fetchColumns('thread_id', 'title');
}
protected function rebuildById($thread)
{
// 1. Clear the table when the process starts (Old logic did this at the end)
if (empty($this->data['is_reset']) && $this->data['start']['thread_id'] == 0) {
$this->app->db()->delete(PTH::table_potential_words, 'ignored = 0');
$this->data['is_reset'] = true;
}
$words = PTH::splitWord(strtolower($thread["title"]));
// Count individual words
foreach (array_count_values($words) as $word => $count) {
if (!isset($this->data['word_count'][$word])) $this->data['word_count'][$word] = 0;
$this->data['word_count'][$word] += $count;
}
// Count word chunks
if (count($words) > 1) {
$chunk_size = 2;
while ($chunk_size <= \XF::options()->pth_potential_word_size) {
foreach ($this->getChunks($words, $chunk_size) as $word) {
if (!isset($this->data['word_count'][$word])) $this->data['word_count'][$word] = 0;
$this->data['word_count'][$word] += 1;
}
$chunk_size++;
}
}
// 2. If the word count in memory exceeds the limit, write to DB and clear RAM
// This definitely resolves the "Data too long" error.
if (count($this->data['word_count']) >= $this->bufferSize) {
$this->flushBuffer();
}
}
/**
* Writes words in memory (RAM) to the database and resets the array.
*/
protected function flushBuffer()
{
if (empty($this->data['word_count'])) {
return;
}
// Filtering settings
$min_word_length = max(\XF::options()->pth_minimum_word_length, 2);
// We set minimum use count to 1 during flush because
// we don't know the total count yet while writing piecemeal. We will clean up at the end.
// Prepare stop words list
if (!$this->stopWords) {
$this->stopWords = array_flip(preg_split("/\r\n|\n|\r/", \XF::options()->pth_stop_words));
}
$insertRows = [];
$existing_tags_synonyms = PTH::repo()->existingTagsOrSynonyms();
foreach ($this->data['word_count'] as $word => $count) {
// We do basic filtering HERE (to prevent database bloat)
if (gettype($word) !== "string") continue;
if (strlen($word) < $min_word_length || strlen($word) > 100) continue;
if (isset($this->stopWords[$word])) continue;
$insertRows[] = [
'word' => $word,
'word_count' => count(explode(' ', $word)),
'use_count' => $count,
'added' => array_key_exists($word, $existing_tags_synonyms) ? 1 : 0
];
}
if (!empty($insertRows)) {
$db = $this->app->db();
// Insert into DB with bulk insert, increment count if exists (ON DUPLICATE KEY UPDATE)
foreach (array_chunk($insertRows, \XF::options()->pth_bulk_insert_chunk_size) as $insertChunk) {
$db->insertBulk(PTH::table_potential_words, $insertChunk, false, "use_count = use_count + VALUES(use_count)");
}
}
// Clear RAM! (The life-saving line)
$this->data['word_count'] = [];
}
protected function getChunks($words, $chunk_size)
{
if (!$this->stopWords) {
$stop_words = preg_split("/\r\n|\n|\r/", \XF::options()->pth_stop_words);
$this->stopWords = array_flip($stop_words);
}
$startIndex = 0;
$maxStartIndex = count($words) - $chunk_size;
while ($startIndex <= $maxStartIndex) {
$chunk = array_slice($words, $startIndex, $chunk_size);
$add = true;
if (!\XF::options()->pth_multiword_allow_stop_words) {
foreach ($chunk as $index => $word) {
if (strlen($word) < 2 || array_key_exists($word, $this->stopWords)) {
$startIndex += $index + 1;
$add = false;
break;
}
}
}
if ($add) {
$startIndex++;
yield implode(' ', $chunk);
}
}
return [];
}
public function complete(): JobResult
{
// Write the remaining data to the database
$this->flushBuffer();
$db = $this->app->db();
// 3. Clean up words below the minimum use count from the database
// (Formerly done on PHP side, now done via SQL)
$min_use_count = max((\XF::options()->pth_min_use_count), 1);
if ($min_use_count > 1) {
$db->delete(PTH::table_potential_words, "use_count < ?", [$min_use_count]);
}
// Optional: If there is too much data, keep the top 50,000 popular ones (Performance optimization)
// This query might be heavy, can be disabled if needed but keeps the table clean.
/*
$db->query("DELETE FROM " . PTH::table_potential_words . "
WHERE word NOT IN (
SELECT word FROM (
SELECT word FROM " . PTH::table_potential_words . "
ORDER BY use_count DESC LIMIT 50000
) as kept_words
) AND ignored = 0");
*/
return parent::complete();
}
public function getStatusMessage()
{
return "Counting words from thread titles (" . $this->data['start']['thread_id'] . ")";
}
public function canCancel()
{
return true;
}
public function canTriggerByChoice()
{
return true;
}
protected function getStatusType()
{
}
```
}Similar threads
- Question
