BIGINT UNSIGNED value is out of range error

[Boofo]

I just got the following error while deleting a thread:

Database error in vBulletin 4.1.12:

Invalid SQL:
UPDATE user SET
totalwords = totalwords - 6
WHERE userid = 61;

MySQL Error : BIGINT UNSIGNED value is out of range in '(`xxxxx_xxxxxxxxx`.`user`.`totalwords` - 6)'
Error Number : 1690
Request Date : Friday, May 18th 2012 @ 07:49:07 AM
Error Date : Friday, May 18th 2012 @ 07:49:07 AM
Script : http://www.iamgrumpy.com/forums/postings.php?do=dodeletethread&threadid=362
Referrer : http://www.iamgrumpy.com/forums/postings.php
IP Address : xxx.xx.xx.xxx
Username : Boofo
Classname : vB_Database
MySQL Version : 5.5.22-cll


It has to do with the following code but I'm not sure how to fix it. I have never run into this error before.

Hook: threaddata_delete
if ($physicaldel)
{
$threadinfo = fetch_threadinfo($threadid);
$postinfo = fetch_postinfo($threadinfo['firstpostid']);
$userdm =& datamanager_init('User', $this->registry, ERRTYPE_SILENT);
$userdm->set_existing($postinfo);
$userdm->set('totalwords', 'totalwords - ' . str_word_count($postinfo['pagetext']), false);
$userdm->save();
unset($userdm);

$posts = $this->dbobject->query_read("
SELECT post.postid
FROM " . TABLE_PREFIX . "post AS post
WHERE post.threadid = $threadid
");

while ($post = $this->dbobject->fetch_array($posts))
{
$postinfo = fetch_postinfo($post['postid']);
$userdm =& datamanager_init('User', $this->registry, ERRTYPE_SILENT);
$userdm->set_existing($postinfo);
$userdm->set('totalwords', 'totalwords - ' . str_word_count($postinfo['pagetext']), false);
$userdm->save();
unset($userdm);
}
}


Does anyone see what I am missing there?

[Paul M]

Well the error means that its trying to subtract 6 from a value that is actually less than 6 to start with.

I'd be a lot more worried about what that code is actually doing, it looks like its running updates on user records for every post in a thread, so if your thread has 4000 posts, thats 4000 updates !

In fact, since its running one query to get all the postids, and then looping round them using fetch_postinfo(), its quite likely most of the posts dont exist in the postcache, so its also running individual queries to get each post, potentially another 4000. :erm:

[Boofo]

Thanks, Paul, for the quick response.

What it is doing is going through each post in the thread and subtracting the amount of words each user has posted in the thread and adjusting their total word count. I agree that a very large thread would be very resource intensive, but I only have a really small site so there is no real way of testing that issue. Is there a better way you run that and account for situation like this in the future? I *think* it is somehow subtracting the thread title word count too and that would account for the negative number.