Database writer plugin - exception error

Is Virtual Radar Server not behaving itself? If so then please report it here.
T-EGJA1
Posts: 93
Joined: Sun Sep 08, 2013 12:11 am
Location: Mid-Devon, UK

Database writer plugin - exception error

Postby T-EGJA1 » Thu Apr 14, 2016 1:30 pm

Hi Andrew,

For the second time in a week, the database writer plugin has stopped working, showing an 'exception error' - extract from the log file below:

Code: Select all

[2016-04-14 10:47:16.741 UTC] [t25] Database writer plugin caught exception on flushing old flights: System.Data.DataException: Error parsing column 2 (LastModified=11/12/2007 11:18:47 - DateTime) ---> System.FormatException: String was not recognized as a valid DateTime.
   at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at System.Data.SQLite.SQLiteDataReader.get_Item(Int32 i)
   at Deserialize40d3c14f-b2a2-4a50-8672-8bd0749a1861(IDataReader )
   --- End of inner exception stack trace ---
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value)
   at Deserialize40d3c14f-b2a2-4a50-8672-8bd0749a1861(IDataReader )
   at Dapper.SqlMapper.<QueryImpl>d__58`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
   at VirtualRadar.Database.BaseStation.Database.Aircraft_GetByIcao(String icao)
   at VirtualRadar.Database.BaseStation.Database.GetOrInsertAircraftByCode(String icao24, Func`2 createNewAircraftFunc)
   at VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.FetchOrCreateAircraft(DateTime now, String icao24)
   at VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.WriteFlightRecords(FlightRecords flightRecords)
   at VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.WriteNewFlights()
   at VirtualRadar.Plugin.BaseStationDatabaseWriter.Plugin.Heartbeat_SlowTick(Object sender, EventArgs args)


This is repeated through the log file at 10 second intervals until I disable then re-enable the database writer ......... any thoughts as to what could be causing this ... and how to prevent it happening again?
Terry

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

Re: Database writer plugin - exception error

Postby agw » Sat Apr 16, 2016 11:28 pm

You have an aircraft in your BaseStation.sqb with a LastModified date string ("11/12/2007 11:18:47") that the ADO.NET SQLite adapter can't convert back into a date. I think usually the date strings in BaseStation.sqb are formatted using the ISO format of YYYY-MM-DD HH:MM:SS, your date string is a local format of either DD/MM/YYYY HH:MM:SS or MM/DD/YYYY HH:MM:SS.

If you use a SQLite editor then you can find the aircraft with the bad dates by running:

Code: Select all

SELECT * FROM [Aircraft] WHERE [LastModified] LIKE '__/%';

(that's two underscores, a forward slash and a percent sign) and fix the dates to something that VRS can read with:

Code: Select all

UPDATE [Aircraft] SET [LastModified] = '2016-04-03 23:23:00' WHERE [LastModified] LIKE '__/%';

Chances are though that other dates in those records are also using the wrong format and LastModified was just the first one that VRS tried to read. You may have to repeat the exercise for the other date fields.

If you're not comfortable with running a SQLite editor and running SQL then feel free to zip up the file, upload it to a file sharing site, then send me the link and I'll do the honours.

T-EGJA1
Posts: 93
Joined: Sun Sep 08, 2013 12:11 am
Location: Mid-Devon, UK

Re: Database writer plugin - exception error

Postby T-EGJA1 » Sun Apr 17, 2016 11:26 pm

Hi Andrew,

I'm happy enough editing the basestation.sqb (using SQLite Expert Personal) - I've tracked down the errant date entries and corrected them; so your kind offer, although much appreciated, won't be required :)

Once again, many thanks for your time and help.
Terry

n7uv
Posts: 5
Joined: Wed Dec 14, 2016 7:22 pm

Database Writer plugin - Unhandled Exception Caught

Postby n7uv » Sun Apr 23, 2017 10:31 pm

Perhaps it's not the same error, but I found this thread while searching for an answer to the problem I'm having recently with my VRS Database Writer plugin. I am getting a "Unhandled Exception Caught", I can't grab the text from the box, i was unable to upload the screenshot as I got an "HTTP error". It basically says that there was an attempt to write a readonly database.
Any ideas? Or should I un-and re-install the plugin?

Cheers - Jon N7UV

n7uv
Posts: 5
Joined: Wed Dec 14, 2016 7:22 pm

Re: Database Writer plugin - Unhandled Exception Caught

Postby n7uv » Sun Apr 23, 2017 10:36 pm

n7uv wrote:Perhaps it's not the same error, but I found this thread while searching for an answer to the problem I'm having recently with my VRS Database Writer plugin. I am getting a "Unhandled Exception Caught", I can't grab the text from the box, i was unable to upload the screenshot as I got an "HTTP error". It basically says that there was an attempt to write a readonly database.
Any ideas? Or should I un-and re-install the plugin?

Cheers - Jon N7UV


Ah, perhaps it has to do with my database being 2.6 GB in size? Seems pretty large... :oops: Is there a way to shrink that?

Cheers - Jon N7UV

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

Re: Database writer plugin - exception error

Postby agw » Mon Apr 24, 2017 1:43 am

The exception should be logged, if you go to Tools | View VirtualRadarLog.txt (or something along those lines, going off memory) then it'll be towards the bottom of that.

A 2.6 GB database won't cause exceptions, SQLite don't have a practical upper limit to the file size. Mine's currently at 9.8 GB and it's running just fine. If you're storing it on a FAT drive then you might run into problems at 4 GB but I don't imagine many people are formatting drives as FAT nowadays.

If the exception is SQLite reporting database corruption then there is a tool that you can download to (hopefully) fix it:

http://www.virtualradarserver.co.uk/Files/SQLiteFileRepairSetup.exe

It should be fairly self explanatory: from memory you have an option to check the file, if that reports errors then there's another option to rebuild it. You just need to make sure that nothing is using the file when you attempt a repair. I think it takes backups automatically but you should probably keep a safety copy of the original, just in case. All the tool does is run sqlite3 against the file.


Return to “Bug Reports”

Who is online

Users browsing this forum: No registered users and 1 guest