Installation woes

[koliha]

I wanted to provide a little feedback regarding the installation of FlightAirMap.

Setup:

• ESXi Server with barebones Debian (stretch) VM (dual quad xeon, ssd storage, 32gb ram)
• PiAware packages built/installed (for FlightAware)
• Using separate inputs for ADSB and MLAT (not using raw for either)

The installation went pretty well, but there were several dependencies which had to be installed. I think it would be a good idea to set this up on a barebones VM/system and get a full list of dependencies created. Not a showstopper, but a lot of people would have given up at this point.

Running FlightAirMap/install/ works, sort of... It creates the database, appears to populate table structure, and then it gives credential errors (for testing I created a db user account with full root/admin rights and used this credential pair in both the db creation and db user sections of the install.php). It seems that the database part of the web based install is broken.

After running install/, it throws an error (mysql auth), but still shows instructions for update/daemon/etc. The instructions indicate that you should run update_db.php to get models, other data, etc. This works fine, until you get to owner data. I let it sit for 10+ hours with no progress (mytop indicated a sql query was running, cpu @ 100%). Assumed it was a performance issue - updated innodb and other memory/performance settings for mysqld, allocated 8 cores (vs 2), doubled RAM for this VM (to 4gb). 2gb RAM (50%) available for mysql. Started over from scratch (wiped db). Still no love, I stopped it after 7 hours.

In playing around, I found that some owner data was actually populated with populate_all.php. So I started over (wiped the db again) and used this workflow:

1. Hit /install/ from the web, verified settings, checked option to have it create DB
2. Credential error seen (expected)
3. Edited settings.php to indicate that the install state = FALSE
4. Ran install_db.php (imported .sql files and built table structure)
5. Ran populate_all.php
6. Ran edited version of update_db.php (removed section to update owner data)

This got it up and running, but ownership stats didn't seem to be populating. Running the normal version of the update_db.php script just resulted in a message that owner data was only updated every 15 days (or something along those lines - it thought that the ownership data was already up to date). So, I copied the update_db script out and edited it to force the owner update to run. Still stuck in the same place.

Here is a screenshot of the query it's stuck on:

Relevant output of the db_update script: Updating aircraft's owners... owner from FlightAirMap website : Download...Gunzip...Add to DB...Done

I'm just going to leave it running to see what happens. Everything else seems to be working at this point. I'll provide feedback if/when it completes.

If there is something that I'm missing here, please let me know.

[Ysurac]

For dependencies I make some check when install script is launched. I will try to make some "howto" for Debian and Gentoo. Really strange for the MySQL auth error. File require/settings.php was modified with correct user/pass ? I updated scripts/update_db.php, it was a duplicate check that was too slow and not needed anymore...

[koliha]

Thanks for the quick response. I'll update this evening and will report back.

Prerequisites were pretty simple, just had to knock them out one by one -- simple things like php was installed but php-xml and other supporting modules were not. Apache2 config file need to be edited to allow overrides so that .htaccess would work, stuff like that.

Some colleagues on our dev team here have asked me to put together a howto, so maybe I will save you some trouble there. I would really expect that the vast majority of your users are probably going to be installing/running this from debian/raspbian. Setting ADS-B monitoring solutions (or RTLSDR anything) on a Pi seems much more popular than the route I took (virtualization). I ended up moving from CentOS to Debian because of the additional support/documentation for dump1090 on that distro.

Anyway, I'll let you know how it goes.

Thanks again, Rob

[koliha]

I stopped it at the 9hr mark, restarted mysql, and dropped the database.

Pulled with git, noticed that the owner sql file was also modified - opted to start with a clean db as a result. Dropped db, changed installation flag to false.

Same starting step -- hit /install/ from the web. Same error (verified creds in settings file and creds entered on page are correct). DB is created, but nothing else it looks like. No table structure.

Went back, modified settings.php to set installed to FALSE, then ran the install_db.sh. Received a SQL error (which I was seeing before).

ERROR 1071 (42000) at line 1 in file: '/path/to/FlightAirMap/db/faamfr.sql': Specified key was too long; max key length is 767 bytes

Contents of faamfr.sql:

CREATE TABLE `faamfr` ( 
    `faamfr_id` INT NOT NULL AUTO_INCREMENT,
    `icao` VARCHAR(10) NOT NULL,
    `mfr` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`faamfr_id`),
    UNIQUE (`mfr`)
) ENGINE = InnoDB;

Some discussion of this error is here, it mentioned setting it to use utf8. I see that for other tables (using MyISAM) you had specified the default charset as utf8.

I modified the SQL file:

CREATE TABLE `faamfr` ( 
    `faamfr_id` INT NOT NULL AUTO_INCREMENT,
    `icao` VARCHAR(10) NOT NULL,
    `mfr` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`faamfr_id`),
    UNIQUE (`mfr`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

Result: Query OK, 0 rows affected (0.05 sec)

Skipped populate_all.php, because I know that if I run this then the update_db.php script will not actually try to pull owner data (without modification). The update_db.php script ran and pulled owner info.

Output: updating NOTAM...NOTAM from FlightAirMap website : Download...Gunzip...Add to DB...Done Routes from FlightAirMap website : Download...Gunzip...Add to DB...Done Translation : Download...Unzip...Add to DB...Done Translation from FlightAirMap website : Download...Gunzip...Add to DB...Done ModeS from FlightAirMap website : Download...Gunzip...Add to DB...Done Modes OGN: Download...Add to DB...Done Banned airlines in Europe from FlightAirMap website : Download...Add to DB...Done Check if new airspace version exist...Airspace from FlightAirMap website : Download...Gunzip...Add to DB...Done Check if new geoid version exist...Geoid from FlightAirMap website : Download...Gunzip...Done Updating aircraft's owners... owner from FlightAirMap website : Download...Gunzip...Add to DB...Done Updating accidents...Import /var/www/html/FlightAirMap/require/../install/tmp/cr-2017.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2016.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2015.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2014.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2013.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2012.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2011.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2010.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2009.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2008.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2007.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2006.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2005.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2004.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2003.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2002.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2001.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-2000.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-1999.csv Import /path/to/FlightAirMap/require/../install/tmp/cr-1998.csv --[snip]-- updating METAR...Downloading METAR cycle...Done - Updating DB...Done Schedules are only updated every 15 days. Updating statistics and archive old data...Update stats ! Count all aircraft types... Count all airlines... Count all registrations... Count all callsigns... Count all owners... Count all pilots... Count all departure airports... Count all detected departure airports... Order departure airports... Count all arrival airports... Count all detected arrival airports... Order arrival airports... Count all flights by countries... Count fatalities stats... Count all flights by months... Count all military flights by months... Count all owners by months... Count all pilots by months... Count all airlines by months... Count all aircrafts by months... Count all real arrivals by months... Airports data... ...Departure ...Arrival Flights data... -> countAllDatesLastMonth... -> countAllDates... -> countAllHours... --- Stats by airlines --- Count all flights by countries by airlines... Count all aircraft types by airlines... Count all aircraft registrations by airlines... Count all callsigns by airlines... Count all owners by airlines... Count all pilots by airlines... Count all departure airports by airlines... Count all detected departure airports by airlines... Order detected departure airports by airlines... Count all arrival airports by airlines... Count all detected arrival airports by airlines... Order arrival airports by airlines... Count all flights by months by airlines... Count all owners by months by airlines... Count all pilots by months by airlines... Count all aircrafts by months by airlines... Count all real arrivals by months by airlines... ...Departure ...Arrival Flights data... -> countAllDatesLastMonth... -> countAllDates... -> countAllHours... Archive old data... Deleting old data... Insert last stats update date... Deleting archive old data...Deleting archive track old data...Done Update 3D models...Models from FlightAirMap website : Download...Check files... Done Space models from FlightAirMap website : Download...Check files... Done Done

What I didn't see in this script are waypoints, airspace, countries, private owners (listing country by country) - all of this is done in the populate_all.php script.

TL;DR

• Original issue (never ending SQL query related to owners) is fixed
• SQL error seen when creating the db/installing from the web I assume is preventing the tables/db from being built (config file written, db created, then it throws that error)
• One .sql file has an issue (hopefully the fix is above) which prevents the faamfr table from being created
• Manual installation instructions do not mention populate_all.php, which seems to add waypoints, airspace, countries, and private owners (something that the install_db.php and update_db.php script do not do).

The web error is probably an easy fix. The routines in populate_all.php should probably be added to the install_db.sh script, so the basics can be setup with a single command. The issue I saw with this was that owner data is skipped by the update_db.php script if you run the populate_all.php script first. That will have to be addressed or it would basically create a 15 day lag before the first owner db update would come through.

With the current version, outside of patching the .sql file and pre-reqs, I believe the best installation path is probably going to be the manual route, but it poses a real problem when you ask people to build php arrays for ports and source names :)

So the easiest route for me to install with the current version:

1. git pull
2. change permissions (chmod 666) cache install/tmp images/airlines require/settings.php
3. update faamfr sql file (probably won't be needed by the time someone reads this)
4. Access /install/ from the web, configure everything there where it's easy
5. Edit require/settings.php and set $globalinstalled to FALSE
6. Run install_db.sh
7. Run update_db.php
8. Run populate_all.php
9. Install the init/flightairmap.init script (not sure what the other init files are there for?)
10. Edit require/settings.php and set $globalinstalled to TRUE
11. Start the service you just setup (via init)

[koliha]

Also, security wise.. What needs write access here? I'm assuming that since you can't really modify the settings from the webUI (without editing the settings.php file), that the settings.php file could go back to 600. Same for install/tmp I guess. Any others?

[koliha]

Systemd setup for the daemon/service...

• cp [path to flightairmap]/install/init/flightairmap/flightairmap.service.systemd /etc/systemd/flightairmap.service
• chmod 644 /etc/systemd/flightairmap.service
• nano /etc/systemd/flightairmap.service (edit path!)
• systemctl daemon-reload
• systemctl enable flightairmap.service (to enable on boot)
• systemctl start flightairmap.service (to start)

[koliha]

Ok, so that added everything and set it up, but it dies at startup:

Jul  6 19:46:49 taco daemon-spotter.php[2431]: \o/ Add  from sbs in Live DB : -- Delete previous data -- (Not adding to archive)success
Jul  6 19:47:22 taco systemd[1]: Started Regular background program processing daemon.
Jul  6 19:47:22 taco systemd[1]: Starting LSB: dump1090 daemon (mutability variant)...
Jul  6 19:47:22 taco daemon-spotter.php[653]: Error connecting to DB: flightair - Error: SQLSTATE[HY000] [2002] No such file or directory
Jul  6 19:47:22 taco daemon-spotter.php[653]: Error connecting to DB: flightair - Error: SQLSTATE[HY000] [2002] No such file or directory
Jul  6 19:47:22 taco daemon-spotter.php[653]: Error connecting to DB: flightair - Error: SQLSTATE[HY000] [2002] No such file or directory
Jul  6 19:47:22 taco daemon-spotter.php[653]: Error connecting to DB: flightair - Error: SQLSTATE[HY000] [2002] No such file or directory
Jul  6 19:47:22 taco daemon-spotter.php[653]: Error connecting to DB: flightair - Error: SQLSTATE[HY000] [2002] No such file or directory
Jul  6 19:47:22 taco daemon-spotter.php[653]: Error connecting to DB: flightair - Error: SQLSTATE[HY000] [2002] No such file or directory
Jul  6 19:47:22 taco daemon-spotter.php[653]: You MUST update to latest schema. Run install/index.php

Starting the service manually works, but it seems that the systemd service file needs a minor edit. I tried to add After=mysqld.service, but no love there. I ended up going with a 30 second startup delay via ExecStartPre.

Here is the new systemd service file:

[Unit]
Description=FlightAirMap

[Service]
ExecStartPre=/bin/sleep 30
ExecStart=/path/to/FlightAirMap/scripts/daemon-spotter.php

[Install]
WantedBy=multi-user.target

[Ysurac]

For install script, can you check your MySQL/MariaDB log ? I don't understand how I can get an access denied here...

Fixed faam in latest commit. You should never run populate_db.php or any other script than index.php in install directory.

install/tmp is used by update_db.php script (when updating owners, notam, accidents, modes,...). data directory should also have write permission, it's used to store file used for geoid calculation and altitude (I will add a check in install script for that).

It launch before DB... Need to check how I can fix that... I really don't like and don't use systemd.

[Ysurac]

Systemd init script are update in latest commit, maybe it's better.

[koliha]

The changes that you made overnight have resolved the db errors when using /install/ from the web.

I made the data directory rw, everything else was ok install/tmp is ok I think because the update script is launched as root. I was also able to chmod the settings.php file to 600 after I was done and everything worked as expected.

The only thing I've noticed is that now all the aircraft show an error relating to GeoID:

I tried forcing a geoid update with: $update_db->update_geoid_fam(); $update_db->insert_last_geoid_update();

Output: Geoid from FlightAirMap website : Download...Done

Still seeing the error from the web UI though.

I'll try the updated systemd script later tonight. The issue I ran into was that it seemed to completely ignore after= and requires= in the systemd script. No idea why (which is why I did the 30s delay). I'll see if your updated script works, and if not I will likely add the restartalways/restartsec settings without a delay (if it dies once at startup and then waits 10 seconds, mysql should be running at that point). The other option I guess would be to add some kind of logic in the daemon to wait xxx seconds between attempts to connect to the SQL server (giving it time to startup). It appears to make several connection attempts, fails, and just exits at the moment. Editing the daemon may be easier than the nuances of various init systems :)

Thanks for your work on this. Let me know if I can gather any data or additional info regarding geoid.

[Ysurac]

Check that egm96-15.pgm is in data directory. Last update will not display this message here.

The daemon should wait 5s between failures and try that 6 times (or $globalDBretry value)

[koliha]

$globalGeoidSource = 'egm2008-2_5';

root@taco:/path/to/FlightAirMap# ls data -aml total 72944 drw-rw-rw- 2 root root 4096 Jul 5 13:35 . drwxr-xr-x 16 root root 20480 Jul 7 10:17 .. -rw-r--r-- 1 root root 74667284 Jul 6 18:14 egm2008-2_5.pgm

Changed back to egm96-15, ran update_db script - it didn't download anything. Modified version of script (forcing a download+update) worked fine to install this though.

$update_db->update_geoid_fam();
$update_db->insert_last_geoid_update();

Same issue, even with the other geoid files.

I looked through the apache2 error log and caught this one: libpng warning: iCCP: known incorrect sRGB profile

It looks like the profile information just needs to be stripped from images: https://wiki.archlinux.org/index.php/Libpng_errors

Can you explain the cache directory? If I click a new aircraft, image loads and no errors from apache2. If I click an aircraft that I've clicked previously, I get: PHP Warning: imagepng(/path/to/FlightAirMap/cache/6900ea-B73X.png): failed to open stream: Permission denied

[Ysurac]

Geoid error will display a better error if needed in latest commit.

I will strip all pics, so error will be gone.

The cache directory is to store pic with a custom color instead of generating them each time. Should be readable, strange. I added a test to check if it's readable.

[koliha]

GeoID error is gone with the latest commit, even using the other EGM file that I was trying to use originally :)

cache folder is r+w to all: drw-rw-rw- 2 root root 4096 Jul 5 13:20 cache

[Fri Jul 07 11:16:33.512919 2017] [:error] [pid 70438] [client 107.77.94.18:14058] PHP Warning: imagepng(/path/to/FlightAirMap/cache/FF0000-A320.png): failed to open stream: Permission denied in /path/to/FlightAirMap/getImages.php on line 92, referer: http://site.com/FlightAirMap/

I used "watch -n1 ls -aml" and accessed the site (inducing the errors). There doesn't appear to be any activity in the cache folder (always remains empty).

Maybe this is also related? No airline images -- all placeholders that point to: http://site.com/FlightAirMap/images/airlines/placeholder.png

These images are shown on the statistics - most common airline page.

[koliha]

Also, is any data meant to populate on these two pages: /owner /airport

Those pages are blank, nothing shown in "upcoming flights"

Not sure if I need to wait for more data to populate? ~1300 logged flights so far.

[Ysurac]

The permission denied is when I write the pic in cache directory. Strange because I check if data directory is writable, I should never get a permission denied...

Really no idea. You can put data directory to read only, this should disable the cache.

owner and airport table should not be blank. You have data in airport and aircraft_owner tables ? upcoming flights need many flights on more than a week to calculate them.

[koliha]

Errors aren't a huge issue, going to leave it r+w and dig in deeper on the cache issue later tonight.

airport table: 41655 rows in set (0.14 sec)

aircraft_owner: 521140 rows in set (1.51 sec)

[koliha]

Maybe those pages have some php error? If you view source on them, there are no closing tags/etc -- html generation seems incomplete.

IE:

  	<div class="social">
  		<!-- I'm not sociable -->
  	</div>
    </div><!--/.nav-collapse -->
  </div>
</div>


<section class="container main-content clear">
<div class="column"><h1>Owners</h1><div class="alphabet-legend">

[Ysurac]

Fixed airline table for xpressair, else you can run this SQL to fix:

update airlines set icao = 'XAR' where name='xpressair';

Yes I saw that pages are not complete. Can you check if you have something in logs ?

[Ysurac]

And you should get alliances logo on airlines page. Strange...

[koliha]

I think I'm going to create a new folder, do a new pull and start from scratch to see how it goes. Wish me luck :)

[koliha]

Clean folder, new pull, left default permissions so it could prompt me to change. Prompted for settings and tmp permissions, changed. Now it just hangs at the /install/ url until Chrome/whatever comes back with

This page isn’t working

gso.mooo.com took too long to respond.
HTTP ERROR 504

apache error log is throwing this message over and over until the page times out:

[Fri Jul 07 12:36:18.789301 2017] [:error] [pid 75887] [client 199.72.62.66:51367] PHP Fatal error: Uncaught Error: Call to a member function prepare() on null in /some/path/FlightAirMap/require/class.Source.php:68\nStack trace:\n#0 /some/path/FlightAirMap/location-geojson.php(34): Source->getLocationInfoByType('gs')\n#1 {main}\n thrown in /some/path/FlightAirMap/require/class.Source.php on line 68, referer: http://site.com/FlightAirMap/

[Ysurac]

It's a connection to DB problem. I think it's always the same problem since install. Are you using MySQL or MariaDB ? which version ?

[Ysurac]

What is your max_connections in the /etc/mysql/my.cnf (if it's set) ?

[Ysurac]

Last commit should fix infinite loop when DB is not available.

[koliha]

It's MariaDB, latest version of Debian, latest update.

Here are relevant package details from apt: mysql-common/stable,now 5.8+1.0.2 all [installed,automatic] mysql-server/stable,now 5.5.9999+default amd64 [installed] php7.0-mysql/stable,now 7.0.19-1 amd64 [installed] apache2/stable,now 2.4.25-3+deb9u1 amd64 [installed,automatic]

Install page (from the web) behaves the same (timeout). Nothing in apache2 error logs now though.

[koliha]

Another thought... I'm using a blank settings.php file (totally fresh install). Have not been able to pull up the installer page (which prompts for db name and creds).

// DATABASE CONNECTION LOGIN
$globalDBdriver = 'mysql'; // PDO driver used. Tested with mysql, maybe pgsql or others work...
$globalDBhost = 'localhost'; //database connection url
$globalDBuser = ''; //database username
$globalDBpass = ''; //database password
$globalDBname = ''; //database name
$globalDBport = '3306'; //database port
$globalTransaction = TRUE; //Activate database transaction support

[Ysurac]

I think there is a problem on your server now. Restart php-fpm (if using that) and apache.

[koliha]

Rebooted, same issue. Restored old db and files (where it was all working before), even with the DB properly configured the install page just hangs. With nothing in the settings, this behavior is the same for main page of the site as well (/FlightAirMap/).

[koliha]

If I run the install/index.php file from shell, it shows:

                <fieldset id="sourceloc">
                        <legend>Sources location</legend>
                        <table class="sources">
                                <tr>
                                        <th>Name</th>
                                        <th>Latitude</th>
                                        <th>Longitude</th>
                                        <th>Altitude</th>
                                        <th>City</th>
                                        <th>Country</th>
                                        <th>Source name</th>
                                </tr>
                                <!--
                Error connecting to DB:  - Error: SQLSTATE[HY000] [1045] Access denied for user ''@'localhost' (using password: NO)

The SQL error is repeated several times, and then the rest of the HTML is generated. The delay is so long though, that the web client times out before the page can be delivered. Maybe a loop to see if the username and dbname fields are filled or not? If present = try to connect, if can't connect, throw an error onscreen and die. If not present = skip attempt to connect and just display the page.

[Ysurac]

I should never work when it's too hot... It's fixed in latest commit.