#! code

Apache can be set up to log all sorts of information. As of Apache 2.2 the basic log file format that a fresh install of Apache will produce will have the following format:

%h %l %u %t "%r" %>s %b

Which doesn't mean a lot to the uninitiated, so here is a short explanation of each.

• %h - The remote host. This is the IP address of the user connecting to the server.
• %l - The remote logname. This is not always present.
• %u - The remote user from auth (nothing if authentication is not used).
• %t - The time in a common log format.
• "%r" - The first line of the request, basically the method used (GET/POST) the URL that was accessed and the HTTP protocol level that was used. This is enclosed in quotes.
• %>s - %s returns the status of the original request request. For some requests Apache will internally create a secondary request, so %>s prints out the last request staus.
• %b - This is the number of bytes transmitted to the user.

This would produce the following sort out output.

127.0.0.1 - - [17/Apr/2009:14:12:20 +0100] "GET / HTTP/1.1" 200 515

This information can be converted into a database format by using the LOAD DATA command. First, lets create the table we need to store this log format.

DROP TABLE IF EXISTS `test`.`apachelog`; CREATE TABLE  `test`.`apachelog` (   `remote_host` varchar(17) DEFAULT NULL,   `remote_logname` varchar(45) DEFAULT NULL,   `remote_user` varchar(45) DEFAULT NULL,   `time1` varchar(22) DEFAULT NULL,   `time2` varchar(7) DEFAULT NULL,   `first_line_of_request` text,   `last_request_status` varchar(4) DEFAULT NULL,   `bytes_sent` varchar(10) DEFAULT NULL )

Here is the command that is used to convert the log file into that table.

LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY 'rn';

Note that our table contains two fields for the time. This is because of two factors. The first is that each field is defined by a space, and because the time value contains a space it is split into two fields. The second is that although we say OPTIONALLY ENCLOSED BY '"' to stop the %r output being split apart, we can't give the LOAD DATA command more than one of these fields. As a result the time is split into the two fields, so we just create a table with more than one field for time to accommodate this.

To improve the table we can use a different output format. Take the following slight alteration to our log file format.

LogFormat "%h,%l,%u,%t,"%r",%>s,%b"

This line can be found in your http.conf file or your httpd-vhosts.conf if you have set up virtual hosts.

This will cause our output to look like the following:

127.0.0.1,-,-,[20/Apr/2009:14:42:01 +0100],"GET / HTTP/1.1",200,515

We can now change out two time columns to a single one, setting the datatype to VARCHAR(30) and using the following LOAD DATA syntax to load our data.

LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Apache2.2/logs/access.log' INTO TABLE apachelog FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY 'rn';

This gives us a much better set of data in our table.

You can also use the following three MySQL commands to convert from the old table format to the new one.

ALTER TABLE apachelog CHANGE time1 time VARCHAR(40); UPDATE apachelog SET time = CONCAT(time,' ',time2); ALTER TABLE apachelog DROP COLUMN time2;

The time2 column will now no longer exist.

Written by Philip Norton.

To redirect from HTTPS to HTTP on the home page only using the following rule.

RewriteCond %{HTTPS} on RewriteRule ^/?$ http://%{SERVER_NAME}/ [R=301,L]

The variable %{HTTPS} will be either "on" or "off" and will be enabled even if SSL is not installed on your site. The rule above sees that HTTPS is on and redirects the home page to the HTTP version. You can even chain lots of rules together like this.

RewriteCond %{HTTPS} on RewriteRule ^/?$ http://%{SERVER_NAME}/ [R=301] RewriteRule ^inner/directory/?$ http://%{SERVER_NAME}/inner/directory/ [R=301,L]

Note that you should end your last rule with L so that no other rules on the page are run. Also, you need to make absolutely sure that you are not redirecting any pages that are integral to the security of your shopping cart as this will turn off HTTPS for those pages.

You can also do the same thing using the ${SERVER_PORT} variable.

RewriteCond %{SERVER_PORT} 443 RewriteRule ^/?$ http://%{SERVER_NAME}/ [R=301,L]

The port for HTTPS is 443 so if the port being communicated through is 443 we need to redirect.

Written by Philip Norton.

Getting started with SVN on Ubuntu takes only a few minutes, and enabling web access to the repository is also very straightforward.

First (in order to actually serve the files) you need to install Apache, open up a terminal window and run the following command. This will ensure that Apache is installed if you unselected it for some reason during the install.

sudo apt-get install apache2

Note the use of the sudo command. This will run the command you give it as a super user as normal users will not generally have access to install software like this. When you use sudo you will be prompted for the super user password. Next, use the following command to install SVN.

sudo apt-get install subversion libapache2-svn

You can now create your subversion repository, it is best to keep all of our repositories under the same directory so that things don't get confusing in the long run. To create the SVN directory run the following.

mkdir svn

The following will create the repository in the directory /svn/myproject.

sudo svnadmin create /svn/myproject

Now we will need to edit some of the settings in our Apache SVN module. Use the following command to edit the correct file.

sudo gedit /etc/apache2/mods-enabled/dav_svn.conf

When you first open this file it is all commented out. You first need to uncomment the Location. This is the directory where subversion will be available from on the Apache side. Here I am setting this to svn so that when I navigate to http://127.0.0.1/svn I will see the correct output.

<Location /svn>

Note that you must also uncomment the closing location tag at the bottom of the document!

Uncomment the DAV line to enable the repository, this enables the DAV module.

DAV svn

If you have a single repository then then enable the SVNPath setting. However, if you have multiple repositories then enable the SVNParentPath setting. this will point to the main directory where your repository (or repositories) is stored.

SVNParentPath /svn

That's about it really, but as a further step lets enable some sort of authentication. You will need to uncomment the following lines (which are grouped together).

AuthType Basic AuthName "Subversion Repository" AuthUserFile /etc/apache2/dav_svn.passwd

And the following line.

Require valid-user

Now, before we restart our Apache server and enable everything you have done we need to setup a username and password for the authentication to work. Use the following command to create a user and assign a password.

sudo htpasswd2 -cm /etc/apache2/dav_svn.passwd user

If this doesn't work then try using htpasswd instead of htpasswd2. You should be prompted to enter your password twice and then receive a message telling you that the user has been created. Note that you should only use the -c option when you create a user for the first time because it creates the password file even if it is already there. If you use this option again in the future you will destroy any other users you have created. Instead, simply drop the -c and use -m on its own which causes the password to be MD5 encrypted, but will not recreate the file.

You can now restart the server by entering the following command.

sudo /etc/init.d/apache2 restart

You might initially try a short cut and do something like the following:

sudo apache2 restart

However, this will not work as the script in /etc/init.d/apache2 explicitly reads some environment variables that are important when starting the server.

You can now go to your subversion directory and see the following:

You will also see an authentication window appear, just use the username and password you set up before.

You now have a fully working subversion server with web access.

Written by Philip Norton.

OK. I've just spent two hours trying to sort this problem out so I thought I would pass on the info.

I installed Apache and PHP and they worked fine, but every time I tried to run any MySQL commands through PHP the Apache server would simply crash.

After looking at the Event Viewer the problem appears to be from a file called php5ts.dll, but trying to do anything with this file will lead you down a blank alley.

What is happening is to do with a file called libmysql.dll. This file can be found in your PHP directory, but it is also to be found in your MySQL install directory. This is where the problem lies. When Apache asks for the libmysql.dll file it will receive the one in the MySQL directory because this included in the Windows PATH variable. This version of the libmysql.dll file causes Apache to crash.

At first I tried to add the PHP folder to the end of the PATH, but this didn't work. What you need to do is add the PHP folder BEFORE the MySQL folder.

Written by Philip Norton.

To stop access to a directory (and anything in that directory) all you need is a simple RewriteRule.

RewriteEngine on RewriteBase / RewriteRule ^exampledirectory/(.*)$ / [R=301,L]

In this example, if this .htaccess file resides in the root directory of the site and you try to access anything within /exampledirectory you will be redirected back to the root folder. To redirect to another folder (like anotherdirectory) on your web server use the following rule.

RewriteEngine on RewriteBase / RewriteRule ^exampledirectory/(.*)$ /anotherdirectory [R=301,L]

Written by Philip Norton.