XF 2.3 MySQL query error [1406]:

[GrnEyedDvl]

The site was imported a little over a week ago from vBulletin 4.2.5 and has been running fine. Yesterday I started getting these, a couple of hundred of them now spread across all kinds of content.

If I am reading that correctly it is suggesting that the string is too long, but at 68 characters I wouldn't think that was the case. And in some cases its on the forum index or on the members page. Stuff with short titles. The longest one I saw just checking through randomly said string(122)

I have 63 of these from the last 12 hours or so and last night I deleted 150ish. All with an identical description. Most of the time there is not a real problem browsing the site but occasionally you get like a 6-10 second page time.

• XF\Db\Exception: MySQL query error [1406]: Data too long for column 'execute_data' at row 1
• src/XF/Db/AbstractStatement.php:230

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1406, '22001')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1406, '22001')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(323): XF\Db\AbstractAdapter->query('UPDATE  `xf_job...', Array)
#4 src/XF/Job/Manager.php(226): XF\Db\AbstractAdapter->update('xf_job', Array, 'job_id = ?', 15942)
#5 src/XF/Job/Manager.php(89): XF\Job\Manager->runJobEntry(Array, 8)
#6 job.php(46): XF\Job\Manager->runQueue(false, 8)
#7 {main}


array(4) {
  ["url"] => string(8) "/job.php"
  ["referrer"] => string(68) "https://www.twcenter.net/threads/ib-aar-the-hunnic-adventure.225534/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}

One of the short ones.

UPDATE  `xf_job` SET `execute_data` = ?, `trigger_date` = ?, `last_run_date` = ? WHERE job_id = ?
------------

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1406, '22001')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1406, '22001')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(323): XF\Db\AbstractAdapter->query('UPDATE  `xf_job...', Array)
#4 src/XF/Job/Manager.php(226): XF\Db\AbstractAdapter->update('xf_job', Array, 'job_id = ?', 15942)
#5 src/XF/Job/Manager.php(89): XF\Job\Manager->runJobEntry(Array, 8)
#6 job.php(46): XF\Job\Manager->runQueue(false, 8)
#7 {main}

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

Spoiler

 

[Jeremy P]

A job is trying to store more than 16MB of state data, which is odd. The error logs don't indicate which job, but you might try running a manual query to list all pending ones:

SELECT unique_key,
    execute_class,
    manual_execute,
    trigger_date,
    last_run_date,
    attempts,
    priority
FROM xf_job
ORDER BY trigger_date ASC

 

[GrnEyedDvl]

OK cool. I will take a look.

Looks like OzzMods is one of them, and so is Taghelper, but the others are native functions.

+----------------------+-------------------------------------------------+----------------+--------------+---------------+----------+----------+
| unique_key           | execute_class                                   | manual_execute | trigger_date | last_run_date | attempts | priority |
+----------------------+-------------------------------------------------+----------------+--------------+---------------+----------+----------+
| threadAction         | XF\Job\ThreadAction                             |              1 |   1746131290 |    1746265420 |        0 |      100 |
| setupCollectWords    | MaZ\PotentialTagHelper:RebuildPotentialTagWords |              1 |   1746169954 |    1746169054 |        0 |      100 |
| userBadgeUpdateQueue | OzzModz\Badges:UserBadgeUpdateQueue             |              0 |   1746402885 |    1746402825 |        0 |      100 |
| cron                 | XF\Job\Cron                                     |              0 |   1746402903 |    1746402825 |        0 |      100 |
| collectWordsCron     | MaZ\PotentialTagHelper:RebuildPotentialTagWords |              0 |   1746403620 |    1746402720 |        0 |      100 |
| xfUpgradeCheck       | XF\Job\UpgradeCheck                             |              0 |   1746451649 |    1746356152 |        0 |      100 |
+----------------------+-------------------------------------------------+----------------+--------------+---------------+----------+----------+
6 rows in set (0.001 sec)

 

[GrnEyedDvl]

I disabled the two addons for now.

EDIT: And I just cleared the log. Will see if they come back.

 

[Jeremy P]

Actually I was mistaken, looking closer at the logs it seems like job 15942. You might try:

SELECT *
FROM xf_job
WHERE job_id = 15942
LIMIT 1

 

[GrnEyedDvl]

Ok wow I ran that and my console is going nuts.

 

[GrnEyedDvl]

That is so much that the buffer ran out of space and all I have is this for 10 thousand lines.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+--------------+---------------+----------+----

 

[GrnEyedDvl]

I have no idea which field is pulling that, maybe if I do not select *? what are the column names?

 

[GrnEyedDvl]

 select job_id, unique_key from xf_job where job_id =15942;
+--------+------------------+
| job_id | unique_key       |
+--------+------------------+
|  15942 | collectWordsCron |
+--------+------------------+
1 row in set (0.001 sec)

 

[GrnEyedDvl]

Ok I didn't pull the blob as I think that is what has all the crap in in it. I think that Potential Tag mod is the problem.

 select job_id, unique_key,execute_class from xf_job where job_id =15942;
+--------+------------------+-------------------------------------------------+
| job_id | unique_key       | execute_class                                   |
+--------+------------------+-------------------------------------------------+
|  15942 | collectWordsCron | MaZ\PotentialTagHelper:RebuildPotentialTagWords |
+--------+------------------+-------------------------------------------------+
1 row in set (0.001 sec)

 

[Jeremy P]

Yeah it’ll be related to that then

I have no idea which field is pulling that, maybe if I do not select *? what are the column names?

Heh, was trying to avoid that in my first query but second-guessed it in the follow up.

 

[GrnEyedDvl]

No worries I just pulled the column names and looked for one that made sense. Once I saw the field types I knew the blob was the problem so I just pulled the key hoping for a clue. Think that add on is trying to process the entire database at once instead of breaking things in pieces.

Thanks for the point in the right direction!