Recurring Problem Database Writer Plugin

Is Virtual Radar Server not behaving itself? If so then please report it here.
Post Reply
paradiselost
Posts: 101
Joined: Sun Apr 06, 2014 10:22 am
Location: Philippines

Recurring Problem Database Writer Plugin

Post by paradiselost » Sun Jul 12, 2015 8:35 pm

A while back I reported a Database Writer plugin anomaly regarding a locally updated database distributed to my feeders. The Database Writer plugins on my feeders machines quit recording at about the same date and time. The only workaround put forward was to just restart the Database Writer plugin and advise my feeders to do the same.

Since that time I have added feeders with sysadmins who haven't a clue what we do and how we do it.

The Exception that craters the Database Writer plugin is VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.MessageQueue_MessageReceived(BaseStationMessageEventArgs args) System.FormatException: String was not recognized as a valid DateTime. After a number of these Exceptions it quits working.

Andrew suggested that maybe it had something to do with manually updating the database and then distributing it to my feeders. I have used his tool to check the database for errors and nothing is found. It is OK.

Looking at this year's log, the Database Writer plugin quits working every month or two at various times UTC not only on my server but on my feeders machines also. I use the Reports for various maintenance purposes and it seems to me that the Database Writer plugin should ignore these types of Exceptions since they obviously don't impact the function of the plugin or program.

If Andrew's SQL check program finds no problem with the database, why not ignore them.

John
Paradiselost
Working Example of Version 2 Beta Virtual Radar Server http://dgteflyovers.ddns.net/virtualradar/
VRS 2 Help Files http://dgteflyovers.ddns.net:8080

agw
Posts: 2241
Joined: Fri Feb 17, 2012 3:20 am

Re: Recurring Problem Database Writer Plugin

Post by agw » Mon Jul 13, 2015 12:49 am

The database is alright, from what I remember the problem lies with a date on one of the aircraft records. SQLite lets you write dates in a variety of different formats and they're all incompatible, so generally if you have dates in an SQLite file it's a good idea to pick one format and stick with it. In the case of BaseStation.sqb Kinetic chose to write dates as a string that complies with the ISO8601 format (https://en.wikipedia.org/wiki/ISO_8601).

In your case you have a record for at least one aircraft, probably an aircraft that you don't see very often, that has a date field that's been written in another format. It's probably going to be a number - either the number of seconds since 1/1/1970, or the number of 100ns ticks since 1/1/1 or something like that. Hard to tell what it is, but what it is not is a valid ISO8601 string.

I have uploaded a LINQPad script that you can use to check your BaseStation.sqb for aircraft records that cannot be loaded. It's not 100% guaranteed to be able to detect the problem, I'm not sure if LINQPad's driver will just try different date formats if it finds one that isn't ISO8601, but you can give it a go and see how you get on. If it does have a problem reading an aircraft record then it will tell you the ICAO code for the aircraft that needs looking at.

To run the script download LINQPad from here (it's free): https://www.linqpad.net/

The script can be downloaded from here: http://www.virtualradarserver.co.uk/Fil ... rcraft.zip

Install LINQPad first and then unzip the script and double-click it to get it loaded into LINQPad. The script contains instructions that tell you how to configure LINQPad so that it can read your BaseStation.sqb. When you run the script make sure that you are not running anything that might be reading or writing BaseStation.sqb.

paradiselost
Posts: 101
Joined: Sun Apr 06, 2014 10:22 am
Location: Philippines

Re: Recurring Problem Database Writer Plugin

Post by paradiselost » Mon Jul 20, 2015 7:09 am

The LINQPad and your script seem to find the records that won't load but it does that in the first hour and after that continues to execute hours on end. My 80 meg database has taken so far 9 hours and it hasn't found anything else since the initial 1215 records with German registry. All the records have icao24 numbers that start with 3xxxxx.

You don't have a script that will do a batch delete instead of having to delete them one by one with Active Display Lite? The bad records start at 3E810E and end at 3FC576.

Since basestation.sqb is used with VRS and it is mainly local, I don't see German registrations in asia. The bad records found had faulty information and seem to have been populated by a program of some sort that didn't work out.

I would run your script again to see if anything else could be found.

I would suggest anyone using this script to copy their basestation.sqb to another directory and run the script on the copy instead of having to take VRS down hours on end.

John
paradiselost
Working Example of Version 2 Beta Virtual Radar Server http://dgteflyovers.ddns.net/virtualradar/
VRS 2 Help Files http://dgteflyovers.ddns.net:8080

agw
Posts: 2241
Joined: Fri Feb 17, 2012 3:20 am

Re: Recurring Problem Database Writer Plugin

Post by agw » Mon Jul 20, 2015 9:22 pm

You could use the command-line sqlite3 program to open the file and delete the records. You can download sqlite3 from SQLite.org (http://sqlite.org/2015/sqlite-shell-win ... 081002.zip) and the commands would be:

sqlite3 BaseStation.sqb (to open the file - do this on a backup!)
DELETE FROM Aircraft WHERE ModeS >= '3E810E' AND ModeS <= '3FC576'; (to delete the aircraft records)
.quit (to come out of sqlite3)

If you want to see how many aircraft records you have then this will tell you:

SELECT COUNT(*) FROM Aircraft;

However, if there's 1200+ records with dodgy dates then it would be a shame to delete them, you could just fix the formatting for those date fields.

You can't do that through LINQPad though, it won't be able to load the dates to change them. You'd need to do it from sqlite3. I can't really tell you the SQL for that without first seeing how the dates are currently formatted for those records. If you like you can zip up a copy of your BaseStation.sqb, upload it to a file sharing site like Dropbox, Google Drive etc., PM me the link and I can take a look, and either fix the dates or give you the SQL that will fix them.

EDIT: Forgot to mention - the script should rattle through your aircraft in a few minutes. If it takes 9 hours then something is looping somewhere. You can press Shift+F5 or click the stop button to stop it running if it seems to be taking too long.

paradiselost
Posts: 101
Joined: Sun Apr 06, 2014 10:22 am
Location: Philippines

Re: Recurring Problem Database Writer Plugin

Post by paradiselost » Mon Aug 03, 2015 10:08 pm

Andrew someone recommended PlaneBase. I had a basestation.sqb about 95 megs that was causing the problem with the 1200+ errors with LinQpad and continuous running.

With PlaneBase I configured it to delete UNKNOWNS on startup and from the database view am able to see and edit all the entries for a record. Their record lookup function is excellent.

The 95 meg basestation.sqb miraculously shrunk to 23 meg saving all of the records of local flights. Maybe it threw out the baby with the bathwater but so far no more Database Writer freezes. A side effect is since the zipped database file is smaller, I don't have to go to the hassle of uploading it to one of the clouds. Hotmail is happy sending it as an attachment to email to my feeders.

I don't recall how many records were in the bloated database to start but there are about 58,000 records left. Using Active Display Lite I had to edit 10-20 records a day whereas now it is less than 5.

Thank you for the help in diagnosing the problem and thanks to the person that recommended PlaneBase.

John
paradiselost
Working Example of Version 2 Beta Virtual Radar Server http://dgteflyovers.ddns.net/virtualradar/
VRS 2 Help Files http://dgteflyovers.ddns.net:8080

Post Reply