more lines in hm_messages than files in data folder

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
sbouli
Normal user
Normal user
Posts: 69
Joined: 2007-11-27 12:37

more lines in hm_messages than files in data folder

Post by sbouli » 2008-05-14 16:58

Hi,

I just discovered that I have 220 566 lines in the hm_messages table and "only" 166 171 files in the data folder(s) (and subfolders).
Is there a way of cleaning the table ? (If I understood correctly, an app which loop over all the lines in the mysql and trying to find the file on the disk would easely find the orphan ? )

Stéphane

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

Re: more lines in hm_messages than files in data folder

Post by martin » 2008-05-14 17:58

I haven't seen any app which does this today. Do you have any clue why the files are missing? Have you restored a backup recently or something?

sbouli
Normal user
Normal user
Posts: 69
Joined: 2007-11-27 12:37

Re: more lines in hm_messages than files in data folder

Post by sbouli » 2008-05-14 18:03

absolutely no clue ...
This king of work can easealy be done by coldfusion or php but installing it just for that is a pain in the bot ... (even the php-cli), maybee I will try to build a c# application ...

Just confirm me that I just need to match any files within /data/* with a line in hm_messages.

Stéphane

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

Re: more lines in hm_messages than files in data folder

Post by martin » 2008-05-14 18:09

Just confirm me that I just need to match any files within /data/* with a line in hm_messages.
That's correct. You should also be aware of the fact that if you've changed data directory location at some points, messages may still be in the old location. However, the hm_messages table contains the full path to files so all you need to check is whether the file exists or not..

sbouli
Normal user
Normal user
Posts: 69
Joined: 2007-11-27 12:37

[SOLVED] Re: more lines in hm_messages than files in data fo

Post by sbouli » 2008-05-15 15:19

Here it is, I did it in php using the cli version (command line version) of it which was installed on the server (I think by hMailServer), I just had to add the mysql extension and to add it in the php.ini.

If you use it, please, correct the password value. i would suggest to run it once with the DELETE part commented (see YOU SHOULD FIRST PUT THIS IN COMMENT).

There is a commented line with //sleep(1);
on some system this sould make the server to operate the patch smoothly (but of course slowlier)

Here is the code source :

Code: Select all

<?php

/*
<!---  --------------------------------------------------------------------------  --->
<!--- 
					Nom fichier  : patch.php
					Date Heure   : 15 Mai 2008
					Developpeur  : Stéphane

					Informations : Patch qui supprime les lignes de la table hm_messages 
					qui n'ont pas de fichier correspondant.

 --->
<!---  --------------------------------------------------------------------------  --->
*/

$user = "root";
$password = "PUTYOURBDDPASSWORD";
$hostname = "localhost";
$dbname = "hMailServer";

echo "DEBUT\n";

$retcon=mysql_connect($hostname, $user, $password);
if ($retcon==FALSE)
{
	echo "== EXIT: impossible de se connecter a mySQLServer $user@$hostname";
	exit(1);
}

$retdb=mysql_select_db ($dbname);
if ($retdb==FALSE)
{
	echo "== EXIT: impossible de selectionner la base $dbname";
	mysql_close();
	exit(1);
}

$qry="select messageid from hm_messages order by messageid desc limit 1;";
$result=mysql_query($qry);
$arrayresult=mysql_fetch_array($result);
$max_id = $arrayresult['messageid'];

echo "max id : ".$max_id."\n";

$stats = array();
$nblignes=0;
$cpt = 0;
$pas = 50000;
$nb_paquets_total=ceil($max_id / $pas);
$cpt_paquet=0;
$boucle = true;
while ($boucle) {
	$cpt_fin=$cpt+$pas;
	$qry="select messageid, messagefilename from hm_messages where messageid >= $cpt and messageid < $cpt_fin and TO_DAYS(NOW()) - TO_DAYS(messagecreatetime) > 0;";
	$result=mysql_query($qry);
	$liste_id="";
	if (mysql_num_rows($result) != 0){
		while($lignes = mysql_fetch_array($result)){
			if (! file_exists($lignes['messagefilename'])) {
				if ($liste_id == "") 
					$liste_id = $lignes['messageid'];
				else
					$liste_id = $liste_id.",".$lignes['messageid'];
				
				$nblignes++;
				
				$chemin = explode("\\", $lignes['messagefilename']);
				
				$token = $chemin[3].'_'.$chemin[4];
				if (array_key_exists($token,$stats)) {
					$stats[$token]++;
				} else {
					$stats[$token]=1;
				}
				
				//echo $lignes['messagefilename']."\n";
				
			}
		}
		$cpt_paquet++;
		

BEGIN YOU SHOULD FIRST PUT THIS IN COMMENT

		if ($liste_id != "") {
			echo "suppresion du paquet ".$cpt_paquet." sur ".$nb_paquets_total."\n";
			//echo "delete from hm_messages where messageid in (".$liste_id.");\n";
			$qry_delete="delete from hm_messages where messageid in (".$liste_id.");";
			$result=mysql_query($qry_delete);
			//sleep(1);
		}

END YOU SHOULD FIRST PUT THIS IN COMMENT

	}
	
	if ($cpt > $max_id) {
		echo "\n\n";
		echo "fin du traitement\n";
		$boucle=false;
	}
	$cpt+=$pas;
}
echo "\n\nnombre de lignes supprimees : ".$nblignes."\n";

echo "\n";
foreach($stats as $cle=>$valeur) {
	echo $cle.' : '.$valeur."\n";
}

mysql_close();

echo "\nFIN\n";
exit(0);
?>

Thanks martin for this perfect software, the bug between lines # in bdd and files is due to manual purges of files. I check out your code, it's just amazing !! (I stop trying to put my addon in, too much work to understand correctly).

Stéphane

Post Reply