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: 10060
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: 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: 10060
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: 10060
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: 10060
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: 10060
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: 10060
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: 10060
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

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-06 10:31

jimimaseye wrote:
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: viewtopic.php?t=19341
Hello,

I was trying to run this script as it is - edited settings based on given instruction but it wasn't success, please see screenshot:
Image

I just copied and pasted the codes and edited the path and change credentials then run the .bat files but no luck. I need to run this as I am getting an error after migrating from 5.6 to 5.7 similar to this:

Code: Select all

"ERROR"	10636	"2021-06-06 10:52:23.688"	"Severity: 3 (Medium), Code: HM5026, Source: PersistentMessage::EnsureFileExistance, Description: Message retrieval failed because message file C:\Program Files\hMailServer\Data\domain.com\user\D7\{D7A00195-7A9F-4233-B971-1E63157EFBF9}.eml did not exist."
"ERROR"	11868	"2021-06-06 10:26:00.780"	"Severity: 3 (Medium), Code: HM4403, Source: Message::GetHeader, Description: Could not read the message header, since the file was not available. File: C:\Program Files\hMailServer\Data\domain.com\user\F0\{F0E07424-87E6-4C0E-BECD-1092DF3E0BBF}.eml"

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

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

Post by jimimaseye » 2021-06-06 10:57

ashtec014 wrote:
2021-06-06 10:31
Hello,

I was trying to run this script as it is - edited settings based on given instruction but it wasn't success, please see screenshot:....
Did you complete all variables shown here (including the last one) ?

Code: Select all

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
If so, please change this line:

Code: Select all

set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb% 
To

Code: Select all

set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database=%mysqldb% 
And try again. Let me know what version of mysql you are using and how you get on please.

[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

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-06 11:41

jimimaseye wrote:
2021-06-06 10:57
Did you complete all variables shown here (including the last one) ?

Code: Select all

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
If so, please change this line:

Code: Select all

set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb% 
To

Code: Select all

set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database=%mysqldb% 
And try again. Let me know what version of mysql you are using and how you get on please.

[Entered by mobile. Excuse my spelling.]
I replaced all variables including the last one. I changed the line as well from

Code: Select all

set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database %mysqldb% 
To:

Code: Select all

set mysql=%mysqlexe% -u%mysqluser% -p%mysqlpass% --database=%mysqldb% 
I saved it, then tried to run the .bat file again and this is what it popped-up:
Image

This is the code after editing it:

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\DATABASE MESSAGE FILE CHECKER\HMSdatabaseRecordCheck.vbs
set HMSpassword=*****

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

set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysqldump.exe"
set mysqluser=root
set mysqlpass=*****
set mysqldb=******

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

set HMSProgDir=C:\Program Files\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="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysqldump.exe"
set mysqluser=root
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
Change this:

Code: Select all

set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysqldump.exe"
To:

Code: Select all

set mysqlexe=C:\wamp64\bin\mysql\mysql5.7.23\bin\mysql.exe
Got different error:
Image

palinka
Senior user
Senior user
Posts: 4461
Joined: 2017-09-12 17:57

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

Post by palinka » 2021-06-06 16:29

ashtec014 wrote:
2021-06-06 11:41

Code: Select all

set ScriptPath=C:\Users\Administrator\Documents\DATABASE MESSAGE FILE CHECKER\HMSdatabaseRecordCheck.vbs
Try putting quotes around the path. Or change the folder name so there are no spaces in the name.

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

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

Post by jimimaseye » 2021-06-06 16:34

set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysqldump.exe
Why is that like that?? That is not in the guide. It should be mysql.exe.

[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

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-06 17:42

palinka wrote:
2021-06-06 16:29
ashtec014 wrote:
2021-06-06 11:41

Code: Select all

set ScriptPath=C:\Users\Administrator\Documents\DATABASE MESSAGE FILE CHECKER\HMSdatabaseRecordCheck.vbs
Try putting quotes around the path. Or change the folder name so there are no spaces in the name.
Hi,

Yeah, I changed folder name as well as put quotes around the path (set ScriptPath="C:\Users\Administrator\Documents\EmailFileChecker\HMSdatabaseRecordCheck.vbs") and got an error:

Image

This is the folder of these files:

Image
Last edited by ashtec014 on 2021-06-06 17:44, edited 1 time in total.

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-06 17:43

jimimaseye wrote:
2021-06-06 16:34
set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysqldump.exe
Why is that like that?? That is not in the guide. It should be mysql.exe.

[Entered by mobile. Excuse my spelling.]
I already changed it to

Code: Select all

set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysql.exe"
but still got an error. :(

palinka
Senior user
Senior user
Posts: 4461
Joined: 2017-09-12 17:57

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

Post by palinka » 2021-06-06 18:04

ashtec014 wrote:
2021-06-06 17:42
Image
Look at the vbs path very carefully. :lol:

Edit - 99.9999% of "path can't be found" errors are a simple typo in the path (or spaces).

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-06 18:14

palinka wrote:
2021-06-06 18:04

Look at the vbs path very carefully. :lol:

Edit - 99.9999% of "path can't be found" errors are a simple typo in the path (or spaces).
I don't know where to check now everything looks fine, I've already double check the path including white spaces. :(

Here's the full code of 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\EmailFileChecker\HMSdatabaseRecordCheck.vbs"
set HMSpassword=*****

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

set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysql.exe"
set mysqluser=root
set mysqlpass=****
set mysqldb=****

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

set HMSProgDir=C:\Program Files\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
I copied HMSdatabaseRecordCheck.vbs as it is. No edit has been made.

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.")

palinka
Senior user
Senior user
Posts: 4461
Joined: 2017-09-12 17:57

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

Post by palinka » 2021-06-06 18:42

ashtec014 wrote:
2021-06-06 18:14
palinka wrote:
2021-06-06 18:04

Look at the vbs path very carefully. :lol:

Edit - 99.9999% of "path can't be found" errors are a simple typo in the path (or spaces).
I don't know where to check now everything looks fine, I've already double check the path including white spaces. :(

Here's the full code of 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\EmailFileChecker\HMSdatabaseRecordCheck.vbs"
set HMSpassword=*****

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

set mysqlexe="C:\wamp64\bin\mysql\mysql5.7.23\bin\mysql.exe"
set mysqluser=root
set mysqlpass=****
set mysqldb=****

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

set HMSProgDir=C:\Program Files\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
I copied HMSdatabaseRecordCheck.vbs as it is. No edit has been made.

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.")
set ScriptPath="C:\Users\Administrator\Documents\EmailFileChecker\HMSdatabaseRecordCheck.vbs"

Plus:

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs

Results in this:

Can not find script file: C:\Users\Administrator\Documents\EmailFileChecker\HMSdatabaseRecordCheck.vbs\HMSdatabaseRecordCheck.vbs

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-06 18:50

palinka wrote:
2021-06-06 18:42

set ScriptPath="C:\Users\Administrator\Documents\EmailFileChecker\HMSdatabaseRecordCheck.vbs"

Plus:

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs

Results in this:

Can not find script file: C:\Users\Administrator\Documents\EmailFileChecker\HMSdatabaseRecordCheck.vbs\HMSdatabaseRecordCheck.vbs
I've deleted HMSdatabaseRecordCheck.vbs from set ScriptPath=C:\Users\Administrator\Documents\EmailFileChecker still an error occurred

Image

I don't know what else needed to modify. :( I badly needed to run this script because some email files are not available acdg to the server error log and some users complaint about undeliverable emails they received.

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

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

Post by jimimaseye » 2021-06-06 19:05

You've made too many errors or changes to keep up. I know the setup works if followed as guided in the original post. Start again with fresh copies of the scripts.

Change

@echo off

To

@echo on

In the bat file, run it and paste ALL output.
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

palinka
Senior user
Senior user
Posts: 4461
Joined: 2017-09-12 17:57

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

Post by palinka » 2021-06-06 19:12

jimimaseye wrote:
2021-06-06 19:05
In the bat file, run it a paste ALL output.
Yes! Please post text, not images.

User avatar
mattg
Moderator
Moderator
Posts: 22437
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

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

Post by mattg » 2021-06-07 01:35

ashtec014 wrote:
2021-06-06 10:31
I need to run this as I am getting an error after migrating from 5.6 to 5.7 similar to this:

Code: Select all

"ERROR"	10636	"2021-06-06 10:52:23.688"	"Severity: 3 (Medium), Code: HM5026, Source: PersistentMessage::EnsureFileExistance, Description: Message retrieval failed because message file C:\Program Files\hMailServer\Data\domain.com\user\D7\{D7A00195-7A9F-4233-B971-1E63157EFBF9}.eml did not exist."
"ERROR"	11868	"2021-06-06 10:26:00.780"	"Severity: 3 (Medium), Code: HM4403, Source: Message::GetHeader, Description: Could not read the message header, since the file was not available. File: C:\Program Files\hMailServer\Data\domain.com\user\F0\{F0E07424-87E6-4C0E-BECD-1092DF3E0BBF}.eml"

These errors normally indicate that the antivirus software installed on the hMailserver server machine hasn't EXCLUDED the data directory from AV scanning
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-07 08:20

jimimaseye wrote:
2021-06-06 19:05
You've made too many errors or changes to keep up. I know the setup works if followed as guided in the original post. Start again with fresh copies of the scripts.

Change

@echo off

To

@echo on

In the bat file, run it and paste ALL output.
Created new folder and start fresh copies of the script, edited 7 parameters as suggested, turning @echo on and here's the output:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>color 0e

C:\Users\Administrator\Documents\CheckFile>set ActionFlag=0

C:\Users\Administrator\Documents\CheckFile>set /p ActionFlag="Enter Action flag
0=Report Only, 1=Update (default=0): "
Enter Action flag 0=Report Only, 1=Update (default=0): 1

C:\Users\Administrator\Documents\CheckFile>SET INIpath=""

C:\Users\Administrator\Documents\CheckFile>If exist "C:\Program Files\hmailserve
r"\bin set INIpath="C:\Program Files\hmailserver"\bin

C:\Users\Administrator\Documents\CheckFile>if "C:\Program Files\hmailserver"\bin
 == "" CLS   & color 0c   & echo. & echo. !!! Hmailserver bin directory not foun
d! (C:\Program Files\hmailserver\bin)   & goto eof

C:\Users\Administrator\Documents\CheckFile>set INIFILE="C:\Program Files\hmailse
rver"\bin\hmailserver.ini

C:\Users\Administrator\Documents\CheckFile>FOR /F "eol=; eol=[ tokens=1,2* delim
s==" %i in ('findstr /b /l /i DataFolder "C:\Program Files\hmailserver"\bin\hmai
lserver.ini') DO set DataDir=%~j

C:\Users\Administrator\Documents\CheckFile>set DataDir=C:\Program Files\hMailSer
ver\Data

C:\Users\Administrator\Documents\CheckFile>rem ' Main, squish sql controls

C:\Users\Administrator\Documents\CheckFile>set mysql="C:\Program Files\MySQL\MyS
QL Server 5.5\bin\Mysql.exe" -uroot -pMy --database mymaildb

C:\Users\Administrator\Documents\CheckFile>rem ' Main, empty var space

C:\Users\Administrator\Documents\CheckFile>set mysqlexe=

C:\Users\Administrator\Documents\CheckFile>set mysqluser=

C:\Users\Administrator\Documents\CheckFile>set mysqlpass=

C:\Users\Administrator\Documents\CheckFile>set mysqldb=

C:\Users\Administrator\Documents\CheckFile>rem ' Main, create sql script to coll
ect file names and IDs of contents

C:\Users\Administrator\Documents\CheckFile>rem - the following is one line, and
[b]should not be word-wrapped[/b]

C:\Users\Administrator\Documents\CheckFile>echo SELECT hm_messages.messagefilena
me,hm_messages.messageid,SPACE(8-length(hm_messages.messageid)),hm_accounts.acco
untaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_
accounts.accountid; Files\hMailServer\Data\buildsql.sql 1>C:\Program

C:\Users\Administrator\Documents\CheckFile>"C:\Program Files\MySQL\MySQL Server
5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb Files\hMailServer\
Data\buildsql.sql Files\hMailServer\Data\MessageList.txt 0<C:\Program 1>C:\Progr
am

C:\Users\Administrator\Documents\CheckFile>cscript.exe //NoLogo C:\Users\Adminis
trator\Documents\CheckFile\HMSdatabaseRecordCheck.vbs /DataDir:C:\Program Files\
hMailServer\Data /ActionFlag:1 /HMSpassword:MyPassword
C:\Users\Administrator\Documents\CheckFile\HMSdatabaseRecordCheck.vbs(32, 1) Mic
rosoft VBScript runtime error: Path not found


C:\Users\Administrator\Documents\CheckFile>rem Cleanup

C:\Users\Administrator\Documents\CheckFile>del C:\Program Files\hMailServer\Data
\MessageList.txt
The system cannot find the path specified.

C:\Users\Administrator\Documents\CheckFile>del C:\Program Files\hMailServer\Data
\buildsql.sql
The system cannot find the path specified.

C:\Users\Administrator\Documents\CheckFile>echo.


C:\Users\Administrator\Documents\CheckFile>echo.


C:\Users\Administrator\Documents\CheckFile>echo.


C:\Users\Administrator\Documents\CheckFile>color 0a
FINISHED!
Press any key to continue . . .

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-07 08:23

mattg wrote:
2021-06-07 01:35
ashtec014 wrote:
2021-06-06 10:31
I need to run this as I am getting an error after migrating from 5.6 to 5.7 similar to this:

Code: Select all

"ERROR"	10636	"2021-06-06 10:52:23.688"	"Severity: 3 (Medium), Code: HM5026, Source: PersistentMessage::EnsureFileExistance, Description: Message retrieval failed because message file C:\Program Files\hMailServer\Data\domain.com\user\D7\{D7A00195-7A9F-4233-B971-1E63157EFBF9}.eml did not exist."
"ERROR"	11868	"2021-06-06 10:26:00.780"	"Severity: 3 (Medium), Code: HM4403, Source: Message::GetHeader, Description: Could not read the message header, since the file was not available. File: C:\Program Files\hMailServer\Data\domain.com\user\F0\{F0E07424-87E6-4C0E-BECD-1092DF3E0BBF}.eml"

These errors normally indicate that the antivirus software installed on the hMailserver server machine hasn't EXCLUDED the data directory from AV scanning
I excluded data directory, temp folder as well as all hMailserver folder from AV scanning. In fact I temporarily disabled AV before upgrading from 5.6 to 5.7 just to make sure that no one is interrupting the migration. I have no issue like this before I switch to 5.7 (reason to upgrade was because of running out of memory error)

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

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

Post by jimimaseye » 2021-06-07 09:05

change

Code: Select all

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
to

Code: Select all

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
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

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-07 10:23

jimimaseye wrote:
2021-06-07 09:05
change

Code: Select all

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
to

Code: Select all

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
Here's the output after changing and running it:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>color 0e

C:\Users\Administrator\Documents\CheckFile>set ActionFlag=0

C:\Users\Administrator\Documents\CheckFile>set /p ActionFlag="Enter Action flag
0=Report Only, 1=Update (default=0): "
Enter Action flag 0=Report Only, 1=Update (default=0): 0

C:\Users\Administrator\Documents\CheckFile>SET INIpath=""

C:\Users\Administrator\Documents\CheckFile>If exist "C:\Program Files\hMailServe
r"\bin set INIpath="C:\Program Files\hMailServer"\bin

C:\Users\Administrator\Documents\CheckFile>if "C:\Program Files\hMailServer"\bin
 == "" CLS   & color 0c   & echo. & echo. !!! Hmailserver bin directory not foun
d! (C:\Program Files\hMailServer\bin)   & goto eof

C:\Users\Administrator\Documents\CheckFile>set INIFILE="C:\Program Files\hMailSe
rver"\bin\hmailserver.ini

C:\Users\Administrator\Documents\CheckFile>FOR /F "eol=; eol=[ tokens=1,2* delim
s==" %i in ('findstr /b /l /i DataFolder "C:\Program Files\hMailServer"\bin\hmai
lserver.ini') DO set DataDir=%~j

C:\Users\Administrator\Documents\CheckFile>set DataDir=C:\Program Files\hMailSer
ver\Data

C:\Users\Administrator\Documents\CheckFile>rem ' Main, squish sql controls

C:\Users\Administrator\Documents\CheckFile>set mysql="C:\Program Files\MySQL\MyS
QL Server 5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb

C:\Users\Administrator\Documents\CheckFile>rem ' Main, empty var space

C:\Users\Administrator\Documents\CheckFile>set mysqlexe=

C:\Users\Administrator\Documents\CheckFile>set mysqluser=

C:\Users\Administrator\Documents\CheckFile>set mysqlpass=

C:\Users\Administrator\Documents\CheckFile>set mysqldb=

C:\Users\Administrator\Documents\CheckFile>rem ' Main, create sql script to coll
ect file names and IDs of contents

C:\Users\Administrator\Documents\CheckFile>rem - the following is one line, and
[b]should not be word-wrapped[/b]

C:\Users\Administrator\Documents\CheckFile>echo SELECT hm_messages.messagefilena
me,hm_messages.messageid,SPACE(8-length(hm_messages.messageid)),hm_accounts.acco
untaddress FROM hm_messages,hm_accounts WHERE hm_messages.messageaccountid = hm_
accounts.accountid; 1>"C:\Program Files\hMailServer\Data"\buildsql.sql

C:\Users\Administrator\Documents\CheckFile>"C:\Program Files\MySQL\MySQL Server
5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb Files\hMailServer\
Data\buildsql.sql Files\hMailServer\Data\MessageList.txt 0<C:\Program 1>C:\Progr
am

C:\Users\Administrator\Documents\CheckFile>cscript.exe //NoLogo C:\Users\Adminis
trator\Documents\CheckFile\HMSdatabaseRecordCheck.vbs /DataDir:C:\Program Files\
hMailServer\Data /ActionFlag:0 /HMSpassword:MyPassword
C:\Users\Administrator\Documents\CheckFile\HMSdatabaseRecordCheck.vbs(32, 1) Mic
rosoft VBScript runtime error: Path not found


C:\Users\Administrator\Documents\CheckFile>rem Cleanup

C:\Users\Administrator\Documents\CheckFile>del C:\Program Files\hMailServer\Data
\MessageList.txt
The system cannot find the path specified.

C:\Users\Administrator\Documents\CheckFile>del C:\Program Files\hMailServer\Data
\buildsql.sql
The system cannot find the path specified.

C:\Users\Administrator\Documents\CheckFile>echo.


C:\Users\Administrator\Documents\CheckFile>echo.


C:\Users\Administrator\Documents\CheckFile>echo.


C:\Users\Administrator\Documents\CheckFile>color 0a
FINISHED!
Press any key to continue . . .

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

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

Post by jimimaseye » 2021-06-07 12:36

ashtec014 wrote:
2021-06-07 10:23

Here's the output after changing and running it:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>set mysql="C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb

"C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe"

Is this really the path to your MySQL executable? (It wasnt earlier)
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

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-07 15:05

jimimaseye wrote:
2021-06-07 12:36
ashtec014 wrote:
2021-06-07 10:23

Here's the output after changing and running it:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>set mysql="C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb

"C:\Program Files\MySQL\MySQL Server 5.5\bin\Mysql.exe"

Is this really the path to your MySQL executable? (It wasnt earlier)
I have 2 versions of MySQL installed in my server: 5.5 and 5.7.

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

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

Post by jimimaseye » 2021-06-07 15:53

This looks a mess. I dont know if its partly due to the screen output being screwed, or because you have (invisible) CRLF or what:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>"C:\Program Files\MySQL\MySQL Server
5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb Files\hMailServer\
Data\buildsql.sql Files\hMailServer\Data\MessageList.txt 0<C:\Program 1>C:\Progr
am
It is created from this command:

Code: Select all

%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt
therefore should look something like:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>"C:\Program Files\MySQL\MySQL Server
 5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb C:\Program Files\hMailServer
 \Data\buildsql.sql > C:\Program Files\hMailServer\Data\MessageList.txt
(allowing for line wrap)

I know it works and (hopefully) you can see from the command how it is formed and wat it is trying to do. In the end it creates an output file called "MessageList.txt" which is then read by the VBS - but the VBS is erroris with 'nout found' because it hasnt bveen created.

You need to look, and try to debug yourself I think. Try replacing it with

Code: Select all

%mysql%<"%DataDir%"\buildsql.sql>"%DataDir%"\MessageList.txt
(which is enclosing the paths in quotes)

Or even (if you are confident enough) hardcode all path variables with the directory paths:

Code: Select all

%mysql%<"C:\Program Files\hMailServer\Data\buildsql.sql">"C:\Program Files\hMailServer\Data\MessageList.txt"

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:"C:\Program Files\hMailServer\Data" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%
There is only so much I can do.
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

ashtec014
Normal user
Normal user
Posts: 234
Joined: 2019-09-05 11:56

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

Post by ashtec014 » 2021-06-07 17:20

jimimaseye wrote:
2021-06-07 15:53
This looks a mess. I dont know if its partly due to the screen output being screwed, or because you have (invisible) CRLF or what:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>"C:\Program Files\MySQL\MySQL Server
5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb Files\hMailServer\
Data\buildsql.sql Files\hMailServer\Data\MessageList.txt 0<C:\Program 1>C:\Progr
am
It is created from this command:

Code: Select all

%mysql%<%DataDir%\buildsql.sql>%DataDir%\MessageList.txt
therefore should look something like:

Code: Select all

C:\Users\Administrator\Documents\CheckFile>"C:\Program Files\MySQL\MySQL Server
 5.5\bin\Mysql.exe" -uroot -pMyPassword --database mymaildb C:\Program Files\hMailServer
 \Data\buildsql.sql > C:\Program Files\hMailServer\Data\MessageList.txt
(allowing for line wrap)

I know it works and (hopefully) you can see from the command how it is formed and wat it is trying to do. In the end it creates an output file called "MessageList.txt" which is then read by the VBS - but the VBS is erroris with 'nout found' because it hasnt bveen created.

You need to look, and try to debug yourself I think. Try replacing it with

Code: Select all

%mysql%<"%DataDir%"\buildsql.sql>"%DataDir%"\MessageList.txt
(which is enclosing the paths in quotes)

Or even (if you are confident enough) hardcode all path variables with the directory paths:

Code: Select all

%mysql%<"C:\Program Files\hMailServer\Data\buildsql.sql">"C:\Program Files\hMailServer\Data\MessageList.txt"

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:"C:\Program Files\hMailServer\Data" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%
There is only so much I can do.
This one works for me:

Code: Select all

%mysql%<"C:\Program Files\hMailServer\Data\buildsql.sql">"C:\Program Files\hMailServer\Data\MessageList.txt"

cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:"C:\Program Files\hMailServer\Data" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%
I was able to run the script, restored missing email files and deleted unnecessary DB rows. Thank you so much Jimi. I really appreciate your help. :)

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

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

Post by jimimaseye » 2021-06-07 17:54

🤯😎

Phew! (I have amended the original to encloise %dataDir% in quotes)
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
SorenR
Senior user
Senior user
Posts: 6315
Joined: 2006-08-21 15:38
Location: Denmark

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

Post by SorenR » 2021-06-07 21:08

jimimaseye wrote:
2021-06-07 17:54
🤯😎

Phew! (I have amended the original to encloise %dataDir% in quotes)
I tried the script and bat file from the first post...

This:
set ScriptPath=C:\Users\Administrator\Documents\BatchFiles & :<--- Enter the path where your HMSdatabaseRecordCheck.vbs script resides

does not work for me...

The effect of it results in this:
cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:"%DataDir%" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%

trying to execute this:

cscript.exe //NoLogo %ScriptPath% /DataDir:"%DataDir%" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%

and the CMD prompt (admin rights) and cscript report "Input Error: There is no file extension" ...

Adding "HMSdatabaseRecordCheck.vbs" to "set ScriptPath=C:\Users\Administrator\Documents\BatchFiles\HMSdatabaseRecordCheck.vbs" and everything works...

oops... Windows Server 2003 R2 ... Not Win 10 :oops:
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

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

Post by jimimaseye » 2021-06-07 21:16

SorenR wrote:
2021-06-07 21:08
jimimaseye wrote:
2021-06-07 17:54
🤯😎

Phew! (I have amended the original to encloise %dataDir% in quotes)
I tried the script and bat file from the first post...

This:
set ScriptPath=C:\Users\Administrator\Documents\BatchFiles & :<--- Enter the path where your HMSdatabaseRecordCheck.vbs script resides

does not work for me...

The effect of it results in this:
cscript.exe //NoLogo %ScriptPath%\HMSdatabaseRecordCheck.vbs /DataDir:"%DataDir%" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%

trying to execute this:

cscript.exe //NoLogo %ScriptPath% /DataDir:"%DataDir%" /ActionFlag:%ActionFlag% /HMSpassword:%HMSpassword%

and the CMD prompt (admin rights) and cscript report "Input Error: There is no file extension" ...

Adding "HMSdatabaseRecordCheck.vbs" to "set ScriptPath=C:\Users\Administrator\Documents\BatchFiles\HMSdatabaseRecordCheck.vbs" and everything works...

oops... Windows Server 2003 R2 ... Not Win 10 :oops:
All I know is the original script as posted (without enclosing quotes) worked on my Windows server 2008 at the time of posting.

It will be some time before I am able to retest it under Windows 10.

Reverted back to no quotes.

[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

benweston
New user
New user
Posts: 20
Joined: 2016-01-20 12:45

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

Post by benweston » 2023-11-27 21:16

I'm running this script on a server with about 30GB of free RAM and 1TB of free drive space and I'm getting:

Code: Select all

CScript Error: Execution of the Windows Script Host failed. (Not enough storage is available to complete this operation).
I'm pretty convinced neither RAM nor drive space are the issue as neither creep up noticeably when it's running. The server has about 2.5 million emails on it (that's the number of lines in MessageList.txt) so wonder if it's a VB limitation in terms of reading that file?

Any suggestions on how I can get this working would be much appreciated.

Windows Server 2016, hMailServer 5.6.8-B2574, 64GB RAM, 1TB free drive space.

User avatar
RvdH
Senior user
Senior user
Posts: 3235
Joined: 2008-06-27 14:42
Location: The Netherlands

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

Post by RvdH » 2023-11-27 21:37

benweston wrote:
2023-11-27 21:16
I'm running this script on a server with about 30GB of free RAM and 1TB of free drive space and I'm getting:

Code: Select all

CScript Error: Execution of the Windows Script Host failed. (Not enough storage is available to complete this operation).
I'm pretty convinced neither RAM nor drive space are the issue as neither creep up noticeably when it's running. The server has about 2.5 million emails on it (that's the number of lines in MessageList.txt) so wonder if it's a VB limitation in terms of reading that file?

Any suggestions on how I can get this working would be much appreciated.

Windows Server 2016, hMailServer 5.6.8-B2574, 64GB RAM, 1TB free drive space.
Running on shares?
https://pitstop.manageengine.com/portal ... is-command

You also could try to replace the INT (maximum 32767) within the script, with LONG, SINGLE or even DOUBLE
https://www.promotic.eu/en/pmdoc/Script ... aTypes.htm
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 3235
Joined: 2008-06-27 14:42
Location: The Netherlands

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

Post by RvdH » 2023-12-02 19:11

HMSdatabaseRecordCheck.vbs (using LONG instead of INT)

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 = CLng(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 = cLng(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.")
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 3235
Joined: 2008-06-27 14:42
Location: The Netherlands

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

Post by RvdH » 2023-12-02 20:42

benweston wrote:
2023-11-27 21:16
The server has about 2.5 million emails on it (that's the number of lines in MessageList.txt) so wonder if it's a VB limitation in terms of reading that file?
Think that (the amount of lines) might be the issue and not the INTEGER to LONG conversion, eg: MessageList.txt being to big to be read line by line through VbScript, failing to read all lines within....
maybe some scripting guru here can think of something to split MessageList.txt sequentially, MessageList.001, MessageList.002, MessageList.003 for example, containing 100000 (example number) lines in each
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 3235
Joined: 2008-06-27 14:42
Location: The Netherlands

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

Post by RvdH » 2023-12-02 20:51

@jimimaseye
the part, SPACE(8-length(hm_messages.messageid)) returns a empty column for me, not entirely sure what that is supposed to do
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
katip
Senior user
Senior user
Posts: 1161
Joined: 2006-12-22 07:58
Location: Istanbul

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

Post by katip » 2023-12-02 22:04

RvdH wrote:
2023-12-02 20:42
benweston wrote:
2023-11-27 21:16
The server has about 2.5 million emails on it (that's the number of lines in MessageList.txt) so wonder if it's a VB limitation in terms of reading that file?
Think that (the amount of lines) might be the issue and not the INTEGER to LONG conversion, eg: MessageList.txt being to big to be read line by line through VbScript, failing to read all lines within....
maybe some scripting guru here can think of something to split MessageList.txt sequentially, MessageList.001, MessageList.002, MessageList.003 for example, containing 100000 (example number) lines in each
i made up a text file in same format with 1 + 5M lines, file size about 350MB and fired a

Code: Select all

Do Until oRead.AtEndOfStream
	'basically what the script does
	MessageInputLine = oRead.ReadLine
	rL = rL + 1
Loop
msgbox rL
it returned 5.000.001 in 41 seconds on my modest PC.
Katip
--
HMS 5.7, MariaDB 10.4.10, SA 4.0.0, ClamAV 0.103.8

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

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

Post by jimimaseye » 2023-12-02 23:55

RvdH wrote:
2023-12-02 20:51
@jimimaseye
the part, SPACE(8-length(hm_messages.messageid)) returns a empty column for me, not entirely sure what that is supposed to do
I will look into tomorrow and answer accordingly. (It's been a year or eight since i wrote it) .
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