User Tools

Site Tools


reference:reset_message_folder_counts

Reset Message Folder Counts

In extremely rare cases, the mail folder message counts in the database may get out of step with the actual stored messages. There are database triggers which will keep the counts in sync during normal operation, but if you have to manipulate the database directly (eg to recover from a corrupted database) it is possible that they will get out of step.

Before running the query below you will need to stop VPOP3. Note that running it will take some time, depending on the size of the database and the performance of the VPOP3 server, and there is no progress indicator for database queries.

At a command prompt in the VPOP3 directory run

psql

(The password is 'vpop3pass')

Then, type

with ff as (select folderid,count(msgid) as cnt, coalesce(sum(length(msgdata)),0) as totsize, 
sum(case when newflag then 1 else 0 end) as newcnt, 
count(case when read and not hold then null else msgid end) as unreadcnt, 
sum(case when hold then 1 else 0 end) as holdcnt from messages.folders left join 
(messages.foldermessages inner join messages.msgdata using(msgdataid)) using(folderid) 
where deletedtime is null group by folderid) 
update messages.folders f set msgcount=ff.cnt, totalsize=totsize, newcount=newcnt, 
unreadcount=unreadcnt, holdcount=holdcnt from ff where f.folderid=ff.folderid;

(Copy and paste if possible!)

N.B. VPOP3 does need to be stopped while you do this

reference/reset_message_folder_counts.txt · Last modified: 2018/11/14 10:45 (external edit)