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

S

siONtI

Well-known member
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"
  }
}
 
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?
 
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 ;)
 
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
 
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.
 
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).
Click to expand...
Any changes made as a result of this issue being resolved may not be rolled out here until later.
 
rdn said:
XF 2.1.7, I still encounter this issue.
Click to expand...
I think there is scope for this happening via entity updates of that field which happens in a few places. If you can provide more context and details such as when this happens and the full error log output that will be useful to ensure we have fixed it.

ShinLim said:
This is what i get today :
  • MySQL query error [1264]: Out of range value for column 'position' at row 1
Click to expand...
That's an entirely different error in terms of the database field it is happening. You will need to start your own thread and provide more details such as the full error log output.
 
Chris D said:
I think there is scope for this happening via entity updates of that field which happens in a few places. If you can provide more context and details such as when this happens and the full error log output that will be useful to ensure we have fixed it.


That's an entirely different error in terms of the database field it is happening. You will need to start your own thread and provide more details such as the full error log output.
Click to expand...

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

Stack trace​

Code: 
UPDATE  `xf_post` SET `position` = ? WHERE `post_id` = 3984091
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1264, '22003')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1264, '22003')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(324): XF\Db\AbstractAdapter->query('UPDATE  `xf_pos...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1348): XF\Db\AbstractAdapter->update('xf_post', Array, '`post_id` = 398...')
#5 src/XF/Entity/Post.php(756): XF\Mvc\Entity\Entity->fastUpdate('position', -1)
#6 src/XF/Entity/Post.php(547): XF\Entity\Post->postHidden()
#7 src/XF/Mvc/Entity/Entity.php(1268): XF\Entity\Post->_postSave()
#8 src/XF/Entity/Post.php(873): XF\Mvc\Entity\Entity->save(true, false)
#9 src/XF/Service/Post/Deleter.php(81): XF\Entity\Post->softDelete('Konu ile alakas...', Object(Datio\AllowedEmails\XF\Entity\User))
#10 src/XF/Pub/Controller/Post.php(270): XF\Service\Post\Deleter->delete('soft', 'Konu ile alakas...')
#11 src/XF/Mvc/Dispatcher.php(352): XF\Pub\Controller\Post->actionDelete(Object(XF\Mvc\ParameterBag))
#12 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:Post', 'Delete', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Post), NULL)
#13 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Post), NULL)
#14 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#15 src/XF/App.php(2345): XF\Mvc\Dispatcher->run()
#16 src/XF.php(512): XF\App->run()
#17 index.php(20): XF::runApp('XF\\Pub\\App')
#18 {main}

Request state​



Code: 
array(4) {
  ["url"] => string(21) "/posts/3984091/delete"
  ["referrer"] => string(84) "https://memoryhackers.org/konular/apex-plat1-ve-ustu-takim-arkadasi-ariyoruz.221684/"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(9) {
    ["reason"] => string(25) "Konu ile alakasız yorum!"
    ["hard_delete"] => string(1) "0"
    ["author_alert"] => string(1) "1"
    ["author_alert_reason"] => string(25) "Konu ile alakasız yorum!"
    ["_xfRedirect"] => string(84) "https://xxxxx"
    ["_xfToken"] => string(8) "********"
    ["_xfRequestUri"] => string(59) "/xxxxxxx/"
    ["_xfWithData"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}

1638208757154.png

version 2.2.7 patch 1
 
Last edited:

Similar threads

R
MySQL query error [1264]: Out of range value for column 'forum_read_date'
Replies
2
Views
530
rdn
R
M
  • Question Question
XF 2.2 MySQL query error [1264]: Out of range value for column 'thread_read_id'
Replies
4
Views
689
PaulB
PaulB
Fajfie
Fixed XF\Db\Exception: MySQL query error [1366]: Incorrect integer value: '' for column `XXX`.`xf_deletion_log`.`delete_user_id` at row 1
Replies
2
Views
986
Jeremy P
Jeremy P
duterte
Duplicate XF\Db\Exception: MySQL query error [1406]: Data too long for column 'old_id' at row 1
Replies
7
Views
1K
duterte
duterte
Wesker
  • Question Question
XF 1.5 URGENT - Out of range value for column 'conversations_unread' at row 1
Replies
4
Views
2K
Wesker
Wesker
Back
Top Bottom