Howto repair missing attachment entries

mclu

Hi…
During previous migrations it seams that some attachments are missing on disk.
This causes issues while indexing with the search service as well as during backup of that store.
How can I fix my database. I don’t care about the missing attachments so possible options:

remove reference to that attachment in the database
autocreate a fake gz file

Are there scripts outside?
For zarafa I created scripts that also finds not referenced attachment files on disk. Maybe I have to refactor and migrate this :-)

Coffee_is_life

HI,

as far as i know a clean reindexing should do it. just stop kopano-search, remove the index-folder (or rename for backup) and start it again. Wait :)

kopano once told me to change the indexer-threads to 1 for reindexing. this will incrase the time the indexer needs but prevents some failures in the past. Dont know if its still necessary/recommended

coffee_is_life

mclu

Ok… I will try this but this will not “repair” the database references.
Lets assume I have moved to a new server and the attachment backup is older than the database. Which means you have less files on disk!

For the other direction (more files on disk than in db) I just refactored my cleanup script… feel free to test it or add to the tooling:

# start like
sudo ./kopano-remove-unreferenced-attachmentfiles.pl /etc/kopano/server.cfg n
# where last "n" indicates to just display and NOT to delete the files. "y" will remove them.

#!/usr/bin/perl -w

# This script checks your current configured attachment directory and checks if there are files which are not 
# referenced within the singleinstances table. If this is the case the file is unreferenced and should be removed.
# This can happen if you restore an older database with a newer backup of the files or
# if the file backup is older than the database and the softdelete removed existing files from DB already.
# 
# Feel free to comment on this. But this is my first perl script :-) Normally java is my base ;-)
# Note:
# you need perl, libdbi-perl and libdbd-mysql-perl to execute it

# Credits: Markus Lutum - 2018


use strict;
use DBI;

my $L1 = 10;
my $L2 = 20;

sub do_error($) {
   exit(1);
}

sub readconfig($) {
	my ($fn) = @_;
	my %options;

	open(CFG, $fn) or die("unable to open ".$fn." config file");
	while (<CFG>) {
		if ($_ =~ /^\s*[#!]/) {
			next;
		}
		if ($_ =~ /^\s*(\S+)\s*=\s*([^\r]+)\r?$/) {
			my $idx = $1;
			my $val = $2;
			chomp($val);
			$val =~ s/\s+$//;
			$options{$idx} = $val;
		}
	}
	close(CFG);
	return %options;
}

# TODO: parse config, and use settings
if(scalar(@ARGV) < 2) {
	print "Usage: $0 <server.cfg> <reallyDelete>\nWhere <reallyDelete> mut be 'y' to start delete unreferenced files.\n\n";
	exit(1);
}

my $servercfg = $ARGV[0];
my $deleteFiles = ($ARGV[1] eq 'y');
$servercfg = "/etc/zarafa/server.cfg" if (!defined($servercfg));
my %serveropt = readconfig($servercfg);

my $basepath = $serveropt{attachment_path};

my $db = DBI->connect("dbi:mysql:database=".$serveropt{mysql_database}.";host=".$serveropt{mysql_host}, $serveropt{mysql_user}, $serveropt{mysql_password})
	or die "Database error: ".$DBI::errstr;

my $res; 
my $sth;
my $rows;
my @row;

if (!defined($db)) {
	print "did not connect to mysql\n";
	exit(1);
}

my $i;
my $f;
my $count=0;
my $countOk=0;
my $ok;
my $countFalse=0;
my $filename;

for($i=0;$i<=9;$i++){
for($f=0;$f<=19;$f++){
	my $currentdir = $basepath."/".$i."/".$f;
print "dir: $currentdir\n";
	opendir DIR, $currentdir or die "cannot open dir $currentdir: $!";
	my @file= readdir DIR;
	closedir DIR;
	
	foreach my $file (@file) { 
	
	# skip . and .. 
	if (($file eq ".") || ($file eq "..")){
           next;
        }

	$filename = $file;
	$filename =~ s{\.[^.]+$}{}; # removes extension
	# print "$file  -  $filename \n"; 
	$count++;

	$sth = $db->prepare("SELECT * FROM singleinstances where instanceid = '$filename' ;");
        $sth->execute() || die $DBI::errstr;;

	if ($sth->rows == 0) {
		print "No attachment found for $currentdir/$filename.\n";
		$countFalse++;
                if ($deleteFiles){
                # can be either with or without compression
		system("rm -f ".$currentdir."/".$filename." ".$currentdir."/".$filename.".gz");
                }
	} else {
		# there is a Db entry. No need to check the data	
		#print "Found attachment found for $filename.\n";
		$countOk++;
                #$ok=$ok." ".$filename;
	}

	} #foreach
	
}
}

print "delete files enabled: $deleteFiles\n";
print "Filetotal:$count \n";	
print "FileOk:$countOk \n";	
print "FileFalse:$countFalse \n";
#print "OK:$ok \n";	

# end for now
print "Done.\n";

compsos

Tried the script but suspect it is listing all the email that do not have an attachment (ie) email only as opposed to an email that does have an attachment. Where I was looking for emails that are missing attachments.
delete files enabled:
Filetotal:245970
FileOk:219405
FileFalse:26565
Done.
A lot of the ones listed did exist but do not have a gz file but were valid email only.
Was that the intention of the script to find orphaned emails without their attachment?

compsos

Try this subject https://forum.kopano.io/topic/1704/kopano-search-issue
It seems to have solved the issue.