Corrupted MySQL?

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
User avatar
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Corrupted MySQL?

Post by Paulo Meireles » 2019-01-08 03:21

Hi,

I have a small hmailserver at home since at least the year 2000. It has always worked wonderfully - until today, when the wife complained that she was not getting email from an external account.
The logs show many occurences of messages like these:
"ERROR" 468 "2018-12-08 00:19:17.447" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Got error 127 from storage engine (Additional info: update hm_fetchaccounts set falocked = 0 where faid = 12)"
"ERROR" 324 "2018-12-08 00:20:56.590" "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Got error 127 from storage engine (Additional info: update hm_fetchaccounts set fanexttry = DATE_ADD(CONCAT(CURDATE(), ' ', CURTIME()), INTERVAL faminutes MINUTE) where faid = 12)"

It seems that the database needs some maintenance, but... I cannot find how to log on MySQL. I already tried using the "DecryptBlowfish.vbs" script, but it spits mangled garbage (aka: special characters) on my face.

This is the database section of my hmailserver.ini file:
[database]
type=MYSQL
username=root
password=xxxxxxxx
port=3307
server=localhost
database=hMailServer
Internal=1

(instead of "xxxxxxxx" there are 8 other characters, all upercase/lowercase letters or numbers)

Databases have always been my weak spot, and I'm afraid to break anything. Can anyone lend a hand?

Regards,
Paulo

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

Re: Corrupted MySQL?

Post by mattg » 2019-01-08 04:06

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
Dravion
Senior user
Senior user
Posts: 1466
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Corrupted MySQL?

Post by Dravion » 2019-01-08 04:32

hMailServer can logon to your MySQL Database Server, thats not the Problem. Its also not a access or permission Problem.

It seems to be a Problem at updating a Date value because a received Email has a wrong or malformed
or null Dateformat which results in a rejected SQL Update query.

I think, logon to MySQL with a tool like MySQL
Workbench and fixing the corrupt dates in table hm_messages should fix the problem.

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

Re: Corrupted MySQL?

Post by mattg » 2019-01-08 04:36

Yes, but they can't remember their MySQL password
Hence the DecryptBlowfish to get the password used by 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
Dravion
Senior user
Senior user
Posts: 1466
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Corrupted MySQL?

Post by Dravion » 2019-01-08 04:55

Did he only forget hMailServers Login users Password or MySQL Root Password to?

If MySQL Root password is avaiable, login with MySQL Workbench via Root user should be ok.

But if hMailServer is conbecting via MySQL Root user + its Password its a diffrent story...

User avatar
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-08 05:05

To use DecryptBlowfish I must "Enter your main hMailServer Administrator password. This is the password you specified during installation."
Well, it's been almost 20 years since I installed it... so I'm not sure if I installed with the same password as I have now, or if I changed it sometime along the way.
Do I really need the installation password, or the current Administrator password will suffice?

Now, here's the output of the blowfish script:
blowfish.png
blowfish.png (3.48 KiB) Viewed 3093 times
Is it supposed to be this way? (guess not...)
And yes, hmailserver seems to be working nonetheless, and I already restarted it - and the VM where it's running.

What can I do to recover the MySQL password so I can correct the problem?

Paulo

User avatar
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-08 05:34

Did he only forget hMailServers Login users Password or MySQL Root Password to?
If MySQL Root password is avaiable, login with MySQL Workbench via Root user should be ok.
But if hMailServer is conbecting via MySQL Root user + its Password its a diffrent story...

As you can see in the snippet of the .ini file I posted above, it unfortunately seems to be using the root account... :(
Current me wants so much to slap past me in the face...

Is there any way to safely/nondestructively reset the MySQL root password, or just create a new account from which to recover the database?
If I reset the root password, how can I put the new password back into hmailserver.ini?

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

Re: Corrupted MySQL?

Post by mattg » 2019-01-08 06:34

Paulo Meireles wrote:
2019-01-08 05:05
To use DecryptBlowfish I must "Enter your main hMailServer Administrator password. This is the password you specified during installation."
When you start the hMaislerver GUI, you probably need to enter a password.
This is the current hMaislerver Administrator password, teh one you need for the DecryptBlowfish.vbs to work

If you don't know that password, then you need to reset it (post back and I'll point you to a link)
Paulo Meireles wrote:
2019-01-08 05:34
Is there any way to safely/nondestructively reset the MySQL root password
Unfortunately no
Unless MySQL support says different...
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
Dravion
Senior user
Senior user
Posts: 1466
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Corrupted MySQL?

Post by Dravion » 2019-01-08 06:35

Ok, if you have such an old MySQL Database, its not safe to execute any of the nowdays procedures to reset it.

First we need to know:
1) What version of MySQL exactly is installed?
2) What version of hMailServer is running?
3) What Windows version do you use?

How big is you hMailServers DATA Folder?

Did you install hMailServer it its default path or something else like c:\hMailServer ?

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

Re: Corrupted MySQL?

Post by jimimaseye » 2019-01-08 09:48

mattg wrote:
2019-01-08 06:34
Paulo Meireles wrote:
2019-01-08 05:05
To use DecryptBlowfish I must "Enter your main hMailServer Administrator password. This is the password you specified during installation."
When you start the hMaislerver GUI, you probably need to enter a password.
This is the current hMaislerver Administrator password, teh one you need for the DecryptBlowfish.vbs to work
TIP: No you dont. I dont know why the decryptblowfish script prompts for the admin password because works it without entering any password (despite you being prompted for one). :roll: You can just leave it blank. (Or, at least, I can since v5.3.3).

Out of interest what version of HMS is OP using?
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
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-08 10:23

1) What version of MySQL exactly is installed?
The one that came bundled with hMailServer a long time ago. The binary is named "mysqld-nt.exe" and is dated 04-16-2005.
2) What version of hMailServer is running?
5.6.8-B2431
3) What Windows version do you use?
Windows XP (yes, I intend to migrate it off that...)

It's installed in d:\hmailserver, and the data folder is around 30GB.
All mysql data together is, however, around 500MB.

Paulo

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

Re: Corrupted MySQL?

Post by jimimaseye » 2019-01-08 13:53

Try running the decrypt s for again but without the admin password.

[Entered by mobile. Excuse my spelling.]
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
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-08 15:23

Thank you.
I will try that as soon as get home - in around 5 hours.

Paulo

User avatar
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-09 00:01

I just tried running the decrypt without the admin password; the result was the same.
Even installed latest (BETA) hmailserver on my PC and ran the decrypt there; same result.

Ran mysql executable from the command line; here's the output stating the version:
mysqld-nt.exe Ver 4.1.18-nt for Win32 on ia32 (MySQL Community Edition (GPL))
Copyright (C) 2000 MySQL AB, by Monty and others

So... what should I try next?
This is not looking good... :(

Paulo

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

Re: Corrupted MySQL?

Post by jimimaseye » 2019-01-09 00:27

PM me (or matt) with the unedited content of the ini file. Include the password (but blank the admin password if you want) . Let's see if we can make sense of it and get better results.

[Entered by mobile. Excuse my spelling.]
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
Dravion
Senior user
Senior user
Posts: 1466
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Corrupted MySQL?

Post by Dravion » 2019-01-09 00:34

You can Reset MySQL 4.1 and higher Password from Windows Command prompt by loading a MySQL Initfile manually which will reset MySQLs Root Password.

The Problem is, if you do so, your hMailServer.ini
Password doesnt work anymore because you used the Root password of MySQL.

However, its your only choice to fix the Problem.

Do the following steps:
1) Stop hMailServer and MySQL Service
2) Follow the MySQL Root Password Reset steps
http://ftp.nchu.edu.tw/MySQL/doc/refman ... sions.html
3) Save your new MySQL Root password
4) Run hMailServer DBSetup.exe
5) Select MySQL and existing DB
6) Use "root" as user plus your new password

Finish

Now start MySQL Service again.
Start hMailServer Admin and login to your
hMailServer (use your old hMailServer Password).

User avatar
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-09 02:01

Thank you, Dravion.

I just sent the ini file to jimimaseye.
If he can make any sense of it, I'd rather avoid resetting the password.
Otherwise, I will follow your instructions.

Regards,
Paulo

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

Re: Corrupted MySQL?

Post by jimimaseye » 2019-01-09 02:05

Ini received.

It doesn't look encrypted to me. Have you actually tried typing it as a password as it appears?

[Entered by mobile. Excuse my spelling.]
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
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-09 02:57

THANKS! That was it - right before my eyes. It was indeed not encrypted. I had tried it before, but it did not work because I was connecting to the wrong port... :oops:
I have meanwhile solved my original issue - a corrupted table - with myisamchk. The wife external email account is pumping again! :)

My current setup still needs to be put to sleep (Windows XP... argh...) so this doesn't happen again. What's the currently recommended way to migrate hmailserver to a new machine?
Is it still this article, or is there something else?
https://www.hmailserver.com/documentati ... _newserver

Regards,
Paulo

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

Re: Corrupted MySQL?

Post by mattg » 2019-01-09 03:57

Paulo Meireles wrote:
2019-01-09 02:57
What's the currently recommended way to migrate hmailserver to a new machine?
Is it still this article, or is there something else?
https://www.hmailserver.com/documentati ... _newserver
Normally I'd say backup and restore using internal tools, but not when you have 30 GB of email

I'd install the last 4.4 version of hMailserver onto a new computer, then upgrade to your current version 5.6.8B2431
Then stop the hmailserver service on both machines, and copy the data folder, the database folder accross

I'd backup the existing database using some tool (PHPMySQL is my preferred tool but that relies on PHP and a web server at both ends as well), and then import that database backup to the new machine, overwriting the HM database and tables.

Then restart new machine, and test test test

You could probably even do that as a trial run once or twice to be sure
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
Dravion
Senior user
Senior user
Posts: 1466
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Corrupted MySQL?

Post by Dravion » 2019-01-09 04:01

You should upgrade to a recent MySQL version (nowdays its MySQL Version 8.x)

But there are a few Problems. You need a libmysql.dll (in your case the 32-Bit version, not later then version 5.24)
and you need to upgrade to Windows 10 or Windows Server because MySQL 8 will require a modern Windows version.

Its possible to upgrade only to MySQL 5.x but this will end up again verry soon in a Out of Support Situation again for you.
MySQL 8 works only on 64-Bit Windows versions. There is a 32-Bit Installer which i tested on Windows 10 32-But it raises lot of
Errors and doesnt let you install the Database server. I dont know if the Error is fixed or MySQL 8.x wont be avaiable for 32-Bit anymore.

If you are looking for a longterm solution amd dont get hit by yearly or monthly changes like Win 10 tendes to do, you should consider
buying a Windows Server 2016 License (Standard works fine - on Amazon arround $389 OEM)

Windows Server 2016 runs out of Support in 2027
https://support.microsoft.com/en-us/lif ... ver%202016

MySQL 5.x end of Support is 2023 / MySQL 8.x end of Support is 2026
http://www.oracle.com/us/support/librar ... 069183.pdf

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

Re: Corrupted MySQL?

Post by mattg » 2019-01-09 04:27

Dravion wrote:
2019-01-09 04:01
You should upgrade to a recent MySQL version (nowdays its MySQL Version 8.x)
Yes I'd do that later, but perhaps not that new a version
Dravion wrote:
2019-01-09 04:01
But there are a few Problems. You need a libmysql.dll
If the OP installs the latest 4.4 of hMailserver, that file is included.
This version of hMailserver creates MySQL (builktin) as the default database, in the hmailserver database folder, same as the OP currently has.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

erkc29
New user
New user
Posts: 3
Joined: 2019-01-09 09:08

Re: Corrupted MySQL?

Post by erkc29 » 2019-01-09 09:17

Corrupted MySQL database don't worry you can repair MySQL Database issues, if you know the right approach to fix it.

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

Re: Corrupted MySQL?

Post by jimimaseye » 2019-01-09 10:10

In theory that simply updating the existing install to latest official build and then to this 5.6.7 then the backup database size limit will be removed and you will then be able to use internal backup/restore of the database. Then the database can be restored to a new install on a new machine with a new version of MySQL installed (and its tools) and all should be ok.
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
SorenR
Senior user
Senior user
Posts: 3210
Joined: 2006-08-21 15:38
Location: Denmark

Re: Corrupted MySQL?

Post by SorenR » 2019-01-09 13:21

Paulo Meireles wrote:
2019-01-09 02:57
I have meanwhile solved my original issue - a corrupted table - with myisamchk. The wife external email account is pumping again! :)
Happy wife, Happy life :mrgreen:
SørenR.

“With age comes wisdom, but sometimes age comes alone.”
- Oscar Wilde

User avatar
Paulo Meireles
Normal user
Normal user
Posts: 67
Joined: 2005-04-16 13:09
Location: Lisbon - Portugal

Re: Corrupted MySQL?

Post by Paulo Meireles » 2019-01-09 15:06

What would be the best supported database? MsSQL or MySQL? If MsSQL, is there an easy way to migrate from MySQL to MsSQL? If MySQL, is MariaDB supported?
Last resort would be creating a new server and using something like IMAPcopy... or even an IMAP client like Thunderbird. It's feasible when it's just a few mailboxes... :)

I had no idea the backup limit size had been removed. Maybe I can use internal backup now... despite having 30GB of data, which is actually not so much nowadays.
Has the backup size limit been removed for good, or just in the specific build mentioned by jimimaseye? I mean, will the limit be put back in a later build?

As for the "Happy wife, Happy life" saying, it's the first time I see it, but you bet I agree! :D

Paulo

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

Re: Corrupted MySQL?

Post by jimimaseye » 2019-01-09 15:24

The backup size limit removal is not in the official builds but is in upcoming 5.7. However it is present in the special build which is compatible with current official plus extra fixes. Many are using it.

Mysql is popular and many also use mariadb. They are preferable over the ms offerings because they are FREE! 😉

[Entered by mobile. Excuse my spelling.]
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