Fixed MySQL query error [1264]: Out of range value for column 'alerts_unread' at row 1

siONtI

Formerly FrederikP
This error started appearing today, if a user try to reply to a thread he get this error. (error is random, them can post 10 times without any issues and then out of nowhere they get this)
346931_again.png

I though this was a bug with one of my add-on so i disabled them all but i'm still getting this error.
Any idea what is causing this and how to fix it?


XF\Db\Exception: MySQL query error [1264]: Out of range value for column 'alerts_unread' at row 1
src/XF/Db/AbstractStatement.php:217

Code:
Stack trace
                    UPDATE xf_user
                    SET alerts_unread = alerts_unread + 1
                    WHERE user_id = ?
             
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1264, '22003')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1264, '22003')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Entity/UserAlert.php(98): XF\Db\AbstractAdapter->query('
                    UPDATE xf...', 295)
#4 src/XF/Mvc/Entity/Entity.php(1208): XF\Entity\UserAlert->_postSave()
#5 src/XF/Repository/UserAlert.php(141): XF\Mvc\Entity\Entity->save()
#6 src/XF/Repository/UserAlert.php(111): XF\Repository\UserAlert->insertAlert(295, 42343, 'Segmentation Fa...', 'post', 2078793, 'insert', Array)
#7 src/XF/Notifier/AbstractNotifier.php(55): XF\Repository\UserAlert->alert(Object(XF\Entity\User), 42343, 'Segmentation Fa...', 'post', 2078793, 'insert', Array)
#8 src/XF/Notifier/Post/AbstractWatch.php(104): XF\Notifier\AbstractNotifier->basicAlert(Object(XF\Entity\User), 42343, 'Segmentation Fa...', 'post', 2078793, 'insert')
#9 src/XF/Service/AbstractNotifier.php(86): XF\Notifier\Post\AbstractWatch->sendAlert(Object(XF\Entity\User))
#10 src/XF/Service/AbstractNotifier.php(44): XF\Service\AbstractNotifier->notifyType(Object(XF\Notifier\Post\ThreadWatch), Array, G)
#11 src/XF/Service/AbstractNotifier.php(56): XF\Service\AbstractNotifier->notify(3)
#12 src/XF/Service/Thread/Replier.php(205): XF\Service\AbstractNotifier->notifyAndEnqueue(3)
#13 src/XF/Pub/Controller/Thread.php(330): XF\Service\Thread\Replier->sendNotifications()
#14 src/XF/Pub/Controller/Thread.php(463): XF\Pub\Controller\Thread->finalizeThreadReply(Object(XF\Service\Thread\Replier))
#15 src/XF/Mvc/Dispatcher.php(321): XF\Pub\Controller\Thread->actionAddReply(Object(XF\Mvc\ParameterBag))
#16 src/XF/Mvc/Dispatcher.php(248): XF\Mvc\Dispatcher->dispatchClass('XF:Thread', 'AddReply', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Thread), NULL)
#17 src/XF/Mvc/Dispatcher.php(100): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Thread), NULL)
#18 src/XF/Mvc/Dispatcher.php(50): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#19 src/XF/App.php(2177): XF\Mvc\Dispatcher->run()
#20 src/XF.php(390): XF\App->run()
#21 index.php(20): XF::runApp('XF\\Pub\\App')
#22 {main}
Code:
Request state
array(4) {
  ["url"] => string(57) "/threads/randomthread.18207/add-reply"
  ["referrer"] => bool(false)
  ["_GET"] => array(1) {
    ["/threads/randomthread_18207/add-reply"] => string(0) ""
  }
  ["_POST"] => array(9) {
    ["message_html"] => string(376) "<p>[QUOTE="User99, post: 2078450, member: 1476438"]</p><p>how many days</p><p>[/QUOTE]</p><p><br></p><p>[spoiler="Help"]randomtexthere[/spoiler]</p>"
    ["attachment_hash"] => string(32) "a13c89e8ff453d16f7bf5880adab2a7e"
    ["attachment_hash_combined"] => string(87) "{"type":"post","context":{"thread_id":18207},"hash":"a13c89e8ff453d16f7bf5880adab2a7e"}"
    ["last_date"] => string(10) "1561728568"
    ["last_known_date"] => string(10) "1561728568"
    ["_xfToken"] => string(8) "********"
    ["_xfRequestUri"] => string(56) "/threads/randomthread.18207/page-280"
    ["_xfWithData"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}
 

Brogan

XenForo moderator
Staff member
That suggests someone has a massive amount of unread alerts.

Check the xf_user table and sort on alerts_unread- what is the largest entry?
 

Brogan

XenForo moderator
Staff member
Yup, that would do it.

The top one is at the maximum value for the column, and the second isn't far behind.

Clear those two and the error should stop.
 

Brogan

XenForo moderator
Staff member
It would be interesting to find out why those values are so high.

On this site the largest value is less than 3,000.

It has been logged as a bug report now.
Changing the column from SMALLINT to INT is an option, which would allow for over 4 billion unread alerts ... hopefully that should be high enough ;)
 

bzcomputers

Active member
Rather than let a user possibly bloat the database with millions of alerts out of ignorance or malice, wouldn't it better to purge older alerts after a specified maximum?

I mean 65,536 seems like waaaaay too much to begin with. Changing the data type to allow even more doesn't seem like the best solution. In actuality how many alerts is a person even going to scroll through, let alone actually look at, a hundred maybe a few hundred if they've been away a long time. I would set a hard max at maybe 5,000 or 10,000 and maybe offer an administrative option to lower the limit.

...just a thought
 

Chris D

XenForo developer
Staff member
The alerts are pruned but the individual user counts aren’t updated when that happens.

FWIW we’re not necessarily going to increase the size of that field. We’ll probably just add code to constrain the number as actually getting that many alerts is extremely unlikely.
 

XF Bug Bot

XenForo bug fixer bot
Staff member
Thank you for reporting this issue. It has now been resolved and we are aiming to include it in a future XF release (2.1.3).

Change log:
When increasing the unread alerts count for a user, do not let the number exceed the maximum storage for the DB field (65,535).
Any changes made as a result of this issue being resolved may not be rolled out here until later.
 
Top