ERROR: Full paths are stored in the database.

Use this forum if you want to discuss a problem or ask a question related to a hMailServer beta release.
Post Reply
User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

ERROR: Full paths are stored in the database.

Post by yon » 2011-12-07 21:12

Test: Collect server details
hMailServer version: hMailServer 5.4-B1931
Database type: MySQL

Test: Test IPv6
IPv6 support is available in operating system.

Test: Test outbound port
SMTP relayer not in use. Attempting mail.hmailserver.com:25
Local address is 2001:470:f178::2.
Trying to connect to host mail.hmailserver.com...
Trying to connect to TCP/IP address 83.169.11.240 on port 25.
ERROR: Unable to bind to address 2001:470:f178::2.



Test: Test message file locations
ERROR: Full paths are stored in the database.


How I fix these wrong ?
:D
IPv6 Email Public Service www.ipv6china.com World's first

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Re: ERROR: Full paths are stored in the database.

Post by Bill48105 » 2011-12-07 23:47

The short answer is this might work:

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
The long answer is there is a chance that can muck up message paths that are non-standard such as if for some reason a short filename was used.

Another thing you can try is running this:

Code: Select all

SELECT * FROM hm_messages where LEFT(messagefilename, 1) <> "{";
That looks for messages that are full (as in haven't already had the full path removed). Running that instead of the UPDATE above is safer in that it doesn't actually change anything & might show you a few troubled emails. Or it could show you tons of messages in which case you likely need to run the UPDATE above then run the SELECT one again to see of there are any weird ones left.

ALWAYS BACKED UP (ESPECIALLY YOUR DATABASE) before doing things like that UPDATE!!

Btw, the full paths don't hurt anything so the 'error' could likely be ignored if you wanted.
Bill
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-13 00:43

I used to use the 5.3 version does not have this problem, which is 5.4 version of the encounter.
IPv6 Email Public Service www.ipv6china.com World's first

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

Re: ERROR: Full paths are stored in the database.

Post by ^DooM^ » 2011-12-13 01:22

full paths were deprecated in 5.4, your install will still work with full paths though, it's just a warning, not an error.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

User avatar
CH-Systems
New user
New user
Posts: 18
Joined: 2011-12-15 17:11
Location: Germany
Contact:

Re: ERROR: Full paths are stored in the database.

Post by CH-Systems » 2011-12-15 17:17

I solved this by running the DataDirectorySynchronizer.exe Utility from the hMailServer\Addons\ directory.
It scanned the folders and wrote the messagefilename (without directory paths) back to the database.

Since I do not have much messages stored directly in hMailServer this solution was simple and fast.
Regards,
Andy

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-15 18:48

ok. I will try fix it later. I am running hmail server for ipv6 email public service in long time.

if ipv6 fans,your can send ipv6 mail to info@ipv6china.com

:D
IPv6 Email Public Service www.ipv6china.com World's first

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-22 13:23

Bill48105 wrote:The short answer is this might work:

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
The long answer is there is a chance that can muck up message paths that are non-standard such as if for some reason a short filename was used.

Another thing you can try is running this:

Code: Select all

SELECT * FROM hm_messages where LEFT(messagefilename, 1) <> "{";
That looks for messages that are full (as in haven't already had the full path removed). Running that instead of the UPDATE above is safer in that it doesn't actually change anything & might show you a few troubled emails. Or it could show you tons of messages in which case you likely need to run the UPDATE above then run the SELECT one again to see of there are any weird ones left.

ALWAYS BACKED (ESPECIALLY YOUR DATABASE) before doing things like that UPDATE!!

Btw, the full paths don't hurt anything so the 'error' could likely be ignored if you wanted.
Bill

ok. I am use your this "

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
"

just fix it. thanks! :D

and
but why it still show" ERROR: Unable to bind to address 2001:470:f178::2." ?

Test: Collect server details
hMailServer version: hMailServer 5.4-B1931
Database type: MySQL

Test: Test IPv6
IPv6 support is available in operating system.

Test: Test outbound port
SMTP relayer not in use. Attempting mail.hmailserver.com:25
Local address is 2001:470:f178::2.
Trying to connect to host mail.hmailserver.com...
Trying to connect to TCP/IP address 83.169.11.240 on port 25.
ERROR: Unable to bind to address 2001:470:f178::2.

Test: Test backup directory
Backup directory has not been specified.



Test: Test local connect
Connecting to TCP/IP address in MX records for local domain domain ipv6china.com...
Trying to connect to host mail.v6.xiaoyu.net...
Trying to connect to TCP/IP address 2001:470:f178::2 on port 25.
Connected successfully.

Test: Test message file locations
Relative message paths are stored in the database for all messages.

Test: Test IP range configuration
No problems were found in the IP range configuration.
IPv6 Email Public Service www.ipv6china.com World's first

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

Re: ERROR: Full paths are stored in the database.

Post by mattg » 2011-12-22 13:30

could that be because the hmailserver.com mailserver isn't IPv6 ready

can you send mail to other IPv6 mailservers?
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
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-22 22:56

mattg wrote:could that be because the hmailserver.com mailserver isn't IPv6 ready

can you send mail to other IPv6 mailservers?

yes. I can send and get mail on ipv6 network.
IPv6 Email Public Service www.ipv6china.com World's first

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-23 08:27

hamail server log show:


"ERROR" 4984 "2011-12-23 13:44:38.250" "Severity: 2 (High), Code: HM5033, Source: DALRecordset::Open, Description: MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2306129367324426240' > whiteloweripaddress1 && whiteloweripaddress2 is not null' at line 1 (Additional info: SELECT COUNT(*) as c FROM hm_whitelist WHERE ('2306129367324426240' = whiteloweripaddress1 && '2306129367324426240' >= whiteloweripaddress2 '2306129367324426240' > whiteloweripaddress1 && whiteloweripaddress2 is not null) AND ('2'} = whiteupperipaddress1 && '2' <= whiteupperipaddress2 '2' < whiteupperipaddress1 && whiteupperipaddress2 is not null) AND (whiteemailaddress = '' OR 'info@ipv6china.com' LIKE whiteemailaddress ESCAPE '/'))"


what's wrong ? how fix it now? :wink:
IPv6 Email Public Service www.ipv6china.com World's first

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Re: ERROR: Full paths are stored in the database.

Post by Bill48105 » 2011-12-23 13:24

Hmm almost looks like a bug with ipv6 & whitelists but could be a database table didn't get upgraded properly if you had an older version of hmail before. Do you get the same error if you try on a clean install of 5.4? We might need to take a closer look at the code vs table structure.
Bill
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-24 09:59

yes, today just I have use: UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42)); and install your beta version then show that error.


before show error :

"ERROR" 1532 "2011-11-11 21:22:37.671" "Severity: 2 (High), Code: HM5033, Source: DALRecordset::Open, Description: MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2306477149156343808' > whiteloweripaddress1 && whiteloweripaddress2 is not null' at line 1 (Additional info: SELECT COUNT(*) as c FROM hm_whitelist WHERE ('2306477149156343808' = whiteloweripaddress1 && '2306477149156343808' >= whiteloweripaddress2 '2306477149156343808' > whiteloweripaddress1 && whiteloweripaddress2 is not null) AND ('1086273029'} = whiteupperipaddress1 && '1086273029' <= whiteupperipaddress2 '1086273029' < whiteupperipaddress1 && whiteupperipaddress2 is not null) AND (whiteemailaddress = '' OR 'gibno@efqaua.com' LIKE whiteemailaddress ESCAPE '/'))"
"ERROR" 1532 "2011-11-11 21:22:38.109" "Severity: 2 (High), Code: HM5033, Source: DALRecordset::Open, Description: MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2306477149156343808' > whiteloweripaddress1 && whiteloweripaddress2 is not null' at line 1 (Additional info: SELECT COUNT(*) as c FROM hm_whitelist WHERE ('2306477149156343808' = whiteloweripaddress1 && '2306477149156343808' >= whiteloweripaddress2 '2306477149156343808' > whiteloweripaddress1 && whiteloweripaddress2 is not null) AND ('1086273029'} = whiteupperipaddress1 && '1086273029' <= whiteupperipaddress2 '1086273029' < whiteupperipaddress1 && whiteupperipaddress2 is not null) AND (whiteemailaddress = '' OR 'gibno@efqaua.com' LIKE whiteemailaddress ESCAPE '/'))"
"ERROR" 1532 "2011-11-11 21:22:43.843" "Severity: 2 (High), Code: HM5033, Source: DALRecordset::Open, Description: MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2306477149156343808' > whiteloweripaddress1 && whiteloweripaddress2 is not null' at line 1 (Additional info: SELECT COUNT(*) as c FROM hm_whitelist WHERE ('2306477149156343808' = whiteloweripaddress1 && '2306477149156343808' >= whiteloweripaddress2 '2306477149156343808' > whiteloweripaddress1 && whiteloweripaddress2 is not null) AND ('1086273029'} = whiteupperipaddress1 && '1086273029' <= whiteupperipaddress2 '1086273029' < whiteupperipaddress1 && whiteupperipaddress2 is not null) AND (whiteemailaddress = '' OR 'gibno@efqaua.com' LIKE whiteemailaddress ESCAPE '/'))"
IPv6 Email Public Service www.ipv6china.com World's first

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Re: ERROR: Full paths are stored in the database.

Post by Bill48105 » 2011-12-24 17:20

yon,
Yeah that error looks like ipv6 isn't supported with whitelist. So that was 5.4 or an earlier version?
Bill
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-25 04:21

Bill48105 wrote:yon,
Yeah that error looks like ipv6 isn't supported with whitelist. So that was 5.4 or an earlier version?
Bill

but I have not whitelist now yet. it should is v5.4
IPv6 Email Public Service www.ipv6china.com World's first

Bill48105
Developer
Developer
Posts: 6192
Joined: 2010-04-24 23:16
Location: Michigan, USA

Re: ERROR: Full paths are stored in the database.

Post by Bill48105 » 2011-12-25 06:24

It could be leftovers from old hmail before 5.4 after upgrade. Could require a manual update/fix of the table, especially if you have not entered any whitelists.
Bill
hMailServer build LIVE on my servers: 5.4-B2014050402
#hmailserver on FreeNode IRC https://webchat.freenode.net/?channels=#hmailserver
*** ABSENT FROM hMail! Those in IRC know how to find me if urgent. ***

User avatar
yon
Normal user
Normal user
Posts: 129
Joined: 2009-11-06 10:44

Re: ERROR: Full paths are stored in the database.

Post by yon » 2011-12-26 18:45

Bill48105 wrote:It could be leftovers from old hmail before 5.4 after upgrade. Could require a manual update/fix of the table, especially if you have not entered any whitelists.
Bill

How I do it? :wink:
IPv6 Email Public Service www.ipv6china.com World's first

User avatar
WolfWare
New user
New user
Posts: 4
Joined: 2013-01-09 03:24

Re: ERROR: Full paths are stored in the database.

Post by WolfWare » 2013-01-09 04:07

Bill48105 wrote:The short answer is this might work:

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
The long answer is there is a chance that can muck up message paths that are non-standard such as if for some reason a short filename was used. Bill
Alternatively (as for some reason I am using SQL Compact Edition, with a reduced command set)...

Code: Select all

UPDATE hm_messages SET messagefilename=SUBSTRING(messagefilename,CHARINDEX('{',messagefilename),42);
...WW :roll:

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

Re: ERROR: Full paths are stored in the database.

Post by mattg » 2013-01-09 04:16

Thanks for the SQL for the MS SQL CE database.

The reason that MS SQL CE was included as the default database for ver 5.X is that 5.0 >> 5.3 are closed source, and MySQL wasn't able to be included as per it's licensing.
MS SQL CE isn't able to be used in commercial environments as per it's EULA, and is really only suitable for small home installations of hMailserver.
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
WolfWare
New user
New user
Posts: 4
Joined: 2013-01-09 03:24

Re: ERROR: Full paths are stored in the database.

Post by WolfWare » 2013-01-09 11:55

Pleasure MG. I am new here and so had to pick my way through the "full path" issue and then adapt a solution from what I was able to glean.

As moderators, who will have seen this issue grow and have a fix on the fixes and threads it has generated, would you be able to put a new thread together with the various syntax's for differing DB back-ends along with a short tutorial on applied method - i.e. I used the freeware CEQueryTool (CEQuery.exe) to resolve the problem?

Appreciate workload and time, just a thought.

I use hMS just as you describe, it is a great soho (and perhaps more) product. I salute you all (DEVS/CONTRIBS) for your efforts and sacrifice.

The three things I would like (small points) are client web access, easier Exchange PRIV/PUB to hMS migration and enhancement of the Public folder deployment and security grouping structures.

OK, I am going to shut up now, as talk is cheap. I applaud your collective efforts and actions once again, bravo, WW.

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

Re: ERROR: Full paths are stored in the database.

Post by mattg » 2013-01-09 14:00

WolfWare wrote:The three things I would like (small points) are client web access, easier Exchange PRIV/PUB to hMS migration and enhancement of the Public folder deployment and security grouping structures.
Feel free to add or vote for existing request enhancements.

What do you mean by 'client web access'?
Exchange is groupware that doesn't necessarily follow RFCs, hMailserver is a RFC standards compliant mail server - can't migrate everything that Exchange does anyway as hMailserver doesn't do shared calendars and contacts.
I agree that public folders could do with some improvement. I'd like to see Public folders per domain, and the enhancements to the GUI that you mention.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

dereks
New user
New user
Posts: 1
Joined: 2013-07-11 17:56

Re: ERROR: Full paths are stored in the database.

Post by dereks » 2013-07-11 20:05

For MS SQL I used this query to remove the full paths. This is assuming your data is stored on the C:\ drive.


UPDATE [hm_messages]
SET messagefilename= SUBSTRING(messagefilename,CHARINDEX('{',messagefilename) ,len(messagefilename)-CHARINDEX('{',messagefilename)+1)
WHERE CHARINDEX('{',messagefilename) >0 AND CHARINDEX('C:\',messagefilename) >0

lyriker
New user
New user
Posts: 4
Joined: 2013-07-04 10:14

Re: ERROR: Full paths are stored in the database.

Post by lyriker » 2013-08-21 13:11

Hello,

I have the same Problem, but i use postgres 9.0 and the sql (UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));) dont work.

The SQL Syntax right(messagefilename,42) not included in postgres 9.0.

How can i fix the 190000 Mails ? :(

Wie need that for our club... !

thanking you in anticipation
my regards to you all!

Lyk

(I only speak broken English, for that I apologize.)

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

Re: ERROR: Full paths are stored in the database.

Post by ^DooM^ » 2013-08-21 13:35

Goto the addons folder in your install directory and run the DDS tool. That tool I believe has the ability to fix the issue you are seeing.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

lyriker
New user
New user
Posts: 4
Joined: 2013-07-04 10:14

Re: ERROR: Full paths are stored in the database.

Post by lyriker » 2013-08-22 07:10

Sorry, I tried, but unfortunately that has brought nothing.

Unfortunately, I can not just switch to 9.2, on the way, the proposed solution shown above works. Only this SQL statement is not under 9.0 and I'm trying for days to write, unfortunately without success.

Please help ...

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

Re: ERROR: Full paths are stored in the database.

Post by ^DooM^ » 2013-08-22 10:57

What specifically did you try? The data directory synchroniser is supposed to fix that problem if you run it.

Also it's not really an error it's more a warning. hMail will function perfectly fine with full paths.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

lyriker
New user
New user
Posts: 4
Joined: 2013-07-04 10:14

Re: ERROR: Full paths are stored in the database.

Post by lyriker » 2013-08-22 12:06

Ok I take it all back and say thank you, it works now after a complete reboot of the server again ....

Rene2009
Normal user
Normal user
Posts: 62
Joined: 2009-12-25 18:40

Re: ERROR: Full paths are stored in the database.

Post by Rene2009 » 2013-12-07 18:31

Hello,

since a week I moved to HMS 5.4 and I also got this "Full path Error".

I have more than 100.000 E-Mails in my HMS Server with MS-SQL 2008 Server in the background.
It seems that the server is running well.
So I read here, that it is not really necessary to cut of the full path.

Can anyone from the Development Team explain the benefit (pros and cons) of doing the operation of cutting of the path.
Shall I take the risk of doing something wrong?
What shall I backup before?
Just the MS SQL Database?

I write some statements to check first:

select SUBSTRING(messagefilename,CHARINDEX('{',messagefilename),42) from hm_messages

select distinct SUBSTRING(messagefilename, 1, 1), COUNT(*) from hm_messages
group by SUBSTRING(messagefilename, 1, 1)

Can someone post the 100% working update string for MS-SQL Server?
I don´t want to loose / destroy 100.000 E-Mails (I´m not an SQL Professional ;-) ).

Best regards,

Rene

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: ERROR: Full paths are stored in the database.

Post by percepts » 2013-12-07 19:40

I'm no MS SQL user but the following should work in your version of MS SQL. It is the code posted earlier in this topic for MS SQL (and not the code for MS SQL CE which you don't want).

Code: Select all

UPDATE hm_messages SET messagefilename=REPLACE(messagefilename, messagefilename, right(messagefilename,42));
The benefits are you are not likely to experience problems down the line with scripts or any other conflicts. Is it necessary to delete them? Well officially no. Its upto you but note that new messages won't have paths so you will have some with and some without which is a bit messy from a data point of view even if it doesn't cause a problem.

Obviously BACKUP DB before doing this and stop hMail service before running it. Then restart.

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

Re: ERROR: Full paths are stored in the database.

Post by mattg » 2013-12-08 07:26

Other benefit comes when (if) you need to change drives for your hmailserver installation.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Post Reply