Jump to Table of Contents

SCOPE

The Australian Speleological Federation's Karst Index Database (the KID) is a database of several thousand Australian caves available via the World Wide Web at http://www.caves.org.au. This document is a guide to the installation of the ASF's Karst Index Database software on a Debian/GNU Linux server. See the Maintenance Guide for how to maintain and run a KID system after it is installed. This document does not cover how to use the KID as a user.

This document is written for a System Administrator. Knowledge and experience in basic UNIX system administration, including Apache configuration and MySQL database administration, is required to understand this document and the ASF's KID.

The code developed by the ASF for the KID software is open source and released under the GNU Public Licence. The ASF has also used open source code from many sources. Most of this code is available under the GPL, the exception being the PDFLib Lite library which is used to create PDF's. This is open source and available from http://www.pdflib.com but is released under their own licence which allows it to be used for free under certain conditions.

AUTHORS

Original Author: Rick Welykochy (rick@praxis.com.au) 2000-11-29
Changes by: Mike Lake (mikeL@speleonics.com.au)

REFERENCED FILES

The following documents/files are referenced by this document.

CONTENTS

Requirements

Conventions

Overview of the KID Installation

1. Create User "kid"
2. Install KID Software into /home/kid/asf-kid/
3. Install the Apache mod_auth_mysql Module
4. Install the Apache mod_perl Module

5. Install Required Perl Modules

6. Edit the httpd.conf File
7. Ensure that mod_perl is Running Properly
8. Edit asf-kid/lib/KID/DB.pm and put in 'the mysql www password'
9. Create a New User 'www' in the MySQL Database
10. Create an auto-login Config File for MySQL Access in the Shell
11. Create the KID Database

1. Setting the Root Password for MySQL
2. Problems with Mod Auth MySQL

top

REQUIREMENTS

The following is a summary of the recommended requirements for installing the ASF KID.

Use a Debian/GNU Linux Distribution: The ASF's KID is run on a virtual server (x86 architecture) running a Debian/GNU Linux distibution. The latest 'stable' version is recommended. The KID would work using a RedHat Fedora or Enterprise Linux but many of the Debian packages refered to in this installation guide will have different names and finding the equivalent RPM package for RedHat may take some time. There may also be different versions of libaries linked to between the Debian and RedHat packages. Nevertheless where known I have suggested possible RPM packgages that may be an equivalent to a Debian package.

Use Apache 1.3 not Apache 2.0: The version of Apache is 1.3. At this stage there are problems with the many perl modules and mod_perl when used with Apache 2.0 so using 1.3 is strongly advised.

Use MySQL 5: The database used is MySQL 5. There will be some differences if you use a later MySQL but we would not expect there to be any unsolvable problems.

top

CONVENTIONS

In this guide the following conventions are used.

Anything that is typed by the user in a terminal is shown in a yellow box like this:

Descriptions of files are shown like this:

top

OVERVIEW OF THE KID INSTALLATION

The following is a short overview of the technical aspects of the KID to help you understand what you are doing in the during the KID installation.

In this installation we presume that you are installing the KID software from a UNIX tarball obtained from the ASF and that the server you will be installing on is 'new' i.e. it is not yet setup and configured to run the KID software.

In steps 1 to 2 we create the UNIX user "kid" and install the KID software.

When a user views a page in the Web-based KID the web server (Apache 1.3) needs to verify the user by checking their password. Allowed user names and passwords are stored in a MySQL database. So that the web server can access this database we install an Apache module called "mod_auth_mysql" This is done in step 3.

The KID code is written in Perl. There are thousands of modules freely available for Perl that provide us with well written and well tested code for doing things like encryption, database access, HTML parsing and formatting and PDF creation. We need to do these things in the KID and thus we install quite a few Perl modules in step 4 and 5.

In steps 6 and 7 we setup the Apache web server configuration for the KID and test it with a small script to make sure that it all works at this point. At this stage there is no database access involved. Note that we must use Apache 1.3. Apache2 with mod_perl has problems as some perl modules are not yet ported to Apache2. So install Apache 1.3 and not Apache2.

In steps 8 to 10 we setup the KID software so that the Perl scripts can query the MySQL database.

Finally in step 11 we load all the tables that make up the KID into MySQL.

1. Create User "kid"

You will need to create a UNIX user called "kid". Login as root and create the user as follows.

# adduser kid
Adding user kid...
Adding new group kid (1001).
Adding new user kid (1001) with group kid.
Creating home directory /home/kid.
Copying files from /etc/skel
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
Changing the user information for kid
Enter the new value, or press return for the default
        Full Name []: kid
        Room Number []: 
        Work Phone []: 
        Home Phone []: 
        Other []: 
Is the information correct? [y/n] y

# 

Now logout as root and login as kid. Edit .bashrc and add an alias rm='rm -i' for some safety.

For the kid user just created, add the following lines to the end of their .bash_profile file. This file would have been created when the user was created above.

export ASFBASEURL=/kid
export ASFDIR=/home/kid/asf-kid
export ASFISDEV=0
export ASFHTTPPORT=80
export ASFDATABASENAME=kid

# ASF KID environment
ASFBEGIN=~/asf-kid/bin/begin
if [ -x $ASFBEGIN ]; then $ASFBEGIN; fi

The purpose of the above lines is setup an ASF KID environment and to run the script "begin" whenever you login as user "kid". This ASF KID environment is required for many of the scripts in the KID system and must be established before running an ASF KID application.

These environment variables are:

Whenever you login the begin script should produce the following output:

***************************************************************************
*
*    Entering the ASF KID shell                                                  
*                                                  
*               ASFDIR:  /home/kid/asf-kid
*             ASFISDEV:  0
*           ASFBASEURL:  /kid 
*          ASFHTTPPORT:  80
*      ASFDATABASENAME:  kid
*                                  
*    Copyright (C) 2003-2004  Australian Speleological Federation
*
***************************************************************************

asf-kid$

2. Install KID Software into /home/kid/asf-kid

The ASF KID should be installed into the root of the home directory of the user "kid" that you created above.
Untar the asf-kid-software-vX.YZ.tar.gz file you have downloaded, from inside the root of the home directory /home/kid/ (X.YZ is the version number).

$ cd /home/kid

$ tar xzf asf-kid-software-vX.YZ.tar.gz  (X.YZ is the version number)
The asf-kid directories should look like the following:
$ ls -l /home/kid/asf-kid/

drwxr-xr-x    5 kid    users        4096 Oct  4 17:12 bin
drwxr-xr-x    2 kid    users        4096 Oct  4 17:12 doc
drwxr-xr-x    9 kid    users        4096 Oct  4 17:12 html
drwxr-xr-x    5 kid    users        4096 Oct  4 17:12 lib
drwxr-xr-x    6 kid    users        4096 Oct  4 17:12 model

3. Install the Apache mod_auth_mysql Module

The KID uses mod_auth_mysql to link user web authentication directly to the MySQL user tables.

There are two options for installing this package; install via a pre-compiled package supplied by your Linux distribution or build and install from source. Using a package is recommmended. Building from source though is not difficult and is very straightforward.

Install from a Package: The Debian package for Apache 1.3 is libapache-mod-auth-mysql - 'Apache module for MySQL authentication'.

(RedHat package mod_auth_mysql-3.0.0-3.1.i386.rpm or similar version may be an equivalent. )

Building from Source: Because the module mod_auth_mysql isn't a Perl module but a c program if you wish to build it from source you will require the gcc compiler, MySQL development files (for mysql.h), the zlib development files, and the Apache Development Kit. Installation details for this can be found here: Module Auth MySQL Guide. Otherwise install from a package.

Note 1: There is also the package libapache2-mod-auth-mysql - 'Apache 2 module for MySQL authentication'. This is for Apache 2 which is not recommended at his stage because of problems with mod_perl.

Note 2: You may wish to look at the Module Auth MySQL Guide and read the section on "Problems" as the problems covered there may occur for binary installed packages.

4. Install the Apache mod_perl Module

This module integrates the perl language interpreter with the Apache web server. It's a package consisting of a compiled object file mod_perl.so which is loaded as a module by Apache and numerous Perl modules which get installed into ypur Perl distribution.

There are a few options for installing this. The easiest is to use an already compiled package for your Linux distribution. Here we use the Debian package libapache-mod-perl which supplies the mod_perl binary and the perl modules that accompany it for installation with the standard Apache. If your really keen you can get the apache source and the mod_perl source and re-compile apache with mod_perl.

(RedHat package mod_perl-1.24_01-2.1.i386.rpm or similar version may be an equivalent.)

(There is also a Debian package apache-perl that has mod_perl statically compiled in. If you use this package you do not need libapache-mod-perl. I suggest that you use the standard apache package with libapache-mod-perl.)

Details of the Debian package libapache-mod-perl are shown below.

$ apt-cache show libapache-mod-perl
Package: libapache-mod-perl
Section: perl
Installed-Size: 1268
Maintainer: Debian Apache Maintainers 
Architecture: i386
Source: apache (1.3.34-4)
Version: 1.29.0.4-4
Depends: apache-common (>= 1.3.34-4), apache-common (<< 1.3.35), perl (>= 5.8.4-2), libwww-perl, 
libmime-base64-perl, libdevel-symdump-perl, liburi-perl, libc6 (>= 2.3.6-6), libperl5.8 (>= 5.8.8)
Suggests: apache-dev (>= 1.3.34-4), libapache-mod-perl-doc
Filename: pool/main/a/apache/libapache-mod-perl_1.29.0.4-4_i386.deb
Size: 485744
Description: integration of perl with the Apache web server mod_perl allows the use of Perl for 
  just about anything Apache-related, including  sections in the config files and the 
  famous Apache::Registry module for caching compiled scripts.

  It can produce anywhere from a 400% to 2000% speed increase on sites using perl scripts, 
  and is used on many large script-based web sites.

Install the package:

$ sudo apt-get install libapache-mod-perl
Reading package lists... Done
Building dependency tree... Done
Suggested packages:
  apache-dev libapache-mod-perl-doc
The following NEW packages will be installed:
  libapache-mod-perl
  0 upgraded, 1 newly installed, 0 to remove and 41 not upgraded.
Need to get 486kB of archives.
After unpacking 1298kB of additional disk space will be used.
  Get:1 http://ftp.iinet.net.au etch/main libapache-mod-perl 1.29.0.4-4 [486kB]
  Fetched 486kB in 2s (184kB/s)              
  Selecting previously deselected package libapache-mod-perl.
  (Reading database ... 113824 files and directories currently installed.)
  Unpacking libapache-mod-perl (from .../libapache-mod-perl_1.29.0.4-4_i386.deb) ...
  Setting up libapache-mod-perl (1.29.0.4-4) ...
  Replacing config file /etc/apache/modules.conf with new version
$ 

Note: Apache2 is not yet recommended but if you do use it the mod_perl package for this is libapache2-mod-perl2 - 'Integration of perl with the Apache2 web server'.

5. Build & Install Required Perl Modules

Several PERL modules are required for the KID. They have to be installed at this stage before we try and setup the web server. A list of the Perl Modules and installation details for them can be found here: Perl Modules Guide

After you have installed these return to here.

6. Edit the httpd.conf File

There are lots of changes and additions to make in the /etc/apache/httpd.conf file. They are detailed below in the order that they appear in httpd.conf with some explanations as to why the changes are made and what the additions are for. Don't forget to comment your changes in the actual httpd.conf file.

We have already compiled and installed the mod_auth_mysql module but now we have to modify httpd.conf so that it loads the module. Edit httpd.conf and insert the lines shown below as additional lines at the end of the Load Module section.

# Dynamic Shared Object (DSO) Support
#
# To be able to use the functionality of a module which was built as a DSO you
# have to place corresponding `LoadModule' lines at this location so the
# directives contained in it are actually available _before_ they are used.
.....
# LoadModule php4_module /usr/lib/apache/1.3/libphp4.so
# LoadModule roaming_module /usr/lib/apache/1.3/mod_roaming.so
# MRL: added mysql_auth_module for KID
LoadModule mysql_auth_module /usr/lib/apache/1.3/mod_auth_mysql.so

Comment out the following as we won't be using this.

# If the perl module is installed, this will be enabled.
#<IfModule mod_perl.c>
#  Alias /perl/ /var/www/perl/
#  <Location /perl>
#    SetHandler perl-script
#    PerlHandler Apache::Registry
#    Options +ExecCGI
#  </Location>
#</IfModule>

Alias the /kid location to /home/kid/asf-kid/html

Let's answer an important question:

Q:  How does the web server find the asf-kid scripts when some asks for http://www.caves.org.au/kid on the server?

A:  By asking for the subdirectory kid, which is aliased by the web server to the kid/asf-kid project directory i.e /home/caves/kid --> /home/kid/asf-kid/html

Edit /etc/apache/http.conf and set the Alias directive to that below.

Alias /kid /home/kid/asf-kid/html

PerlSetEnv ASFBASEURL        /kid
PerlSetEnv ASFDIR            /home/kid/asf-kid
PerlSetEnv ASFISDEV          0
PerlSetEnv ASFHTTPPORT       80
PerlSetEnv ASFDATABASENAME   kid
PerlSetEnv PERL5LIB          /home/kid/asf-kid/lib

The URL "http://www.caves/kid" will now be automatically mapped by the web server to the directory "/home/kid/asf-kid/html".

The Perl env variable PERL5LIB which is a special env variable that lets Perl know where there are extra libraries such as the ones written for the KID. Without this the executable Perl scripts would have to have the library paths hard coded into them.

##########################################
#                                        #
#   Beginning of KID Location Settings   #
#                                        #
##########################################

# We refer to <Location> rather than <Directory> conceptually easier that way!

<Location />
	Options Indexes FollowSymLinks
	AllowOverride All
</Location>  

# Eschew the user of .htaccess files: instead, configure subdirectories here
<Location /kid>
	Options FollowSymLinks
	AllowOverride All

	SetHandler perl-script
	PerlHandler Apache::Registry
	PerlSendHeader On
	Options +ExecCGI

	DirectoryIndex index
</Location>

# /kid/media: not perl
<Location /kid/media>
	SetHandler none
	Options Indexes
	DirectoryIndex index  
</Location>

# /kid/template: not accessible
<Location /kid/template>
	Order deny,allow
	Deny from all
</Location>

# /kid/users: password required
<Location /kid/users>
	AuthName "ASF KID Web Administration"
	AuthType Basic
	AuthGroupFile /dev/null
	AuthMySQLCryptedPasswords On
	AuthMySQLHost localhost
	AuthMySQLUser www
	AuthMySQLPassword the_mysql_www_password
	AuthMySQLDB kid
	AuthMySQLUserTable sys_user
	AuthMySQLNameField username
	AuthMySQLPasswordField password

	<Limit GET POST>
	require valid-user
	</Limit>
</Location>

# /kid/users/admin: KID::AdminCheck is invoked for strict access checking
#                   note that password check is inherited from <Location /kid/users> above
<Location /kid/users/admin>
	PerlHandler KID::AdminCheck Apache::Registry
</Location>

# /kid/users/admin/doc: not perl
<Location /kid/users/admin/doc>
	SetHandler none
</Location>

# /kid/users/admin/ttemplate/template: not perl
<Location /kid/users/admin/ttemplate/template>
	SetHandler none
</Location>

####################################
#                                  #
#   End of KID Location Settings   #
#                                  #
####################################

Restart your web server

# apachectl configtest

Syntax OK

# apachectl restart

#

There should be no errors when you restarted the web server above.

# apachectl configtest
Syntax error on line 18 of /etc/apache/kiddev-httpd.conf:
Invalid command 'PerlSetEnv', perhaps mis-spelled or defined by a module not included in the server configuration
# 

then mod_perl is not installed or is not working.

top

7. Ensure that mod_perl is Running Properly

Now that the perl modules and mod_auth_mysql is installed and the web server's httpd.conf file is edited we should check that mod_perl is running fine

In the directory asf-kid/html there should be a file called "test". It should be executable with permissions:

-rwxr-xr-x    1 kid      kid           256 Dec 14 20:35 test

If it's not executable make it so with: "chmod +x test".

The file should have the following contents:

File: asf-kid/html/test

#!/usr/bin/perl
 
use strict;
use warnings;
use KID;

print qq{Content-type: text/html\r\n
<html>
	<head><title>ASF KID Test</title></head>
	<body>
	<h1>ASF-KID Test</h1>
	ASF-KID Version $KID::VERSION<br>
	Mod perl is working. 
	</body>
</html>
};         

Run it in the shell

Change to the asf-kid/html directory and run the file.

asf-kid/html$ ./test

Content-type: text/html

<html>
	<head><title>ASF KID Test</title></head>
	<body>
	<h1>ASF-KID Test</h1>
	ASF-KID Version <br>
	Mod perl is working. 
	</body>
</html>
asf-kid/html$ 

Run it from a web browser

Open the following location in your web browser:  http://localhost/kid/test
The test file should be interpreted by Apache as a Perl script and it should run, producing a HTML page as shown below.

ASF-KID Test
ASF-KID Version 1.0
Mod perl is working. 

8. Edit asf-kid/lib/KID/DB.pm and put in "the_mysql_www_password"

The KID scripts will authenticate themselves to the MySQL database using a username and password. This username is "www" and the password will be called "the_mysql_www_password". You will need to choose a password to use.

It needs to be inserted into the file asf-kid/lib/KID/DB.pm so that the scripts can supply the username and password to MySQL and into the MySQL database "mysql" so that MySQL can check that the script is supplying the correct credentials.

Here we insert it into DB.pm. Notice that you must have single quotes around the username and password values as these are strings.

Edit asf-kid/lib/KID/DB.pm

if (defined $ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ /cgi/i)
{
	$username = 'www';
	$password = 'the_mysql_www_password';  # obviously pick something sensible
}

Next we insert it into the mysql database.

9. Create a New User "www" in the MySQL Database

In the above section we added user "www" and "the_mysql_www_password" to DB.pm and now we have to create a new MySQL user called "www" in your "mysql" database. Note: this is not the KID database but MySQL's own mysql database that manages the security aspects of the system. To be more specific; we will be inserting the "www" user into the user table within the mysql database.

mysql> show databases;
+-----------+
| Database  |
+-----------+
| kid       |
| kiddev    |
| mysql     | <-- this database implements security for all the databases.
+-----------+

Below is the SQL script that will be sourced to create the user. Note the there needs to be single quotes surrounding the password.

use mysql;
insert into user values('localhost','www',password('the_mysql_www_password'),
'Y','Y','Y','Y',
'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',
'','','','',
'0','0','0','0');

To run this script you will have to login as the MySQL root user, using the MySQL root password. By specifying just "-p" in the command below MySQL will prompt us for the root password. This is better than entering on the command line (i.e. like -pthe_mysql_root_password) as there is a small chance that it can be sniffed whilst the command is being run. The final "mysql" specifies that we will be using the main mysql database.

Change to the asf-kid/model/init directory as this is where the SQL script is that will add the "www" user.

asf-kid/doc$ mysql --user=root -p the_mysql_root_password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54 to server version: 4.02.23
 
Type 'help' for help.

mysql>

Before we source the SQL script, to add the www user, we can look at the table that it will update. The user table specifies the access privileges of the users that can access the database. I have highlighted in color and at the right of the table the values that the script will change from the default.

mysql> desc user;

+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60)                       |      | PRI |         |       | localhost
| User                  | varchar(16)                       |      | PRI |         |       | www
| Password              | varchar(41)                       |      |     |         |       | encrypted password
| Select_priv           | enum('N','Y')                     |      |     | N       |       | Y
| Insert_priv           | enum('N','Y')                     |      |     | N       |       | Y
| Update_priv           | enum('N','Y')                     |      |     | N       |       | Y
| Delete_priv           | enum('N','Y')                     |      |     | N       |       | Y
| Create_priv           | enum('N','Y')                     |      |     | N       |       | 
| Drop_priv             | enum('N','Y')                     |      |     | N       |       | 
| Reload_priv           | enum('N','Y')                     |      |     | N       |       | 
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       | 
| Process_priv          | enum('N','Y')                     |      |     | N       |       | 
| File_priv             | enum('N','Y')                     |      |     | N       |       | 
| Grant_priv            | enum('N','Y')                     |      |     | N       |       | 
| References_priv       | enum('N','Y')                     |      |     | N       |       | 
| Index_priv            | enum('N','Y')                     |      |     | N       |       | 
| Alter_priv            | enum('N','Y')                     |      |     | N       |       | 
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       | 
| Super_priv            | enum('N','Y')                     |      |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       | 
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       | 
| Execute_priv          | enum('N','Y')                     |      |     | N       |       | 
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       | 
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       | 
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       | 
| ssl_cipher            | blob                              |      |     |         |       | 
| x509_issuer           | blob                              |      |     |         |       | 
| x509_subject          | blob                              |      |     |         |       | 
| max_questions         | int(11) unsigned                  |      |     | 0       |       | 
| max_updates           | int(11) unsigned                  |      |     | 0       |       | 
| max_connections       | int(11) unsigned                  |      |     | 0       |       | 
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)

Lets now "source" the SQL script.

mysql> source Mysql-v4-add-user-www.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 1 row affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Verify that the entry was successful.

mysql> select Host,User,Password from user where User = 'www';
+-----------+-------+------------------+
| Host      | User  | Password         |
+-----------+-------+------------------+
| localhost | www   | ac14f89260g214b9 | (this is not a real password)
+-----------+-------+------------------+
2 rows in set (0.00 sec)

Don't forget to "flush privileges". The KID will not be able to be accessed via a browser until you have flushed the MySQL privileges table.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

If this works then the scripts, and hence the web pages, should be able to access the kid database as user "kid".

top

10. Create an auto-login Config File for MySQL Access in the Shell

We have to supply a way for shell scripts to access the MySQL database so that the script that will create the kid database and its tables can be run from a shell. Otherwise we would have to enter a password several times during the install.

MySQL can automatically read the database access username and password from a special config file (.my.cnf), obviating the need for you to supply these on the command line. This will also come in quite useful when we run various other shell scripts that read or change the MySQL KID tables.

Before we create this special config file though we should check that MySQL has been installed correctly - at this stage the user "kid" should NOT be able to able to gain access to the MySQL tables unless they explicitly supply the MySQL root password. If you try without a password you should get something like this:

$ mysql -e 'show tables' mysql

ERROR 1045: Access denied for user: 'kid@localhost' (Using password: NO)

But you should be able to see the tables by supplying the MySQL root password like this:

$ mysql -u root -p{the_mysql_root_password} -e 'show tables' mysql

+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+  

Now lets create the auto-login config file /home/kid/.my.cnf:

File: /home/kid/.my.cnf

[client]
host=localhost
user=root
password=the_mysql_root_password

Check that it's permissions are 400 only! It will run perfectly fine with permissions of just 400 (ref: MySQL manual).

kid$ chmod 400 .my.cnf

kid$ ls -al .my*
-r--------    1 kid   caves   50 Jan  1 15:50 .my.cnf
-rw-------    1 kid   caves    5 Jan  1 15:47 .mysql_history

When you or a script try to access MySQL it will read the above file and connect you to the database using the username and password (and host) in the .my.cnf file. Now try the simple mysql command above again:

$ mysql -e 'show tables' mysql

+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+  

If you can now invoke mysql without -u and -p on the command line you and the web server will be able to run the scripts.

Note 1: The web server does not read .my.cnf for security reasons. If it did then other processes running on the web server could also read this file (since the web server itself would have to be able to read .my.cnf).

Note 2: The .my.cnf file contains the MySQL root password because we want to create and drop tables and things like that and root can do all this and more. We could have created a special user just for this purpose and provided JUST enough MySQL permissions for the shell based scripts to do their job but that complicates the system even more. The .my.cnf file has permissions of just 400 so it will be quite secure.

11. Create the KID Database

Let's create the kid database. The database to create is called "kid", since ASF KID is hardcoded to use "kid".

Part of the creation process is loading all the table information and data from files in the project's model/dumps directory. There are a few dozen files as in the example below. These files are in MySQL's mysqldump format.

asf-kid/model/dumps$ ls
AR0000    CA0012  CA0049  CA0071  CA0259  PL0218  PL0601    SYS00SPC
AR0331    CA0018  CA0050  CA0072  CA0421  PL0219  
AR0333    CA0030  CA0051  CA0074  CA0427  PL0368  
          CA0035  CA0052  CA0075  CA0439  PL0397  PS0000    sys_fid
          CA0037  CA0053  CA0231  CA0447  PL0406  PS0574    sys_fieldvalue
CA0000    CA0041  CA0055  CA0241  OR0000  PL0558  PS0578    sys_search
CA0007    CA0043  CA0066  CA0245  PE0000  PL0586  RE0000    sys_table_description
CA0008    CA0046  CA0069  CA0257  PL0000  PL0587  SYS00SPA  sys_user
CA0011    CA0048  CA0070  CA0258  PL0203  PL0598  SYS00SPB

When you run the script "load_tables kid", to create the kid database, the basic system tables (sys_*) will be created followed by the cave, map and other data tables. They will be filled by loading all the data from the dump files. Remember this is done as user "kid" as we have setup the /home/kid/.my.cnf file.

Below is a transcript of part of the output:

model/dumps$ load_tables kid

TABLENAMES=AR0000 AR0331 AR0333 CA0000 CA0007 CA0008 CA0011
CA0012 CA0018 CA0030 CA0035 CA0037 CA0041 CA0043 CA0046 CA0048 CA0049 CA0050
CA0051 CA0052 CA0053 CA0055 CA0066 CA0069 CA0070 CA0071 CA0072 CA0074 CA0075
CA0231 CA0241 CA0245 CA0257 CA0258 CA0259 CA0421 CA0427 CA0439 CA0447 OR0000
PE0000 PL0000 PL0203 PL0218 PL0219 PL0368 PL0397 PL0406 PL0558 PL0586 PL0587
PL0598 PL0601 PS0000 PS0574 PS0578 RE0000 SYS00SPA SYS00SPB
SYS00SPC sys_fid sys_fieldvalue
sys_search sys_table_description sys_user
./AR0000 ==> kid.AR0000
./AR0331 ==> kid.AR0331
./AR0333 ==> kid.AR0333
./CA0000 ==> kid.CA0000
./CA0007 ==> kid.CA0007

... etc ...

./SYS00SPA ==> kid.SYS00SPA
./SYS00SPB ==> kid.SYS00SPB
./SYS00SPC ==> kid.SYS00SPC
./sys_fid ==> kid.sys_fid
./sys_fieldvalue ==> kid.sys_fieldvalue
./sys_search ==> kid.sys_search
./sys_table_description ==> kid.sys_table_description
./sys_user ==> kid.sys_user

model/dumps$

List the tables in the database to check that they have been created.

$ mysql -e 'show tables' kid

+-----------------------+
| Tables in kid         |
+-----------------------+
| AR0000                |
| AR0331                |
| AR0333                |
| CA0000                |
| CA0007                |
| CA0008                |
| CA0011                |
| CA0012                |
| CA0018                |
| CA0030                |
| CA0035                |
| CA0037                |
| CA0041                |

...

| sys_fid               |
| sys_fieldvalue        |
| sys_table_description |
+-----------------------+   

Execute the db test in asf-kid/bin/

$ cd asf-kid/bin/

$ ./tdb

database name is kid
sys_table_description:
  description = Table description (system read-only table)
  tablename = sys_table_description
 
A few table descriptions
  AR0000:         Articles
  AR0331:         Authors
  AR0333:         Article comments

...etc...

Lookup hash for FID 7: rock_type
  02    limestone
  04    dune limestone
  06    dolomite
  08    marble
  10    basalt
  12    dolerite
  14    granite
  16    gypsum
  18    ice
  20    lava
  21    magnesite
  22    mudstone
  24    quartzite
  26    sandstone
  28    soil
  30    tuff
  
  
Lookup hash for attribute internal_flow: FID 5
  2     connects to perennial internal flow
  4     connects to intermittent internal flow

The above test shows you that the database has been created, including the FIDs, the fields, table descriptions, local tables and ASF-KID tables from the data files.

The Karst Index Database should now be up and running and accessable from:  http://localhost/kid
You will be able to login to the Search Page with username "guest" and password "guest".

You will also be able to login to the KID Administration Page at http://localhost/kid/users/admin
Login with username "admin" and password "admin". From there you can add more users and edit their access rights.

You should change the admin password to another password if your site is internet accessable!

Now would also be a good time to write down the following passwords and store them somewhere in a safe place.

top

APPENDICES

Setting the Root Password for MySQL

If you have just installed MySQL for this KID then its likely that there is no MySQL root password set for MySQL. You will need to set one! Note that the MySQL root password is not the same as the operating system's root password - they are completely different.

First try to run mysql from the command-line:

$ mysql -e 'show tables' mysql

The output should look something like the following, if the database required NO authorisation to access it. This is very insecure. Ensure a username and password are required to access the database.

+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+  

If you cannot execute the above command, and you get an error message like this:

try this:

    mysql -u {username} -p{theRootPassword} -e 'show tables' mysql

where username=root (in your case) and you supply the password.
NOTE: -u username but no space after -p: -ppassword

Problems with mod_auth_mysql

top

CHANGE HISTORY OF THIS DOCUMENT

Latest changes at end of this table.
MRL=Mike Lake
RW=Rick Welykochy

top