SQL error checking

mbreitba · Post by **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.

^DooM^ · Post by **^DooM^** » 2006-12-12 02:19

Nice Idea.

Slug · Post by **Slug** » 2006-12-12 03:23

Seconded, even though I have never had a table issue

Michael

^DooM^ · Post by **^DooM^** » 2006-12-12 12:42

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

ric melia · Post by **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)

Post by **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 · Post by **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.

Post by **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 · Post by **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?

Post by **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 · Post by **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.

Post by **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.

Slug · Post by **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

Post by **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.

Slug · Post by **Slug** » 2007-06-24 18:14

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

Michael

Post by **martin** » 2007-06-24 18:17

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

Slug · Post by **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

SQL error checking

Do you need this feature?

SQL error checking