SQL error checking

This forum contains features that has been archived. This section contains implemented features, duplicate requests, and requests which we have decided not to implement.

Do you need this feature?

Yes
9
90%
No
1
10%
 
Total votes: 10

mbreitba
Senior user
Senior user
Posts: 340
Joined: 2006-04-14 22:25

SQL error checking

Postby mbreitba » 2006-12-12 01:11

For the built-in MySQL server, have the server automatically repair database errors that it encounters.

To me, it seems silly to have the server completely hang because the greylisting table (or any other non-essential table) is corrupt.

I propose that if Hmailserver detects a database error, it stops itself (if it's a table that is absolutely required to run) and repairs the table, or disables the feature (greylisting in our current case) repairs the table, then re-enables the feature.
hMailServer 4.4.2 B281 with external MSSQL 2005
Win 2003 SP1
IIS 6
PHP 4.4.2
SquirrelMail 1.4.8
SpamAssassin 3.2.4 and ClamAV .92 on Backend Ubuntu systems

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

Postby ^DooM^ » 2006-12-12 02:19

Nice Idea.

User avatar
Slug
Moderator
Moderator
Posts: 1369
Joined: 2005-03-13 05:42
Location: Sydney Australia
Contact:

Postby Slug » 2006-12-12 03:23

Seconded, even though I have never had a table issue 8)

Michael
Missing Hmailserver ... Now running Debian servers

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

Postby ^DooM^ » 2006-12-12 12:42

Me either but SQL errors seem to be coming more common place unfortunately :(

ric melia
Normal user
Normal user
Posts: 33
Joined: 2004-12-13 15:11

Postby ric melia » 2006-12-12 15:16

I would suggest including this for stand alone mysql servers too. I've had a couple of corrupted tables over the time i've used hMailServer - always after a crash or unexpected restart (not hMails fault at all)

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

Postby martin » 2006-12-12 21:06

I think I have two different opinions on this. My developer perspective opinion is that this would be very "ugly" feature in terms of "problem ownership" etc. Also have a more pragmatic opinion where I see that this may be needed.

If you add the following line to \hMailServer\MySQL\my.ini, MySQL will check whether the file is corrupt. If so, it will repair it and backup the current file.

I have never had a problem with MySQL corruption myself, so it's a bit hard to test in a good way. I did however test this by corrupting one of the database files (by editing it in notepad). After adding the row below and restarted MySQL, MySQL automatically repaired the table.

myisam-recover=FORCE,BACKUP

I'm not sure what effect this has on MySQL performance though.

mbreitba
Senior user
Senior user
Posts: 340
Joined: 2006-04-14 22:25

Postby mbreitba » 2006-12-12 21:10

I didn't know that this was an option with MySQL (not very well versed with the application settings).

From our experience, it is all too easy to corrupt the MyISAM tables by having an unexpected shutdown or lockup. I'm going to add that line to our config, as it's definately something that we need to have working.

I do tend to agree with you on the ownership issues though. If MySQL has this functionality built in then I don't know that this needs to be implimented in Hmailserver.

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

Postby martin » 2006-12-12 21:15

I agree that it would not make much sense to modify the hMailServer implementation if the above settings does not effect performance to much. Think adding the above setting to the default MySQL ini file and documentation should be sufficient.

mbreitba
Senior user
Senior user
Posts: 340
Joined: 2006-04-14 22:25

Postby mbreitba » 2006-12-12 21:23

One quick question - will this fix tables that are corrupted while MySQL is running?

Our latest issue was the greylisting table became corrupted during a RAID rebuild. This killed Hmailserver, but from what I can tell from your posts above, is that this fixes the tables when MySQL is restarted, rather than on the fly. Is this correct, or does it repair corrupted tables on the fly also?

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

Postby martin » 2006-12-12 21:33

As far as I can tell from the docs, it's made on the fly. My tests indicated that as well. What I ment with restarting in my previous post was that I had to restart MySQL for the change in my.ini to take effect.

Automatic recovery is activated if you start mysqld with the --myisam-recover option. In this case, when the server opens a MyISAM table, it checks whether the table is marked as crashed or whether the open count variable for the table is not 0 and you are running the server with external locking disabled. If either of these conditions is true, the following happens:

* The server checks the table for errors.
* If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).
* If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.
* If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

If the recovery wouldn't be able to recover all rows from previously completed statementas and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log.

If the table is "really corrupt", rows may be lost. If you never want the repair function to remove rows, you shouldn't use the FORCE flag.

Shiloh
Normal user
Normal user
Posts: 163
Joined: 2006-04-14 00:00

Postby Shiloh » 2006-12-12 23:18

Here are a couple of easy ideas to implement:
1) Change the mysql ini file to include the autorepair during the initial install. This will only help new installations with the default MySQL DB, but it is an easy change to make.
2) Check to see if a table is corrupt when hMailServer starts (not while running). If a critical table is corrupt, hMailServer should tell the DB server to repair the corrupted table.

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

Postby martin » 2007-06-24 17:47

The MySQL ini that comes with the installation is now configured to use auto repair if a corrupt table occurs.

User avatar
Slug
Moderator
Moderator
Posts: 1369
Joined: 2005-03-13 05:42
Location: Sydney Australia
Contact:

Postby Slug » 2007-06-24 18:09

martin wrote:The MySQL ini that comes with the installation is now configured to use auto repair if a corrupt table occurs.


From what version ? and will a update of hMs automatically enable this ?

Michael
Missing Hmailserver ... Now running Debian servers

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

Postby martin » 2007-06-24 18:10

4.4 Alpha Build 263 (2007-03-04)

* The built-in MySQL server is now configured to auto-recover if corrupt tables occur (myisam-recover=FORCE,BACKUP). This change only effects new installations.

User avatar
Slug
Moderator
Moderator
Posts: 1369
Joined: 2005-03-13 05:42
Location: Sydney Australia
Contact:

Postby Slug » 2007-06-24 18:14

So I will have to manually enable it ... yes ?

Michael
Missing Hmailserver ... Now running Debian servers

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

Postby martin » 2007-06-24 18:17

Correct. By adding
myisam-recover=FORCE,BACKUP
to the end of my.ini.

User avatar
Slug
Moderator
Moderator
Posts: 1369
Joined: 2005-03-13 05:42
Location: Sydney Australia
Contact:

Postby Slug » 2007-06-24 18:18

martin wrote:Correct. By adding
myisam-recover=FORCE,BACKUP
to the end of my.ini.


Thanks (as always)
Michael
Missing Hmailserver ... Now running Debian servers


Return to “Archived feature requests”



Who is online

Users browsing this forum: No registered users and 2 guests