I have got the server back running, thank you very much for the assistance. Here are the scripts I used to reconstruct the database.
The script to find the latest commits and to delete references to non-existent (deleted) libraries;
resync-seafile-database.php:
#!/usr/bin/php
<?php
/*
* @File resync-seafile-database
* @Author skauranne@naturalsciences.be
* @Created 2018-04-12
* Released to public domain without any kind of warranty or liability.
* Always make sure to dump a backup of your database before attempting a re-sync.
* This script is intended for re-synchronizing a backed up version of the
* Seafile v6.x database with a more recent state of the filesystem.
* Use of this script in other circumstances or with incorrect configuration
* may damage your Seafile installation.
*/
echo("\nAttempting to re-synchronize the seafile database with filesystem...\n");
echo("Connection to Seafile database...\n");
$basePath = "/opt/seafile/seafile-data/storage/commits/";
$dbUser = "USERNAME";
$dbPass = "PASSWORD";
$dbTables = ["Branch","Repo","RepoFileCount","RepoGroup","RepoHead","RepoHistoryLimit","RepoOwner","RepoSize","RepoValidSince"];
$db = new PDO("mysql:host=127.0.0.1;dbname=seafiledb", $dbUser, $dbPass);
if(!$db)
die("Failed! Terminating...\n");
// Get the list of repositories
$repos = $db->query("SELECT * FROM Branch");
echo($repos->rowCount()." repositories found in the database.\n");
$updated=$skipped=$notExist=$errors=0;
// Loop through the repositories in the database
while($repo = $repos->fetch(PDO::FETCH_OBJ)) {
echo('Repository '.$repo->repo_id.':');
// Look for a corresponding directory in the filesystem
$repoPath = $basePath . $repo->repo_id;
if(!is_dir($repoPath)) {
$notExist++;
echo("Not found on the filesystem, deleting from database.\n");
foreach($dbTables as $table) {
$db->exec("DELETE FROM $table WHERE repo_id='{$repo->repo_id}'");
}
continue;
}
// find the most recent commit file in the filesystem
$commitMeta=explode(' ', exec("find $repoPath -type f -print0 | xargs -0 stat --format '%Y :%y %n' | sort -nr | cut -d: -f2- 2>/dev/null | head -n 1"));
$commitId = str_replace('/', '', str_replace($repoPath, '', $commitMeta[3]));
// Database shows the same commit id
if($commitId == $repo->commit_id) {
echo("Database up to date!\n");
$skipped++;
}
// Database shows a different commit id, assume filesystem is correct
else {
echo("Last commit on filesystem on $commitMeta[0] at ".substr($commitMeta[1],0,8).", updating database...");
if(0 < $db->exec("UPDATE Branch SET commit_id='$commitId' WHERE repo_id='{$repo->repo_id}'")) {
echo("Success!\n");
$updated++;
}
else {
echo("Failed!\n");
$errors++;
}
}
}
echo("Finished!\n" .
"$notExist references to non-existent repositories deleted from the database.\n" .
"$updated existing repositories updated, $skipped skipped, $errors errors encountered.\n\n");
And the script to find orphaned libraries and to add the new ones to the database:
find-seafile-fs-orphans.php
#!/usr/bin/php
<?php
/*
* @File find-seafile-fs-orphans
* @Author skauranne@naturalsciences.be
* @Created 2018-04-12
* This script has been released to public domain without any kind of warranty.
* This script is intended to search for libraries in the physical filesystem
* which are newer than a certain date, and to create the necessary database
* entries to make these available to seafile in a scenario where the database
* has been recovered from an older backup. Using this script in a different
* scenario might damage your database.
* Always make a backup dump of your Seafile database before launching this script.
*/
echo("\nSearching for orphaned libraries in the filesystem...\n");
echo("Connection to Seafile database...\n");
$basePath = "/opt/seafile/seafile-data/storage/commits/";
$dbUser = "USERNAME";
$dbPass = "PASSWORD";
// Time of the backup point to decide, if a library was created before or after
$backupTime = strtotime('YYYY-MM-DD HH:MM:SS +HHMM');
$db = new PDO("mysql:host=127.0.0.1;dbname=seafiledb", $dbUser, $dbPass);
if(!$db)
die("Failed! Terminating...\n");
// Get the list of repositories in the filesystem
$repos = scandir($basePath);
echo( count($repos)-2 . " repositories found in the filesystem.\n");
$found=$notFoundOld=$notFoundNew=$created=$errors=0;
// Loop through the repositories
foreach($repos as $repoId) {
$repoPath = $basePath . $repoId;
if($repoId=='.' || $repoId=='..') {
continue;
}
// Check if the database has an entry for this Repo Id.
$repoDb = $db->query("SELECT * FROM Repo WHERE repo_id='$repoId'");
// No match found
if($repoDb->rowCount()==0) { // No match
echo("Repository $repoId not found in the database!\n");
// Get the newest commit on the filesystem
$newestCommitMeta=explode(' ', exec("find $repoPath -type f -print0 | xargs -0 stat --format '%Y :%y %n' | sort -nr | cut -d: -f2- 2>/dev/null | head -n 1"));
$newestCommitTime = $newestCommitMeta[0] .' '. substr($newestCommitMeta[1],0,8);
$newestCommitId = str_replace('/', '', str_replace($repoPath, '', $newestCommitMeta[3]));
// Get the oldest commit from the filesystem
$oldestCommitMeta=explode(' ', exec("find $repoPath -type f -print0 | xargs -0 stat --format '%Y :%y %n' | sort -n | cut -d: -f2- 2>/dev/null | head -n 1"));
$oldestCommitTime = $newestCommitMeta[0] .' '. substr($newestCommitMeta[1],0,8);
$oldestCommit = json_decode(file_get_contents($oldestCommitMeta[3]));
$owner = $oldestCommit->creator_name;
// Check if the oldest commit took place after the time the database originates from (== new library)
if(strtotime($oldestCommitTime) > $backupTime) {
echo("New library: Created on $oldestCommitTime by $owner\n");
$notFoundNew++;
echo("Writing database entries...");
if(1 > $db->exec("INSERT INTO Repo (repo_id) VALUES ('$repoId')")) {
$errors++;
echo("Error creating Repo record $repoId, aborting!\n");
}
elseif(1 > $db->exec("INSERT INTO RepoOwner (repo_id, owner_id) VALUES ('$repoId', '$owner')")) {
$errors++;
echo("Error setting owner $owner to $repoId, aborting!\n");
}
elseif(1 > $db->exec("INSERT INTO RepoHead (repo_id, branch_name) VALUES ('$repoId', 'master')")) {
$errors++;
echo("Error setting Branch 'master' to $repoId, aborting!\n");
}
elseif(1 > $db->exec("INSERT INTO Branch (repo_id, commit_id, name) VALUES ('$repoId', '$newestCommitId', 'master')")) {
$errors++;
echo("Error setting latest commit $newestCommitId to $repoId branch master, aborting!\n");
}
else {
$created++;
echo("Success!\n\n");
}
}
else {
echo("Ignoring deleted library created on $oldestCommitTime\n\n");
$notFoundOld++;
}
}
// Match in the db, everything ok.
else {
$found++;
}
}
echo("Finished!\n" .
"$notFoundNew New repositories with no record in the database found.\n" .
"$created Records successfully created in the database, $errors database errors encountered.\n" .
"$notFoundOld Deleted repositories with no record in the database ignored.\n" .
"$found repositories OK. \n\n");