NZACARS - MySQL and ACARSD

Adding a databse to my acarsd system was no easy task! Following the instructions that come with
acarsd was pretty hopeless but having a background of dealing with databases and in particular
Micosoft SQL I decided to start off on my own with limited reference to the acarsd help files.

For all the actions below I will assume that acarsd in NOT running.

Obviously the first thing to do is to download MySQL (http://dev.mysql.com/downloads/) and install a
default instance with a root user and set MySQL to start as a Windows Service. The next thing to do
was to download the MySQL GUI tools as I hate command line interaction at the best of times, so
grab these from http://dev.mysql.com/downloads/gui-tools/5.0.html.

The next step is to create a database called acars using MySQL Administrator. So under Schemata,
right click and Create New Schema and call it acars.

Now create a user called acarsd and assign all privileges for the schema acars to new user acarsd.

In the acarsd programme root folder is a file called mysql.dat which has a couple of errors in it where
the default date and time values should be set for your locale. In the case of mine I've set the
default time as HH:mm:ss and the date as YYYY-mm-dd, so depending on your local settings open
mysql.dat up with a text editor and edit the CREATE TABLE statement like this:

CREATE TABLE acars (
card tinyint(2) default 0,
channel char(1) default '-',
link enum('U','D') DEFAULT 'D' NOT NULL,
time time DEFAULT '00:00:00' NOT NULL,
date date DEFAULT '0000-00-00' NOT NULL,
reg varchar(7) DEFAULT '' NOT NULL,
iata char(2) DEFAULT '' NOT NULL,
mode char(1) DEFAULT '' NOT NULL,
label char(2) DEFAULT '' NOT NULL,
blockid char(1) DEFAULT '' NOT NULL,
msgno varchar(4) DEFAULT '' NOT NULL,
flight varchar(6) DEFAULT '' NOT NULL,
routing varchar(32) DEFAULT '',
message text,
PRIMARY KEY (time,date,reg,link,blockid,flight));

Start MySQL Query Browser, logging in as acarsd with the default schema of acars and paste the
CREAT TABLE statement into the top window and execute it. That's the table created so we can now
go to acarsd and configure the acarsd.ini file for the MySQL settings.

These are the settings you should make in the .ini file:

[MySQL Section]

# To create the database and all needed tables, use the following command:
# mysql < mysql.dat

# You can save your contacts to an MySQL database if you wish.
# Please add here the hostname where your MySQL server is running.
MysqlHost localhost

# On which port listens your MySQL server? Default is: 3306
# DONT CHANGE THIS IF YOU DONT KNOW WHAT YOU'RE DOING
MysqlPort 3306

# Please define a username which has access to the ACARS database on your MySQL server
MysqlUser acarsd

# Please enter the password for the database user
MysqlPass acarsd_password

# Please enter the name of your database (default: ACARS)
MysqlDBName acars

# If you enable this switch, acarsd will create the default table for you
# Later you can change the table definitions with the Mysql Interface (see the Database Menu)
#MysqlCreateTab

Save the acarsd.ini file. Now check that the acarsdgui.ini settings for the INSERT INTO statement
match the fields created in the CREATE TABLE statement earlier. Open up acarsdgui.ini and find the
following lines:

MysqlInsert INSERT INTO acars (card,link,time,date,reg,msgno,flight) VALUES
('%%Card%%','%%Link%%',%%Time%%,%%Date%%,'%%Reg%%','%%MsgNo%%','%%Flight%%');

In our case they do not match so we need to amend the acarsdgui.ini settings by adding the
additional fields and placeholders. The avialable placeholders are:

Card numeric Number of soundcard. First soundcard = 0
ChannelName string Name of channel
Link string 1 Char! (D=Downlink U=Uplink)
Time numeric Time of day (HHMM)
Date numeric Date (YYYYMMDD)
Reg string Aircraft registration
IATA string Airline IATA code from flightnumber or database
Mode string ACARS Mode (1 char)
ICAO string Airline ICAO code from database
Label string Message Label
BlockID string BlockID (1 char)
MsgNo string Message Number
Flight string Flightnumber
Route string Routing (taken from database)
Message string Message content
Manufacturer string Aircraft Manufacturer
AcShort string Aircraft short desc (A320, B735)
AcFull string Aircraft full desc
Cn string Aircraft Construction Number
Hostname string Hostname of this acarsd System
Timestamp numeric Timemask (Seconds since 01.01.1970)
Airline string Airline (taken from database)
Day numeric Day (1-31)
Month numeric Month (1-12)
Year numeric Year
Hour numeric Hour (00-23)
Minute numeric Minute (00-59)
Second numeric Second (00-59)
Monthname string Monthname
Squitter numeric Squitter (1=yes, 0=no)
CRCOK numeric CRCOK (1=yes, 0=no)
Translated numeric Entry was translated (1=yes, 0=no)
Groundstation string Name of used Groundstation
OnChannel numeric Channelnumber
OrigFlight string Original flightnumber before translation
OrigReg string Original registration before translation
LabelText string Label Description

So amend the statement like this:

MysqlInsert INSERT INTO acars (card,link,time,date,reg,iata,mode,label,blockid,msgno,flight,routing)
VALUES
('%%Card%%','%%Link%%',%%Time%%,%%Date%%,'%%Reg%%','%%IATA%%','%%Mode%%','%%L
abel%%','%%BlockID%%','%%MsgNo%%','%%Flight%%','%%Route%%');

Save the acarsdgui.ini file.

The main causes of getting the dreaded "No MySQL settings defined" are:

Your MySQL server is not running,
You have no acars database or table or no acarsd user
Your .ini settings are not set properly.

If all steps above are followed then all should be good when you start acarsd, however, the main
point of failure hereon in is your ability to type properly!! Your INSERT statement in the acarsdgui.ini
file will probably let you down unless the placeholders (which are case sensitive) are correct and the
fields match thos in your acars table.

Start acarsd and see how you get on. Drop me an email if you have any problems.