Howto repair missing attachment entries

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 :-)


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


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 ./ /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($) {

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

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

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";

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

	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 "..")){

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

	$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";
                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";
                #$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";