Differences

This shows you the differences between two versions of the page.

--- reference:reset_message_folder_counts [2014/11/19 12:50] – created paul
+++ reference:reset_message_folder_counts [2018/11/14 10:45] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ======Reset Message Folder Counts======
-  with ff as (select folderid,count(*) as cnt, sum(length(msgdata)) as totsize,
+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,
-  sum(case when read and not hold then 0 else 1 end) as unreadcnt,
+  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
+  sum(case when hold then 1 else 0 end) as holdcnt from messages.folders left join
-  messages.foldermessages inner join messages.msgdata using(msgdataid) where deletedtime is null group by folderid)
+  (messages.foldermessages inner join messages.msgdata using(msgdataid)) using(folderid)
-  update messages.folders f set msgcount=ff.cnt, totalsize=totsize, newcount=newcnt, unreadcount=unreadcnt,
+  where deletedtime is null group by folderid)
-  holdcount=holdcnt from ff where f.folderid=ff.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

PSCS Wiki

User Tools

Site Tools

Differences

Page Tools