Delete Visitors Message Error

[Terrablade]

While trying to delete a specific user's visitor messages sent I encounter this error..

Database error in vBulletin 3.8.7:

Invalid SQL:

				UPDATE user
				SET vmunreadcount = CASE userid WHEN 31 THEN vmunreadcount - 1 WHEN 1579 THEN vmunreadcount - 1 WHEN 3267 THEN vmunreadcount - 1 WHEN 3449 THEN vmunreadcount - 1 WHEN 3579 THEN vmunreadcount - 1 WHEN 4241 THEN vmunreadcount - 1 WHEN 5140 THEN vmunreadcount - 1 WHEN 5717 THEN vmunreadcount - 1 WHEN 5936 THEN vmunreadcount - 1 WHEN 5971 THEN vmunreadcount - 1 WHEN 6370 THEN vmunreadcount - 1 WHEN 6570 THEN vmunreadcount - 1 WHEN 6635 THEN vmunreadcount - 1 WHEN 7559 THEN vmunreadcount - 1 WHEN 7757 THEN vmunreadcount - 1 WHEN 8598 THEN vmunreadcount - 1 WHEN 8839 THEN vmunreadcount - 1 WHEN 9676 THEN vmunreadcount - 1 WHEN 11037 THEN vmunreadcount - 1 WHEN 11269 THEN vmunreadcount - 1 WHEN 13080 THEN vmunreadcount - 1 WHEN 14213 THEN vmunreadcount - 1 WHEN 14713 THEN vmunreadcount - 1 WHEN 15486 THEN vmunreadcount - 1 WHEN 15738 THEN vmunreadcount - 1 WHEN 15960 THEN vmunreadcount - 1 WHEN 16578 THEN vmunreadcount - 1 WHEN 16639 THEN vmunreadcount - 1 WHEN 16822 THEN vmunreadcount - 1 WHEN 17554 THEN vmunreadcount - 1 WHEN 18255 THEN vmunreadcount - 1 WHEN 19632 THEN vmunreadcount - 1 WHEN 19972 THEN vmunreadcount - 1 WHEN 20270 THEN vmunreadcount - 1 WHEN 20434 THEN vmunreadcount - 1 WHEN 20675 THEN vmunreadcount - 1 WHEN 20779 THEN vmunreadcount - 1 WHEN 20816 THEN vmunreadcount - 1 WHEN 20978 THEN vmunreadcount - 1 WHEN 21160 THEN vmunreadcount - 1 WHEN 21184 THEN vmunreadcount - 1 WHEN 21339 THEN vmunreadcount - 1 WHEN 21561 THEN vmunreadcount - 1 WHEN 21920 THEN vmunreadcount - 1 WHEN 22079 THEN vmunreadcount - 1 WHEN 22161 THEN vmunreadcount - 1 WHEN 22517 THEN vmunreadcount - 1 WHEN 22593 THEN vmunreadcount - 1 WHEN 22679 THEN vmunreadcount - 1 WHEN 22741 THEN vmunreadcount - 1 WHEN 22788 THEN vmunreadcount - 1 WHEN 22976 THEN vmunreadcount - 1 WHEN 23093 THEN vmunreadcount - 1 WHEN 23110 THEN vmunreadcount - 1 WHEN 23211 THEN vmunreadcount - 1 WHEN 23369 THEN vmunreadcount - 1 WHEN 23678 THEN vmunreadcount - 1 WHEN 23819 THEN vmunreadcount - 1 WHEN 23873 THEN vmunreadcount - 1 WHEN 24114 THEN vmunreadcount - 1 WHEN 24122 THEN vmunreadcount - 1 WHEN 24278 THEN vmunreadcount - 1 WHEN 24388 THEN vmunreadcount - 1 WHEN 24394 THEN vmunreadcount - 1 WHEN 24805 THEN vmunreadcount - 1 WHEN 24886 THEN vmunreadcount - 1 WHEN 25212 THEN vmunreadcount - 2 WHEN 25493 THEN vmunreadcount - 1 WHEN 25682 THEN vmunreadcount - 1 WHEN 26037 THEN vmunreadcount - 1 WHEN 26962 THEN vmunreadcount - 1 WHEN 27268 THEN vmunreadcount - 1 WHEN 27404 THEN vmunreadcount - 1 WHEN 27457 THEN vmunreadcount - 1 WHEN 27648 THEN vmunreadcount - 1 WHEN 27714 THEN vmunreadcount - 1 WHEN 27990 THEN vmunreadcount - 1 WHEN 28073 THEN vmunreadcount - 1 WHEN 28348 THEN vmunreadcount - 1 WHEN 29187 THEN vmunreadcount - 1 WHEN 29234 THEN vmunreadcount - 1 WHEN 29507 THEN vmunreadcount - 1 WHEN 30278 THEN vmunreadcount - 1 WHEN 30377 THEN vmunreadcount - 1 WHEN 30774 THEN vmunreadcount - 1 WHEN 30919 THEN vmunreadcount - 1 WHEN 31570 THEN vmunreadcount - 1 WHEN 31588 THEN vmunreadcount - 1 WHEN 31624 THEN vmunreadcount - 1 WHEN 31907 THEN vmunreadcount - 1 WHEN 32374 THEN vmunreadcount - 1 WHEN 32648 THEN vmunreadcount - 1 WHEN 33184 THEN vmunreadcount - 1 WHEN 33335 THEN vmunreadcount - 1 WHEN 33467 THEN vmunreadcount - 1 WHEN 33474 THEN vmunreadcount - 1 WHEN 33519 THEN vmunreadcount - 1 WHEN 33670 THEN vmunreadcount - 1 WHEN 33703 THEN vmunreadcount - 1 WHEN 33763 THEN vmunreadcount - 1 WHEN 33765 THEN vmunreadcount - 1 WHEN 33766 THEN vmunreadcount - 1 WHEN 33771 THEN vmunreadcount - 1 ELSE vmunreadcount END
				WHERE userid IN(31,1473,1506,1579,1768,2266,3267,3449,3579,4241,4793,5140,5717,5936,5971,6119,6370,6570,6635,7559,7757,8598,8839,9676,10543,10737,11037,11269,13080,13834,14213,14713,15486,15738,15960,16578,16639,16822,17554,18255,18407,19079,19342,19632,19972,20270,20434,20450,20675,20779,20816,20978,21160,21184,21339,21561,21586,21920,22079,22161,22224,22323,22517,22593,22679,22741,22788,22876,22976,22996,23002,23093,23110,23211,23369,23678,23819,23873,24114,24122,24278,24388,24394,24453,24599,24769,24805,24886,25195,25212,25493,25545,25682,25793,26037,26273,26515,26962,27268,27404,27457,27648,27714,27990,28073,28348,28491,29187,29234,29507,29743,29946,30278,30377,30774,30919,31570,31588,31624,31677,31907,32326,32374,32648,33184,33335,33455,33467,33474,33519,33670,33703,33722,33763,33765,33766,33771,33783);

MySQL Error   : BIGINT UNSIGNED value is out of range in '(`astronau_heaven1`.`user`.`vmunreadcount` - 1)'
Error Number  : 1690
Request Date  : Sunday, August 17th 2014 @ 05:39:30 PM
Error Date    : Sunday, August 17th 2014 @ 05:39:30 PM
Script        : http://www.mysite.com/admincp/usertools.php?do=killsentvms
Referrer      : http://www.mysite.com/admincp/usertools.php?do=removesentvms&u=33782
IP Address    : removed for obvious reasons
Username      : Admin
Classname     : vB_Database
MySQL Version : 5.5.37-cll

How to fix this issue?

[Terrablade]

yOOHOOOOO.. anyone knowledgeable about this issue?

[Lynne]

What is the user. vmunreadcount for the user "astronau_heaven1" in the database?

[Terrablade]

Didnt understand your question :S

[Lynne]

Take a look at the user table in your database. Find the row for the user "astronau_heaven1" and then tell us what is listed for vmunreadcount for that user.

[Terrablade]

Lynne,
Looked through the entire mysql DB and saw regardng what u asked me to look. :/

[squidsk]

Is that a custom query? I'm asking because it is not designed very well and leaves a lot to be desired. The problem you have is that one (or more) of those users has no unread vms, so when you set vmunreadcount to be vmunreadcount - 1 for those users it sets the vmunreadcount to -1, which will throw that db error you're seeing.

The query should be something like this instead:

UPDATE user
SET vmunreadcount = IF (vmunreadcount > 0, vmunreadcount - 1,  0)
WHERE userid IN (31,1473,1506,1579,1768,2266,3267,3449,3579,4241,4793,5140,5717,5936,5971,6119,6370,6570,6635,7559,7757,8598,8839,9676,10543,10737,11037,11269,13080,13834,14213,14713,15486,15738,15960,16578,16639,16822,17554,18255,18407,19079,19342,19632,19972,20270,20434,20450,20675,20779,20816,20978,21160,21184,21339,21561,21586,21920,22079,22161,22224,22323,22517,22593,22679,22741,22788,22876,22976,22996,23002,23093,23110,23211,23369,23678,23819,23873,24114,24122,24278,24388,24394,24453,24599,24769,24805,24886,25195,25212,25493,25545,25682,25793,26037,26273,26515,26962,27268,27404,27457,27648,27714,27990,28073,28348,28491,29187,29234,29507,29743,29946,30278,30377,30774,30919,31570,31588,31624,31677,31907,32326,32374,32648,33184,33335,33455,33467,33474,33519,33670,33703,33722,33763,33765,33766,33771,33783);

This query assumes that all userids listed in the where clause should have their unreadvmcount lowered by 1, if possible.

[Terrablade]

so how to fix that?