ZeroTier Networking

[Oblivian]

Ahh yeah. Windows.

I can't think what triggered the ID to reset. Logs full of conflicts now as a result. I've not done anything to the 'live' dbase. Other than an accidental data recovery. Rolled back to the original untouched versions and it didn't correct :/

Hard to see where it applies the auto-increments from to correct too. Unless I just need to re-index them all, or try trash it and fresh recover from the backed up one I took a few nights ago.

Will mean loss since the 22nd is all.

[Oblivian]

Current state...

left 1.10 Mysql Dbase buggered for now - will try a rebuild restore later and see if I can resurrect the fligth ID table to increment from the most recent record again

Currently working with the sqlite version (that worked on windows) from before latest tweaks/fixes trying to see if I can re-write all my PHP pages to use PDO sqlite connection and get the same results as mysql

[coupe]

You forced me to startup my old windows VM. Tried running adsbsqlite.jar and it said can't open database, just like you. Tried moving the jdbc file around, no joy. Tried sqlitebrowser and it seems to find the databse just fine. I'll have to try and remember how to use Windows. I switched to Linux about 30 years ago, so it's a slog working with that technology. I just assumed Java did the right thing under both OS. So much for "compile once and run anywhere"TM ha!

[Oblivian]

Glad to know I'm not the only one to spend crazy hours trying to get stuff working that just.. isnt but should

But don't feel the need to go out of the way. Given i borked my 1.1 dbase, and it was dumping fine, I'd be fine if MySQL 1.9 tweaked to not do -999 etc was restored to github vs reinvention I think I kept the pre-tweak version

Oddly the previous build with the -999 issue sqlite is working fine for me to test re-engineering of my code

I've spent the last couple of hours adjusting to pdo file connections from mysql and working how to do utc to local time conversion..

That is, after I worked out how to read the timestamp now it's pure unix millisecond

If you don't learn something new each day....

[coupe]

Loaded netbeans on Windows and compiled. Seems to work now. Here's the java jar: https://github.com/srsampson/ADSBSQL...master/Windows

[coupe]

I don't have version 1.9 anymore. I lost most of my stuff when backup drive decided to blow up. It's kind of refreshing to not have all that old code available anymore, ha.

[coupe]

Looking at the problem, I think the repository for adsbsqlite was compiled with a new jdbc file sqlite-jdbc-3.32.3.2.jar
Put I included an older jdbc file in the distribution. Hopefull have fixed that now. The one in the Windows was compiled with the 3.30 JDBC, so you might try that one first.

[Oblivian]

I could have sworn this was gone the other day when I looked and only sqlite version left?.. https://github.com/srsampson/ADSBMySQL
Or am I going batty. (that still has the -999 etc errors right?)

I've nabbed it anyway. If I can fix my current SQL data and convert/get it merged back to work with above version with the changes I'll be back faster than reinventing stuff.

Both versions now seem to start. Just can't read the version on the bar for the mysql copy

Made some progress with queries to an sqlite file in a similar way that mysql was. For historic stuff at least.
Some things don't work as nice. Like SELECT queries with time intervals like it had :/ (last 24hr period check from current time sort of thing)

And more work for UTC to local conversion. Not sure if that is a PHP limitation or not.

But hey, it's a friday evening here. So pleanty of time to mess about now I have recent copy of both writers

Merci

[Oblivian]

I just noticed the new verticaltrend column

Previously I was basically doing the calc and flagging if they were climbing or decending based on the actual rate change reported.

$rate=$rows['verticalRate'];
if ($rate <0)
{$rate="▼";}
if ($rate >0)
{$rate="▲";}
else {$rate="";}

Trend seems to be that you've taken that and made it a calculation?. Neat

`verticalTrend` int DEFAULT NULL COMMENT 'Calculated Vertical Trend -1 = down, 0 = level, 1 = up',

[Oblivian]

Dropped my (mysql) schema 1.10 totally.. 8 years worth *sniff*
Updated to new php/mysql etc.
Reimported recovered dbase from 22-8 (always backup it seems!)

Success. Indexing on target table has continued from flight_id 58959 as I hoped

YUS! At least there is some more live data being captured.

Meanwhile I'll start to create a 1.90 version alongside and try massage the same into it. While making duplicate sqlite file version. Always helps to have multi facet

/edit Spoke too soon.

Something I've configured is dumping into target history as 7 decimal place fractional. So the UTC reporting calculations can't handle it all of a sudden. Theres another to add to my fix list

[Oblivian]

Looks like above issue is change from time storage in mysql 5.4 vs 5.6. Which has happened as part of WAMPserver upgrade.

Seems the older version of mysqladsb drops most tables as unix time. Apart from targethistory. Where it is done as a normal expanded time varchar. yyyy-mm-dd HH:SS.sss. While new writes after 5.6 are in SSSSS.sss fractional (or similar). So unexpected outcome of mysql changing their table storage ways breaks standard timedate queries

Nevermind. Have a new version to target use of. Attempting to export the 8 years data into CSV to import into a 1.9 based schema.

Spent last couple of hours working out how SQLITE handles time calculations differently to mysql to extract a 'last 24hr' query.

SELECT acid, utcdetect, utcfadeout, datetime(utcfadeout/1000, 'unixepoch') as newtime, altitude, callsign, squawk
FROM targethistory
WHERE newtime >= datetime('now','-2 day')
order by newtime desc

The trick was changing the millisecond stamps to unixepoch. And working out the proper datetime() format to use (was use to INTERVAL)

[coupe]

I just noticed the new verticaltrend column
`verticalTrend` int DEFAULT NULL COMMENT 'Calculated Vertical Trend -1 = down, 0 = level, 1 = up',

That probably should be:

`verticalTrend` tinyint(1) NOT NULL DEFAULT '0'

I'll probably change it, as I don't think a NULL is ever written.

[Oblivian]

Don't go too many changes. I won't keep up or be able to import/reuse data!

as long as there has been very few chances in table settings/fields and how they are dumped from the 1.10 and 1.90 versions I have then what I'm doing now should work (crossed everything)

So far have original dbase sitting in stale state as of 22-8. Hopefully this import into the 1.90 table (mysql 5.6) will complete and the new one will continue on adding data to it with the fields same for datetime etc.

importing data by table (to allow column remap) from my 1.10 dbase

Using workbench to dump SQL is so SLOOOWW. When mysqldump can do in seconds. Only missing the headings, but importing target echo now (0200hrs :P ). Target history will be tomorrows task. All up only ~230MB each which isn't bad for 8 years. Metrics was up to 1Gb already after I reset it recently so skipping

If it doesn't start to add data to it nicely and in the same format between versions. Well, guess it'll be back to working out how to split it and open 2 tables and do JOINs to find the new formatting and stitch it to the old.

Of course most fields like ACID and common data from aircraft will be the same. But my timestamp conversions in PHP may need massaging to get it to work. So be it.

[coupe]

OK, I updated ADSBMySQL to long integer dates, getting rid of timestamps. That was a dumb idea anyway. The current version is designed for MySQL 8 which has an added date field when connecting. I changed this to UTC. According to the docs that should work everywhere. Also changed verticalTrend to an integer with default 0, not NULL. Altitude is now NULL rather than -9999.

Tested this with modesdeco2 port 30003 and seems to work. Upped the version number to 1.91.

Not sure if anyone will ever use this, but there you go!

[Oblivian]

I use it. So customerbase level is at least.... 1

Someone else here in NZ use to. But don't think they run it anymore. And it's over the head of most others I've suggested use it it seems.

I just find it better than the likes of Basestation logging alone and so on. Can manipulate it to do and lookup so much more


Not having much luck with transposing the data from old 1.10 dbase to new one. Leave a 200Mb csv dump going all day and hardly 2% complete importing back.
Quite likely the 2Gb ram and lack of CPU (or innodb write enabled) causing it. So have massaged my php pages to work with sqlite db for now, just fear that is more likely to get corrupted or fail vs a mysql with a bit of correction.

It'll do for the forseeable future. But I wouldn't mind being able to do a lookup and have it seamlessly do joins to the mysql 8yrs worth at same time.

Might have to 1:1 clone the setup on this box and see if 16G and 4 cores can do it quicker.