HOW TO: Read database Messages file to check the physical file exists

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2015-01-13 17:30

The Data Directory Synchronizer , provided as the standard install of Hmailserver, checks physical email files in the data directory are all included in the database. If and email file is found that is not in the database then it provides the option to import the file into the database or delete the file from disk.

However, there is no feature to do the reverse - ie, check that physical files exist for every database record.

This script addresses that.

This script is for databases using an external MYSQL database and utilises both SQL commands and the Com API to ensure things are done whilst maintaining Hmailserver integrity. It should work with other flavours of external SQL database but you would have to make the necessary amendments to paths and executable in the BAT file to suit your system. Note: there is a similar script for INTERNAL DATABASE here

It comes in 2 parts:
1, the dedicated VBS script (called 'HMSdatabaseRecordCheck.vbs')
2, A calling BAT file ('HMSdatabaseRecordCheck.bat')

IMPLEMENTATION

A, Save both scripts (with the relevant VBS and BAT extensions) into the same storage directory of your machine where you would require them to run from.
B, Modify the BAT file and enter the necessary parameters at the top of the script (there are 7 to review).

Note: Modify all parameter values in the BAT script where marked " & :<----- INPUT blah ", and delete all whitespace/spaces and the actual " & :<----- INPUT blah " instruction from the line (after your parameter value).

C, Run the BAT script.

The code

First, the VBS script called HMSdatabaseRecordCheck.vbs (that will be called from a BAT file):

Code: Select all

Option Explicit

Const AdminUser = "administrator"

Dim Elog, FSO, DataDir, Mode, HMSpass
Dim MessageCount, ErrorCount,DeletedCount

Mode = 0
MessageCount = 0
ErrorCount = 0
DeletedCount = 0

Dim obApp, oDomain, oAccount
Dim oReadObj, oRead, SearchFile, MessageInputLine, SearchfileFolder, CheckFilePath, FoundIt, AccountAddr, MessageID

HMSpass = WScript.Arguments.Named("HMSpassword")
DataDir = WScript.Arguments.Named("DataDir")
Mode = WScript.Arguments.Named("ActionFlag")

Set FSO = CreateObject("Scripting.FileSystemObject")

Set Elog = CreateObject("hMailServer.EventLog")
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate(AdminUser, HMSpass)

Dim obDatabase, sSQL
Set obDatabase = obApp.Database

Elog.Write ("Start process hm_messages")
Elog.Write ("")
Set oReadObj  = CreateObject("Scripting.FileSystemObject")
Set oRead = oReadObj.OpenTextFile(DataDir&"\MessageList.txt", 1)
Do Until oRead.AtEndOfStream
	MessageInputLine = oRead.ReadLine
	MessageInputLine = Replace(MessageInputLine,vbTab," ")
	FoundIt = 0
	SearchFile = left(MessageInputLine,42)
	MessageID = trim(mid(MessageInputLine,44,10))
	AccountAddr = trim(mid(MessageInputLine,54))
	MessageCount = MessageCount + 1
	If MessageCount > 1 then ' ignores first HEADER row
		oDomain = mid(AccountAddr,InStr(AccountAddr,"@")+1)
		oAccount = left(AccountAddr,InStr(AccountAddr,"@")-1)
		SearchfileFolder = mid(SearchFile,2,2) 
		CheckFilePath = DataDir & "\" & oDomain & "\" & oAccount &"\"&SearchfileFolder&"\"&SearchFile
		If not (FSO.FileExists(CheckFilePath)) then
			Elog.Write ("File not found: " & SearchFile & ", Account: " & AccountAddr & ", MessageID: " & MessageID )
			ErrorCount = ErrorCount + 1
			If (Mode = 1) then
				sSQL = "DELETE FROM hm_messages WHERE messageid = " & MessageID
				Call obDatabase.ExecuteSQL(sSQL)
				DeletedCount = DeletedCount + 1
			End If
		End If
		If MessageCount / 1000 = INT(MessageCount / 1000) Then
			Elog.Write ("Message checked upto: " & MessageCount)
			wscript.echo ("Message checked upto: " & MessageCount)
		End IF
	End If
Loop
oRead.Close()  ' Close input file

Elog.Write ("")
Elog.Write ("Missing eml files : " & ErrorCount)
Elog.Write ("Deleted DB Rows : " & DeletedCount)
Elog.Write ("Rows checked : " & MessageCount - 1)
Elog.Write ("")
Elog.Write ("End process hm_Messages")

wscript.echo ("Missing eml files : " & ErrorCount)
wscript.echo ("Deleted DB Rows : " & DeletedCount)
wscript.echo ("Rows checked : " & MessageCount - 1)
wscript.echo ("See hmailserver_events.log for more detail on the missing files.")
Second the calling bat file called HMSdatabaseRecordCheck.bat :

Code: Select all

@echo off
rem ***************************************************************************************
rem
rem   Modify all parameter values below where marked   " & :<-----  blah blah "  and delete
rem   all whitespace/spaces and the " & :<-----  blah blah "  instruction from the line.
rem   There are 7 of them to review and update.
rem
rem   ENSURE ALL WHITESPACE/spaces are deleted at the end of your parameter value
rem
rem ***************************************************************************************
 
set ScriptPath=C:\Users\Administrator\Documents\BatchFiles      & :<---  Enter the path where your HMSdatabaseRecordCheck.vbs script resides
set HMSpassword=HMSsecretpassword                               & :<---  Enter Hmailserver admin password

rem ------------
rem MYSQL vars
rem ------------

set mysqlexe="C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe"        & :<-- INPUT YOUR MYSQL.exe path/executable here
set mysqluser=root                            & :<-- INPUT YOUR MySQL db user
set mysqlpass=sqlpass                         & :<-- INPUT YOUR MySQL db password
set mysqldb=mymaildb                          & :<-- INPUT YOUR database name

rem ------------
rem Hmail Vars
rem ------------

set HMSProgDir=C:\Program Files (x86)\hmailserver

rem ------------------------------------------------------------------------
rem Do not modify below this line
rem ------------------------------------------------------------------------
cls
color 0e
set ActionFlag=0
set /p ActionFlag="Enter Action flag 0=Report Only, 1=Update (default=0): "

SET INIpath=""
If exist "%HMSProgDir%"\bin set INIpath="%HMSProgDir%"\bin
if %INIpath% == "" CLS & color 0c & echo.&echo. !!! Hmailserver bin directory not found! (%HMSProgDir%\bin) & goto eof
set INIFILE=%INIpath%\hmailserver.ini
FOR /F "eol=; eol=[ tokens=1,2* delims==" %%i in ('findstr /b /l /i DataFolder= %INIFILE%') DO set DataDir=%%~j

rem ' Main, squish sql controls
set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb%
rem ' Main, empty var space
set mysqlexe=
set mysqluser=
set mysqlpass=
set mysqldb=

rem ' Main, create sql script to collect file names and IDs of contents
rem - the following is one line, and [b]should not be word-wrapped[/b]

echo SELECT hm_messages.messagefilename,hm_messages.messageid,SPACE(8-length(hm_messages.messageid)),hm_accounts.accountaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_accounts.accountid;>%DataDir%\buildsql.sql
%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:%DataDir% /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%

rem Cleanup
del %DataDir%\MessageList.txt
del %DataDir%\buildsql.sql
echo.
echo.
echo.
color 0a
@echo off
echo FINISHED!
pause
:eof
Notes on speed/timings:
I ran this and it took 17 seconds (on read only check) to check nearly 32900 database records. However, running it a second time, it took TWO seconds (probably due to caching). This should give some idea of timings based on your message count.

With 'Action Flag'=1 (ie, update mode), the time might be a little longer to run if it has records to delete as the Hmailserver API is considerably slow (it is only called on each missing message). Obviously the speed deficit depends on actually how many records it finds that need deleting. (Rough example, allow 1 second per deletion required).

Also, I have the Hmailserver data folder indexed (with windows INDEX OPTIONS). Maybe this adds added benefit in speed file lookups (although I cannot clarify either way).


FOOTNOTE:

Other scripts are also available throughout this forum. Noticeably, there is one here that simply uses SQL commands and not the Com API (but should be equally as quick). Caution should be taken with such methods that do not use the Com API for updates: https://www.hmailserver.com/forum/viewtopic.php?t=19341
Last edited by jimimaseye on 2016-02-23 13:03, edited 7 times in total.
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

SniffTheGlove
Normal user
Normal user
Posts: 60
Joined: 2014-08-22 20:43

Re: HOW TO: Read database Messages file to check the physical file exists

Post by SniffTheGlove » 2015-02-17 09:00

Thanks Jim

After running the scripts I hade made a few modifications for my own needs.

I was writing a script in the EventHandlers.vbs to do some specific SPAM handling and messed up by creating 90,000 test messages.
See
So I ran Jim's script above and it worked great however I noticed that at some point hMailServer recreated the deleted messages by creating dummy Undeliverable eml files. I have no idea why it did but presume it is some function of hMailServer.
From: Postmaster
Subject: Message undeliverable
Date: Sat, 14 Feb 2015 08:33:04 +0000

The mail server could not deliver the message to you since the file C:\Program Files\hMailServer\Data\mail.localhost\ham\EE\{EEE653FE-EF6C-4AEE-A124-A4EDD0F1A4DB}.eml does not exist on the server.

The file may have been deleted by anti virus software running on the server.

hMailServer
So I modified the scripst to also seek out these undeliverable messages and delete those as well (physically from disk and database)


HMSdatabaseRecordCheck.bat

Code: Select all

@echo off
cls

set ScriptPath=C:\Users\Me\Documents\Server~1
rem ------------
rem MYSQL vars
rem ------------

set mysqlexe=C:\Bitnami\wampstack\mysql\bin\Mysql.exe
set mysqluser=root
set mysqlpass=********
set mysqldb= mymailserver

rem ------------
rem Hmail Vars
rem ------------

set DataDir=C:\Progra~1\hMailServer\Data

rem ------------------------------------------------------------------------
rem Do not modify below this line
rem ------------------------------------------------------------------------

rem ' Main, squish sql controls
set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb%
rem ' Main, empty var space
set mysqlexe=
set mysqluser=
set mysqlpass=
set mysqldb=

rem ' Main, create sql script to collect file names and IDs of contents
rem - the following is one line, and [b]should not be word-wrapped[/b]

echo Creating SQL to into %DataDir%\buildsql.sql

echo SELECT hm_messages.messageid, hm_messages.messagefilename, hm_accounts.accountaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_accounts.accountid ORDER BY hm_messages.messageid;>%DataDir%\buildsql.sql
echo Running SQL to dump into %DataDir%\MessageList.txt
%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt
echo Running %ScriptPath%\HMSdatabaseRecordCheckScript.vbs /DataDir:%DataDir%
cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheckScript.vbs /DataDir:%DataDir%

rem Cleanup
del %DataDir%\MessageList.txt
del %DataDir%\buildsql.sql

HMSdatabaseRecordCheckScript.vbs

Code: Select all

' ModIfied Script from
' https://www.hmailserver.com/forum/viewtopic.php?f=20&t=27646&p=170991#p170991
'
' This script checks that a physical file exist for every email that exists in the database (hm_messages).
' Set Debug to.....
'      = 0 to NOT provide a verbose output to the Event Log
'      = 1 to provide a verbose output to the Event Log
' Set Mode to 0 to provide a report in the Event Log
' Set Mode to.....
'      = 0 to provide a Full Report only in the Event Log. You must set Debug to 1
'      = 1 to delete the database record If the eml does not exist in the Data directories
'      = 2 to delete the database record and the eml If the eml is a dummy Undeliverable (See below)
'      = 3 to do Mode 1 and Mode 2
'
'Dummy Undeliverable eml file
'      From: Postmaster
'      Subject: Message undeliverable
'      Date: Sat, 14 Feb 2015 08:33:04 +0000
'
'      The mail server could not deliver the message to you since the file C:\Program Files\hMailServer\Data\mail.localhost\ham\EE\{EEE653FE-EF6C-4AEE-A124-A4EDD0F1A4DB}.eml does not exist on the server.
'
'      The file may have been deleted by anti virus software running on the server.
'
'      hMailServer

Option Explicit

Const AdminUser = "administrator"
Const AdminPassword = "***********"

Dim HMS_Database_Record_Check_Search_For_One, HMS_Database_Record_Check_Search_For_Two, Mode
Dim HMS_Database_Record_Check_Prefix
Dim MessageCount, ErrorCount, DeletedCount, UndeliverableCount, Comma_One, Comma_Two, Length_Of_MessageInputLine, File_Size, Debug
Dim obApp, oDomain, oAccount, FSO, objFSO, objFile, oFSO, oReadObj, oRead, EventLog
Dim DataDir, SearchFile, MessageInputLine, SearchfileFolder, CheckFilePath, AccountAddr, MessageID
Debug = 1
Mode = 0
MessageCount = 0
ErrorCount = 0
DeletedCount = 0
UndeliverableCount = 0

HMS_Database_Record_Check_Prefix = "HMS_Database_Record_Check: "
HMS_Database_Record_Check_Search_For_One = "Subject: Message undeliverable"
HMS_Database_Record_Check_Search_For_Two = "does not exist on the server"
DataDir = WScript.Arguments.Named("DataDir")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oReadObj  = CreateObject("Scripting.FileSystemObject")
Set oRead = oReadObj.OpenTextFile(DataDir & "\MessageList.txt", 1)
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate(AdminUser, AdminPassword)
Set EventLog = CreateObject("hMailServer.EventLog")
'If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Start process")
'If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Debug = " & Debug)
'If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Mode = " & Mode)
Do Until oRead.AtEndOfStream
	MessageInputLine = oRead.ReadLine
	MessageInputLine = Replace(MessageInputLine,vbTab,",")
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine: " & MessageInputLine)
	Length_Of_MessageInputLine = Len(MessageInputLine)
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Length: " & Length_Of_MessageInputLine)
	Comma_One = InStr(1,MessageInputLine,",")
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Comma_One: " & Comma_One)
	Comma_Two = InStr(Comma_One + 1,MessageInputLine,",")
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Comma_Two: " & Comma_Two)
	MessageID = trim(mid(MessageInputLine,1,Comma_One - 1 ))
    If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Message ID: " & MessageID)	
	SearchFile = trim(mid(MessageInputLine,Comma_One + 1,Comma_Two - 1 - Comma_One ))
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine File: " & SearchFile)
	AccountAddr = trim(mid(MessageInputLine,Comma_two + 1,Length_Of_MessageInputLine - Comma_Two +1 ))
	If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "MessageInputLine Email Address: " & AccountAddr)
	MessageCount = MessageCount + 1
	If (MessageCount > 1) then 
		oDomain = mid(AccountAddr,InStr(AccountAddr,"@") + 1)
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Domain: " & oDomain)
		oAccount = left(AccountAddr,InStr(AccountAddr,"@")-1)
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Account: " & oAccount)
		SearchfileFolder = mid(SearchFile,2,2)
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "SearchfileFolder: " & SearchfileFolder)
		CheckFilePath = DataDir & "\" & oDomain & "\" & oAccount & "\" & SearchfileFolder & "\" & SearchFile
		If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "CheckFilePath: " & CheckFilePath)
		If (FSO.FileExists(CheckFilePath)) then
			If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " exists in the Data directories")
			set objFile = objFSO.getFile(CheckFilePath)
			If (objFile.size > 0) then
				File_Size = Round(ObjFile.size / 1024, 2)
				If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " size is " & File_Size & "kb")
				If InStr(oFSO.OpenTextFile(CheckFilePath).ReadAll, HMS_Database_Record_Check_Search_For_One) > 0 and InStr(oFSO.OpenTextFile(CheckFilePath).ReadAll, HMS_Database_Record_Check_Search_For_Two) > 0 Then
					UndeliverableCount = UndeliverableCount + 1
					If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " is an undeliverable")
					If ((Mode = 2) or (Mode = 3)) then
						obApp.Domains.ItemByName(oDomain).Accounts.ItemByAddress(AccountAddr).Messages.DeleteByDBID(MessageID)
						objFile.DeleteFile CheckFilePath 
					End If
					If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from hMailServer")
					If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from Data Directory")
					DeletedCount = DeletedCount + 1
				End If
			Else
				If ((Mode = 2) or (Mode = 3)) then
					objFile.DeleteFile CheckFilePath
				End If
				If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from Data Directory")
				DeletedCount = DeletedCount + 1
			End If
		Else
			If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " does not exist in the Data directories")
			ErrorCount = ErrorCount + 1
			If ((Mode = 1) or (Mode = 3)) then
				obApp.Domains.ItemByName(oDomain).Accounts.ItemByAddress(AccountAddr).Messages.DeleteByDBID(MessageID)
			End If
			If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & CheckFilePath & " has been deleted from hMailServer")
			DeletedCount = DeletedCount + 1
		End If
	End If
Loop
oRead.Close()  ' Close input file

If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Missing *.eml files : " & ErrorCount)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Undeliverable *.eml files : " & UndeliverableCount)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Deleted DB Rows : " & DeletedCount)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "Rows checked : " & MessageCount - 1)
If (Debug = 1) then EventLog.Write (HMS_Database_Record_Check_Prefix & "End Process")


--
Sniff

hMailServer 5.6 B2145 on Windows 7 Pro Workstation (My Mini Server running Mail,Web and DNS)

9657d3c8658701f14294f87a8ee0d878

User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2015-02-17 10:47

cheers sniff

For the avoidance of doubt to readers, the 'undeliverables' that you mentioned 'reappearing' (prompting you to rewrite the script) were NOT a consequence of the original script, and that the original script can be used for the purpose initially described. That is that the original script DOES NOT create these undeliverables so shouldnt worry about running the script. (Readers should note that the cause of this condition prompting the posted rewrite was due to another problem unrelated script poster "sniff" had run earlier that generated thousands of rogue spam mails.)
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2016-02-19 16:01

Original script modified to use SQL delete command instead of ComAPI 'deletebyID' to prevent crash where hm_message records are *orphaned* from any existing account/domain.
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2016-02-23 21:41

BAT script modified to auto-search for data directory.
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Marziano
New user
New user
Posts: 1
Joined: 2019-03-22 18:15

Re: HOW TO: Read database Messages file to check the physical file exists

Post by Marziano » 2019-03-22 18:20

I'm a little late at the party :-)

Sorry for the possibly apparently dumb question, but: will this script work even if i'm running microsoft SQL server instead of MySQL? Thank you

User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2019-03-22 19:58

This script is for databases using an external MYSQL database and utilises both SQL commands and the Com API to ensure things are done whilst maintaining Hmailserver integrity. It should work with other flavours of external SQL database but you would have to make the necessary amendments to paths and executable in the BAT file to suit your system.
.... as i don't know how to.
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

northernsoul
New user
New user
Posts: 4
Joined: 2006-10-03 18:06
Location: GB

Re: HOW TO: Read database Messages file to check the physical file exists

Post by northernsoul » 2020-07-24 16:06

jimimaseye,

A big thank you for the above.

I don't know what went wrong on my installation but I suspect it has been happening for years. It is only on a single user account, where emails seem to have been duplicated many times and stored both in the data directory and wriitten to the database but where not being show to the user via IMAP.

That user account grew to:- 167GB of data and in excess of 2.7million emails.

I had managed to sort everything except update the database file. Then I found the above script and after running for about 36 hours has sorted the database out.

Thanks again for the script.

User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2020-07-24 19:48

👍 cheers.

[Entered by mobile. Excuse my spelling.]
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

mivimex
New user
New user
Posts: 15
Joined: 2014-04-10 09:54

Re: HOW TO: Read database Messages file to check the physical file exists

Post by mivimex » 2020-10-06 17:03

For those, who use MS SQL Compact, going in bundle with HMS instead of separate MySql server (I believe that there is plenty of such installations)
HMS does not have any opportunity to delete outdated database records, which does not match any e-mail file. This affects to disk space quotas and poor disk usage.
The only way to locate and delete such records is kind of script.
You can migrate to MySql, but if you want to continue using embedded MSSQL CE, you have to connect to database.

A colleague of mine (thank you, Nikolai) has adapted and tested .vbs script (attached) and here are some comments:

1. Get MSSQL database password, use DecryptBlowfish.vbs - (see https://www.hmailserver.com/documentati ... t_to_mssql)
2. Get MS SQL Server Compact 3.5 with SP2 from here: https://www.microsoft.com/en-US/downloa ... px?id=5783
3. Install it, remember that x64 OS requires both x32 and x64 versions, so
- stop HMS service
- make database backup (hMailServer.sdf)
- install first x32, then x64 MS SQL CE 3.5 SP2 software (see http://support.microsoft.com/kb/974247)
4. Adapt .vbs code (database location, data files location, database password. See !!!!! characters)
5. Comment deleteSQL command (line 74)
6. Run script and analise log file: script logs outdated database records (line 13 should be uncommented)
7. If OK, uncomment deleteSQL command and run script; wrong records will be deleted from database.
8. Start HMS service and enjoy refreshed database )

========
tested at HMS 5.6.5-B2367 x32, running at Windows server 2016 x64

Code: Select all

'Imports System.Data.SqlServerCe

sub log(logfile, message)
	'wscript.echo(message)
	logfile.writeLine(message)
end sub

'Recursive folder traversal function
Sub CheckSubFolders(Folder)
	'Search for the required file in each Subfolder
	For Each file in Folder.Files
		FilesOnData.Add file.Name
'		Call log (LogFile, file.Name)   '!!!!! Logging existing files
		Next

	'Looping through subfolders
	For Each Subfolder in Folder.SubFolders
		CheckSubFolders (Subfolder)

	Next
End Sub

'declare the variables
Dim ConnectionString
Dim Recordset
Dim SQL
Dim cn
Dim HMData
Dim FilesOnData

set objFSO = CreateObject("Scripting.FileSystemObject")
set LogFile = objfso.opentextfile("mail-check.log",2,True)
set FilesOnData = CreateObject("System.Collections.ArrayList")

HMData = "C:\Program Files\hMailServer\Data\"  ' !!!! Location of e-mail files


'declare the SQL statement that will query the database
SQL = "SELECT MESSAGEID, MESSAGEFILENAME FROM HM_MESSAGES"

'define the connection string, specify database driver
set cn = CreateObject("ADODB.Connection")
set Recordset = CreateObject("ADODB.Recordset")

cn.connectionstring = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Program Files\hMailServer\Database\hMailServer.sdf;ssce:database password=FFFFFFFFFFFF" '!!!!! database password !!!!!

cn.open

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,cn

call log(LogFile, "START CHECKING MAILS AND FILES")

'Create a list of e-mails on disk
CheckSubFolders (objFSO.GetFolder(HMData))

'first of all determine whether there are any records
If Recordset.EOF Then
call log(LogFile, "No records returned.")
Else


'if there are records then loop through the fields
Do While NOT Recordset.Eof
id = Recordset("MESSAGEID")
filename = Recordset("MESSAGEFILENAME")



'if Not objFSO.FileExists(filename) then
if FilesOnData.IndexOf(filename, 0) < 0 then
	deleteSQL = "DELETE FROM HM_MESSAGES WHERE MESSAGEID = '" & id & "'"
	call log(LogFile, "ID: " & id & " - " & filename & " > DELETED")
'	cn.execute deleteSQL ' !!!!!! Deleting DATABASE records!!!!!
end if
Recordset.MoveNext
Loop
End If

'close the connection and recordset objects freeing up resources
Recordset.Close
Set Recordset=nothing
cn.Close
Set cn=nothing

call log(LogFile, "DONE") 
LogFile.Close

mivimex
New user
New user
Posts: 15
Joined: 2014-04-10 09:54

Re: HOW TO: Read database Messages file to check the physical file exists

Post by mivimex » 2020-10-07 10:12

update: when testing, ~300 outdated entries deleted, the total amount of messages ~40000, script worked <1min
+
6.line 13 should be uncommented only if you want to log every existing file, this make script work much longer.
Line 73 logs only database file entries to be deleted

!!No email files are deleted, just database entries!!!

User avatar
jimimaseye
Moderator
Moderator
Posts: 8778
Joined: 2011-09-08 17:48

Re: HOW TO: Read database Messages file to check the physical file exists

Post by jimimaseye » 2020-10-07 13:08

Thanks. I have updated the initial post and referred to your script above for Internal Database.
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Post Reply