changing path/location of Data and Log directories

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

changing path/location of Data and Log directories

Post by rekabis » 2009-04-15 22:48

Greetings!

Due to security, backup requirements and other concerns (including drive space), I would like to know how to move the program-created data away from the installation directory. Specifically, I would like to move the following:

/data/
/logs/
the SQL Server 2003 database

Away from C:/ entirely.

I have already found out how to do it with the database, and hMailServer is currently running just peachy with the DB files on D:/. I have found the INI file that specifies the location of /data/ and /logs/, but I need to know if this is what the program loads into memory each and every time it launches.

As well, I would love to know if anyone has already created an SQL statement I could adapt to run on the DB (in the hm_messages table) so that the paths to the individual e-mails could be changed to reflect the new location.

Thanks to anyone who can help!

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: changing path/location of Data and Log directories

Post by ^DooM^ » 2009-04-15 22:51

Easiest way to do hm_messages table is to dump it find and replace the paths and import it back again. This process will work with any SQL server :)
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

Re: changing path/location of Data and Log directories

Post by rekabis » 2009-04-15 23:00

Yeah, well. Works fine if you only have a few accounts with maybe a hundred e-mails at most. I'm dealing with over a dozen domains and potentially thousands of e-mails. Hence, the SQL query-and-replace, to make things easier. ;-)

Seriously. My e-mail server processes over 30,000 e-mails on a *slow* day. If some n00b gets it into their heads to spam-flood my server, it can top out at over twice that.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: changing path/location of Data and Log directories

Post by martin » 2009-04-15 23:15

Why wouldn't that method work if you have a larger number of messages? If you're using a text editor which doesn't support find and replace you need to upgrade it... :)

rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

Re: changing path/location of Data and Log directories

Post by rekabis » 2009-04-15 23:17

Just confirmed that editing the INI file (while hMailServer is not running) does indeed work once hMailServer gets back up and running. I have done so for my logs, and all new log entries are being made to the new location.

All I need to know is if this works for the data directory as well. If I were to edit the INI file, will the paths in the database (the hm_messages table) that point to the stored e-mails also show this same change? (And yes, I know that this would be only for newly arrived e-mails).

If this is a correct assumption, then all I need now is an SQL statement that would allow me to change all pre-existing paths in the hm_messages table to the correct location on D:/.

TIA.

rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

Re: changing path/location of Data and Log directories

Post by rekabis » 2009-04-15 23:21

martin wrote:Why wouldn't that method work if you have a larger number of messages? If you're using a text editor which doesn't support find and replace you need to upgrade it... :)
IMHO it's a lot quicker to net stop hmailserver, run an SQL query, open the table to do a quick double-check (to see if it took), copy over the data directory contents, and then net start hmailserver. I don’t want to mess around with a whole bunch of dumps and imports, which are kinda like dismounting and dropping an engine just to replace spark plugs.

Thanks anyhow.

rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

Re: changing path/location of Data and Log directories

Post by rekabis » 2009-04-15 23:28

Come to think of it, why does the DB have to store the entire path?? Why not simply store the path inside the /data/ directory, and have the program itself pull the initial path from the INI file? This would make, IMHO, a lot more sense than storing the entire path in the DB.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: changing path/location of Data and Log directories

Post by martin » 2009-04-15 23:33

Sure, but it *would work*.

Anyway, if you search the forum you'll find examples of SQL statements to do it. Here's one:

UPDATE hm_message SET messagefilename=REPLACE(messagefilename,'OldPath','NewPath');

Of course, if the syntax is incorrect in some way you'll blow up everything. I recommend that you create a copy of the database, execute the SQL statement on the copy, confirm that the paths in hm_messages are correct and after that perform the operation on your production db.

> This would make, IMHO, a lot more sense than storing the entire path in the DB.

Agreed. It's done because it was done this way since the start and has not been changed. There are some theoretical benefits with the current solution but the main reason it hasn't been done is prioritization.

rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

Re: changing path/location of Data and Log directories

Post by rekabis » 2009-04-15 23:44

martin wrote:Sure, but it *would work*.

Anyway, if you search the forum you'll find examples of SQL statements to do it. Here's one:

UPDATE hm_message SET messagefilename=REPLACE(messagefilename,'OldPath','NewPath');

Of course, if the syntax is incorrect in some way you'll blow up everything. I recommend that you create a copy of the database, execute the SQL statement on the copy, confirm that the paths in hm_messages are correct and after that perform the operation on your production db.

> This would make, IMHO, a lot more sense than storing the entire path in the DB.

Agreed. It's done because it was done this way since the start and has not been changed. There are some theoretical benefits with the current solution but the main reason it hasn't been done is prioritization.
The SQL statement seems to have worked like a charm. Thanks!!

As for the path issue, another one I can think of is backward compatibility. You would have to put a fork into the program to handle any "old" paths vs the "new" paths once you upgrade hMailServer. This could be a big headache, and prone to serious errors.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: changing path/location of Data and Log directories

Post by martin » 2009-04-15 23:52

> As for the path issue, another one I can think of is backward compatibility.

Yes. If it had been as easy as just running an update and trimming away the paths then it wouldn't be any problem. I will have to create some kind of pre-upgrade-tool which does what you just did - move all the messages to the "new" data directory and then upgrade the database. As you say, this can potentially give a bigger headache than my recurring migraine. ;)

rekabis
New user
New user
Posts: 25
Joined: 2007-02-25 11:34
Location: Kelowna, British Columbia, Canada
Contact:

Re: changing path/location of Data and Log directories

Post by rekabis » 2009-04-16 00:02

martin wrote:> As for the path issue, another one I can think of is backward compatibility.

Yes. If it had been as easy as just running an update and trimming away the paths then it wouldn't be any problem. I will have to create some kind of pre-upgrade-tool which does what you just did - move all the messages to the "new" data directory and then upgrade the database. As you say, this can potentially give a bigger headache than my recurring migraine. ;)
Have you thought about making an hMailServer that would "break" with previous versions, requiring users to run an "upgrade tool" which would do the necessary modifications to the DB and so forth? That way, you could have a three-step upgrade to the new version:

[upgrade to latest "old" version] -> [run upgrade tool & do any other requirements manually] -> [install "new" version]

Presto, the ability to leave any undesirable or obsolete features behind, in favour of a new system, and without having to do any if-else compatibility coding. I believe I read about this method somewhere… just can’t remember where.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: changing path/location of Data and Log directories

Post by martin » 2009-04-16 00:09

ave you thought about making an hMailServer that would "break" with previous versions, requiring users to run an "upgrade tool" which would do the necessary modifications to the DB and so forth?
Well, that's already a bit how it works today. If you upgrade from hMailServer vN to vN+1, you sometimes need to upgrade the database. This is done almost automatically though (most of the time) so I wouldn't call it "breaking" though..

But dropping backwards compatibility completely probably isn't worth it. The path-problem isn't a huge issue, and there's probably a perfect solution to it if time is just spent on it.

The hMailServer code could be rewritten to be able to deal with both path types, and then it could run a background thread internally moving the message files after the upgrade one at a time. If a message file can't be moved for some reason, an error could be logged and the admin could look into it whenever he has time. When all the moving is done, the background thread would end and never be started again. This is probably a crappy solution - I'm just saying there's many things which could be done to solve it which isn't extremely complex.

It's always nice to drop backwards compatibility completely. But 5 minutes after the new release has been made you have new features you want to drop backwards compatibility with. ;-)

Uzi1
Normal user
Normal user
Posts: 37
Joined: 2008-11-09 01:12

Re: changing path/location of Data and Log directories

Post by Uzi1 » 2009-04-16 00:13

martin wrote:I will have to create some kind of pre-upgrade-tool which does what you just did - move all the messages to the "new" data directory and then upgrade the database.
Sorry if I'm missing something here but why would you have to move the database during an upgrade?

Should it be enough just to shut down old hmailserver service, issue an SQL statement that strips the path of the message files of the path found in hmailserver.ini and after the SQL statement is done start the new hmailserver service?

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: changing path/location of Data and Log directories

Post by martin » 2009-04-16 00:22

We're not talking about the database - we're talking about the message files.

If a user has half of his messages in C:\Data and the other half in D:\Data, simply stripping away C:\Data and D:\data from all message files in the database won't be good..

bjanzen
New user
New user
Posts: 5
Joined: 2009-11-25 05:28

Re: changing path/location of Data and Log directories

Post by bjanzen » 2009-11-25 05:39

Here is what I find to work the best
UPDATE hm_messages SET `messagefilename` = replace(messagefilename,'\\OldServer\\storage\\MailData\\data', '\\\\NewServer\\storage\\MailData\\data')

needed the \\\\ = \\ in PHP this was ran through phpmyadmin


Brett

Post Reply