October 2025
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728293031  

Categories

October 2025
M T W T F S S
 12345
6789101112
13141516171819
20212223242526
2728293031  

SSL CERT EXPIRE certwatch

Certwatch checks for Apache certificates which are due to expire. By default on Red Hat / Centos there is a cron job in /etc/cron.daily which runs and sends its output to root. To configure it:

    vi /etc/sysconfig/httpd

Add a line such as:

    CERTWATCH_OPTS=”–period 30 –address test@rmohan.com”

It is also possible to switch it off with:

    NOCERTWATCH=yes

Linux Security

Centralized SSH Keys

There are times when it is beneficial to take control of SSH key management on a server. This brief tutorial will centralize all user SSH keys to a single location and allow root to manage them.

We will be keeping all keys in a single directory located in /etc/ssh/authorized_keys. Within the directory, there will be a file for each user account containing its authorized_keys.

The below commands will configure the server with centralized SSH keys and add a ssh public key to the keyfile.

Create the directory:
Bash

mkdir /etc/ssh/authorized_keys

Create a file for each user account and add a key:
Bash

touch /etc/ssh/authorized_keys/username
cat id_rsa.pub >> /etc/ssh/authorized_keys/username

Ensure the created file has the proper permissions or else authentication will not work.
Bash

chmod 600 /etc/ssh/authorized_keys/username

Inside the ssh configuration file, you will have to edit the Authorized_keys. Open /etc/ssh/sshd_config with your favorite text editor (hopefully VIM) and make the following changes.

Change the following.
Bash

#AuthorizedKeysFile     .ssh/authorized_keys
AuthorizedKeysFile      /etc/ssh/authorized_keys/%u
http://www.maxbooks.info/ebooks/

https://www.suse.com/documentation/sles11/
https://www.suse.com/documentation/sles11/singlehtml/book_security/book_security.html

Disable Interactive Shell Logins
July 20, 2012 / admin posted in Bash, Linux, Security / No Comments

Often you have a server with users are required an account to access certain services but you do not want them interactively log into the server. This is very common on an ftp/sftp server. If you change their shell to /sbin/nologin or /bin/false this could prevent them from utilizing the service you have put into place. The simplest way to accommodate this is to write your own shell specifically for them.

Create a file with the name of your shell and place the below contents in it. I am using mine to restrict sftp users, so it is call ftponly. This shell will accept their login, display the message and then close their session. I have included a trap to catch any signals in the event a user gets crafty and tries to CTRL+C quickly.

Bash

    

#!/bin/bash
#
# ftponly shell
#
trap “/bin/echo Sorry; exit 0” 1 2 3 4 5 6 7 10 15
#
/bin/echo
/bin/echo “***************************************************************”
/bin/echo ”  These credentials are NOT allowed interactive access to “
/bin/echo ”                  <SERVER NAME> Server”
/bin/echo
/bin/echo ”              Account restricted to ftp access.”
/bin/echo
/bin/echo ”       Contact admin@example.com with any issues.”
/bin/echo “***************************************************************”
/bin/echo
#
exit 0

Place this file in /bin and ensure it is owned by root and excutable.
Bash

chown root.root /bin/ftponly
chmod 755 /bin/ftponly

Now place the shell name into the /etc/shells file:
Bash

# /etc/shells: valid login shells
/bin/bash
/bin/csh
/bin/esh
/bin/fish
/bin/ftponly
/bin/ksh
/bin/sash
/bin/sh
/bin/tcsh
/bin/zsh

You can now assign the shell to a user either in the passwd file or during account creation.
Bash

bob:x:1005:100:bob’s ftp accounttg :/home/bob:/bin/ftponly

Disable USB storage support in RHEL 5
June 2, 2011 / admin posted in Linux, Security / No Comments

To easily disable USB storage device support in RHEL 5 just add the following line to /etc/modprobe.conf:

install usb-storage :

This will prevent modprobe from loading the usb-storage module, but will allow administrators to manually load the module with insmod when needed.

If you will never need USB support you can simply remove the USB Storage driver from the kernel. Note: This will have to be repeated after each kernel update.

mv /lib/modules/kernelversion(s)
        /kernel/drivers/usb/storage/usb-storage.ko /root

You could also append nousb to the kernel line in your /etc/grub.conf. Make sure that your /etc/grub.conf is password protected or someone could just edit the line from it in single user mode.

kernel /vmlinuz-version ro vga=ext
        root=/dev/VolGroup00/LogVol00 rhgb quiet nousb

Disable CD/DVD access for normal users.
June 2, 2011 / admin posted in Linux, Security / No Comments

I just set up some workstations that I was required to disable CDROM access for normal users, but retain it for root. The easiest way, with the least amount of impact, is to disable GNOME’s automounting from the gnome-volume-manager program. This program mounts devices and removable media (DVDs, CDs and USB flash drives) when they are inserted into the system.

As root:

gconftool-2 –direct
–config-source xml:readwrite:/etc/gconf/gconf.xml.mandatory
–type bool
–set /desktop/gnome/volume_manager/automount_media false
 
gconftool-2 –direct
–config-source xml:readwrite:/etc/gconf/gconf.xml.mandatory
–type bool
–set /desktop/gnome/volume_manager/automount_drives false

You can then verify the changes by viewing the output of:

gconftool-2 -R /desktop/gnome/volume_manager

Another, less elegant method to accomplish this task is to relocate the cdrom driver. You can always put it back as root and insmod to reactivate it.

mv /lib/modules/<kernels>/drivers/cdrom/cdrom.ko /root

Iptables rewirte for 8080 and 8443

Another way of achieving the same thing is to add an iptable redirection rule.

We have done it this way, because we didn’t want to add the xinit package to our standard configuration.

These instructions were created on a Debian Lenny system.

Create a new “if-up” script:
sudo vim /etc/network/if-up.d/jira-redirect

Make this its content:

#!/bin/bash
for i in $(sudo ifconfig  | grep ‘inet addr:’| grep -v ‘127.0.0.1’ | cut -d: -f2 | awk ‘{ print $1}’); do
        sudo iptables -t nat -I PREROUTING 1 -d $i -p tcp –dport 80 -j DNAT –to $i:8080
        sudo iptables -t nat -I PREROUTING 1 -d $i -p tcp –dport 443 -j DNAT –to $i:8443
done

This script will take all the IPs the server is using as shown by ifconfig, and will add the redirect rules for them.

Make the script executable:
sudo chmod +x /etc/network/if-up.d/jira-redirect

Create a new “if-down” script:
sudo vim /etc/network/if-down.d/jira-redirect-clearer

Make this its content:

#!/bin/bash
sudo iptables -F -t nat

This script will clear all the NAT rules by using the flush (-F) directive, when the networking is restarted.

Adding this “if-down” script better handles cases where you changed a networking setting and the old ones are still there until a reboot. however as this does clear ALL of the NAT settings, make sure this doesn’t affect other things on your setup.

Make the script executable:
sudo chmod +x /etc/network/if-down.d/jira-redirect-clearer

Restart the networking service for the changes to take affect:
sudo /etc/init.d/networking restart

MySQL 5.5

TABLE OF CONTENTS (HIDE)

1.  Introduction
1.1  Relational Database and Structure Query Language (SQL)
1.2  SQL By Examples
1.3  MySQL Relational Database Management System (RDBMS)
2.  How to Install MySQL 5.5 and Get Started
2.1  Step 1: Download and Install MySQL
2.2  Step 2: Create the Configuration File
2.3  Step 3: Start the Server
2.4  Step 4: Start a Client
2.5  Step 5: Set the Password for Superuser “root” and Remove the Anonymous User
2.6  Step 6: Create a New User
2.7  Step 7: Create a new Database, a new Table in the Database, Insert Records, Query and Update
2.8  Summary of Frequently-Used Commands:
2.9  Exercise
3.  Many-to-many Relationship
4.  Java Database Programming
4.1  Installing the MySQL JDBC Driver
4.2  Writing a Java Database Program
5.  Backup and Restore Databases
5.1  Backup via “mysqldump” Utility Program
5.2  Restore via “source” command in a mysql client
6.  (For Advanced Users) NetBeans and MySQL
7.  (For Advanced Users) MySQL GUI Tool – MySQL Workbench
8.  (For Advanced Users) Running MySQL as a Windows Service

1.  Introduction

1.1  Relational Database and Structure Query Language (SQL)

A relational database organizes data in tables. A table has rows (or records) and columns (or fields). Tables can be related based on common columns to eliminate data redundancy.

Popular Relationship Database Management System (RDBMS) includes the commercial Oracle, IBM DB2, Microsoft SQL Server and Access, SAP SyBase and Teradata; and the free MySQL, Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base.

A high-level language, called Structure Query Language (SQL), had been designed for structuring relational databases; and for creating, updating, reading and deleting (CURD) records. SQL defines a set of commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, and etc.

1.2  SQL By Examples

Suppose we have created a table called class101, in a database called studentdb, with 3 columns: id, name and gpa. Each column has a data type. We choose: INT (integer) for column id, FLOAT (floating-point number) for gpa, and VARCHAR(50) (variable-length strings of up to 50 characters) for name. There are three records in the table as follows:

Database: studentdb
Table: class101
+-----------+----------------+-------------+
| id (INT)  | name (VARCHAR) | gpa (FLOAT) |
+-----------+----------------+-------------+
|   11      | Tan Ah Teck    |  4.4        |
|   33      | Kumar          |  4.8        |
|   44      | Kevin          |  4.6        |
+-----------+----------------+-------------+

We can issue these SQL commands, which are intuitive, to query the database or manipulate data:

-- SELECT columns FROM tableName WHERE criteria
SELECT name, gpa FROM class101
SELECT * FROM class101             
  -- The wildcard * denotes all the columns
SELECT name, gpa FROM class101 WHERE gpa >= 2.0
  -- You can compare numbers using =, >, <, >=, <=, <> (!=)
SELECT name, gpa FROM class101 WHERE name = 'Tan Ah Teck'
SELECT name, gpa FROM class101 WHERE name LIKE 'T_n%'
  -- Strings are enclosed in single quotes
  -- You can compare strings with full-match (= or !=)
  -- You can also use operator "LIKE" for pattern-matching, with
  --     wildcard % matches zero or more (any) characters;
  --     wildcard _ matches one (any) character;
SELECT * FROM class101 WHERE gpa > 3.0 OR name LIKE '%T_n%' ORDER BY gpa DESC, name ASC
  -- Order the results in DESC (descending) or ASC (Ascending)

-- DELETE FROM tableName WHERE criteria
DELETE FROM class101  // Delete ALL rows!!!
DELETE FROM class101 WHERE id = 33

-- INSERT INTO tableName VALUES (columnValues)
INSERT INTO class101 VALUES (88, 'Mohammed Ali', 4.88)
INSERT INTO class101 (name, gpa) VALUES ('Kumar', 4.55)

-- UPDATE tableName SET columnName = value WHERE criteria
UPDATE class101 SET gpa = 5.0  // ALL rows
UPDATE class101 SET gpa = gpa + 1.0 WHERE name = 'Tan Ah Teck'

-- CREATE TABLE tableName (columnDefinitions)
CREATE TABLE class101 (id INT, name VARCHAR(50), gpa FLOAT)
 
-- DROP TABLE tableName
DROP TABLE class101  // Delete the table. No UNDO!!!

SQL keywords, names, strings may or may not be case-sensitive, depending on the implementation. In most of the implementations, the keywords are not case-sensitive. For programmers, it is best to treat the names and strings case-sensitive. SQL strings are enclosed in single quotes, but most implementations also accept double quotes.

1.3  MySQL Relational Database Management System (RDBMS)

MySQL is the most used, and possibly the best free, open-source and industrial-strength Relational Database Management System (RDBMS). MySQL was developed by Michael “Monty” Widenius and David Axmark in 1995. It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010.

MySQL is successful, not only because it is free and open-source (there are many free and open-source databases, such as Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base), but also for its speed, ease of use, reliability, performance, connectivity (full networking support), portability (run on most OSes, such as Unix, Windows, Mac), security (SSL support), small size, and rich features. MySQL supports all features expected in a high-performance relational database, such as transactions, foreign key, replication, subqueries, stored procedures, views and triggers.

MySQL is often deployed in a LAMP (Linux-Apache-MySQL-PHP), WAMP (Windows-Apache-MySQL-PHP), or MAMP (Mac-Apache-MySQL-PHP) environment. All components in LAMP is free and open-source.

The mother site for MySQL is www.mysql.com. The ultimate reference for MySQL is the “MySQL Reference Manual”, available at http://dev.mysql.com/doc/. The reference manual is huge – the PDF has over 3700 pages!!!

 

1.  Introduction

1.1  Relational Database and Structure Query Language (SQL)

A relational database organizes data in tables. A table has rows (or records) and columns (or fields). Tables can be related based on common columns to eliminate data redundancy.

Popular Relationship Database Management System (RDBMS) includes the commercial Oracle, IBM DB2, Microsoft SQL Server and Access, SAP SyBase and Teradata; and the free MySQL, Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base.

A high-level language, called Structure Query Language (SQL), had been designed for structuring relational databases; and for creating, updating, reading and deleting (CURD) records. SQL defines a set of commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, and etc.

1.2  SQL By Examples

Suppose we have created a table called class101, in a database called studentdb, with 3 columns: id, name and gpa. Each column has a data type. We choose: INT (integer) for column id, FLOAT (floating-point number) for gpa, and VARCHAR(50) (variable-length strings of up to 50 characters) for name. There are three records in the table as follows:

Database: studentdb
Table: class101
+-----------+----------------+-------------+
| id (INT)  | name (VARCHAR) | gpa (FLOAT) |
+-----------+----------------+-------------+
|   11      | Tan Ah Teck    |  4.4        |
|   33      | Kumar          |  4.8        |
|   44      | Kevin          |  4.6        |
+-----------+----------------+-------------+

We can issue these SQL commands, which are intuitive, to query the database or manipulate data:

-- SELECT columns FROM tableName WHERE criteria
SELECT name, gpa FROM class101
SELECT * FROM class101             
  -- The wildcard * denotes all the columns
SELECT name, gpa FROM class101 WHERE gpa >= 2.0
  -- You can compare numbers using =, >, <, >=, <=, <> (!=)
SELECT name, gpa FROM class101 WHERE name = 'Tan Ah Teck'
SELECT name, gpa FROM class101 WHERE name LIKE 'T_n%'
  -- Strings are enclosed in single quotes
  -- You can compare strings with full-match (= or !=)
  -- You can also use operator "LIKE" for pattern-matching, with
  --     wildcard % matches zero or more (any) characters;
  --     wildcard _ matches one (any) character;
SELECT * FROM class101 WHERE gpa > 3.0 OR name LIKE '%T_n%' ORDER BY gpa DESC, name ASC
  -- Order the results in DESC (descending) or ASC (Ascending)

-- DELETE FROM tableName WHERE criteria
DELETE FROM class101  // Delete ALL rows!!!
DELETE FROM class101 WHERE id = 33

-- INSERT INTO tableName VALUES (columnValues)
INSERT INTO class101 VALUES (88, 'Mohammed Ali', 4.88)
INSERT INTO class101 (name, gpa) VALUES ('Kumar', 4.55)

-- UPDATE tableName SET columnName = value WHERE criteria
UPDATE class101 SET gpa = 5.0  // ALL rows
UPDATE class101 SET gpa = gpa + 1.0 WHERE name = 'Tan Ah Teck'

-- CREATE TABLE tableName (columnDefinitions)
CREATE TABLE class101 (id INT, name VARCHAR(50), gpa FLOAT)
 
-- DROP TABLE tableName
DROP TABLE class101  // Delete the table. No UNDO!!!

SQL keywords, names, strings may or may not be case-sensitive, depending on the implementation. In most of the implementations, the keywords are not case-sensitive. For programmers, it is best to treat the names and strings case-sensitive. SQL strings are enclosed in single quotes, but most implementations also accept double quotes.

1.3  MySQL Relational Database Management System (RDBMS)

MySQL is the most used, and possibly the best free, open-source and industrial-strength Relational Database Management System (RDBMS). MySQL was developed by Michael “Monty” Widenius and David Axmark in 1995. It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010.

MySQL is successful, not only because it is free and open-source (there are many free and open-source databases, such as Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base), but also for its speed, ease of use, reliability, performance, connectivity (full networking support), portability (run on most OSes, such as Unix, Windows, Mac), security (SSL support), small size, and rich features. MySQL supports all features expected in a high-performance relational database, such as transactions, foreign key, replication, subqueries, stored procedures, views and triggers.

MySQL is often deployed in a LAMP (Linux-Apache-MySQL-PHP), WAMP (Windows-Apache-MySQL-PHP), or MAMP (Mac-Apache-MySQL-PHP) environment. All components in LAMP is free and open-source.

The mother site for MySQL is www.mysql.com. The ultimate reference for MySQL is the “MySQL Reference Manual”, available at http://dev.mysql.com/doc/. The reference manual is huge – the PDF has over 3700 pages!!!

MySQL RDBMS operates as a client-server system over TCP/IP network. The server runs on a machine with an IP address, on a chosen TCP port number. The default TCP port number for MySQL is 3306, but you are free to choose another port number between 1024 and 65535. Users can access the server via a client program, connecting to the server at the given IP address and TCP port number.

A MySQL database server contains one or more databases (aka schemas). A database contains one or more tables. A table consists of rows (records) and columns (fields).

2.  How to Install MySQL 5.5 and Get Started

2.1  Step 1: Download and Install MySQL

  1. Download MySQL from www.mysql.com.
    1. From the top-level tabs, select the “Downloads (GA)” ? “MySQL Community Server” ? “General Available (GA) Release”, “MySQL Community Server 5.5.xx”, where xx is the latest update number.
    2. Select “Microsoft Windows”; or your target platform.
    3. Choose the ZIP Archive (without MSI Installer) “mysql-5.5.xx-win32.zip (143M)”. (DO NOT use “mysql-5.5.xx.zip” (28M), which is the source distribution and requires compilation.)
    4. Click “No thanks, just start my downloads!”.
  2. UNZIP into a directory of your choice. DO NOT unzip into your desktop (because it is hard to locate the path). I suggest that you unzip into “d:\myproject“. MySQL will be unzipped as “d:\myproject\mysql-5.5.xx-win32“. For ease of use, we shall shorten and rename the directory to “d:\myproject\mysql“.

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 1.

I recommend using the “ZIP” version, instead of the “Installer” version. You can simply delete the entire MySQL directory when it is no longer needed (without running the un-installer). You are free to move or rename the directory. You can also install (unzip) multiple copies of MySQL in the same machine on different directories.

I shall assume that MySQL is installed in directory “d:\myproject\mysql“. But you need to TAKE NOTE OF YOUR MySQL INSTALLED DIRECTORY. Hereafter, I shall denote the MySQL installed directory as <MYSQL_HOME> in this article.

MySQL distribution includes:

  1. A SQL server;
  2. A command-line client;
  3. Utilities (for database administration, backup/restore, and others);
  4. Client libraries for you to write your own client.

2.2  Step 2: Create the Configuration File

(Note) Programmers need to view the file extension (such as .txt, .ini). To display the file extension in Windows, run “Control Panel” ? “Folder Options” ? Select tab “View” ? Uncheck “Hide extensions for known file types”.

 

Use a text editor to create the following configuration file called “my.ini” and save it in your MySQL INSTALLED DIRECTORY (e.g., “d:\myproject\mysql“). You can ignore lines beginning with #, which are comments. IMPORTANT: You need to modify the highlighted lines for your MySQL installed directory!!!

# Save as my.ini in your MySQL installed directory
[mysqld]
# Set MySQL base (installed) directory
# @@ Change to your MySQL installed directory @@
basedir=d:/myproject/mysql

# Set MySQL data directory
# @@ Change to sub-directory "data" of your MySQL installed directory  @@
datadir=d:/myproject/mysql/data

# Run the server on this TCP port number
port=8888
   
[client]
# MySQL client connects to the server running on this TCP port number
port=8888
Explanation
  • [mysqld]
    [client]
    The MySQL operates as a client-server system, and consists of a server program and a client program. There are two sections in the configuration: [mysqld] for the server program, and [client] for the client program.
  • basedir=…
    datadir=…
    basedir” and “datadir” specify the MySQL installed directory and data directory, respectively. Make sure that you set their values according to your own installation. You need to use Unix-style Forward-Slash '/' as the directory separator, as shown (instead of Windows-style backward-slash '\').
  • port=8888
    MySQL is a TCP/IP application. The default TCP port number for MySQL is 3306. However, it may crash with a MySQL server already running in some lab machines. You may choose any port number between 1024 to 65535, which is not used by an existing application. I choose 8888 for our server.
  • This configuration file specifies the bare minimum. There are many more configuration options. Sample configuration files are provided in <MYSQL_HOME>.

(For Notepad Users) If you use Notepad, make sure that you double-quote the filename "my.ini" when saving the file; otherwise, it may be saved as "my.ini.txt". Worse still, you will not see the file extension “.txt” unless you enable displaying of file extension. Nonetheless, “my.ini.txt” has file-type description of “Text Document”; while “my.ini” has “Configuration Settings”.
Don’t use Notepad for programming!!! At the minimum, you should have Notepad++.

(For Mac Users) If you use the DMG installer, skip this step. MySQL server will be running on the default port number of 3306.
You could read “How to Install MySQL 5 on Mac” Step 2 on how to configure the port number.

(For Advanced Users Only) Where to place my.ini (or my.cnf) configuration file?
In Windows, the options are combined from the following files in this order: c:\Windows\my.ini, c:\Windows\my.cnf, c:\my.ini, c:\my.cnf, <MYSQL_HOME>\my.ini, <MYSQL_HOME>\my.cnf. If an option is specified in more than one place, the last setting takes effect. Our my.ini is kept in <MYSQL_HOME>, which overrides all the previous settings.
Alternatively, you can keep the my.ini in any location, and use the startup option --defaults-file=filename to specify the location of the configuration file and bypass all the files listed above.

2.3  Step 3: Start the Server

The MySQL is a client-server system. The database is run as a server application. Users access the database server via a client program, locally or remotely, as illustrated:

  1. The database server program is called “mysqld.exe” (with a suffix 'd', which stands for daemon – a daemon is a program/process running in the background).
  2. The interactive client program is called “mysql.exe” (without the 'd').

The MySQL executable programs are kept in the “bin” sub-directory of the MySQL installed directory.

Startup Server

To start the database server, launch a CMD shell. Set the current directory to “<MYSQL_HOME>\bin“, and run command “mysqld --console“.

-- Change the current directory to MySQL's "bin" directory.
-- Assume that the MySQL installed directory is "d:\myproject\mysql".
Prompt> d:                   -- Change the current drive
D:\...> cd \                 -- Change Directory (cd) to the ROOT directory of drivce 'd'
D:\> cd myproject\mysql\bin  -- Change Directory (cd) to the MySQL's bin directory
   
-- Start the MySQL Database Server
D:\myproject\mysql\bin> mysqld --console

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 3 on how to start the server.

You shall see these messages in the console. Take note of the TCP port number.

......
......
XXXXXX XX:XX:XX [Note] mysqld: ready for connections.
Version: '5.5.xx-community'  socket: ''  port: 8888  MySQL Community Server (GPL)

The MySQL database server is now started, and ready to handle clients’ requests.

Explanation
  • The --console option directs the server output messages to the console. Without this option, you will see a blank screen.
  • Observe that the database server runs on TCP port 8888, as configured in the “my.ini“.

(Skip Unless …) “Anything that can possibly go wrong, does.” Read “Common Problems in Starting the MySQL Server after Installation“.

Shutdown Server

The quickest way to shut down the database server is to press control-c (or control-break) to initiate a normal shutdown. DO NOT KILL the server via the “close” button.

Observe these messages from the MySQL server console:

XXXXXX XX:XX:XX [Note] mysqld: Normal shutdown
......
XXXXXX XX:XX:XX  InnoDB: Starting shutdown...
XXXXXX XX:XX:XX  InnoDB: Shutdown completed; log sequence number 0 44233
......
XXXXXX XX:XX:XX [Note] mysqld: Shutdown complete

(For Advanced Users Only) Alternatively, you could shutdown the server via the utility program “mysqladmin“. Start another CMD shell. Run the “mysqladmin” (with user root) as follows:

-- If root has no password
d:\myproject\mysql\bin> mysqladmin -u root shutdown
-- If root has a password
d:\myproject\mysql\bin> mysqladmin -u root -ppassword shutdown

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 3 on how to shutdown the server.

2.4  Step 4: Start a Client

Recall that the MySQL is a client-server system. Once the server is started, one or more clients can be connected to the database server. A client could be run on the same machine (local client); or from another machine (remote client).

To login to a MySQL server, you need to provide a username and password. During the installation, MySQL provides a privileged user (aka superuser) called “root” WITHOUT setting its password.

Let’s start a local client (on the same machine) with this superuser “root“. First, make sure that the server is running (see previous step to re-start the server if it has been shutdown).

Start a Client

Recall that the server program is called “mysqld” (with a suffix 'd' for daemon), while the client program is called “mysql” (without the 'd'). Start another CMD shell to run the client:

-- Change the current directory to <MYSQL_HOME>\bin.
-- Assume that the MySQL is installed in "d:\myproject\mysql".
Prompt> d:                   -- Change the current drive
D:\...> cd \                 -- Change Directory (cd) to the ROOT directory
D:\> cd myproject\mysql\bin  -- Change Directory to YOUR MySQL's "bin" directory
   
-- Starting a client with superuser "root"
D:\myproject\mysql\bin> mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

-- Client started. The prompt changes to "mysql>".
-- You can now issue SQL commands.
mysql>

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 4 on how to start/stop a client.

(Skip Unless…) Read “Common Problems in Starting the MySQL Client“.

Before we proceed, let’s issue a “status” command. Check the TCP Port Number. Make sure that you are connecting to YOUR server (running on port 8888), because there could be many MySQL servers running in your machine (on different port numbers installed by other people).

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.51, for Win32 (ia32)
......
Current user:           root@localhost
TCP port:               8888
......

2.5  Step 5: Set the Password for Superuser “root” and Remove the Anonymous User

As mentioned earlier, the MySQL installation provides a privileged user called “root” WITHOUT setting its password. “root” is a superuser that can do anything, including deleting all the databases. Needless to say, you have to set a password for root. The root’s password shall only be made available to the database administrator, not the common users.

Set Password for “root”

Let’s continue with our client session:

-- Query all the users and their passwords from database "mysql" table "user"
mysql> select host, user, password from mysql.user;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | root |          |
| 127.0.0.1 | root |          |
| ::1       | root |          |
| localhost |      |          |
+-----------+------+----------+
4 rows in set (0.09 sec)
   
-- Set password for 'root'@'127.0.0.1'. Replace xxxx with your chosen password
-- Take note that strings are to be enclosed by a pair of single-quotes.
mysql> set password for 'root'@'127.0.0.1'=password('xxxx');
Query OK, 0 rows affected (0.00 sec)
   
-- Set password for 'root'@'localhost'
mysql> set password for 'root'@'localhost'=password('xxxx');
Query OK, 0 rows affected (0.00 sec)
  
-- Set password for 'root'@'::1'
mysql> set password for 'root'@'::1'=password('xxxx');
Query OK, 0 rows affected (0.00 sec)
  
-- Query the users and passwords again.
-- Passwords are not stored in clear text. 
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| 127.0.0.1 | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| ::1       | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
   
-- logout and terminate the client program
mysql> quit
Bye
Explanation
  • select host, user, password from mysql.user;
    This command lists all the existing users, kept in the system database “mysql” table “user“. Observe that four user accounts are installed by default: 'root'@'localhost', 'root'@'127.0.0.1', 'root'@'::1' and ''@'localhost' (this is an anonymous user with empty string as the username), all WITHOUT passwords (or empty string as password). Take note that a MySQL user is identified by three attributes: username, password, and the IP address (or hostname) of the client’s machine. For example, the user root can only login from the localhost, or IPv4 127.0.0.1, or IPv6 ::1; but NOT from other IP addresses (or hostnames).
  • A command can span more than one line. You have to terminate each command with a semicolon ';'. Otherwise, a “->” prompt will appear to prompt for the rest of the command. Enter a ';' to complete the command; or '\c'to cancel the command. For example,
    mysql> select host, user, password from mysql.user;     
    ......
     
    mysql> select host, user, password from mysql.user
        -> ;
    ......
     
    mysql> select 
        -> host, user, password
        -> from mysql.user;
    ...... 
       
    mysql> select host, u \c
    mysql>
    -- Semicolon terminates the command,
    -- and sends the command to the server for processing.
     
    -- This command is not complete, to be continued in the next line.
    -- The semicolon terminates the command, and sends it to the server.
       
      
    -- A command can span several lines, terminated by a semicolon.
      
       
       
      
    -- "\c" cancels the command, and does not send it to the server.
       
  • set password for ‘root’@’localhost’=password(‘xxxx’);
    You can set the password for 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1' using the “set password” command, where ‘xxxx‘ denotes your chosen password.
  • “localhost” is a special hostname, meant for local loop-back (i.e., the server is running in the same machine as the client). It has the IPv4 address of 127.0.0.1, and IPv6 address of ::1.
  • Issue “quit“, “exit“, or press control-c to terminate the client.
  • During a client session, you can issue “help” or “\h” for help.
Start a client with Password

We have just set a password for root and exited the client. Start a client and login as root again. BUT now, you need to include a "-p" option, which prompts the user for the password:

-- Change the current working directory to <MYSQL_HOME>\bin
D:\myproject\mysql\bin> mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.
......
  
-- client started, ready to issue SQL command
mysql>
Remove the Anonymous User

For security reason, remove the anonymous user (identified by an empty-string username) as follows:

mysql> drop user ''@'localhost';
   
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| 127.0.0.1 | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| ::1       | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
   
mysql> quit

2.6  Step 6: Create a New User

The superuser “root” is privileged, which is meant for database administration and is not meant for common use. We shall create a new user – let’s call it “myuser” – with a lesser privilege. To create a new user, start a client with superuser “root“:

D:\myproject\mysql\bin> mysql -u root -p
Enter password: ********
   
-- Create a new user called "myuser", which can login from localhost, with password "xxxx"
mysql> create user 'myuser'@'localhost' identified by 'xxxx';
Query OK (0.01 sec)
   
-- Grant permission to myuser
mysql> grant all on *.* to 'myuser'@'localhost';
Query OK (0.01 sec)
   
-- Query all users and passwords
mysql> select host, user, password from mysql.user;
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| localhost | root   | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| 127.0.0.1 | root   | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| ::1       | root   | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| localhost | myuser | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+-----------+--------+-------------------------------------------+
4 rows in set (0.00 sec)
   
mysql> quit
Explanation
  • create user ‘myuser’@’localhost’ identified by ‘xxxx’
    We use the command “create user” to create a new user called 'myuser'@'localhost', who can login to the server locally from the same machine (but not remotely from another machine), with password “xxxx“.
  • grant all on *.* to ‘myuser’@’localhost’
    The newly created user has no privilege to perform any database operation such as select, insert and update. We use the “grant” command to grant “all” the privileges (including select, insert, delete, and so on) to this new user for all the databases and all the tables (“on *.*“). For production, you should grant only the necessary privileges (e.g., “grant select, insert, update on...“, without the “delete“) on selected databases and selected tables (e.g., "on studentdb.*” – all the tables of the database studentdb).

2.7  Step 7: Create a new Database, a new Table in the Database, Insert Records, Query and Update

A MySQL server contains many databases (aka schema). A database consists of many tables. A table contains rows (records) and columns (fields).

Let’s create a database called “studentdb“, and a table called “class101” in the database. The table shall have three columns: id (of the type INT – integer), name (of the type VARCHAR(50) – variable-length string of up to 50 characters), gpa (of the type FLOAT – floating-point number).

CAUTION: Programmers don’t use blank and special characters in names (such as database names, table names, column names). It is either not supported, or will pose you many more challenges.

TIPS: Before we proceed, here are some tips on using the client:

  • You need to terminate your command with a semicolon ';', which sends the command to the server for processing.
  • A command can span several lines, and terminated by a semicolon ';'.
  • You can use \c to cancel (abort) the current command.
  • You can use up/down arrow keys to retrieve the previous commands (history commands).
  • You should enable copy/paste functions of CMD shell. [To enable copy/paste, click the CMD’s icon ? Properties ? Options ? Edit Options ? Check “QuickEdit Mode”. You can then select the desired texts and use a “right-click” to copy the selected text; another “right-click” to paste.]

Let’s start a client with our newly-created user “myuser“.

D:\myproject\mysql\bin> mysql -u myuser -p
Enter password: ********
   
-- Create a new database called "studentdb"
mysql> create database if not exists studentdb;
Query OK, 1 row affected (0.08 sec)
   
-- list all the databases in this server
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studentdb          |
| test               |
+--------------------+
5 rows in set (0.07 sec)
   
-- Use "studentdb" database as the current (default) database
mysql> use studentdb;
Database changed
   
-- Remove the table "class101" in the current database if it exists
mysql> drop table if exists class101;
Query OK, 0 rows affected (0.15 sec)
   
-- Create a new table called "class101" in the current database 
--  with 3 columns of the specified types
mysql> create table class101 (id int, name varchar(50), gpa float);
Query OK, 0 rows affected (0.15 sec)
   
-- List all the tables in the current database "studentdb"
mysql> show tables;
+---------------------+
| Tables_in_studentdb |
+---------------------+
| class101            |
+---------------------+
1 row in set (0.00 sec)
   
-- Describe the "class101" table (list its columns' definitions)
mysql> describe class101;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| gpa   | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
   
-- Insert a row into "class101" table.
-- Strings are to be single-quoted. No quotes for INT and FLOAT.
mysql> insert into class101 values (11, 'Tan Ah Teck', 4.8);
Query OK, 1 row affected (0.03 sec)
   
-- Insert another row
mysql> insert into class101 values (22, 'Mohamed Ali', 4.9);
Query OK, 1 row affected (0.03 sec)
   
-- Select all columns (*) from table "class101"
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.8 |
| 22 | Mohamed Ali |  4.9 |
+----+-------------+------+
2 rows in set (0.00 sec)
  
-- Select columns from table "class101" with criteria
mysql> select name, gpa from class101 where gpa > 4.85;
+-------------+------+
| name        | gpa  |
+-------------+------+
| Mohamed Ali |  4.9 |
+-------------+------+
1 rows in set (0.00 sec)
  
-- Update selected records
mysql> update class101 set gpa = 4.4 where name = 'Tan Ah Teck';
Query OK, 1 row affected (0.03 sec)
   
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.4 |
| 22 | Mohamed Ali |  4.9 |
+----+-------------+------+
2 rows in set (0.00 sec)
   
-- delete selected records
mysql> delete from class101 where id = 22;
Query OK, 1 row affected (0.03 sec)
   
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.4 |
+----+-------------+------+
1 rows in set (0.00 sec)
   
-- You can store SQL commands in a text file (called SQL script) and run the 
--   script, instead of entering each of the SQL commands from the prompt.
-- For example, use a text editor to create a SQL script called "mycommands.sql" 
--   containing the following three SQL statements. Save the file under "d:\myproject".
insert into class101 values (33, 'Kumar', 4.8); insert into class101 values (44, 'Kevin', 4.6); Select * from class101;

-- Use the following "source" command to run the SQL script.
-- You need to provide the full path to the script.
-- Use Unix-style forward slash '/' as directory separator, 
--   instead of Windows-style back slash '\'.

mysql> source d:/myproject/mycommands.sql
Query OK, 1 row affected (0.00 sec)   // INSERT command output
Query OK, 1 row affected (0.00 sec)   // INSERT command output
+------+-------------+------+         // SELECT command output
| id   | name        | gpa  |
+------+-------------+------+
|   11 | Tan Ah Teck |  4.4 |
|   33 | Kumar       |  4.8 |
|   44 | Kevin       |  4.6 |
+------+-------------+------+
3 rows in set (0.00 sec)
Try:
  1. Select records with names starting with letter 'K'. (Hints: name like 'K%')
  2. Select records with names NOT starting with letter 'K'. (Hints: name NOT like ...)
  3. Select records with gpa between 4.35 and 4.65. (Hints: gpa >= 4.3 AND ...)
  4. Select records with names having a letter 'e'. (Hints: name like '%e%')
  5. Select records with names having a letter 'e' or 'a'. (Hints: ... OR ...)
  6. Select records with names NOT having a letter 'e' or 'a'. (Hints: NOT (... OR ...))
  7. Select records with names having a letter 'e' and gpa ? 4.5.

(Skip Unless… ) Read “Common Problems in Using the mysql Client“.

(For Advanced Users only) Remote Login: If a user is permitted to login remotely from another machine, you can use command “mysql -h serverHostname -u username -p” to login remotely by specifying server’s hostname or (IP address) via the '-h' option.

2.8  Summary of Frequently-Used Commands:

Starting MySQL Server and Client
// Start the Server
> cd path-to-mysql-bin
> mysqld --console
 
// Start a Client
> cd path-to-mysql-bin
> mysql -u userid        // Without password
> mysql -u userid -p     // To prompt for password
Frequently-used MySQL Commands

MySQL commands are NOT case sensitive.

// General
STATUS      // Displays status such as port number
;           // Sends command to server for processing (or \g)
\c          // Cancels (aborts) the current command
\G          // Displays the row vertically
 
// Database-level
DROP DATABASE databaseName                 // Deletes the database
DROP DATABASE IF EXISTS databaseName       // Deletes only if it exists
CREATE DATABASE databaseName               // Creates a new database
CREATE DATABASE IF NOT EXISTS databaseName // Creates only if it does not exists
SHOW DATABASES                             // Shows all databases in this server
   
// Set current (default) database.
// Otherwise you need to use the fully-qualified name, in the form 
//   of "databaseName.tableName", to refer to a table.
USE databaseName
SELECT DATABASE();       // show the current database
   
// Table-level
DROP TABLE tableName
DROP TABLE IF EXISTS tableName
CREATE TABLE tableName (column1Definition, column2Definition, ...)
CREATE TABLE IF NOT EXISTS tableName (column1Definition, column2Definition, ...)
SHOW TABLES              // Shows all the tables in the default database
DESCRIBE tableName       // Describes the columns for the table
DESC tableName           // Same as above
   
// Record-level (CURD - create, update, read, delete)
INSERT INTO tableName VALUES (column1Value, column2Value,...)
INSERT INTO tableName (column1Name, ..., columnNName) 
   VALUES (column1Value, ..., columnNValue)
DELETE FROM tableName WHERE criteria
UPDATE tableName SET columnName = expression WHERE criteria
SELECT column1Name, column2Name, ... FROM tableName 
   WHERE criteria
   ORDER BY columnAName ASC|DESC, columnBName ASC|DESC, ...
  
// Running a script of MySQL statements
SOURCE full-Path-Filename

2.9  Exercise

  1. Show all the databases.
  2. Create a new database called “ABCTrading“.
  3. Set the “ABCTrading” database as the default database.
  4. Show all the tables in the default database.
  5. Create a new table called “products” with the columns and type indicated below. Show the table description. Insert the following records:
    +-------+----------+-------------+----------+---------+
    | id    | category | name        | quantity | price   |
    | (INT) | CHAR(3)  | VARCHAR(20) | (INT)    | (FLOAT) |
    +-------+----------+-------------+----------+---------+
    | 1001  | PEN      | Pen Red     |     5000 |  1.23   |
    | 1002  | PEN      | Pen Blue    |     8000 |  1.25   |
    | 1003  | PEN      | Pen Black   |     2000 |  1.25   |
    | 1004  | PCL      | Pencil 2B   |    10000 |  0.49   |
    | 1005  | PCL      | Pencil HB   |    10000 |  0.48   |
    +-------+----------+-------------+----------+---------+
  6. Try issuing some SELECT, UPDATE, and DELETE commands.

3.  Many-to-many Relationship

A book is written by one or more authors. An author may write zero or more books. This is known as a many-to-many relationship. Can you model many-to-many relationship in one single table? Try it out!

The many-to-many relationshop between books and authors can be modeled with 3 tables, as shown:

Exercise
  1. Create a database called “mybookstore“, and use it as the default database.
  2. Create 3 tables “books“, “authors“, and “books_authors” in the database “mybookstore“.
  3. Insert the respective records into the tables. Check the contents of each of the tables (via SELECT * from tableName command).
  4. Try this query and explain the output:
    SELECT books.title, books.price, authors.name
       FROM books, books_authors, authors
       WHERE books.isbn = books_authors.isbn 
          AND authors.authorID = books_authors.authorID
          AND authors.name = 'Tan Ah Teck';
  5. Issue a query to display all the books (title, price, qty) by “Tan Ah Teck” with price less than 20.
  6. Issue a query to display all the authors (name and email) for the book title “Java for Dummies”.
  7. Issue a query to display the books (title, price, qty) and all the authors (name and email) for books with title beginning with “Java” (Hints: title LIKE 'Java%').

4.  Java Database Programming

Instead of using the “mysql” client program provided (as in the previous section), you can write your own client program (in Java or other programming languages) to access the MySQL server. Your client program shall create a TCP socket to connect to the database server at the given IP address and TCP port number 8888, issue the SQL commands, and process the results received.

Before you proceed, I shall assume that you are familiar with Java Programming and have installed the followings:

  1. JDK (Read “How to install JDK and Get Started“).
  2. A programming text editor, such as TextPad or Notepad++ (Read “Programming Text Editor“); or a Java IDE such as Eclipse or NetBeans (Read “How to Install Eclipse” or “How to Install NetBeans“).

4.1  Installing the MySQL JDBC Driver

You need to install an appropriate JDBC (Java Database Connectivity) driver to run your Java database programs. The MySQL’s JDBC driver is called “MySQL Connector/J”.

  1. Download MySQL JDBC driver from www.mysql.com. Select top-level tab “Downloads” ? “Connectors” ? “Connector/J” ? 5.1.xx ? ZIP version (“mysql-connector-java-5.1.xx.zip”, 3.9MB).
  2. UNZIP the download file (into “d:\myproject“).
  3. Copy “mysql-connector-java-5.1.xx-bin.jar” to your JDK’s extension directory at “<JAVA_HOME>\jre\lib\ext” (e.g., “c:\program files\java\jdk1.7.xx\jre\lib\ext“).

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 5 on how to install MySQL JDBC Driver.

(For Advanced User Only) You can compile Java database programs without the JDBC driver. But to run the JDBC programs, the driver’s JAR-file must be included in the CLASSPATH, or the JDK’s extension directory. You could set the CLASSPATH via “Control Panel” ? System ? (Vista/7 only) Advanced system settings ? Switch to “Advanced” tab ? “Environment variables” ? Choose “System Variables” (for all users in this system) or “User Variables” (for this login user only) ? Choose “New” or “Edit”? In “Variable Name”, enter “classpath” ? In “Variable Value”, enter “.;path-to\mysql-connector-java-5.1.xx-bin.jar“, where “path-to” refers to the full path to the driver’s JAR-file. You could also include the CLASSPATH in the command-line “java -cp .;path-to\mysql-connector-java-5.1.xx-bin.jar JdbcProgramClassname” to run your program.

4.2  Writing a Java Database Program

A Java database program consists of the following steps:

  1. Allocate a Connection object to the database server.
  2. Create a Statement object inside the Connection.
  3. Write a SQL query and execute the query, via the Statement and Connection created.
  4. Process the query results.
  5. Close the Statement and Connection.

The Java program uses a so-called database-URL to connect to the server. The database-URL for MySQL is in the form of “jdbc:mysql://localhost:8888/studentdb“, where "localhost” (with IP address of 127.0.0.1) is the hostname for local loop-back; "8888” is the server’s TCP port number; and “studentdb” is the default database (equivalent to the MySQL command “use databaseName“). You need to provide the username and password as well.

Example 1: SQL CREATE TABLE, INSERT, DELETE, UPDATE – executeUpdate()

The following program creates a table called “class202” in the database “studentdb” (which has been created in the earlier exercise; otherwise, you need to create this database using the “mysql” client before running this program). It also issues INSERT, DELETE and UPDATE commands.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
import java.sql.*;                // Using JDBC package
 
public class MySQLCreateTest {          // Requires JDK 6 and above
   public static void main(String[] args) throws SQLException {
      Connection conn = null;  // Declare a Connection object called conn
      Statement stmt = null;   // Declare a Statement object called stmt
      try {
         // Step 1: Allocate a database "Connection"
         conn = DriverManager.getConnection( "jdbc:mysql://localhost:8888/studentdb", "myuser", "xxxx");  // <== check
         // database-url(protocol://hostname:port/defaultDatabase), userid, password
         // Use IP address 127.0.0.1 if localhost does not work!
 
         // Step 2: Allocate a "Statement" from the "Connection"
         stmt = conn.createStatement();
 
         // Step 3 & 4: Query and process the query result
         String sqlStr;   // SQL string
         int returnCode;  // return-code of the database server running the SQL statement
 
         // Remove table "class202" from the current database "studentdb"
         // (specified in the database-url)
         sqlStr = "drop table if exists class202";
         System.out.println(sqlStr);  // Echos the command for debugging
         returnCode = stmt.executeUpdate(sqlStr);  // Send to database server
         if (returnCode == 0) {       // Check the return code
            System.out.println("Result: Table 'class202' dropped");
         }
         System.out.println();
 
         // Create table "class202" in the current database "studentdb"
         sqlStr = "create table class202 (id int, name varchar(50), gpa float)";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         if (returnCode == 0) {
            System.out.println("Result: Table 'class202' created");
         }
         System.out.println();
 
         // Insert a few records
         sqlStr = "insert into class202 values (3333, 'Kumar', 4.4)";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) inserted");
 
         sqlStr = "insert into class202 values (4444, 'Kevin Jones', 4.6), (5555, 'Peter Jones', 4.3)";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) inserted");
         System.out.println();
 
         // Update record
         sqlStr = "update class202 set gpa=4.9 where id=5555";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) updated");
         System.out.println();
 
         // Delete record
         sqlStr = "delete from class202 where id=3333";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) deleted");
 
      } catch (SQLException ex) {
         ex.printStackTrace();
      } finally {
         // Step 5: Always close the resources
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
      }
   }
}

In the above example, we invoke the method executeUpdate() to run the DROP TABLE, CREATE TABLE, INSERT, UPDATE and DELETE commands. The executeUpdate() returns an int, indicating the result of the operation (e.g., number of rows affected).

Start a “mysql” client (as in the previous section) to verify the result of the above program:

> cd path-to-mysql-bin
> mysql -u myuser -p
password: xxxx
mysql> show databases;
......
mysql> use studentdb;
......
mysql> show tables;
......
mysql> select * from class202;
......
TRY:
  1. Insert a new record (8888, 'Tan Ah Teck', 4.4). Check the result via “mysql” client.
  2. Increase the GPA of 'Tan Ah Teck' by 0.1. Check the result via “mysql” client.
  3. Remove records with names containing “Jones”. Check the result via “mysql” client.
Example 2: SQL SELECT – executeQuery() and ResultSet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
import java.sql.*;                // Using JDBC package
 
public class MySQLSelectTest {          // Requires JDK 6 and above
   public static void main(String[] args) throws SQLException {
      Connection conn = null;  // Declare a Connection object called conn
      Statement stmt = null;   // Declare a Statement object called stmt
      try {
         // Step 1: Allocate a database "Connection"
         conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:8888/studentdb", "myuser", "xxxx");  // <== check
         // database-url(protocol://hostname:port/defaultDatabase), user, password
         // Use IP address 127.0.0.1 if localhost does not work!
 
         // Step 2: Allocate a "Statement" from the "Connection"
         stmt = conn.createStatement();
 
         // Step 3 & 4: Query and process the query result
         String sqlStr;   // SQL string
 
         // Query the database
         sqlStr = "select id, name from class202";
         System.out.println(sqlStr);     // Echo the command for debugging
         ResultSet rset = stmt.executeQuery(sqlStr);  // Send to database server
         // Process the returned "ResultSet", i.e., resultant table.
         // The cursor initially points before the first record.
         while (rset.next()) {   // Move the cursor to the next record
            // Retrieve the fields for each record
            int id = rset.getInt("id");
            String name = rset.getString("name");
            System.out.printf("%4d %-30s%n", id, name);
         }
         rset.close();
 
      } catch (SQLException ex) {
         ex.printStackTrace();
      } finally {
         // Step 5: Always close the resources
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
      }
   }
}

We invoke the executeQuery() method (instead of executeUpdate() method) to run the SELECT command. The executeQuery() returns a ResultSet object, which stores the resultant table. The ResultSet is associated with a so-called cursor. The cursor initially points before the first record in the ResultSet. We use rset.next() method to advance the cursor to the next record, and rset.getXxx("columnName") to retrieve the field. rset.next() returns false if the cursor is pointing at the last record.

TRY:
  1. Select and display all the 3 columns. (Hints: retrieve the column via getFloat("gpa"), and modify your print statement to print the gpa.)
  2. Select records with names starting with letter 'K'. (Hints: name like 'K%')
  3. Select records with names having a letter 'J'. (Hints: name like '%J%')
  4. Select records with gpa ? 4.5. (Hints: gpa >= 4.5)
  5. Select records with names having a letter 'J' and gpa ? 4.5. (Hints: name like '%J%' AND gpa >= 4.5)

 

Read “Java Database Programming (JDBC) Basics” for more about Java Database programming.

(Skip Unless…) Read “Common Errors in JDBC Programming on MySQL“.

(For JDK prior to JDK 6) You need to explicitly load the MySQL driver in your program, before allocating a Connection.

try {
   // Load Database driver
   Class.forName("com.mysql.jdbc.Driver");         // for MySQL
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  // for MS Access
} catch (ClassNotFoundException ex) {
   ex.printStackTrace();
}

(For Advanced Users Only) JDK 7: JDK 7 introduces a new try-with-resources syntax, which automatically closes the opened resources in the try clause. The above example can be re-written as follow:

import java.sql.*;                 // Use JDBC package
public class MySQLJdbcTestJDK7 {   // Need JDK 7 to compile and run
   public static void main(String[] args) {
 
      try (
         // Step 1: Create a database "Connection"
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:8888/studentdb", "myuser", "xxxx");
               // Database-url(host, port, default database name), user, password
  
         // Step 2: Create a "Statement" from the "Connection"
         Statement stmt = conn.createStatement()
      ) {
         // Step 3 & 4: Query and Process the query result
         String sqlStr;   // SQL statement string
         int returnCode;  // return code of the database server running the SQL statement
 
         // Remove table "class202" from the current database "studentdb" 
         // (specified in the database-url)
         sqlStr = "drop table if exists class202";
         System.out.println(sqlStr);  // Echos the command for debugging
         returnCode = stmt.executeUpdate(sqlStr);  // Send to database server
         if (returnCode == 0) {  // Check the return code
            System.out.println("Table 'class202' dropped");
         }
         System.out.println();
 
         // Create table "class202" in the current database "studentdb"
         sqlStr = "create table class202 (id int, name varchar(50), gpa float)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         if (returnCode == 0) {
            System.out.println("Table 'class202' created");
         }
         System.out.println();
 
         // Insert a few records
         sqlStr = "insert into class202 values (3333, 'Kumar', 4.4)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println(returnCode + " row inserted");
         sqlStr = "insert into class202 values (4444, 'Kevin Jones', 4.6)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println(returnCode + " row inserted");
         sqlStr = "insert into class202 values (5555, 'Peter Jones', 4.3)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println(returnCode + " row inserted");
         System.out.println();
 
         // Query the database
         sqlStr = "select * from class202";
         System.out.println(sqlStr);  // Echos the command for debugging
         ResultSet rset = stmt.executeQuery(sqlStr); // Send to database server
         // Process the returned "ResultSet", i.e., resultant table.
         // A cursor initially points before the first record.
         while (rset.next()) {   // Move the cursor to the next record
            // Retrieve the fields from each record
            int id = rset.getInt("id");
            String name = rset.getString("name");
            System.out.printf("%4d %-30s%n", id, name);
         }
      } catch (SQLException ex) {
         ex.printStackTrace();
      }
      // Step 5: Close the resources - done by try-with-resources automatically
   }
}

(For Advanced Users Only) UTF-8 Support: To request for UTF-8 support, use the following database-URL:

jdbc:mysql://hostname:port/studentdb?useUnicode=yes&characterEncoding=UTF-8

Your Eclipse/NetBeans should also be configured to use UTF-8 encoding.

5.  Backup and Restore Databases

5.1  Backup via “mysqldump” Utility Program

You can use the "mysqldump" utility program to back up the entire server (all databases), selected databases, or selected tables of a database. The “mysqldump” program generates a SQL script that can later be executed to re-create the databases, tables and their contents.

For example, the following command backups the entire “studentdb” database to a SQL script “d:\myproject\backup_studentdb.sql“.

-- Change the current working directory to <MYSQL_HOME>\bin
D:\myproject\mysql\bin> mysqldump -u myuser -p --databases studentdb > "d:\myproject\backup_studentdb.sql"

Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to re-create the database and tables dumped.

Syntax
-- Dump selected databases with --databases option
Prompt> mysqldump -u username -p --databases database1Name [database2Name ...] > backupFile.sql
-- Dump all databases in the server with --all-databases option, except mysql.user table (for security)
Prompt> mysqldump -u root -p --all-databases --ignore-table=mysql.user > backupServer.sql
  
-- Dump all the tables of a particular database
Prompt> mysqldump -u username -p databaseName > backupFile.sql
-- Dump selected tables of a particular database
Prompt> mysqldump -u username -p databaseName table1Name [table2Name ...] > backupFile.sql

5.2  Restore via “source” command in a mysql client

You can restore from the backup by running the “source” command in an interactive client. For example, to restore the studentdb backup earlier:

-- Start and login to client
D:\myproject\mysql\bin> mysql -u username -p
......
-- Run the backup script to recreate the database
mysql> drop database if exists studentdb;
mysql> source d:/myproject/backup_studentdb.sql

6.  (For Advanced Users) NetBeans and MySQL

NetBeans provides direct support to MySQL server. You can use NetBeans as a GUI client to access a MySQL server, as well as an administrative tool (e.g., starting and stopping the server).

Read NetBeans’ article “Connecting to a MySQL Database” @ http://netbeans.org/kb/docs/ide/mysql.html.

Configuring NetBeans to Support MySQL

From NetBeans “Window” menu ? Select “Services”. The “Services” tab shall appear on the left panel.

  1. Right-click on the “Databases” node ? “Register MySQL Server”. (If you have already registered a MySQL server, you can right-click on Server node “MySQL Server at hostname:port” ? Properties, to modify its properties.)
  2. Select the “Basic Properties” tab, enter the hostname, port number, root user and password.
  3. Select the “Admin Properties” tab:
    1. Leave the “Path/URL to admin tool” empty.
    2. In “Path to start command”, enter “<MYSQL_HOME>\bin\mysqld.exe“; in the “Arguments”, enter “--console
    3. In “Path to stop command”, enter “<MYSQL_HOME>\bin\mysqladmin.exe“, in the “Arguments”, enter “-u root -ppassword shutdown“.
  4. A server nodeMySQL Server at hostname:port” appears.
Database Administration – Start/Stop the Server and Create Databases
  1. You can start the MySQL server by right-clicking on the server node ? select “start”. [There seems to be a problem here. If a “connection refused: connect” error occurs, enter the password again.]
  2. Once the MySQL server is started and connected, you can see the list of databases by expanding the MySQL server node. You can create a new database by right-clicking on it and choose “Create Database…”.
Create a new Connection

You need a connection to manipulate data. You can create multiple connections with different users and default databases.

  1. Right-click on the “Databases” ? “New Connection…” ? Select the driver “MySQL Connector/J” ? Next ? Enter hostname, port number, default database, a general username and password ? “Test Connection” (make sure that MySQL is started) ? Finish.
  2. A connection nodejdbc:mysql://hostname:port/defaultDatabase” appears.
Manipulating Data via a Connection
  1. Right-click on a connection node (e.g., “jdbc:mysql://hostname:port/defaultDatabase“) ? Choose “Connect” (if not connected, provided that the MySQL server has been started).
  2. You can expand the connection node to view all the databases.
  3. Expand an existing database. There are three sub-nodes “Tables”, “View” and “Procedures”. Right-click on the “Tables” to create table or execute command. Similarly, right-click on the “View” and “Procedures”.
  4. To view/manipulate the records in a table, right-click on the selected table ? You can choose to “View Data…”, “Execute Command…”, etc.
  5. You can right-click on the connection to “connect” or “disconnect” from the server.
Create a SQL Script and Run the Script

You can create a SQL script by right-clicking on a project ? New ? “SQL File”. You can run the script by right-clicking on the SQL script ? “Run File” ? Select an existing connection (or create a new connection) to run the script. You could also run a single statement (right-click on the statement ? Run Statement) or a selected group of statements (highlight the statements ? Right-click ? Run Selection).

7.  (For Advanced Users) MySQL GUI Tool – MySQL Workbench

“MySQL Workbench (GUI tool)” provides a graphical user interface for interacting with the MySQL server. To install:

  1. From http://www.mysql.com/downloads/, select “MySQL Workbench (GUI Tool), Generally Available Release: 5.2.xx”. Download the ZIP version WITHOUT installer (e.g., “mysql-workbench-gpl-5.2.xx-win32-noinstall.zip”).
  2. UNZIP the downloaded files (into “d:\myproject“).

MySQL Workbench supports three main functions:

  1. MySQL Development: Connect to existing databases and run SQL queries, scripts, edit data and manage database objects.
  2. MySQL Server Administration: Configure your database server, setup user accounts, browse status variables and server logs.
  3. Data Modeling: Create and manage database models, forward and reverse engineering, compare and synchronize database schemas, and report.

To launch the MySQL Workbench, run “MySQLWorkbench.exe“, which can be found under the installed directory.

SQL Development
  • Select “Open connection to start querying”, enter “127.0.0.1” or “localhost” as hostname; “8888” as port number; “myuser” as username; the password; and “studentdb” as the default schema (default database).
  • Enter a SQL statement, and push the “execute” button.
Server Administration
  • “New Server Instance” ? “Localhost” ? Next ? Enter “localhost” as hostname; “8888” as port number; “root” as username; the password; and leave the default schema (default database) empty. You may need to specify the MySQL installed directory and the my.ini configuration file.
Data Modeling

To create the database diagram for an existing database (reverse engineering), select “Create EER model from existing database”, …

Read the MySQL Workbench documentation, available at http://dev.mysql.com/doc/workbench/en/index.html.

MySQL Workbench graphical tool, in my opinion, is not mature and is quite hard to use. There are others famous web-based MySQL graphical tools, such as “SQL Buddy” and “phpMyAdmin”. Unfortunately, they are written in PHP and need to be run under a web server (such as Apache). They are often used in a so-called WAMP (Windows-Apache-MySQL-PHP) or LAMP (Lunix-Apache-MySQL-PHP) environment.

NetBeans also provides a GUI interface to MySQL Server (described above).

8.  (For Advanced Users) Running MySQL as a Windows Service

In a production environment, it is more convenient to run MySQL as a Windows “service”. A Windows service is a background process, which does not interact with the users. It is called daemon in Unix. This is because a service can start automatically whenever the system is started, and re-start automatically after an unexpected interruption.

To install MySQL as a service, start a CMD shell (with administrator right) and run:

<MYSQL_HOME>\bin> mysqld --install
Service successfully installed.

A MySQL service named “mysql” would be installed and will start automatically whenever the system is started. Check “Control Panel” ? “Administrative Tools” ? “Services” ? Service “mysql“.

You can start the MySQL service via the “Control Panel” ? “Administrator Tools” ? “Services” ? Service “mysql” ? “Start”; or issue the following command from a CMD shell:

<MYSQL_HOME>\bin> net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

You can stop MySQL service via the “Control Panel”; or issue the following command:

<MYSQL_HOME>\bin> net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.

To uninstall MySQL service, use:

<MYSQL_HOME>\bin> mysqld --remove
Service successfully removed.

Link to MySQL References & Resources

 

Apache Tomcat 7

TABLE OF CONTENTS (HIDE)

1.  Introduction
1.1  Web Application (Webapp)
1.2  Hypertext Transfer Protocol (HTTP)
1.3  Apache Tomcat HTTP Server
2.  How to Install Tomcat 7 and Get Started with Java Servlet Programming
2.1  STEP 0: Read the Tomcat Documentation
2.2  STEP 1: Download and Install Tomcat
2.3  STEP 2: Create an Environment Variable JAVA_HOME
2.4  STEP 3: Configure Tomcat Server
2.5  STEP 4: Start Tomcat Server
2.6  STEP 5: Develop and Deploy a WebApp
2.7  STEP 6: Write a “Hello-world” Java Servlet
2.8  STEP 7: Write a Database Servlet
2.9  (Advanced) Deploying Servlet using @WebServlet (Servlet 3.0 on Tomcat 7)
3.  How to Debug?

 

 

This tutorial can be completed in a 3-hour session, with guidance from instructor.

This installation and configuration guide is applicable to Tomcat 7, and possibly the earlier versions.

(EE3072 Students) This guide is applicable to “IM3072”, but NOT applicable to “EE3072”. For EE3072, please read “Tomcat 6 – How to Install and Configure“.

1.  Introduction

1.1  Web Application (Webapp)

A web application (or webapp), unlike standalone application, runs over the Internet. Examples of web applications are google, amazon, ebay, facebook and twitter. A webapp is typically a 3-tier (or multi-tier) client-server application, typically involving a database.

Most of the webapps run on the HTTP application protocol, with browser as the client to access an HTTP server.

A web database application requires five components, as illustrated below:

  1. HTTP Server: E.g., Apache HTTP Server, Apache Tomcat HTTP Server, Microsoft IIS, and etc.
  2. HTTP Client (or Web Browser): E.g., MSIE, FireFox, Chrome, and etc.
  3. Database: E.g., MySQL, Oracle, DB2, Infomix, MS SQL Server, MS Access, and etc.
  4. Client-Side Programs: could be written in HTML Form, JavaScript, VBScript, Flash, and etc.
  5. Server-Side Programs: could be written in Java Servlet/JSP, ASP, PHP, and etc.

 

A user, via a web browser, issue a URL to an HTTP server to start a webapp. The webapp first downloads a client-side program (such as an HTML form) into the browser. The user fills up the query criteria in the form. The client-side program sends the query parameters to a server-side program, which queries the database and returns the query result to the client. The client-side program displays the result on the browser.

1.2  Hypertext Transfer Protocol (HTTP)

HTTP is an asynchronous request-response application-layer protocol. A client sends a request message to the server. The server returns a response message to the client. The syntax of the message is defined in the HTTP specification.

.3  Apache Tomcat HTTP Server

Apache Tomcat is a Java-capable HTTP server, which could execute special Java programs known as Java Servlet and Java Server Pages (JSP). It is the official Reference Implementation (RI) for Java Servlets and JavaServer Pages (JSP) technologies. Tomcat is an open-source project, under the “Apache Software Foundation” (which also provides the famous open-source industrial-strength Apache HTTP Server). The mother site for Tomcat is http://tomcat.apache.org. Alternatively, you can find tomcat via the Apache mother site @ http://www.apache.org.

2.  How to Install Tomcat 7 and Get Started with Java Servlet Programming

2.1  STEP 0: Read the Tomcat Documentation

Tomcat’s documentation is available at Tomcat mother site @ http://tomcat.apache.org. Select “Documentation” ? “Tomcat 7.0”.

2.2  STEP 1: Download and Install Tomcat

  1. From http://tomcat.apache.org ? Select “Downloads” ? “Tomcat 7.0” ? “7.0.xx” (where xx is the latest upgrade number) ? “Binary Distributions” ? “Core” ? “zip” ? “apache-tomcat-7.0.xx.zip“.
  2. UNZIP into a directory of your choice. DO NOT unzip onto the Desktop (because its path is hard to locate). I suggest using “d:\myproject“. Tomcat will be unzipped into directory “d:\myproject\apache-tomcat-7.0.xx“. For ease of use, we shall shorten and rename this directory to “d:\myproject\tomcat“. Take note of Your Tomcat Installed Directory. Hereafter, I shall refer to the Tomcat installed directory as <TOMCAT_HOME>.

I recommend “zip” version, as you could simply delete the entire directory when Tomcat is no longer needed (without running any un-installer). You are free to move or rename the Tomcat’s installed directory. You can install (unzip) multiple copies of Tomcat in the same machine.

(For Mac Users) Read “How to Install Tomcat 7 on Mac” Step 1.

2.3  STEP 2: Create an Environment Variable JAVA_HOME

You need to create an environment variable called “JAVA_HOME” and set it to your JDK installed directory.

  1. First, take note of your JDK installed directory (the default is “c:\program files\java\jdk1.7.0_xx”).
  2. Start a CMD shell, and issue the command “SET JAVA_HOME” to check if variable JAVA_HOMEis set:
    prompt> set JAVA_HOME
    Environment variable JAVA_HOME not defined

    If JAVA_HOME is set (by other applications), check if it is set to your JDK installed directory.

  3. To set/change an environment variable in Windows 2000/XP/Vista/7: Click “Start” button ? “Control Panel” ? “System” ? (Vista/7) “Advanced system settings” ? Switch to “Advanced” tab ? “Environment Variables” ? “System Variables” (or “User Variables” for the current user only) ? “New” (or “Edit” for modification) ? In “Variable Name” field, enter “JAVA_HOME” ? In “Variable Value” field, enter your JDK installed directory (e.g., “c:\program file\java\jdk1.7.0“) – I suggest that you copy and paste the directory name to avoid typo error!
  4. To verify, RE-STARTa CMD shell and issue:
    prompt> set JAVA_HOME
    JAVA_HOME=c:\program file\java\jdk1.7.0   <== CHECK! YOUR JDK installed directory

(For Mac Users) Take note of your JDK installed directory (most likely under /usr/local). Start a Terminal and run:

$ echo $JAVA_HOME                       // Check if JAVA_HOME is already set

// If JAVA_HOME is not set, then
$ java_home=your_jdk_install_directory  // Set environment variable java_home
$ export java_home                      // Make this variable available to all applications

To make permanent changes, store the above command in “.bash_profile” in your home directory (denoted as '~'), and run “source $HOME/.bash_profile” to refresh.

2.4  STEP 3: Configure Tomcat Server

(Note) Programmers need to view the file extension (such as .txt, .ini). To display the file extension in Windows, run “Control Panel” ? “Folder Options” ? Select tab “View” ? Uncheck “Hide extensions for known file types”.

The Tomcat configuration files are located in the “conf” sub-directory of your Tomcat installed directory (e.g. “d:\myproject\tomcat\conf“). There are 3 configuration files:

  1. server.xml
  2. web.xml
  3. context.xml

Make a backup of the configuration files before you proceed.

Step 3(a) “server.xml” – Set the TCP Port Number

Use a text editor (e.g., NotePad++, TextPad or NotePad) to open the configuration file “server.xml“, under the “conf” sub-directory of Tomcat installed directory (e.g. “d:\myproject\tomcat\conf“).

The default TCP port number configured in Tomcat is 8080, you may choose any number between 1024 and 65535, which is not used by an existing application. We shall use port 9999 in this article. (For production server, you should use port 80, which is pre-assigned to HTTP server as the default port number.)

<!-- Define a non-SSL HTTP/1.1 Connector on port 8080 -->
<Connector port="9999" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" />
Step 3(b) “web.xml” – Enabling Directory Listing

Again, use a text editor to open the configuration file “web.xml“, under the “conf” sub-directory of Tomcat installed directory.

We shall enable directory listing by changing “listings” from “false” to “true” for the “default” servlet, as shown in red.

<!-- The default servlet for all web applications, that serves static     -->
<!-- resources.  It processes all requests that are not mapped to other   -->
<!-- servlets with servlet mappings.                                      -->
<servlet>
  <servlet-name>default</servlet-name>
  <servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
  <init-param>
    <param-name>debug</param-name>
    <param-value>0</param-value>
  </init-param>
  <init-param>
    <param-name>listings</param-name>
    <param-value>true</param-value>
  </init-param>
  <load-on-startup>1</load-on-startup>
</servlet>
Step 3(c) “context.xml” – Enabling Automatic Reload

Again, use a text editor to open the configuration file “context.xml“, under the “conf” sub-directory of Tomcat installed directory.

We shall add the attribute reloadable="true" to the <Context> element to enable automatic reload after code changes, as shown in red.

<Context reloadable="true">
   ......
</Context>

2.5  STEP 4: Start Tomcat Server

The Tomcat’s executable programs are kept in the “bin” sub-directory of the Tomcat installed directory (e.g., “d:\myproject\tomcat\bin“).

Step 4(a) Start Server

Launch a CMD shell. Set the current directory to “<TOMCAT_HOME>\bin“, and run “startup.bat” as follows:

// Change the current directory to Tomcat's "bin"
// Assume that Tomcat is installed in "d:\myproject\tomcat"
prompt> d:                     // Change the current drive
D:\...> cd \                   // Change Directory to ROOT directory
D:\> cd \myproject\tomcat\bin  // Change Directory to YOUR Tomcat's "bin" directory
 
// Start Tomcat Server
D:\myproject\tomcat\bin> startup

A new Tomcat console window appears. Study the messages on the console. Look out for the Tomcat’s port number (double check that Tomcat is running on port 9999). Future error messages will be send to this console. System.out.println() issued by your Java servlets will also be sent to this console.

......
INFO: Initializing Coyote HTTP/1.1 on http-9999
......
INFO: Starting Coyote HTTP/1.1 on http-9999
......
INFO: Server startup in 699 ms

(For Mac Users) Read “How to Install Tomcat 7 on Mac” Step 2.

Step 4(b) Start a Client to Access the Server

Start a browser (as a HTTP client), and issue URL “http://localhost:9999” to access the Tomcat server’s welcome page. The hostname “localhost” (with IP address of 127.0.0.1) is meant for local loop-back testing. For users on the other machines over the net, they have to use the server’s IP address or DNS domain name or hostname in the format of “http://serverHostnameOrIPAddress:9999“.

 

 

Step 4(c) Shutdown Server

You can shutdown the server by running “<TOMCAT_HOME>\bin\shutdown.bat“. If Tomcat does not respond to the shutdown command, you could terminate Tomcat by pushing control-c (or control-break) on the Tomcat’s console. DO NOT kill the cat by pushing the “close” button.

(Skip Unless …) Cannot Start Tomcat
  • Check the Error Messages on Tomcat’s Console. Most of the error messages have tens of lines. You need to scroll up slowly from the last message to look for the first-line of the error messages.
  • Check the Tomcat’s log files, located at "<TOMCAT_HOME>\logs“. “catalina.yyyy-mm-dd.log” shows the Tomcat’s startup messages. Also check the “localhost.yyyy-mm-dd.log“.

Read “Common Errors in Starting Tomcat after Installation“.

2.6  STEP 5: Develop and Deploy a WebApp

Step 5(a) Create the Directory Structure for your WebApp

 

First of all, choose a name for your webapp. Let us call it “hello“. Navigate to Tomcat’s “webapps” sub-directory, and create the following directory structure for you webapp “hello“, as illustrated:

  1. Under Tomcat’s “webapps“, create your web application root directory “hello” (i.e., “<TOMCAT_HOME>\webapps\hello“).
  2. Under “hello“, create a sub-directory “WEB-INF” (case sensitive, a “dash” not an underscore) (i.e., “<TOMCAT_HOME>\webapps\hello\WEB-INF“).
  3. Under “WEB-INF“, create a sub-directory “classes” (case sensitive, plural) (i.e., “<TOMCAT_HOME>\webapps\hello\WEB-INF\classes“).

You need to keep your web resources (e.g., HTMLs, CSSs, images, scripts, servlets, JSPs) in the proper directories:

  • hello“: The is called the context root (or document base directory) of your web application. You should keep all your HTML files and resources visible to the web users (e.g., CSSs, images, scripts, JSPs) under this context root.
  • hello\WEB-INF“: This directory, although under the context root, is not visible to the web users. This is where you keep your application’s configuration files “web.xml“.
  • hello\WEB-INF\classes“: This is where you keep all the Java classes such as servlet class-files.

You should RE-START your Tomcat server. Check the Tomcat’s console to confirm that “hello” application has been properly depolyed:

......
INFO: Deploying web application directory ...\hello
......

You can issue the following URL to access the web application “hello“:

http://localhost:9999/hello

You should see the directory listing of the directory “<TOMCAT_HOME>\webapps\hello“, which shall be empty.

Step 5(b) Write a Welcome Page

Create the following HTML page and save as “HelloHome.html” in your application’s root directory “hello“.

1
2
3
4
5
6
<html>
  <head><title>My Home Page</title></head>
  <body>
    <h1>My Name is so and so. This is my HOME.</h1>
  </body>
</html>

You can browse this page by issuing this URL:

You can browse this page by issuing this URL:

http://localhost:9999/hello/HelloHome.html

Alternatively, you can issue an URL to your web application root “hello“:

http://localhost:9999/hello

T

Now, the server will redirect the directory request to “index.html“, if the root directory contains an “index.html“, instead of serving the directory listing.

You can check out the home page of your peers by issuing (or access your own server from another machine):

http://YourPeerHostnameOrIPAddress:9999/hello
http://YourPeerHostnameOrIPAddress:9999/hello/HelloHome.html
http://YourPeerHostnameOrIPAddress:9999/hello/index.html

with a valid “YourPeerHostnameOrIPAddress“, provided that your peer has started his/her web server. You can use command such as “ipconfig“, “winipcfg“, “ping” to find your IP address.

 

(Skip Unless…) The likely errors are “Unable to Connect”, “Internet Explorer cannot display the web page”, and “404 File Not Found”. Read “How to Debug” section.

2.7  STEP 6: Write a “Hello-world” Java Servlet

A servlet is Java program that runs inside a Java-capable HTTP Server, such as Apache Tomcat. A web user invokes a servlet by issuing an appropriate URL from a web browser (or HTTP client).

Before you proceed, I shall assume that you are familiar with Java Programming and have installed the followings:

  1. JDK (Read “How to install JDK and Get Started“).
  2. A programming text editor, such as TextPad or Notepad++ (Read “Programming Text Editor“); or a Java IDE such as Eclipse or NetBeans (Read “How to Install Eclipse” or “How to Install NetBeans“).
Step 6(a) Install Servlet API Library

Before we can write our first servlet, we need to install the Servlet API. Servlet API is not part of JDK (but belongs to Java EE). Tomcat also includes a copy of Servlets API.

COPY the Tomcat’s Servlet API JAR-file located at “<TOMCAT_HOME>\lib\servlet-api.jar“, (e.g., “d:\myproject\tomcat\lib\servlet-api.jar“) into JDK’s extension directory at “<JAVA_HOME>\jre\lib\ext“, (e.g., “c:\program files\java\jdk1.7.0\jre\lib\ext“).

(For Advanced Users Only) Alternatively, you could include the Servlet API JAR-file in the CLASSPATH. Open “Control Panel” ? System ? (Vista/7 only) Advanced system settings ? Switch to “Advanced” tab ? Environment variables ? Choose “System Variables” (for all users in this system) or “User Variables” (for this login user only) ? Choose “New” or “Edit”? In “Variable Name”, enter “classpath” ? In “Variable Value”, enter “.;path-to\servlet-api.jar“, where “path-to” includes the drive letter and path of the Servlet API jar-file “servlet-api.jar“. You can also compile your program using “javac -cp .;path-to\servlet-api.jar ServletName“.

Step 6(b) Write a “Hello-world” Java Servlet

A Java servlet is a Java program that runs inside a HTTP server. A web user invokes a servlet by issuing a URL from a browser (or HTTP client).

In this example, we are going to write a Java servlet called HelloServlet, which says “Hello, world!”. We will then write a configuration such that web users can invoke this servlet by issuing URL http://hostname:port/hello/sayhello from the browser, as illustrated:

Write the following source codes called “HelloServlet.java” and save it under your application “classes” directory (i.e., “<TOMCAT_HOME>\webapps\hello\WEB-INF\classes\HelloServlet.java“). Compile the source into “HelloServlet.class“. This servlet says “Hello”, echos some request information, and prints a random number upon each request.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
// To save as "<TOMCAT_HOME>\webapps\hello\WEB-INF\classes\HelloServlet.java"
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class HelloServlet extends HttpServlet {
   @Override
   public void doGet(HttpServletRequest request, HttpServletResponse response)
         throws IOException, ServletException {
 
      // Set the response MIME type of the response message
      response.setContentType("text/html");
      // Allocate a output writer to write the response message into the network socket
      PrintWriter out = response.getWriter();
 
      // Write the response message, in an HTML page
      try {
         out.println("<html>");
         out.println("<head><title>Hello, World</title></head>");
         out.println("<body>");
         out.println("<h1>Hello, world!</h1>");  // says Hello
         // Echo client's request information
         out.println("<p>Request URI: " + request.getRequestURI() + "</p>");
         out.println("<p>Protocol: " + request.getProtocol() + "</p>");
         out.println("<p>PathInfo: " + request.getPathInfo() + "</p>");
         out.println("<p>Remote Address: " + request.getRemoteAddr() + "</p>");
         // Generate a random number upon each request
         out.println("<p>A Random Number: <strong>" + Math.random() + "</strong></p>");
         out.println("</body></html>");
      } finally {
         out.close();  // Always close the output writer
      }
   }
}

 

(Skip Unless…) Read “Common Errors in Compiling Java Servlet“.

Step 6(c) Configure Servlet’s Request URL in “webapps\hello\WEB-INF\web.xml

A web user invokes a servlet, which is kept in the web server, by issuing a request URL from the browser. We need to configure this request URL for our HelloServlet.

Create the following configuration file called “web.xml“, and save it under “webapps\hello\WEB-INF” (i.e., “<TOMCAT_HOME>\webapps\hello\WEB-INF\web.xml“).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app version="3.0"
  xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
 
   <!-- To save as "hello\WEB-INF\web.xml" -->
 
   <servlet>
      <servlet-name>HelloWorld</servlet-name>
      <servlet-class>HelloServlet</servlet-class>
   </servlet>
 
   <!-- Note: All <servlet> elements MUST be grouped together and placed IN FRONT of the <servlet-mapping> elements -->
 
   <servlet-mapping>
      <servlet-name>HelloWorld</servlet-name>
      <url-pattern>/sayhello</url-pattern>
   </servlet-mapping>
</web-app>

In the above configuration, a servlet having a class file “HelloServlet.class” is mapped to request URL “/sayhello” (via an arbitrary servlet-nameHelloWorld“), under this web application “hello“. In other words, the complete request URL for this servlet is “http://hostname:port/hello/sayhello“.

This configuration file, saved under your web application “hello“, is applicable only to this particular web application “hello“.

Restart your Tomcat server.

IMPORTANT: For EACH servlet, you need to write a pair of <servlet> and <servlet-mapping> elements with a common <servlet-name>. Take note that all the <servlet> elements MUST be grouped together and placed IN FRONT of the <servlet-mapping> elements.

Step 6(d) Invoke the Servlet

To run this servlet, start a browser, and issue the request URL configured earlier:

http://localhost:9999/hello/sayhello

You shall see the output of the servlet displayed in your web browser.

Refresh the browser, you shall see a new random number upon each refresh. In other word, the doGet() method of the servlet runs once per request.

Try “View Source” to look at the output received by the web users. Take note that the web users receive only the output of the servlet (generated via the out.println() statements). They have no access to the servlet programs (which may contain confidential information).

<html>
<head><title>Hello, World</title></head>
<body>
<h1>Hello, world!</h1>
<p>Request URI: /hello/sayhello</p>
<p>Protocol: HTTP/1.1</p>
<p>PathInfo: null</p>
<p>Remote Address: 127.0.0.1</p>
<p>A Random Number: <strong>0.3523682325749493</strong></p>
</body>
</html>

 

(Skip Unless…) The likely errors are “404 File Not Found” and “500 Internal Server Error”. Read “How to debug” Section.

2.8  STEP 7: Write a Database Servlet

This section assumes that you are familiar with Java database programming and MySQL database server. Otherwise, read “How to Install MySQL and Get Started“.

Step 7(a) Setup a Database on MySQL

Start your MySQL server. Take note of the server’s port number. I shall assume that the MySQL server is running on port 8888 (whereas the Tomcat is running on port 9999).

D:\myproject\mysql\bin> mysqld --console

Start a MySQL client. I shall assume that there is a user called “myuser” with password “xxxx“.

D:\myproject\mysql\bin> mysql -u myuser -p

Run the following SQL statements to create a database called “ebookshop“, with a table called “books” with 5 columns: id, title, author, price, qty.

create database if not exists ebookshop;

use ebookshop;

drop table if exists books;
create table books (
   id     int,
   title  varchar(50),
   author varchar(50),
   price  float,
   qty    int,
   primary key (id));

insert into books values (1001, 'Java for dummies', 'Tan Ah Teck', 11.11, 11);
insert into books values (1002, 'More Java for dummies', 'Tan Ah Teck', 22.22, 22);
insert into books values (1003, 'More Java for more dummies', 'Mohammad Ali', 33.33, 33);
insert into books values (1004, 'A Cup of Java', 'Kumar', 55.55, 55);
insert into books values (1005, 'A Teaspoon of Java', 'Kevin Jones', 66.66, 66);

select * from books;
Step 7(b) Install MySQL JDBC Driver

You need to download MySQL JDBC driver if you have not done so. Read “Installing the MySQL JDBC Driver“.

(For Advanced User Only) You could also place the MySQL driver jar-file “mysql-connector-java-5.1.xx-bin.jar” in Tomcat’s “lib” directory.

Step 7(c) Write a Client-side HTML Form

Let’s write an HTML script to create a query form with 3 checkboxes and a submit button, as illustrated below.  Save the HTML file as “querybook.html” in your application root directory “<TOMCAT_HOME>\webapps\hello”.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<html>
<head>
  <title>Yet Another Bookshop</title>
</head>
<body>
  <h2>Yet Another Bookshop</h2>
  <form method="get" action="http://localhost:9999/hello/doquery">
    <b>Choose an author:</b>
    <input type="checkbox" name="author" value="Tan Ah Teck">Ah Teck
    <input type="checkbox" name="author" value="Mohammad Ali">Ali
    <input type="checkbox" name="author" value="Kumar">Kumar
    <input type="submit" value="Search">
  </form>
</body>
</html>

You can browse the HTML page by issuing the following URL:

http://localhost:9999/hello/querybook.html

Check a box (e.g., “Tan Ah Teck”) and click the “Search” button.  An HTTP GET request will be issued to the URL specified in the <form>‘s “action” attribute.  Observe the URL of the HTTP GET request:

http://localhost:9999/hello/doquery?author=Tan+Ah+Teck

The request consists of two part: a URL corresponding to the “action” attribute of the <form> tag, and the “name=value” pair extracted from the <input> tag, separated by a '?'. Take note that blanks are replaced by '+' (or %20), because blanks are not allowed in the URL.

If you check two boxes (e.g., “Tan Ah Teck” and “Mohammad Ali”), you will get this URL, which has two “name=value” pairs separated by an '&'.

http://localhost:9999/hello/doquery?author=Tan+Ah+Teck&author=Mohammad+Ali

You are expected to get an error “404 File Not Found”, as you have yet to write the server-side program.

Step 7(d) Write the Server-side Database Query Servlet

The next step is to write a Java servlet, which responses to the client’s request by querying the database and returns the query results.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
// To save as "<TOMCAT_HOME>\webapps\hello\WEB-INF\classes\QueryServlet.java".
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class QueryServlet extends HttpServlet {  // JDK 6 and above only
 
   // The doGet() runs once per HTTP GET request to this servlet.
   @Override
   public void doGet(HttpServletRequest request, HttpServletResponse response)
               throws ServletException, IOException {
      // Set the MIME type for the response message
      response.setContentType("text/html");
      // Get a output writer to write the response message into the network socket
      PrintWriter out = response.getWriter();
 
      Connection conn = null;
      Statement stmt = null;
      try {
         // Step 1: Allocate a database Connection object
         conn = DriverManager.getConnection( "jdbc:mysql://localhost:8888/ebookshop", "myuser", "xxxx"); // <== Check!
            // database-URL(hostname, port, default database), username, password
 
         // Step 2: Allocate a Statement object within the Connection
         stmt = conn.createStatement();
 
         // Step 3: Execute a SQL SELECT query
         String sqlStr = "select * from books where author = " + "'" + request.getParameter("author") + "'" + " and qty > 0 order by price desc";
 
         // Print an HTML page as the output of the query
         out.println("<html><head><title>Query Response</title></head><body>");
         out.println("<h3>Thank you for your query.</h3>");
         out.println("<p>You query is: " + sqlStr + "</p>"); // Echo for debugging
         ResultSet rset = stmt.executeQuery(sqlStr);  // Send the query to the server
 
         // Step 4: Process the query result set
         int count = 0;
         while(rset.next()) {
            // Print a paragraph <p>...</p> for each record
            out.println("<p>" + rset.getString("author")
                 + ", " + rset.getString("title")
                 + ", $" + rset.getDouble("price") + "</p>");
            count++;
         }
         out.println("<p>==== " + count + " records found =====</p>");
         out.println("</body></html>");
     } catch (SQLException ex) {
        ex.printStackTrace();
     } finally {
        out.close();  // Close the output writer
        try {
           // Step 5: Close the resources
           if (stmt != null) stmt.close();
           if (conn != null) conn.close();
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
     }
   }
}
Step 7(e) Configure the Request URL for the Servlet

Open the configuration file “web.xml” of your application “hello” that you have created earlier for the HelloServlet, i.e., “<TOMCAT_HOME>\webapps\hello\WEB-INF\web.xml“. Add the lines that are shown in red at the correct locations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app version="3.0"
  xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
 
   <!-- To save as "hello\WEB-INF\web.xml" -->
 
   <servlet>
      <servlet-name>HelloWorld</servlet-name>
      <servlet-class>HelloServlet</servlet-class>
   </servlet>
 
   <servlet>
      <servlet-name>UserQuery</servlet-name>
      <servlet-class>QueryServlet</servlet-class>
   </servlet>
 
   <!-- Note: All <servlet> elements MUST be grouped together and placed IN FRONT of the <servlet-mapping> elements -->
 
   <servlet-mapping>
      <servlet-name>HelloWorld</servlet-name>
      <url-pattern>/sayhello</url-pattern>
   </servlet-mapping>
 
   <servlet-mapping>
      <servlet-name>UserQuery</servlet-name>
      <url-pattern>/doquery</url-pattern>
   </servlet-mapping>
</web-app>

The above lines configure the following URL to invoke QueryServlet:

http://localhost:9999/hello/doquery
Step 7(f) Invoke the Servlet from the Client-Side Form

Issue the following URL to browse the HMTL form “querybook.html” that you have created earlier:

http://localhost:9999/hello/querybook.html

Select an author (e.g., “Tan Ah Teck”) and click the submit button, which activates the following URL coded in the <form>‘s “action” attribute, together with the name=value pair:

http://localhost:9999/hello/doquery?author=Tan+Ah+Teck

This URL “/doquery” triggers QueryServlet. The QueryServlet retrieves the name=value pair of “author=Tan+Ah+Teck“. Inside the QueryServlet, the method request.getParameter("author") returns “Tan Ah Teck“, which is inserted into the SQL SELECT command to query the database. The processed query result is then written to the client as an HTML document.

 

(Skip Unless…) The likely errors are “404 File Not Found” and “500 Internal Server Error”. Read “How to debug” Section.

2.9  (Advanced) Deploying Servlet using @WebServlet (Servlet 3.0 on Tomcat 7)

Servlet 3.0, which is supported by Tomcat 7, introduces the @WebServlet annotation, which greatly simplifies the deployment of servlets. You no longer need to write the deployment descriptor in “web.xml“. Instead, you can use the @WebServlet annotation to specify the url mapping.

For example, let us write a new servlet called AnotherHelloServlet.java, by modifying the HelloServlet.java written earlier, with url mapping of “sayhi“.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// To save as "<TOMCAT_HOME>\webapps\hello\WEB-INF\classes\AnotherHelloServlet.java"
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
 
@WebServlet("/sayhi")
public class AnotherHelloServlet extends HttpServlet {
   @Override
   public void doGet(HttpServletRequest request, HttpServletResponse response)
         throws IOException, ServletException {
 
      // Set the response MIME type
      response.setContentType("text/html;charset=UTF-8");
      // Allocate a output writer to write the response message into the network socket
      PrintWriter out = response.getWriter();
 
      // Write the response message, in an HTML page
      try {
         out.println("<html>");
         out.println("<head><title>Hello, World</title></head>");
         out.println("<body>");
         out.println("<h1>Hello world, again!</h1>");  // says Hello
         // Echo client's request information
         out.println("<p>Request URI: " + request.getRequestURI() + "</p>");
         out.println("<p>Protocol: " + request.getProtocol() + "</p>");
         out.println("<p>PathInfo: " + request.getPathInfo() + "</p>");
         out.println("<p>Remote Address: " + request.getRemoteAddr() + "</p>");
         // Generate a random number upon each request
         out.println("<p>A Random Number: <strong>" + Math.random() + "</strong></p>");
         out.println("</body></html>");
      } finally {
         out.close();  // Always close the output writer
      }
   }
}

In Line 7, the annotation @WebServlet("/sayhi") is used to declare the url mapping for this servlet, i.e., http://localhost:9999/hello/sayhi. There is no need to provide any more configuration in “web.xml“!

3.  How to Debug?

“Everything that can possibly go wrong will go wrong.”

Always…
  1. Refresh your browser using Cntl-F5 (instead of refresh button or simply F5) to get a fresh copy, instead of from the cache.
  2. You may re-start your Tomcat. You may also re-start your browser to clear the cache.
  3. Check your spelling! Always assume that all programs are case-sensitive. Don’t type, copy and paste if possible!
  4. If Tomcat can be started, but you cannot run your servlet. Goto the Tomcat’s home page (http://localhost:9999), and try to run the “servlets examples” to ensure that Tomcat is properly installed.
  5. Try accessing your application root, e.g., http://localhost:9999/hello. You shall see the directory listing (if you have enabled the directory listing option in “web.xml” in Step 3(b)).
  6. and MOST IMPORTANTLY – Find the ERROR MESSAGE!!!
    1. Check the Error Messages on Tomcat’s Console. Most of the error messages have tens of lines. You need to scroll up slowly from the last message to look for the first-line of the error messages.
    2. Check the Tomcat’s log files, located at “<TOMCAT_HOME>\logs” directory. “catalina.yyyy-mm-dd.log” shows the Tomcat’s startup messages. Also check the “localhost.yyyy-mm-dd.log“.
  7. If things were running fine until the lightning strikes, ask yourself “what have I changes?”
(Firefox) Unable to Connect
(IE) Internet Explorer cannot display the webpage
(Chrome) Oops! Google Chrome could not connect to …

Cause: You are simply not connecting to your Tomcat.

Solution:

  1. Check if your Tomcat server has been started?
  2. Check the hostname and port number, separated by a colon ':', of your URL (http://localhost:9999/...).
Error 404 File Not Found

Cause: You have connected to your Tomcat. But Tomcat server cannot find the HTML file or Servlet that your requested.

Solution:

  1. Check your spelling! The path is case-sensitive!
  2. For HTML file with URL http://localhost:9999/xxxx/filename.html:
    1. Open Tomcat’s “webapps” directory, check if sub-directory “xxxx” exists. It is case-sensitive.
    2. Open the “xxxx” directory, check if “filename.html” exists.
  3. For Servlet with URL http://localhost:9999/xxxx/servletURL:
    1. Check the Tomcat’s console for error message. Your application cannot be deployed if you make a mistake in editing “web.xml“, which triggered many error messages.
    2. Check the Tomcat console to make sure that your application has been deployed.
    3. Open Tomcat’s “webapps” directory, check if sub-directory “xxxx” exists.
    4. Open the “xxxx” directory, check if sub-sub-directory “WEB-INF” (uppercase with a dash) exists.
    5. Open the “WEB-INF“, check if sub-sub-sub directory “classes” (lowercase, plural) exists.
    6. Open the configuration file “WEB-INF\web.xml“:
      1. Check that servletURL is defined in a <servlet-mapping> tag. Take note of the name in <servlet-name> tag.
      2. Based on the name noted, look for the matching <servlet-class> tag. Take note of the ServletClassname.
      3. Open “WEB-INF\classes“, check if “ServletClassname.class” that you noted exists (Note: It is “.class“, and NOT “.java“. You need to compile the “.java” to get the “.class“.)
Error 500 Internal Server Error

Error 500 should have triggered many error message in the Tomcat’s console. Go to the Tomcat’s console, find the error message. The error message spans tens of lines. You need to scroll up slowly to look for the first line of the error message. The error message should tell you the cuase of this error, e.g. SQL syntax error, wrong user/password, etc.

For database servlet, you may check the error messages at “Common Errors in JDBC Programming“.

  • For “No suitable driver found” (Windows) or NullPointerException (Mac and Linux): Read Step 7(b) again, again, and again.
Error 505: GET (or POST) Method Not Supported

Check you servlet to make sure that you have defined a doGet() (or doPost()) method.

Others

This article is meant for advanced programmers who is interested to know more about Tomcat; or using Tomcat for production. For novices, read “How to Install and Get Started with Tomcat“.

The authoritative source of information on Tomcat is the Tomcat’s documentation, available under Tomcat’s “webapps\docs” directory. You may also refer to the Java Servlet, JSP and JSF specifications, as Tomcat is the Reference Implementation for these technologies.

I shall assume that Tomcat is installed in d:\myproject\tomcat, and shall denote this directory as $CATALINA_HOME (Unix-style) or %CATALINA_HOME% (Windows-style). Catalina is the codename for Tomcat 5 and above.

Tomcat’s Architecture and the main configuration file “server.xml”

Tomcat is an HTTP server. Tomcat is also a container that can execute Java Servlet, and converting JavaServer Pages (JSP) and JavaServerFaces (JSF) to Java Servlet. Tomcat employs a hierarchical and modular architecture as illustrated:

 

Tomcat’s main configuration file is the “server.xml“, kept under the $CATALINA_HOME\conf directory. The default “server.xml” is reproduced as follows (after removing the comments and some minor touch ups):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?xml version='1.0' encoding='utf-8'?>
<Server port="8005" shutdown="SHUTDOWN">
  <Listener className="org.apache.catalina.core.JasperListener" />
  <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" />
  <Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener" />
  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />
  <Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener" />
 
  <GlobalNamingResources>
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />
  </GlobalNamingResources>
 
  <Service name="Catalina">
    <Connector port="8080" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />
    <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />
 
    <Engine name="Catalina" defaultHost="localhost">
 
      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
               resourceName="UserDatabase"/>
      </Realm>
 
      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log." suffix=".txt"
               pattern="%h %l %u %t &quot;%r&quot; %s %b" />
      </Host>
    </Engine>
  </Service>
</Server>

Server

Server (Line 2) is top component, representing an instance of Tomcat.It can contains one or more Services, each with its own Engines and Connectors.

<Server port="8005" shutdown="SHUTDOWN"> ...... </Server>

Listeners

The Server contains several Listeners (Lines 3-7). A Listener listens and responses to specific events.

  • The JasperListener enables the JasperJSP engine, and is responsible for re-compiling the JSP pages that have been updated.
    <Listener className="org.apache.catalina.core.JasperListener" />
  • The GlobalResourcesLifecycleListenerenables the global resources, and makes possible the use of JNDI for accessing resources such as databases.
    <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />

Global Naming Resources

The <GlobalNamingResources> element (Line 9-15) defines the JNDI (Java Naming and Directory Interface) resources, that allows Java software clients to discover and look up data and objects via a name.

The default configuration defines a JNDI name called UserDatabase via the <Resource> element (Line 10-14), which is a memory-based database for user authentication loaded from “conf/tomcat-users.xml“.

<GlobalNamingResources>
  <Resource name="UserDatabase" auth="Container"
            type="org.apache.catalina.UserDatabase"
            description="User database that can be updated and saved"
            factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
            pathname="conf/tomcat-users.xml" />
</GlobalNamingResources>

You can define other global resource JNDI such as MySQL database to implement connection pooling.

Services

A Service associates one or more Connectors to a Engine. The default configuration defines a Service called “Catalina“, and associates two Connectors: HTTP and AJP to the Engine.

<Service name="Catalina"> ...... </Service>

Connectors

A Connector is associated with a TCP port to handle communications between the Service and the clients. The default configuration defines two Connectors:

  • HTTP/1.1: Handle HTTP communication and enable Tomcat to be an HTTP server. Clients can issue HTTP requests to the server via this Connector, and receive the HTTP response messages.
    <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" />

    The default chooses TCP port 8080 to run the Tomcat HTTP server, which is different from the default port number of 80 for HTTP production server. You can choose any number between 1024 to 65535, which is not used by any application, to run your Tomcat server.
    The connectionTimeout attribute define the number of milliseconds this connector will wait, after accepting a connection, for the request URI line (request message) to be presented. The default is 20 seconds.
    The redirect attribute re-directs the SSL requests to TCP port 8443.

  • AJP/1.3: Apache JServ Protocol connectorto handle communication between Tomcat server and Apache HTTP server.
    <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />

    You could run Tomcat and Apache HTTP servers together, and let the Apache HTTP server handles static requests and PHP; while Tomcat server handles the Java Servlet/JSP. Read “How To Configure Tomcat to work with Apache“.

Containers

Tomcat refers to Engine, Host, Context, and Cluster, as container. The highest-level is Engine; while the lowest-level is Context. Certain components, such as Realm and Valve, can be placed in a container.

Engine

A Engine is the highest-level of a container. It can contains one or more Hosts. You could configure a Tomcat server to run on several hostnames, known as virtual host.

<Engine name="Catalina" defaultHost="localhost">

The Catalina Engine receives HTTP requests from the HTTP connector, and direct them to the correct host based on the hostname/IP address in the request header.

Realm

A Realm is a database of user, password, and role for authentication (i.e., access control). You can define Realm for any container, such as Engine, Host, and Context, and Cluster.

<Realm className="org.apache.catalina.realm.LockOutRealm">
  <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>
</Realm>

The default configuration defines a Realm (UserDatabaseRealm) for the Catalina Engine, to perform user authentication for accessing this engine. It uses the JNDI name UserDatabase defined in the GlobalNamingResources.

Besides the UserDatabaseRealm, there are: JDBCRealm (for authenticating users to connect to a relational database via the JDBC driver); DataSourceRealm (to connect to a DataSource via JNDI; JNDIRealm (to connect to an LDAP directory); and MemoryRealm (to load an XML file in memory).

Hosts

A Host defines a virtual host under the Engine, which can in turn support many Contexts (webapps).

<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true">

The default configuration define one host called localhost. The appBase attribute defines the base directory of all the webapps, in this case, $CATALINA_HOME\webapps. By default, each webapp’s URL is the same as its directory name. For example, the default Tomcat installation provides four webapps: docs, examples, host-manager and manager under the webapps directory. The only exception is ROOT, which is identified by an empty string. That is, its URL is http://localhost:8080/.

The unpackWARs specifies whether WAR-file dropped into the webapps directory shall be unzipped. For unpackWARs="false", Tomcat will run the application from the WAR-file directly, without unpacking, which could mean slower execution.

The autoDeploy attribute specifies whether to deploy application dropped into the webapps directory automatically.

Cluster

Tomcat supports server clustering. It can replicate sessions and context attributes across the clustered server. It can also deploy a WAR-file on all the cluster.

Valve

A Valve can intercept HTTP requests before forwarding them to the applications, for pre-processing the requests. A Valve can be defined for any container, such as Engine, Host, and Context, and Cluster.

In the default configuration, the AccessLogValve intercepts an HTTP request and creates a log entry in the log file, as follows:

<Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
       prefix="localhost_access_log." suffix=".txt"
       pattern="%h %l %u %t &quot;%r&quot; %s %b" />

Other valves include:

  • RemoteAddrValve: which blocks requests from certain IP addresses,
  • RemoteHostValve: which blocks request based on hostnames,
  • RequestDumperValve: which logs details of the requests,
  • SingleSignOn Valve: when placed under a <host>, allows single sign-on to access all the webapp under the host.

Context (Application or Webapp)

A web context is a single web application (webapp). It is the lowest-level container, that you can define components such as Realm and Valve. By default, all webapps are kept under the $CATALINA_HOME\webapps directory (as configured in the <host> element appBase attribute.

A Java webapp may contain many types of files, such as HTML, CSS, Scripts, images, JSP, servlet, utility classes, external library jar-files. A Java webapp must follow a strict directory structure as depicted in the Servlet/JSP specifications. This enables deployment in a Java-capable web server (such as Apache Tomcat and Glassfish). The resources must be kept in the correct directories and sub-directories.

The URL of a webapp, by default, is the same as the base directory name (or context root) of the webapp.

Webapp’s Directory Structure

The directory structure of a webapp is as follows:

ContextRoot“: contains the resources that are visible and accessible by the web clients, such as HTML, CSS, Scripts and images. These resources will be delivered to the clients as it is. You could create sub-directories such as images, css and scripts, to further categories the various resources.

  • ContextRoot\WEB-INF“: This directory, although under the context root, is NOT visible to the web users. In other words, it is NOT accessible by the clients directly (for security reason). This is where you keep your application-specific configuration files such as “web.xml“. It’s sub-directories contain program classes, source files, and libraries.
  • ContextRoot\WEB-INF\src“: Keeps the Java program source files. It is optional but a good practice to separate the source files and classes to facilitate deployment.
  • ContextRoot\WEB-INF\classes“: Keeps the Java classes (compiled from the source codes). Classes defined in packages must be kept according to the Java package directory structure.
  • ContextRoot\WEB-INF\lib“: Keeps the libraries (jar-files), which are provided by other packages, specific and available to this webapp only.
  • ContextRoot\META-INF\“: This is also a hidden directory, to keep resources and configurations (e.g., “context.xml“) related to the server. In contrast, “WEB-INF” is for resources related to this web application, independent of the server.
Webapp-Specific Configuration Files

These are the configuration files specific to a webapp: (a) WEB-INF\web.xml; (b) META-INF\context.xml.

You can configure a webapp in many ways: (a) Write a <context> element in server.xml under <Host> element, (b) contextRoot\META-INF\context.xml, and (c) conf\Catalina\localhost\webapp.xml, and (d) conf\context.xml. See “Setting the Context Root Directory and Request URL of a Webapp“.

Tomcat’s Directory Structure

Tomcat installation provides these directories:

  • bin: for Tomcat’s binary codes.
  • conf: global configuration applicable to all the webapps. The default installation provides:
    • One policy file: catalina.policy for specifying security policy.
    • Two properties files: catalina.properties and logging.properties,
    • Four configuration files: server.xml (Tomcat main configuration file), web.xml (global web application deployment descriptors), context.xml (global Tomcat-specific configuration options) and tomcat-users.xml (a database of user, password and role for authentication and access control).

    The conf also contain a sub-directory for each engine, e.g., Catalina, which in turn contains a sub-sub-directory for each of its hosts, e.g., localhost. You can place the host-specific context information (similar to context.xml, but named as webapp.xml for each webapp under the host).

  • lib: Keeps the JAR-file that are available to all webapps. The default installation include servlet-api.jar, jasper.jar (JSP), jasper-el.jar (EL). You may also keep the MySQL JDBC driver (mysql-connector-java-5.1.xx-bin.jar), and JSTL (jstl.jar and standard.jar) here.
  • logs: contains the engine logfile Catalina.yyyy-mm-dd.log, host logfile localhost.yyyy-mm-dd.log, and other application logfiles such as manger and host-manager. The access log (created by the AccessLogValve) is also kept here.
  • temp: temporary files used by Tomcat.
  • webapps: the default appBase – web applications base directory of the host localhost.
  • work: contains the translated servlet source files and classes of JSP/JSF. Organized in hierarchy of engine name (Catalina), host name (localhost), webapp name, followed by the Java classes package structure.

Other Configuration Files: web.xml, context.xml, tomcat-users.xml

[TODO]

Some Configuration Options

They are so many things that you can configured in Tomcat. I describe some of the configurations that I found useful in this section.

Enabling Directory Listing

When the request URL refers to a directory instead of a file, e.g., http://host:port/hello/, you can configure Tomcat to serve the directory listing, or a welcome file, or issue error “404 Page Not Found”. Enabling directory listing is handy for test server but NOT desire for production server (as it reveal the directory structure and expose the entire directory hierarchy).

Enabling Directory Listing for ALL Webapps

To enable directory listing for all the web applications, you could modify the $CATALINA_HOME\conf\web.xml, by changing “listings” from “false” to “true” for the “default” servlet, as follows:

<!-- The default servlet for all web applications, that serves static     -->
<!-- resources.  It processes all requests that are not mapped to other   -->
<!-- servlets with servlet mappings.                                      -->
<servlet>
  <servlet-name>default</servlet-name>
  <servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
  <init-param>
    <param-name>debug</param-name>
    <param-value>0</param-value>
  </init-param>
  <init-param>
    <param-name>listings</param-name>
    <param-value>true</param-value>
  </init-param>
  <load-on-startup>1</load-on-startup>
</servlet>
    
<!-- The mapping for the default servlet -->
<servlet-mapping>
  <servlet-name>default</servlet-name>
  <url-pattern>/</url-pattern>
</servlet-mapping>
   
<!-- ==================== Default Welcome File List ===================== -->
<!-- When a request URI refers to a directory, the default servlet looks  -->
<!-- for a "welcome file" within that directory and, if present,          -->
<!-- to the corresponding resource URI for display.  If no welcome file   -->
<!-- is present, the default servlet either serves a directory listing,   -->
<!-- or returns a 404 status, depending on how it is configured.          -->
<welcome-file-list>
  <welcome-file>index.html</welcome-file>
  <welcome-file>index.htm</welcome-file>
  <welcome-file>index.jsp</welcome-file>
</welcome-file-list>

The above configuration maps URL “\” (root directory of the web context) (in <url-pattern>) to Java class DefaultServlet (in <servlet-class>) via the common servlet name of default (in <servlet-name>). We enable directory listing by changing the servlet’s initialization parameter listings to true.

If a user requests for a directory, and the directory listing is enabled and it contains one of the files in the <welcome-file> list, the welcome file will be served; otherwise, the directory listing will be served. On the other hand, if a directory request is received and the directory listing is not enabled, the server returns an error “404 Page Not Found”.

Enabling Directory Listing for a particular Webapp

If you wish to allow directory listing of a particular web application only, you could disable the directory listing in “$CATALINA_HOME\conf\web.xml” globally, and define the following <servlet> and <servlet-mapping> in your application-specific WEB-INF\web.xml, as follows. You need to use another <servlet-name> in place of DefaultServlet.

<servlet>
  <servlet-name>DirectoryListing</servlet-name>
  <servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
  <init-param>
    <param-name>debug</param-name>
    <param-value>0</param-value>
  </init-param>
  <init-param>
    <param-name>listings</param-name>
    <param-value>true</param-value>
  </init-param>
</servlet>
   
<servlet-mapping>
  <servlet-name>DirectoryListing</servlet-name>
  <url-pattern>/</url-pattern>
</servlet-mapping>

Automatic Servlet Reload

To enable automatic servlet reload (whenever a servlet is re-compiled), you need to specify <Context reloadable="true">...</Context>, in “$CATALINA_HOME\conf\context.xml” for all web applications, or the <Context> element in “$CATALINA_HOME\conf\server.xml” for a particular web application.

The following messages appear on the Tomcat’s console if you re-compile a servlet:

XXX X, XXXX XX:XX:XX XX org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with path [/hello] has started
XXX X, XXXX XX:XX:XX XX org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with path [/hello] is completed

Enabling automatic servlet reload is handy during application development, but it requires significant runtime overhead to listen to the changes, and is not recommended for production systems. Instead, you could use the “manager” to trigger reloads on demand.

Setting the Context Root Directory and Request URL of a Webapp 

A server could run many web applications. A webapp is also called a web context. The context root (or document base directory) refers to the base directory of a webapp. They are a few ways to configure a context root and its request URL of a webapp:

  1. (RECOMMENDED) Create a directory under $CATALINA_HOME\webapps for your webapp. A context will be created with request URL set to the name of the directory. For example, if you create a directory called “hello” under Tomcat’s “webapps“. This application can be accessed by web users via URL http://host:port/hello.
    To change the request URL of the webapp, create a “context.xml” configuration file, as follows, and place it under “ContextRoot\META-INF“:
    <Context path="/yourURLPath" />
  2. Alternatively, you can write a <Context> element in $CATALINA_HOME\conf\server.xml, under the <Host>element. You can specify both the URL and the base directory. For example,
            ......
            ......
            <Context path="/ws" docBase="d:/workshop" reloadable="true">
            </Context>
          </Host>
        </Engine>
      </Service>
    </Server>

    In the above example, we define a web context with URL “/ws“, with context root (docBase or document base directory) at “d:\workshop“. This application can be accessed via URL http://host:port/ws.
    Take note that:

    • The configuration creates a mapping from the “URL Path” issued by the web users to the “document base directory” in the server’s file system, where you store your webapp resources.
    • Place the <Context> element before the ending tag of the <Host> element.
    • Use Unix-style forward slash '/' as the directory separator in the configuration file, instead of Window-style back slash '\'.
    • The attribute reloadable="true" asks Tomcat to monitor your servlets for changes, and automatically reload the servlets if changes is detected. This is handy for a development system, but inefficient in a production system.
  3. Write a configuration file with a <Context> element and place it under Tomcat’s “conf\Catalina\localhost“. For example, suppose we wish to create a webapp with URL “hello” in base directory “d:\myproject\myHello“, create the following file “hello.xml“:
    <?xml version="1.0" encoding="UTF-8"?>
    <Context docBase="D:\myproject\myHello" path="/hello" />

Changing the Default “webapps” Directory

The default directory for deploying web applications is $CATALINA_HOME\webapps. You could change the default by modifying the configuration file "conf\server.xml" <Host> element’s “appBase” attribute as follows:

<Host name="localhost" appBase="webapps"
      unpackWARs="true" autoDeploy="true"
      xmlValidation="false" xmlNamespaceAware="false">
   ......
</host>

Deploying a Web Application in a WAR file

You could use the JDK’s jar utility to “zip” up all the files of a web application to produce a so-called WAR (Web Application Archive) file for deployment, or distribution.

.... Change current directory to the web application's context root contextRoot> jar cvf test.war .

Drop the test.war into $CATALINA_HOME\webapps. A context called test will be created automatically. You can access the web application via URL http://host:port/test.

Tomcat actually unpacks the test.war into a “test” directory in $CATALINA_HOME\webapps. You need to remove this directory, if you reload a new version.

Running Tomcat as a Windows Service

This section is applicable for Windows. You need to download the Windows-specific version of Tomcat (from Tomcat’s download, choose 32-bit or 64-bit Window zip, e.g., apache-tomcat-7.0.2-windows-x86.zip).

Read “Windows service How-To” in the Tomcat documentation ($CATALINA_HOME\webapps\docs\windows-service-howto.html).

In a production environment, it is more convenient to run Tomcat as a service, so that it can start automatically whenever the system is started (or re-start automatically after an unexpected interruption).

To install Tomcat as a service, start a CMD shell (with administrator right) and run the $CATALINA_HOME\bin\service.bat with install option:

... Change directory to $CATALINA_HOME\bin ...
$CATALINA_HOME\bin> service install
Installing the service 'Tomcat7' ...
......
The service 'Tomcat7' has been installed.

The Tomcat service called “Apache Tomcat 7” is installed and will start automatically whenever the system is started. Check the “Services” under “Control Panel” ? “Administrative Tools”.

A GUI application called Tomcat7w is available for monitoring and configuring Tomcat services. Launch Tomcat7w:

$CATALINA_HOME\bin> Tomcat7w

You could put the Tomcat icon in the system tray via the MS (Monitor Service) option:

$CATALINA_HOME\bin> Tomcat7w //MS//

You can start/stop the Tomcat service now via:

  1. Tomcat7w;
  2. “Control Panel” ? “Administrator Tools” ? “Services” ? “Apache Tomcat 7” ? “Start”;
  3. From CMD shell, Issue “net” command:
    prompt> net start tomcat7
    The Apache Tomcat 7 service is starting..
    The Apache Tomcat 7 service was started successfully.
    ......
    ......
    prompt> net stop tomcat7
    The Apache Tomcat 7 service is stopping..
    The Apache Tomcat 7 service was stopped successfully.

To uninstall Tomcat Service, run the $CATALINA_HOME\bin\service.bat with remove option:

$CATALINA_HOME\bin> service remove
The service 'Tomcat7' has been removed

A flip side of running Tomcat as a service is you need to read the error messages from $CATALINA_HOME\logs instead of the Tomcat console.

Tomcat’s Startup Script

To start tomcat server, you could invoke the batch file “startup.bat” (in directory “$CATALINA_HOME\bin“, where CATALINA_HOME refers to the Tomcat installed directory). The “startup.bat” invokes “catalina.bat start“.

Alternatively, you could call the “catalina.bat” directly, which provides more options of starting Tomcat. Enter “catalina” to view the options:

$CATALINA_HOME/bin> catalina
Using CATALINA_BASE:   D:\xxx\tomcat7.0.xx
Using CATALINA_HOME:   D:\xxx\tomcat7.0.xx
Using CATALINA_TMPDIR: D:\xxx\tomcat7.0.xx\temp
Using JRE_HOME:        d:\xxx\jdk1.6
Usage:  catalina ( commands ... )
commands:
  debug             Start Catalina in a debugger
  debug -security   Debug Catalina with a security manager
  jpda start        Start Catalina under JPDA debugger
  run               Start Catalina in the current window
  run -security     Start in the current window with security manager
  start Start Catalina in a separate window
  start -security   Start in a separate window with security manager
  stop              Stop Catalina
  version           What version of tomcat are you running?

Study the source codes of “Tomcat.bat” and “catalina.bat“, if interested. Read “Command Shell – How to” about the syntax of batch files. At times, I need to use these files (comment-out the “ECHO OFF“) to debug Tomcat’s startup problems.

Web Application Deployment Descriptors – “web.xml”

The “web.xml” contains the deployment descriptors. There are two sets of web.xml:

  1. $CATALINA_HOME\conf\web.xml: applicable to ALL webapps.
  2. ContextRoot\WEB-INF\web.xml: applicable to the specific web context. It overrides the global setting, if any.

The complete specification for “web.xml” can be found in the “Java Servlet Specification” under “Deployment Descriptor”.

Tomcat’s Manager

Read “Apache Tomcat – Manager App HOW-TO” @ “webapps/docs/manager-howto.html”.

Tomcat webapp manager allows you to deploy a new web application; start, stop, reload or un-deploy an existing one, without having to shut down and restart the server.

To enable Tomcat manager, edit “$CATALINA_HOME\conf\tomcat-users.xml” to include a role called “manager” and a user with “manager” role.

<role rolename="manager"/>
<user username="tomcatmanager" password="xxxx" roles="manager,manager-script,admin"/>

Use http://localhost:8080/manager/html to invoke manager web application.

Tomcat with SSL 

SSL, or Secure Socket Layer, allows web browsers and web servers to communicate over a secured connection. Tomcat provides built-in support for SSL. Before you attempt to turn on the SSL support, make sure that your tomcat is running fine for HTTP without SSL.

Read:

  • “SSL Configuration How-to” of Tomcat Documentation @ “$CATALINA_HOME\webapps\docs\ssl-howto.html“.
  • “keytool – Key and Certificate Management Tool” @ JDK documentation.

The steps to turn on SSL support are:

Step 1: Check your JDK version. Tomcat’s SSL uses Java Secure Socket Extension (JSSE), which has been integrated into JDK since 1.4.

Step 2: Prepare the Tomcat’s server certificate, using the JDK’s key and certificate management tool called “keytool” (in “$JAVA_HOME\bin” ), as follows:

> keytool
... display the help menu ...
   
> keytool -genkey -alias tomcat -keyalg RSA -keystore d:\tomcat\conf\.keystore
Enter keystore password: xxxxxxxx
Re-enter new password: xxxxxxxx
What is your first and last name?
  [Unknown]:
What is the name of your organizational unit?
  [Unknown]:
What is the name of your organization?
  [Unknown]:
What is the name of your City or Locality?
  [Unknown]:
What is the name of your State or Province?
  [Unknown]:
What is the two-letter country code for this unit?
  [Unknown]:
Is CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown correct?
  [no]:  y
Enter key password for <tomcat>
        (RETURN if same as keystore password):
  • The “-genkey” option is used to generate a public-private key pair. The public key is wrapped into an X.509 v1 self-signed certificate. The certificate and the private key are stored in a new keystore entry identified by the alias. In our case, the alias name must be “tomcat“.
  • The “-keyalg” option specifies the key generation algorithm. RSA public key algorithm is used in this case.
  • The “-keystore” option specifies the name and location of the key store file.
  • The password for <tomcat> must be the same as the keystore (i.e., hit enter for the last question).

Step 3: Enable SSL support for Tomcat. SSL is built into Tomcat. The Tomcat’s configuration file commented out the SSL configuration directive. Uncomment them by removing the <!-- and --> around the SSL Coyote HTTP/1.1 Connector as follows:

<!-- Define a SSL HTTP/1.1 Connector on port 8443
     This connector uses the JSSE configuration, when using APR, the 
     connector should be using the OpenSSL style configuration
     described in the APR documentation -->
<Connector port="8443" protocol="org.apache.coyote.http11.Http11Protocol"
     SSLEnabled="true" maxThreads="150" scheme="https" secure="true"
     clientAuth="false" sslProtocol="TLS" 
     keystoreFile="d:\tomcat\conf\.keystore"
     keystorePass="passwordOfKeyStore" />

Note that the SSL (or HTTPS) is running on port 8443 instead of its default port number 443.

Add in the keystoreFile and keyStorePass attributes. The keystoreFile attribute specified the location of the keystore file. The keyStorePass provides the password for accessing the keystore file.

Step 4: Start your tomcat (run “$CATALINA_HOME\bin\startup.bat“). After that, start a web browser and issue an HTTPS request as follows:

https://localhost:8443

User Authentication in Tomcat 

Read Tomcat documentation “Realm Configuration HOW-TO” (@ “$CATALINA_HOME\webapps\docs\realm-howto.html“) and “Java EE 5 Tutorial”, Part IV “Services”, Chapters 28-30 on Security.

In Information Security:

  • Access control deals with identifying which resources require protection, and which users (roles) are authorized to access the protected resources.
  • Authentication deals with verifying users’ credential, i.e., ensuring the user is “who he said he is”. User’s credential is typically provided in the form of username/password. Other means include biometrics (finger-prints, retina) and digital certificates.
  • Confidentiality deals with the encryption of the transmitted data over the network. This is often carried out via employing HTTP over SSL (Secure Socket Layer), known as HTTPS.
  • Message Integrity: message is not tempered during transmission (via message digest or hash).
  • Non-repudiation: If he/she has sent a message, he/she cannot deny (via public-key or digital certificate).

In Tomcat’s web applications, a user is identified via username/password. A user is assigned role(s) (e.g., manager, admin, user, etc). Tomcat grants access for web application to role(s), instead of individual users. A realm is a database or file, which contains user information such as username/password, and roles.

Tomcat supports the following types of realm:

  • UserDatabaseRealm: user information kept in a XML file “conf\tomcat-users.xml“, accessed via JDNI (Java Naming and Directory Interface).
  • JDBCRealm: user information kept in a relational database such as MySQL, accessed via JDBC.
  • others.

You can used the <realm> element to configure a realm in “conf\server.xml“. <realm> element can be placed in <engine>, <host>, or <context>, which determines the scope of the <realm> – all virtual hosts under the engine, a particular host, or a particular web application.

“Declarative security” is handled by the server. The server-side programs (servlets, JSPs) do not need any security-aware code. That is, the security control is totally transparent to the server-side programs.

UserDatabaseRealm

UserDatabaseRealm stores user information in a XML file and accessed via JNDI. By default, the XML file is “$CATALINA_HOME\conf\tomcat-users.xml“.

Tomcat provide a JSP example to configure UserDatabaseRealm in “WEB-INF\examples\jsp\security\protected“, accessed via http://localhost:8080/examples/jsp/security/protected/index.jsp”. Let us study this example.

conf\server.xml

Tomcat enables UserDatabaseRealm, in default installation, with the following configuration directives in “server.xml“. It defines a JDNI named “UserDatabase” to the file “conf\tomcat-users.xml“. The UserdatabaseRealm is defined within the <Engine> elements, and thus applicable to all the virtual hosts and web applications, under this server.

<Server ...... >
  <!-- Global JNDI resources -->
  <GlobalNamingResources>
    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />
  </GlobalNamingResources>
  
  <Service name="Catalina">
    <Engine name="Catalina" defaultHost="localhost">
      <!-- This Realm uses the UserDatabase configured in the global JNDI
           resources under the key "UserDatabase".  Any edits
           that are performed against this UserDatabase are immediately
           available for use by the Realm.  -->
      <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
             resourceName="UserDatabase" />
      <Host name="localhost" ......
        ......
      </Host>
    </Engine>
  </Service>   
</Server>
conf\tomcat-users.xml

Recall that a user is identified via username/password. A user is assigned role(s). Accesses for web applications are granted to role(s) instead of individual users. “Tomcat-users.xml” contains the following roles and username/password, but commented-out. Uncomment them for testing the example.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<tomcat-users>
  <role rolename="tomcat" />
  <role rolename="role1" />
  <user username="tomcat" password="tomcat" roles="tomcat" />
  <user username="both" password="tomcat" roles="tomcat,role1" />
  <user username="role1" password="tomcat" roles="role1" />
</tomcat-users>
ContextRoot\WEB-INF\web.xml

For the “examples” web context, the security roles are defined using <security-constraint> element in “examples\WEB-INF\web.xml” as follows. The URL pattern /jsp/security/protected/* (GET, POST, DELETE, PUT methods) are accessible by users having roles of tomcat and role1 only.

<web-app ......>
  ......
  <security-constraint>
    <display-name>Example Security Constraint</display-name>
    <web-resource-collection>
      <web-resource-name>Protected Area</web-resource-name>
      <!-- Define the context-relative URL(s) to be protected -->
      <url-pattern>/jsp/security/protected/*</url-pattern>
      <!-- If you list http methods, only those methods are protected -->
      <http-method>DELETE</http-method>
      <http-method>GET</http-method>
      <http-method>POST</http-method>
      <http-method>PUT</http-method>
    </web-resource-collection>
    <auth-constraint>
      <!-- Anyone with one of the listed roles may access this area -->
      <role-name>tomcat</role-name>
      <role-name>role1</role-name>
    </auth-constraint>
  </security-constraint>
  
  <!-- Default login configuration uses form-based authentication -->
  <login-config>
    <auth-method>FORM</auth-method>
    <realm-name>Example Form-Based Authentication Area</realm-name>
    <form-login-config>
      <form-login-page>/jsp/security/protected/login.jsp</form-login-page>
      <form-error-page>/jsp/security/protected/error.jsp</form-error-page>
    </form-login-config>
  </login-config>
        
  <!-- Security roles referenced by this web application -->
  <security-role>
    <role-name>role1</role-name>
  </security-role>
  <security-role>
    <role-name>tomcat</role-name>
  </security-role>    
Form-based Authentication Method

The above example uses FORM-based authentication method, defined in element <login-config>. All accesses to the protected URL (http://localhost:8080/examples/jsp/security/protected/*) will be redirected to the login.jsp page (defined in <form-login-page>), which prompts user for the credential. For example, if a user requests for http://localhost:8080/examples/jsp/security/protected/index.jsp, the login.jsp will be displayed.

The login.jsp page shall contain a html <form> (thus called Form-based authentication):

<html>
<head><title>Login Page for Examples</title></head>
<body>
<form method="POST" action='<%= response.encodeURL("j_security_check") %>' >
  Username:<input type="text" name="j_username">
  Password:<input type="password" name="j_password">
  <input type="submit" value="Log In">
</form>
</body>
</html>

The login page shall submit the username and password in parameters j_username and j_password to j_security_check. You should use <input type="password" ...> for the password text field, which will display the password as *’s. The response.encodeURL(URL) encodes the specified URL by including the session ID if URL-rewriting is used for session tracking; it returns the URL unchanged if cookie is used. For robust session tracking, all URLs emitted by server-side programs (servlet/JSP) should be run through this method.

If login fails, user will be redirected to error.jsp page, for example,

<html>
<head><title>Error Page For Examples</title></head>
<body>
Invalid username and/or password, please try again
<a href='<%= response.encodeURL("index.jsp") %>'>again</a>.
</body>
</html>

If login succeeds, the user will get the page he requested for. Study the “examples\jsp\security\protected\index.jsp” source.

  • To logoff, terminate the current session via session.invalidate().
  • You can use request.getRemoteUser() to get the authenticated login username; request.getUserPrincipal() to get a java.security.Principal object containing the name of the current authenticated user; request.isUserInRole(role) to check if the authenticated user is included in the specified role.
HTTPS

The username and password send in form data are in clear text, and susceptible to eavesdropping. Hence, it is important to encrypt the transport by turning on SSL (HTTPS). Read “Tomcat with SSL” on how to setup Tomcat with SSL.

To enforce user to use secure transport (HTTPS), add a <transport-guarantee>CONFIDENTIAL</transport-guarantee>, inside the <security-constraint>, as follows:

<security-constraint>
  <display-name>Example Security Constraint</display-name>
  <web-resource-collection>
    <web-resource-name>Protected Area</web-resource-name>
    <url-pattern>/jsp/security/protected/*</url-pattern>
    ......
  </web-resource-collection>
  <auth-constraint>
    <role-name>tomcat</role-name>
    ......
  </auth-constraint>
  <!-- must use SSL for secure transport -->
  <user-data-constraint> <transport-guarantee>CONFIDENTIAL</transport-guarantee> </user-data-constraint>
</security-constraint>

All accesses to http at port 8080 (e.g., http://localhost:8080/examples/jsp/security/protected/index.jsp) will be redirected to https at port 8443 (e.g., https://localhost:8443/examples/jsp/security/protected/index.jsp).

HTTP Basic Authentication

Other than Form-based authentication, you could use the Basic Authentication Scheme available in HTTP server to authenticate user. Change the <login-config>‘s <auth-method> to BASIC, instead of FORM.

<login-config>
   <auth-method>BASIC</auth-method>
   <realm-name>Example Basic Authentication Area</realm-name>
</login-config>

Again, Basic Authentication sends the username and password in clear text. It is totally insecure, unless you should use a secure transport (HTTPS).

HTTP Digest Authentication

Tomcat also support HTTP Digest Authentication Scheme to authenticate user. Change the <login-config>‘s <auth-method> to DIGEST. Instead of sending password in clear text, the digest of password is send to the server. Digest authentication is more secure.

<login-config>
   <auth-method>BASIC</auth-method>
   <realm-name>Example Basic Authentication Area</realm-name>
</login-config>

JDBCRealm

In JDBCRealm, user information kept in a relational database such as MySQL, accessed via JDBC.

Setting up Database

We shall set up our user database in MySQL. Read “How to install MySQL“.

The following script can be used to set up the user database. Two tables are required: a users table containing username and password, and a user_roles containing username and the role assigned.

create database tomcat_users;
  
use tomcat_users;
  
create table users (
  username varchar(15) not null primary key,
  password varchar(15) not null
);
  
create table user_roles (
  username varchar(15) not null,
  role     varchar(15) not null,
  primary key (username, role)
);
  
insert into users values 
  ('tomcat', 'tomcat'), 
  ('both', 'tomcat'), 
  ('role1', 'tomcat');
  
insert into user_roles values 
  ('tomcat', 'tomcat'), 
  ('role1', 'role1'), 
  ('both', 'tomcat'), 
  ('both', 'role1');
JDBC Driver

Next, copy the MySQL’s JDBC driver (“mysql-connector-java-5.1.xx-bin.jar“) into Tomcat’s lib (“$CATALINA_HOME\lib“).

conf\server.xml
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
   driverName="com.mysql.jdbc.Driver"
   connectionURL="jdbc:mysql://localhost/tomcat_users?user=dbuser&amp;password=dbpass"
   userTable="users" userNameCol="username" userCredCol="password"
   userRoleTable="user_roles" roleNameCol="role" />
ContextRoot\WEB-INF\web.xml

Same as UserDatabaseRealm.

Authentication Methods

Same as UserDatabaseRealm, you can use FORM, BASIC or DIGEST authentication method.

Testing

You need to start MySQL server before starting the Tomcat Server.

Setting up Database Connection Pooling in Tomcat

Read “Database Connection Pooling in Tomcat with MySQL“.

Configuring Virtual Hosts

To set up a virtual host called “www.mytest.com” (suppose that you have registered this hostname with at static IP address). Include the following <Host> element in server.xml under the Engine Catalina:

<Engine name="Catalina" >
  <Host name="localhost .....>
    ......
  </Host>
  <Host name="www.mytest.com" appBase="webapps_mytest.com" unpackWARs="true" autoDeploy="true" > <Alias>mytest.com</Alias> <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" prefix="mytest.com_access_log." suffix=".log" pattern="%h %l %u %t &quot;%r&quot; %s %b" resolveHosts="false" /> </Host>
</Engine>

The above lines configure a virtual host with hostname “www.mytest.com“, with webapps base directory at “$CATALINA_HOME\webapps_mytest.com“. We also define a alias called “mytest.com“. That is, this host can be accessed via http://www.mytest.com:port or http://mytest.com:port. We also define a Valve, which intercepts the request message to write a log entries (similar to localhost).

Next:

  1. Create a directory “webapps_mytest.com” under $CATALINA_HOME, according to the appBase.
  2. Create a web application called ROOT, by creating a directory ROOT under the “webapps_mytest.com“. Recall that ROOT was configured with an empty string URL. In other words, http://www.mytest.com:port/ accesses the ROOT application.
  3. Create a directory “www.mytest.com” under “conf\Catalina“.
  4. Write a welcome page called “index.html” and save it in “webapps_mytest.com\ROOT“.
    <html>
    <head><title>Testing Virtual Host</title></head>
    <body>
      <h1>It's work on virtual host</h1>
    </body>
    </html>

To test the virtual host, without registering the hostname with an ISP, edit “C:\Windows\System32\drivers\etc\hosts” to include the following lines (required administrative authority):

127.0.0.1   www.mytest.com
127.0.0.1   mytest.com

These lines maps host names www.mytest.com and mytest.com to IP address 127.0.0.1, which is the localhost. As the IP software checks the host file before asking Domain Name Service (DNS) to resolve a host name, you willl be able to test your virtual host.

Now, you are ready to test the virtual hosts. Start the Tomcat server and issue these URL:

http://www.mytest.com:8080
http://mytest.com:8080
http://www.mytest.com:8080/
http://mytest.com:8080/
http://www.mytest.com:8080/index.html
http://mytest.com:8080/index.html

 

 

REFERENCES & RESOURCES

  • Tomcat mother site @ http://tomcat.apache.org
  • Tomcat’s Documentation @ “$CATALINA_HOME\webapps\docs“.
  • Java Servlet, JavaServer Pages (JSP), and JavaServer Faces (JSF) specifications.

 

 

yum command

Task: Display list of updated software (security fix)

Type the following command at shell prompt:
# yum list updates

Task: Patch up system by applying all updates

To download and install all updates type the following command:
# yum update

Task: List all installed packages

List all installed packages, enter:
# rpm -qa
# yum list installed

Find out if httpd package installed or not, enter:
# rpm -qa | grep httpd*
# yum list installed httpd

Task: Check for and update specified packages

# yum update {package-name-1}
To check for and update httpd package, enter:
# yum update httpd

Task: Search for packages by name

Search httpd and all matching perl packages, enter:
# yum list {package-name}
# yum list {regex}
# yum list httpd
# yum list perl*

Sample output:

Loading "installonlyn" plugin
Loading "security" plugin
Setting up repositories
Reading repository metadata in from local files
Installed Packages
perl.i386                                4:5.8.8-10.el5_0.2     installed
perl-Archive-Tar.noarch                  1.30-1.fc6             installed
perl-BSD-Resource.i386                   1.28-1.fc6.1           installed
perl-Compress-Zlib.i386                  1.42-1.fc6             installed
perl-DBD-MySQL.i386                      3.0007-1.fc6           installed
perl-DBI.i386                            1.52-1.fc6             installed
perl-Digest-HMAC.noarch                  1.01-15                installed
perl-Digest-SHA1.i386                    2.11-1.2.1             installed
perl-HTML-Parser.i386                    3.55-1.fc6             installed
.....
.......
..
perl-libxml-perl.noarch                  0.08-1.2.1             base
perl-suidperl.i386                       4:5.8.8-10.el5_0.2     updates 

Task: Install the specified packages [ RPM(s) ]

Install package called httpd:
# yum install {package-name-1} {package-name-2}
# yum install httpd

Task: Remove / Uninstall the specified packages [ RPM(s) ]

Remove package called httpd, enter:
# yum remove {package-name-1} {package-name-2}
# yum remove httpd

Task: Display the list of available packages

# yum list all

Task: Display list of group software

Type the following command:
# yum grouplist
Output:

Installed Groups:
   Engineering and Scientific
   MySQL Database
   Editors
   System Tools
   Text-based Internet
   Legacy Network Server
   DNS Name Server
   Dialup Networking Support
   FTP Server
   Network Servers
   Legacy Software Development
   Legacy Software Support
   Development Libraries
   Graphics
   Web Server
   Ruby
   Printing Support
   Mail Server
   Server Configuration Tools
   PostgreSQL Database
Available Groups:
   Office/Productivity
   Administration Tools
   Beagle
   Development Tools
   GNOME Software Development
   X Software Development
   Virtualization
   GNOME Desktop Environment
   Authoring and Publishing
   Mono
   Games and Entertainment
   XFCE-4.4
   Tomboy
   Java
   Java Development
   Emacs
   X Window System
   Windows File Server
   KDE Software Development
   KDE (K Desktop Environment)
   Horde
   Sound and Video
   FreeNX and NX
   News Server
   Yum Utilities
   Graphical Internet
Done

Task: Install all the default packages by group

Install all ‘Development Tools’ group packages, enter:
# yum groupinstall "Development Tools"

Task: Update all the default packages by group

Update all ‘Development Tools’ group packages, enter:
# yum groupupdate "Development Tools"

Task: Remove all packages in a group

Remove all ‘Development Tools’ group packages, enter:
# yum groupremove "Development Tools"

Task: Install particular architecture package

If you are using 64 bit RHEL version it is possible to install 32 packages:
# yum install {package-name}.{architecture}
# yum install mysql.i386

Task: Display packages not installed via official RHN subscribed repos

Show all packages not available via subscribed channels or repositories i.e show packages installed via other repos:
# yum list extras
Sample output:

Loading "installonlyn" plugin
Loading "security" plugin
Setting up repositories
Reading repository metadata in from local files
Extra Packages
DenyHosts.noarch                         2.6-python2.4          installed
VMwareTools.i386                         6532-44356             installed
john.i386                                1.7.0.2-3.el5.rf       installed
kernel.i686                              2.6.18-8.1.15.el5      installed
kernel-devel.i686                        2.6.18-8.1.15.el5      installed
lighttpd.i386                            1.4.18-1.el5.rf        installed
lighttpd-fastcgi.i386                    1.4.18-1.el5.rf        installed
psad.i386                                2.1-1                  installed
rssh.i386                                2.3.2-1.2.el5.rf       installed

Task: Display what package provides the file

You can easily find out what RPM package provides the file. For example find out what provides the /etc/passwd file:
# yum whatprovides /etc/passwd
Sample output:

Loading "installonlyn" plugin
Loading "security" plugin
Setting up repositories
Reading repository metadata in from local files
setup.noarch                             2.5.58-1.el5           base
Matched from:
/etc/passwd
setup.noarch                             2.5.58-1.el5           installed
Matched from:
/etc/passwd

You can use same command to list packages that satisfy dependencies:
# yum whatprovides {dependency-1} {dependency-2}
Refer yum command man page for more information:
# man yum

 

IPTABLES TO BLOCK FACEBOOK

-A FORWARD -s 192.168.0.232 -m string –string “facebook.com” –algo kmp –to 65535 -j ACCEPT
A FORWARD -m string –string “youtube.com” –algo kmp –to 65535 -j DROP
-A FORWARD -m string –string “facebook.com” –algo kmp –to 65535 -j DROP
-A FORWARD -m string –string “meebo.com” –algo kmp –to 65535 -j DROP

Apache Httpd Server root privileges other than permission to start

Error message

 Will output the following message when you run with the privileges other than root privileges when starting Apache HTTPD Server. 1    Permission denied: make_sock: could not bind to address 0.0.0.0:80

Cause

 Smaller than the 1024 port enables the user to access only the root level of the port.
Resolution

 run with root privileges, change the port or perform normally execute but does not have the smooth processing.

 Httpd daemon running as a regular user account, as shown below, and then change the permissions, just as the root, daemon will float. 1

2    chown root:sys httpd
chmod +s httpd

Apache force use https

Force Apache to use https :

RequestHeader set X_FORWARDED_PROTO 'https'

Apache Rewrite Cheat Sheet

Regular Expression Syntax

 ^ Start of string
 $ End of string
 . Any single character
 (a|b) a or b
 (…) Group section
 [abc] Item in range (a or b or c)
 [^abc] Not in range (not a or b or c)
 a? Zero or one of a
 a* Zero or more of a
 a+ One or more of a
 a{3} Exactly 3 of a
 a{3,} 3 or more of a
 a{3,6} Between 3 and 6 of a
 !(pattern) “Not” prefix. Apply rule when URL does not match pattern
 RewriteRule FLAGS

R[=code]

Redirect to new URL, with optional code .

Prefix Substitution with http://thishost[:thisport]/ (which makes the new URL a URI) to force a external redirection. If no code is given a HTTP response of 302 (MOVED TEMPORARILY) is used. If you want to use other response codes in the range 300-400 just specify them as a number or use one of the following symbolic names: temp (default), permanent, seeother. Use it for rules which should canonicalize the URL and give it back to the client, e.g., translate “/~” into “/u/” or always append a slash to /u/user, etc.

Note: When you use this flag, make sure that the substitution field is a valid URL! If not, you are redirecting to an invalid location! And remember that this flag itself only prefixes the URL with http://thishost[:thisport]/, rewriting continues. Usually you also want to stop and do the redirection immediately. To stop the rewriting you also have to provide the ‘L’ flag.

F

Forbidden (sends 403 header)

This forces the current URL to be forbidden, i.e., it immediately sends back a HTTP response of 403 (FORBIDDEN). Use this flag in conjunction with appropriate RewriteConds to conditionally block some URLs.

G

Gone (no longer exists)

This forces the current URL to be gone, i.e., it immediately sends back a HTTP response of 410 (GONE). Use this flag to mark pages which no longer exist as gone.

P

Proxy

This flag forces the substitution part to be internally forced as a proxy request and immediately (i.e., rewriting rule processing stops here) put through the proxy module. You have to make sure that the substitution string is a valid URI (e.g., typically starting with http://hostname) which can be handled by the Apache proxy module. If not you get an error from the proxy module. Use this flag to achieve a more powerful implementation of the ProxyPass directive, to map some remote stuff into the namespace of the local server.

Notice: To use this functionality make sure you have the proxy module compiled into your Apache server program. If you don’t know please check whether mod_proxy.c is part of the “httpd -l” output. If yes, this functionality is available to mod_rewrite. If not, then you first have to rebuild the “httpd” program with mod_proxy enabled.

L

Last Rule

Stop the rewriting process here and don’t apply any more rewriting rules. This corresponds to the Perl last command or the break command from the C language. Use this flag to prevent the currently rewritten URL from being rewritten further by following rules.

For example, use it to rewrite the root-path URL (‘/’) to a real one, e.g., ‘/e/www/’.

N

Next(i.e. restart rules)

Re-run the rewriting process (starting again with the first rewriting rule). Here the URL to match is again not the original URL but the URL from the last rewriting rule. This corresponds to the Perl next command or the continue command from the C language. Use this flag to restart the rewriting process, i.e., to immediately go to the top of the loop.

But be careful not to create an infinite loop!

C

Chain

This flag chains the current rule with the next rule (which itself can be chained with the following rule, etc.). This has the following effect: if a rule matches, then processing continues as usual, i.e., the flag has no effect. If the rule does not match, then all following chained rules are skipped. For instance, use it to remove the “.www” part inside a per-directory rule set when you let an external redirect happen (where the “.www” part should not to occur!).

T=mime-type

Set Mime Type

Force the MIME-type of the target file to be MIME-type. For instance, this can be used to simulate the mod_alias directive ScriptAlias which internally forces all files inside the mapped directory to have a MIME type of “application/x-httpd-cgi”.
NS

Skip if internal sub-request

This flag forces the rewriting engine to skip a rewriting rule if the current request is an internal sub-request. For instance, sub-requests occur internally in Apache when mod_include tries to find out information about possible directory default files (index.xxx). On sub-requests it is not always useful and even sometimes causes a failure to if the complete set of rules are applied. Use this flag to exclude some rules.

Use the following rule for your decision: whenever you prefix some URLs with CGI-scripts to force them to be processed by the CGI-script, the chance is high that you will run into problems (or even overhead) on sub-requests. In these cases, use this flag.
NC

Case insensitive

This makes the Pattern case-insensitive, i.e., there is no difference between ‘A-Z’ and ‘a-z’ when Pattern is matched against the current URL.
QSA

Append query string This

flag forces the rewriting engine to append a query string part in the substitution string to the existing one instead of replacing it. Use this when you want to add more data to the query string via a rewrite rule.
NE

Do not escape output

This flag keeps mod_rewrite from applying the usual URI escaping rules to the result of a rewrite. Ordinarily, special characters (such as ‘%’, ‘$’, ‘;’, and so on) will be escaped into their hexcode equivalents (‘%25?, ‘%24?, and ‘%3B’, respectively); this flag prevents this from being done. This allows percent symbols to appear in the output, as in

RewriteRule /foo/(.*) /bar?arg=P1\%3d$1 [R,NE]

which would turn ‘/foo/zed’ into a safe request for ‘/bar?arg=P1=zed’.
PT

Pass through

This flag forces the rewriting engine to set the uri field of the internal request_rec structure to the value of the filename field. This flag is just a hack to be able to post-process the output of RewriteRule directives by Alias, ScriptAlias, Redirect, etc. directives from other URI-to-filename translators. A trivial example to show the semantics: If you want to rewrite /abc to /def via the rewriting engine of mod_rewrite and then /def to /ghi with mod_alias:

RewriteRule ^/abc(.*) /def$1 [PT]
 Alias /def /ghi

If you omit the PT flag then mod_rewrite will do its job fine, i.e., it rewrites uri=/abc/… to filename=/def/… as a full API-compliant URI-to-filename translator should do. Then mod_alias comes and tries to do a URI-to-filename transition which will not work.

Note: You have to use this flag if you want to intermix directives of different modules which contain URL-to-filename translators. The typical example is the use of mod_alias and mod_rewrite..
S=x

Skip next x rules

This flag forces the rewriting engine to skip the next num rules in sequence when the current rule matches. Use this to make pseudo if-then-else constructs: The last rule of the then-clause becomes skip=N where N is the number of rules in the else-clause. (This is not the same as the ‘chain|C’ flag!)
E=var:value

Set environment variable “var” to “value”

This forces an environment variable named VAR to be set to the value VAL, where VAL can contain regexp backreferences $N and %N which will be expanded. You can use this flag more than once to set more than one variable. The variables can be later dereferenced in many situations, but usually from within XSSI (via ) or CGI (e.g. $ENV{‘VAR’}). Additionally you can dereference it in a following RewriteCond pattern via %{ENV:VAR}. Use this to strip but remember information from URLs.

RewriteCond FLAGS

NC Case insensitive
OR Allows a rule to apply if one of a series of conditions are true.

Redirection Header Codes

 301 Moved permanently
 302 Moved temporarily
 403 Forbidden
 404 Not found
 410 Gone
 Server Variables
 Format
 %{NAME_OF_VAR}
 HTTP Headers
 HTTP_USER_AGENT
 HTTP_REFERER
 HTTP_COOKIE
 HTTP_FORWARDED
 HTTP_HOST
 HTTP_PROXY_CONNECTION
 HTTP_ACCEPT
 Request
 REMOTE_ADDR
 REMOTE_HOST
 REMOTE_USER
 REMOTE_IDENT
 REQUEST_METHOD
 SCRIPT_FILENAME
 PATH_INFO
 QUERY_STRING
 AUTH_TYPE
 Server
 DOCUMENT_ROOT
 SERVER_ADMIN
 SERVER_NAME
 SERVER_ADDR
 SERVER_PORT
 SERVER_PROTOCOL
 SERVER_SOFTWARE
 Time
 TIME_YEAR
 TIME_MON
 TIME_DAY
 TIME_HOUR
 TIME_MIN
 TIME_SEC
 TIME_WDAY
 TIME
 Special
 API_VERSION
 THE_REQUEST
 REQUEST_URI
 REQUEST_FILENAME
 IS_SUBREQ
 Directives
 RewriteEngine
 RewriteOptions
 RewriteLog
 RewriteLogLevel
 RewriteLock
 RewriteMap
 RewriteBase
 RewriteCond
 RewriteRule