phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Can't View PM Boxes (SQL Error : 1264 Out of range value...)

Goto page 1, 2  Next
 
Search this topic... | Search General Support... | Search Box
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Author Message
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Mon Jun 18, 2018 4:11 am 
Post subject: Can't View PM Boxes (SQL Error : 1264 Out of range value...)

I get this error message when I try to view my private messages...

Code:
Could not update private message new/read status for user

DEBUG MODE

SQL Error : 1264 Out of range value for column 'user_unread_privmsg' at row 1

UPDATE l*****n***_users SET user_unread_privmsg = user_unread_privmsg + user_new_privmsg, user_new_privmsg = 0, user_last_privmsg = 1529293967 WHERE user_id = 2

Line : 1868
File : privmsg.php


I've downloaded the privmsg.php file currently in use for my forum and found the line(s) referenced in the error message, but am not sure what to do to fix it.

Can anyone please help?
Back to top
StarWolf3000
Board Member



Joined: 10 Jun 2010

Posts: 97
Location: Germany


flag
PostPosted: Mon Jun 18, 2018 5:19 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Does your summed up number of unread and new PMs exceed 65535 (thats unsigned smallint(5))?
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Mon Jun 18, 2018 5:44 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

The sum for the entire board (all members' PMs)? I'm not sure. How do I find out?

In cPanel I navigate to the phpMyAdmin section and view the privmsgs part of the database and the last private message ID number is 110672, but I think that includes messages that have been deleted over the years and isn't an accurate number of messages currently stored?

Sorry, I don't know too much about all this and it's been many years since I had a problem like this.

I think one time this sort of thing happened before and it was caused by the last message I tried to send to someone ... like something got corrupted by sending a message somehow and when I deleted it from the outbox, the error went away. I forget exactly now. It's been too long.

EDIT: Okay, I see now that the total number of PMs is 18,676
Back to top
StarWolf3000
Board Member



Joined: 10 Jun 2010

Posts: 97
Location: Germany


flag
PostPosted: Mon Jun 18, 2018 6:13 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

No, I meant your own total new and unread PMs (the counters only do your own PMs, not others). Deleted PMs aren't counted, since they are no longer in the database (they're physically deleted).

Yes, the IDs don't reflect the current stored count.

Can you look up what user_unread_privmsg and user_new_primsg are for your user? Maybe somehow that data is damaged or has too high values.
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Mon Jun 18, 2018 6:26 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Yes, here is what I'm seeing...

user_unread_privmsg = 65,534
user_new_privmsg = 2

....and the user right under me also has 65,534 "unread_prvmsg" (plus 1 new_privmsg)
(edit: I also now see several others with 65,534 or a few less, while most have zero unread).

The actual total number of allowed PMs (inbox/sentbox/savebox) configured for each user on the forum should be around 5,000.

Should the number of "unread" PMs showing in the database be the same as "new" PMs?
Back to top
StarWolf3000
Board Member



Joined: 10 Jun 2010

Posts: 97
Location: Germany


flag
PostPosted: Mon Jun 18, 2018 7:16 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Well, there's the problem: Summing both values for you and the user below you exceeds the maximum allowed value for the smallint datatype. Looks like it somehow got messed up (since the total count of all private messages according to your database stats is lower than that).

Using this query you can fix the counters for each user (!!UNTESTED!! Just in case create a backup of the database!):
Code:
UPDATE l*****n***_users SET user_unread_privmsg = (SELECT COUNT(privmsgs_id) FROM l*****n***_privmsgs WHERE privmsgs_to_userid = insert_here_your_user_id AND privmsgs_type = 5) WHERE user_id = insert_here_your_user_id


Replace insert_here_your_userid with the designated user id (run the query for each user separate).
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Mon Jun 18, 2018 7:31 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Thank you for helping!

I need to get to sleep now, so will wait to try your suggestion until tomorrow in case something goes wrong and I need extra time to fix things.
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Mon Jun 18, 2018 4:51 pm 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

I've only run SQL queries once or twice before, and a long time ago, so probably a dumb question but I want to be careful here...

When I navigate to the SQL tab in PHPMyAdmin from the users table, there is already some text in the box where I would type in my query. Should I delete that text or put my command/query under it?

This is the text: SELECT * FROM `l****t_users` WHERE 1

EDIT: ...or should I simply navigate to the SQL tab after clicking on the database name at the top of the PHPMyAdmin sidebar (instead of from the specific "users" table), which brings up an empty query/text box?
Now that I think about it, I'm guessing that's how to do it since the query itself already specifies the user table.
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Tue Jun 19, 2018 2:34 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

I ran the query you provided on all users who had giantly inflated 65,000+ unread message counts like my own and it apears to have worked perfectly.

I can't thank you enough for your speedy assistance!
Without your help and this great forum still being here I am sure I'd still be stuck. My searching around on the internet for an answer to my specific question was getting me nowhere.
I thought this forum was gone. I'm so glad it is still here and there are still people on it willing to help.
Back to top
Vendethiel
Board Member



Joined: 26 Oct 2014

Posts: 77



PostPosted: Tue Jun 19, 2018 9:18 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

That's an interesting number of unreads. Do you send daily/weekly/monthly newsletter, maybe a point system, that would inflate the number this way?
_________________
Developer on EzArena, the ADR premod.
Developer on Icy Phoenix, the phpBB hybrid cms.
Developer on IntegraMOD, the full-featured premod.
Help me archive premods on github! (fixed for recent PHPs).
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Tue Jun 19, 2018 3:23 pm 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

No, nothing like that. I can't explain the circumstances that cause it exactly, but I think there is some sort of bug that sometimes when a person sends a PM and then deletes it from their outbox before it gets to the recipient, it can cause a problem. I've already been on the forum after I'd seen earlier in the day someone had sent me a PM but I didn't read it yet and then when I went back later, there is no PM and it says "You Have 65,535 Messages" until I click the link and go to my inbox (and find no new messages). I might have gotten a new pop-up notification too, as if the person had deleted, resent and then deleted the message again. I don't know, it seems to have something to do with sending and deleting the messages. Maybe the sender is editing or deleting right at the same time the recipient does something (logs-in, clicks pop-up, views inbox ?) and something gets messed up.
Back to top
Vendethiel
Board Member



Joined: 26 Oct 2014

Posts: 77



PostPosted: Wed Jun 20, 2018 11:59 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Interesting. Maybe there is a negative overflow at play here? I'm surprised no one would have had that issue then...
_________________
Developer on EzArena, the ADR premod.
Developer on Icy Phoenix, the phpBB hybrid cms.
Developer on IntegraMOD, the full-featured premod.
Help me archive premods on github! (fixed for recent PHPs).
Back to top
StarWolf3000
Board Member



Joined: 10 Jun 2010

Posts: 97
Location: Germany


flag
PostPosted: Thu Jun 21, 2018 6:50 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Vendethiel wrote:
Interesting. Maybe there is a negative overflow at play here? I'm surprised no one would have had that issue then...

You mean, when reading new messages it overflowed on subtracting? Since its unsigned, it would overflow when the right-hand operand is bigger than the left-hand operand. In this case, the issue itself must have happened in an early version of MySQL on the server, because proper handling (means: throwing an error) has been implemented as of MySQL 5.5.5: https://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html
Back to top
Chapter 24
Board Member



Joined: 30 Dec 2008

Posts: 48



PostPosted: Fri Jun 22, 2018 4:56 pm 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Having another, related issue now.

The member who had sent me the PMs the other day that seemed to trigger the first error message I asked about here, had also sent two messages to another member and those messages could not be opened by the recipient (she got an error message whenever she tried).

Those messages are also stuck in the sender's outbox. She gets the following error message when trying to delete them:

Code:
Could not update user pm counters

DEBUG MODE

SQL Error : 1690 BIGINT UNSIGNED value is out of range in '(`l*****e_LNForum`.`l*****t_users`.`user_unread_privmsg` - 1)'

UPDATE l*****t_users SET user_unread_privmsg = user_unread_privmsg - 1 WHERE user_id IN (3)

Line : 839
File : privmsg.php


I'm still trying to gather more details about the situation, but I know the sender has sent replacement messages and those were able to be read just fine.
I think the recipient may have been able to delete the first messages from her inbox (her new and unread PM counters are at zero), but they are still stuck in the sender's outbox.

I believe this is what happened to me once a long time ago and I think I ended up going into the database area (PHPMyAdmin) and deleting the offending/stuck message from there simply by clicking "delete" on the row for the specific message within the privmsgs table.
Is that an okay way to fix this?
Back to top
StarWolf3000
Board Member



Joined: 10 Jun 2010

Posts: 97
Location: Germany


flag
PostPosted: Sat Jun 23, 2018 7:55 am 
Post subject: Re: Can't View PM Boxes (SQL Error : 1264 Out of range value

Chapter 24 wrote:
Is that an okay way to fix this?

Usually not, since the counter doesn't get updated. If the counter is already at zero, then this error happens (you cannot subtract a positive number from zero with unsigned data types, that would result in overflow).

I think to fix all counters, first disable the PM system globally. That way you prevent anyone from sending or managing unread messages.
Then you need to set the table fields user_unread_privmsg and user_new_privmsg to 0 for every user (this can be done with only one SQL query for all users at once):
Code:
UPDATE l*****t_users SET user_unread_privmsg = 0, user_new_privmsg = 0;

Finally, you can reenable the PM system again globally. All private messages are now marked as read, but the counters have been reset and should work now as expected.
Back to top
Display posts from previous:   
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT
Goto page 1, 2  Next
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0297 seconds using 16 queries. (SQL 0.0022 Parse 0.0009 Other 0.0266)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo