HOW TO: Upgrading Hmailserver - Absolute to relative data paths check

This section contains user-submitted tutorials.
Post Reply
User avatar
jimimaseye
Moderator
Moderator
Posts: 8095
Joined: 2011-09-08 17:48

HOW TO: Upgrading Hmailserver - Absolute to relative data paths check

Post by jimimaseye » 2015-11-15 12:10

You will, by now have already at least read the recommendations: https://www.hmailserver.com/documentati ... mendations and read the advice on upgrading https://www.hmailserver.com/documentati ... e=overview, and perhaps now you may also have upgraded. This advice below then applies to you.

Old versions of Hmailserver 5.4.1 and earlier used absolute datapaths in the filenames of the .EML files (stored in the database). Upgrading to 5.4.2 or later should involve an automatic process that removes this feature and makes the filename paths relative (that means they will be found in relation to where you have stated the data path to be). This instruction applies up to current latest version of Hmailserver 5.6.x.

It may be that the upgrade process successfully runs the necessary changes and/or you may never need to worry about what I have said above. However, for completeness, and to prevent any doubt of problems in the future being caused due to the lack of changes (problems, for example, when you choose to relocate your data), if possible I advise to do a simply check to ensure that upgrade has completed the changes to your database by following this procedure.

Note: This relies on the ability to access your database and that you are safe in the knowledge you know what you are doing. If you do not know how to access your external database or you are asking "what does he mean by 'database'?" then you should stop reading now and do not go any further! Those using the INTERNAL database to access and update it would require extra 3rd party software such as DatabaseBrowserPortable (archived here). So if you are running the internal database and do not have such software then stop reading now. To access with DatabaseBrowserPortable you will need to decrypt the database password from your hmailserver INI file to access the internal database. See footnote below**.

PERFORM THE CHECK AND UPDATE

CHECK

Open your SQL database prompt/admin tool of choice and connect to your Hmailserver database. Then run the following SQL statement:

Code: Select all

SELECT * FROM hm_messages where LEFT(messagefilename, 1) <> "{";
NOTE:
Alternative: Some SQL flavours do not like the LEFT() function (such as DatabaseBrowser). If you encounter an error due to using incorrect syntax then use this alternative command:

Code: Select all

SELECT * FROM hm_messages where messagefilename not like '{%' ;

UPDATE

The above check MAY show a number of records (containing full pathnames instead of filenames only in the 'filename' field). This indicates records that have not had the absolute path removed. (If no records are returned, then all is fine and you can stop/exit).

Warning: It is also advisable to do this AFTER you have done a database backup first. Again, if you do not know what you are doing here then STOP as you are about to modify your database.

If records were returned in the check, run the following update SQL:

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
NOTE:
Alternative: Some SQL flavours do not like the RIGHT() or REPLACE functions (such as DatabaseBrowser). If you encounter an error due to using incorrect syntax then use this alternative command:

Code: Select all

UPDATE hm_messages SET messagefilename=substring( messagefilename , len ( messagefilename ) - 41 , 42 ) where messagefilename not like '{%' ;
(if you still have problems then please research the specific command for your particular database for its 'substr' and 'len' variation)

And then check with the SQL first statement again (this time hopefully not showing any records).

RESTART

If you needed to update the records, then now stop and restart your Hmailserver service.


** FOOTNOTE:
To access the internal database using DatabaseBrowserPortable you wll need the database password:
  • a, open your HMAILSERVER.INI file and select the 'password' string under
    [Database]
    Password=a2bd344fc6608264d2d6b3ca2f89303
    b, Run hmailsever/Addons/Utilities/DecryptBlowfish.vbs and where prompted enter the string copied. This will create and display a short string of characters (this is the password). Copy this string of characters.

    c, Run DatabaseBrowserPortable, and choose 'Connections - Add' and point to your internal Data directory database (remember to choose MSSQL CE as database type). Enter "Hmailserver" as the User name and the above decrypted string as the password.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 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
mattg
Moderator
Moderator
Posts: 20028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Upgrading Hmailserver - Absolute to relative data paths check

Post by mattg » 2015-11-16 00:49

jimimaseye wrote:Upgrading to 5.4 or later should involve an automatic process that removes this feature and makes the filename paths relative (that means they will be found in relation to where you have stated the data path to be).
I'm not certain that this is correct.

I think the only way to upgrade all paths is to run the Data Directory Synchroniser, or manually edit the database entries.
I chose to manually change the database entries as you have done.
jimimaseye wrote:Then run the following SQL statement...
It should be noted that the SQL statements shown are for MySQL and not necessarily other SQL servers supported by hMailserver. These are also the commands that I used, but they won't fit every installation.

It should ALSO be noted that the number of characters in the update query is from the specific instance of jimimaseye's server, and my well be different on your server. It really depends on the install path and more specifically the path to the hMailserver data directory. Installing hMailserver in a default location on a 64 bit machine will NOT make 42 the correct number of characters. My recollection is that 42 is correct when hMailserver is installed to "C:\Program Files\hMailserver\" which is ONLY going to happen by default in 32 bit English versions of Windows.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

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

Re: Upgrading Hmailserver - Absolute to relative data paths check

Post by jimimaseye » 2015-11-16 01:32

mattg wrote:It should be noted that the SQL statements shown are for MySQL and not necessarily other SQL servers supported by hMailserver. These are also the commands that I used, but they won't fit every installation.
I have checked and this command is compatible certainly with MSSQL (https://msdn.microsoft.com/en-gb/library/ms186862.aspx) and MySQL (https://dev.mysql.com/doc/refman/5.7/en ... on_replace). I suspect it is compatible with most SQL languages as it is using the base commands of SQL (but of course if users are unsure and are not using MSSQL or MySQL then always check first).
mattg wrote:Installing hMailserver in a default location on a 64 bit machine will NOT make 42 the correct number of characters. My recollection is that 42 is correct when hMailserver is installed to "C:\Program Files\hMailserver\" which is ONLY going to happen by default in 32 bit English versions of Windows.
The "42" reference in the UPDATE command is irrelevant to the path name: it refers to the opening character of the filename (after the path). A Filename is in the form of:

{E6120459-4E06-43A9-9EA2-F4A541586175}.eml

(as in "C:\Program Files (x86)\hMailServer\Data\{E6120459-4E06-43A9-9EA2-F4A541586175}.eml"). As far as I know this unique Filename format is the same in both 32 and 64 bit systems (and in fact I am running on a 64bit system which, I guess, most other people are).

You will see that the SQL command actually refers to the RIGHT 42 characters of the full filename; and counting 42 from right-most is always the opening "{" - the whole of the path name (to the left of this) is irrelevant.

C:\Program Files (x86)\hMailServer\Data\{E6120459-4E06-43A9-9EA2-F4A541586175}.eml
or
D:\hms\Data\{E6120459-4E06-43A9-9EA2-F4A541586175}.eml
will always have RIGHT(filename,42) as "{E6120459-4E06-43A9-9EA2-F4A541586175}.eml

So this command procedure for checking should always be compatible.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 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

stephan123
Normal user
Normal user
Posts: 37
Joined: 2009-10-29 13:03

no automatic conversion, but this works!

Post by stephan123 » 2016-05-06 09:34

jimimaseye wrote: Upgrading to 5.4 or later should involve an automatic process that removes this feature and makes the filename paths relative (that means they will be found in relation to where you have stated the data path to be).
I just upgraded from 5.3 to current 5.6.5 and the absolute paths were not converted automatically during install.
jimimaseye wrote:

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
That worked for me without any problem even with our non-standard data path. Excellent tutorial, thanks!

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

Re: Upgrading Hmailserver - Absolute to relative data paths check

Post by jimimaseye » 2016-05-06 09:50

Thanks for that Stephan, and the confirmation.

For clarity to readers:
stephan123 wrote:
jimimaseye wrote: Upgrading to 5.4 or later should involve an automatic process that removes this feature and makes the filename paths relative (that means they will be found in relation to where you have stated the data path to be).
I just upgraded from 5.3 to current 5.6.5 and the absolute paths were not converted automatically during install.
It should be noted I never said it should remove the datapaths, I just said it should remove the feature as in the program stops the datapaths being adhered to (ignores them) simply leaving just the filenames being referred to in a 'relative' location scenario according to datapath set in the .INI. The filenames (as proven) do remain in the database and so the SQL statement in this tutorial was provided to remove them to avoid any future potential problem or confusion.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 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