Replicating weblinks
-
- Posts: 10
- Joined: Thu Nov 01, 2012 8:05 pm
Replicating weblinks
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?
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: Replicating weblinks
So please try to copy the file "Data/bookmark_db" from the old server. If still fails, please contact us via support email.
-
- Posts: 10
- Joined: Thu Nov 01, 2012 8:05 pm
Re: Replicating weblinks
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.
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.
-
- Posts: 10
- Joined: Thu Nov 01, 2012 8:05 pm
Re: Replicating weblinks
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.
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
}
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: Replicating weblinks
You are a good man! Thanks for sharing your script, I think it will be helpful for other people.
-
- Posts: 1
- Joined: Fri Apr 28, 2023 2:29 pm
Re: Replicating weblinks
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.
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.
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: Replicating weblinks
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.
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: Replicating weblinks
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](https://www.wftpserver.com/bbsres/sharedfolder.png)
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.
![Image](https://www.wftpserver.com/bbsres/sharedfolder.png)
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.