Page 1 of 1

Replicating weblinks

Posted: Mon Dec 19, 2022 3:35 pm
by manair
We have two WingFTP servers and replicate the XML config files and user data files between them using DFSR. This works well apart from since the move from flat files to the SQLite Database for weblinks. If a user creates a Weblink on one server and a recipient tries to download the file on the other it obviously fails. Is there a method to replicate the tables with bookmark_db between the servers?

Re: Replicating weblinks

Posted: Tue Dec 20, 2022 12:56 am
by FTP
So please try to copy the file "Data/bookmark_db" from the old server. If still fails, please contact us via support email.

Re: Replicating weblinks

Posted: Tue Dec 20, 2022 9:53 am
by manair
This is not a one off copy. We have two servers active for resilience. As the file is open by the server DFSR does not replicate it in a timely manner.

I have written a PowerShell script that copies new entries from one database to the the other and visa-versa, I'll test it for a few days and post here if it works.

Re: Replicating weblinks

Posted: Thu Dec 22, 2022 8:50 am
by manair
Our setup is two WingFtp Servers with a shared IP using Microsoft NLB.
The _weblinks, groups, users and user data (uploaded files) folders are replicated using windows DFSR.
This has always been enough in the past, but with the introduction of the bookmarks_db database weblinks broke in our setup.

The following PowerShell script running every minute from a scheduled task has resolved this gap.
Please excuse any bad practice in the code, I'm not a developer and the script works, so good enough for me.

Code: Select all

#Requires -Modules pssqlite
<#
    Used to keep WingFTP weblink database sync with partner server
    Will remove any entries from databases that does not have a corresponding file in the _Weblinks folder on either server
    Will add entries to database if missing and found on partner server.
    Shares must be created to allow account running scripts access to _Weblinks folder and databases    	 
#>
 
#set variables
$CurDir = $PSScriptRoot
$ServerA = "xxx-ftpx-pp-a01"
$ServerB = "xxx-ftpx-pp-b01"
$DatabaseA = "\\$ServerA\WingFTPData\bookmark_db"
$DatabaseB = "\\$serverB\WingFTPData\bookmark_db"
$WeblinksFolderA = "\\$ServerA\WingFTPData\_WEBLINK"
$WeblinksFolderB = "\\$ServerB\WingFTPData\_WEBLINK"
$logfile = "$CurDir\Replicate Weblinks Database.csv"

$SelectQuery = "SELECT * from wftp_weblink"
$UpdateQuery = "INSERT INTO wftp_weblink (f_weblink, f_domain, f_username, f_is_uplink, f_create_time, f_update_time, f_count, f_filename, f_filepath, f_filesize, f_clientinfo, f_email, f_expire_time) 
    VALUES (@q_weblink, @q_domain, @q_username, @q_is_uplink, @q_create_time, @q_update_time, @q_count, @q_filename, @q_filepath, @q_filesize, @q_clientinfo, @q_email, @q_expire_time)"


#remove deleted Weblinks from Database
try {
    #get entries from databases on both servers
    $DatabaseALinks = Invoke-SqliteQuery -DataSource $DatabaseA -Query $SelectQuery
    $DatabaseBLinks = Invoke-SqliteQuery -DataSource $DatabaseB -Query $SelectQuery
} Catch {
    #exit if any errors are encountered
    throw $_
}
If ((Test-Path $WeblinksFolderA) -and (Test-Path $WeblinksFolderB)){ #check it can reach both weblink file locations
    $LiveWeblinkFiles=@{} #create hash table for files
    gci $WeblinksFolderA | % {$LiveWeblinkFiles[$_.name]=1} # Add found files in _weblinks to hash table
    gci $WeblinksFolderB | % {$LiveWeblinkFiles[$_.name]=1} # Add found files in _weblinks to hash table
    $RemoveFromServerA = $DatabaseALinks | ? {!$LiveWeblinkFiles.ContainsKey($_.f_weblink)} #find entries in database where there isn't a _weblink file
    $RemoveFromServerB = $DatabaseBLinks | ? {!$LiveWeblinkFiles.ContainsKey($_.f_weblink)} #find entries in database where there isn't a _weblink file
    $RemoveFromServerA | % { #remove entries from database
        Invoke-SqliteQuery -DataSource $DatabaseA -Query "DELETE from wftp_weblink WHERE f_weblink = ""$($_.f_weblink)"""
    }
     $RemoveFromServerB | % { #remove entries from database
        Invoke-SqliteQuery -DataSource $DatabaseB -Query "DELETE from wftp_weblink WHERE f_weblink = ""$($_.f_weblink)"""
    }
}

#add new entries from database
try {
    #get entries from databases on both servers
    $DatabaseALinks = Invoke-SqliteQuery -DataSource $DatabaseA -Query $SelectQuery
    $DatabaseBLinks = Invoke-SqliteQuery -DataSource $Databaseb -Query $SelectQuery
} Catch {
    #exit if any errors are encountered
    throw $_
}

$ToAddtoDatabaseA = $DatabaseBLinks | ? {$DatabaseALinks.f_weblink -notcontains $_.f_weblink} # find entries that are on one server and not the other
$ToAddtoDatabaseB = $DatabaseALinks | ? {$DatabaseBLinks.f_weblink -notcontains $_.f_weblink} # find entries that are on one server and not the other


$ToAddtoDatabaseA | % { #insert missing entries
    Invoke-SqliteQuery -DataSource $DatabaseA -Query $UpdateQuery -SQLParameters @{
		q_weblink = $_.f_weblink
        q_domain = $_.f_domain
        q_username = $_.f_username
        q_is_uplink = $_.f_is_uplink
        q_create_time = $_.f_create_time
        q_update_time = $_.f_update_time
        q_count = $_.f_count
        q_filename = $_.f_filename
        q_filepath = $_.f_filepath
        q_filesize = $_.f_filesize
        q_clientinfo = $_.f_clientinfo
        q_email = $_.f_email
        q_expire_time = $_.f_expire_time

	}
}

$ToAddtoDatabaseB | % {#insert missing entries
    Invoke-SqliteQuery -DataSource $DatabaseB -Query $UpdateQuery -SQLParameters @{
		q_weblink = $_.f_weblink
        q_domain = $_.f_domain
        q_username = $_.f_username
        q_is_uplink = $_.f_is_uplink
        q_create_time = $_.f_create_time
        q_update_time = $_.f_update_time
        q_count = $_.f_count
        q_filename = $_.f_filename
        q_filepath = $_.f_filepath
        q_filesize = $_.f_filesize
        q_clientinfo = $_.f_clientinfo
        q_email = $_.f_email
        q_expire_time = $_.f_expire_time

	}
}

#update log files
$logs=@()
$logs += $ToAddtoDatabaseA | select @{l="Data";e={get-date}},*,@{l="Action";e={"Added to $WeblinksFolderA"}} 
$logs += $ToAddtoDatabaseB | select @{l="Data";e={get-date}},*,@{l="Action";e={"Added to $WeblinksFolderB"}}
$logs += $RemoveFromServerA | select @{l="Data";e={get-date}},*,@{l="Action";e={"Removed from $WeblinksFolderA"}}
$logs += $RemoveFromServerB | select @{l="Data";e={get-date}},*,@{l="Action";e={"Removed from $WeblinksFolderB"}}
if ($logs) {
    $logs  | Export-Csv $logfile -Append
    $logs | select f_filepath ,action
}

Re: Replicating weblinks

Posted: Fri Dec 23, 2022 1:48 am
by FTP
You are a good man! Thanks for sharing your script, I think it will be helpful for other people.

Re: Replicating weblinks

Posted: Fri Apr 28, 2023 2:37 pm
by svazulia
hello, i try do the same (by shell script):
i create weblink file
than i add record to bookmark_db
and .... nothing.
weblink become working after server restart.
my enviroment is: two wftp servers v7.2.0 with shared storage (_WEDLINK directory is there) running on ubuntu servers.
of couse, i need sync weblinks between my servers.

Re: Replicating weblinks

Posted: Thu May 04, 2023 5:14 am
by FTP
If restarting the WingFTP service will let the new created weblink work, then I think maybe the SQLite records were not inserted really, at least WingFTP can't read the new records. So please check the related SQLite operation part.

Re: Replicating weblinks

Posted: Fri May 05, 2023 12:18 pm
by FTP
Another method is using a shared folder to store weblink files and SQLite db file, you can set shared folders under "Server > Settings > General Settings > Miscellaneous", like this:

Image

After configuring the above settings, you also need to copy the file "Data/settings.xml" into another WingFTP server.

BTW, the same domain ID is necessary for all the WingFTP servers, and if you want to keep the same weblinks, you had better use Mysql/ODBC database for storing user accounts, then all the servers will share one copy of the user account.