BOFH Hunter

death to the tilde

Well, for the past 2 hours, I’ve been fighting with mod_rewrite to get rid of the ~ character that apache shoves into a url for the UserDir directive by default. Since having a url like http://example.com/username/foo.html is far neater than http://example.com/~username/foo.html, and mostly because hughesjr has been after me to fix it, I finally have a solution.

So, for those of you thinking “uh, there’s an example for doing this right in the httpd docs”… let me save you the trouble. It doesn’t work. the instructions at http://httpd.apache.org/docs/2.2/rewrite/rewrite_guide.html cause the url to be rewritten properly, but then promptly 404’s because it looks in /var/www/html/u/username/ for the files. Afer digging around in google, and working with some friendly folks in #apache (yes they do exist) we have a solution:

RewriteEngine On
#RewriteLog logs/rewrite.log # Uncomment for rewrite logging
#RewriteLogLevel 3 # uncomment for verbose logging
RewriteCond %{REQUEST_URI} ^/([^/]+)
RewriteCond /home/%1 -d
RewriteRule ^/([^/]+)(.*) /home/$1/public_html/$2


Take that bit of code, and drop it into /etc/httpd/conf.d/homedir.conf or wherever else you’d like in your httpd configs, and reload apache. From there you’ll be able to use shorter, sexier UserDir urls for your user’s webpages.

Sysinit triggers

When you boot up a typical RHEL or CentOS system, there are a number of checks that occur during that boot process. While you can see most of them mentioned during startup, or via the green [OK] during boot if you’ve disabled the quiet boot, a few useful ones are still hidden.  These are control files which can be dropped into / by an admin  and are checked silently by /etc/rc.sysinit on boot. These hidden files can have a profound impact on your system’s behavior if you use them properly. Because these files aren’t meant to be permenant, they’re often deleted after they’re used.

Autorelabel

If you have the file /.autorelabel  your system will  check to see  if you’re using selinux, and subsequently relabel your entire file structure on boot.  If you’re having an issue getting your selinux contexts correct, this is a handy way to fix things if you’re rebooting anyway.  Now, this isn’t just a completely automatic utility, so if you want to fine-tune things, or do this on your own, you can add ‘AUTORELABEL=0′ on its own line to /etc/sysconfig/selinux. This will drop you to  init1 so that you can  fix things manually.

Autofsck

Having a file called /.autofsck will cause the system to fsck its filesystems on boot. If you have a file called /fsckoptions, or /etc/sysconfig/autofsck, they’ll be parsed for instructions about just exactly how you want fsck to run against your file systems.

Forcequotacheck

The /forcequotacheck file does essentially exactly what it says. With this file in place, your system will check every applicable mount point for quota compliance.  If you use quotas on your filesystems, keep this one in mind. If not, you can ignore it like everyone else.

Unconfigured

We saved the best for last with this one. The /.unconfigured file, if present on your system will trigger a whole host of actions.  This file will on reboot, essentially return the system to a ‘firstboot’-like state. It will prompt you for a keyboard type, root password, network configuration info,  timezone, and authentication method. This file is useful for resellers and VARs who push out machines on a regular basis. You can install, configure it how you want it, put all your bits in, and then drop a file and ship it off to a customer to ‘configure’ when it arrives.  It also makes for a darn MEAN April fool’s day joke with your local BOFH.

PHP with ODBC and MSSQL

Building off yesterday’s entry, today we’re going to add php into the mix. Mostly to add php, you’ll need to go through all of the previously mentioned steps, and a couple more for php. I’m going to assume that you have isql odbc queries working and that you’ve installed php-odbc. We’ll move on from there.

Since the Data Source Names, or DSN’s are user specific, and apache is a system account, we have to change a few things to make this work. You’ll need to create a system wide DSN, and to do this, we’re going to need to edit /etc/odbc.ini.

Open up /etc/odbc.ini as root, and add an entry similar to the data template we added yesterday. It should look like the one below.

[mymssql]
Driver = FreeTDS
Description = Sample Database
Trace = No
Server = my.mssql.server
Port = 1433

With this in place, you can go ahead and get your php ready. Sample code is below:


<?php
echo "How many users logged in last week";
//Connect to the database
$connect = odbc_connect("mymssql", "statuser", "statpass") or die("Could not connect to the database");
//Basic query, salt to taste
$query = "SELECT COUNT(user_id) from USERS";
// actually run the query
$result = odbc_exec($connect, $query);
//iterate through the results to test
while(odbc_fetch_row($result)) {
odbc_result_all($result);
};
odbc_close($connect);
?>

That’s it. That’s all it takes to make php work with Microsoft SQL Server via odbc. There are some issues that you may run into. For example, counting the results on certain versions of MSSQL will always return a value of -1, which is less than useful. You can either code around this yourself, or you can use adodb to communicate with your database and continue to simplify things.

Chatting with MSSQL

As much as I wish I could have a pure Linux machine room, this just is not to be. I have to watch over several windows machines in addition to the Linux bits I love so much. Recently I’ve had a need to talk to our Microsoft SQL servers and pull data from them to our Linux servers for statistics gathering. In order to get CentOS to talk to Microsoft’s SQL server, you’re going to need FreeTDS.

To get FreeTDS, you will need to use the RPMForge repository. With RPMForge enabled, run the command yum install freetds. Once this command finishes it’s time to configure freetds.

To configure FreeTDS, there really isn’t that much you need to do. Simply open /etc/freetds.conf, scroll to the bottom of the file, and add a line for your MSSQL server similar to the sample listings in the file already. Basically you should have a section looking like the one below:


[mymssql]
host = my.mssql.server
port = 1433
tds version = 8.0


Now, at this point we need to test the connection between FreeTDS and MSSQL, so I’ll assume that you have a user who can connect to MSSQL via a network connection. If not, you need to make one. To test FreeTDS, open up a terminal window and type in tsql -S mymssql -U username If all goes well, you should be prompted for a password, and then get a numbered prompt. See below for the expected results:

[root@statbox etc]# tsql -S mymssql -U statuser
locale is “en_US.UTF-8″
locale charset is “UTF-8″
Password:
1>

If you get the numbered prompt like the one above, then so far so good. At this point, you can actually run sql queries directly from FreeTDS’s tsql client, but it can be incredibly cumbersome to do so. A better method to extract data from MSSQL is to use the unixODBC package to communicate with FreeTDS for you. The unixODBC client will clean up and clarify the responses you get from FreeTDS so that you can actually make sense of things. It should already be installed on your system, but you can verify by running the command rpm -q unixODBC. If rpm tells you that it’s not installed, simply use yum to get it from the base CentOS repository.

To use the unixODBC package, we again have to configure a few things, because by default unixODBC comes configured only for postgresql. Most of the guides you’ll find reference the gui utilities for configuring things, but there’s really no need for all of this. The easiest way is to simply create a couple template files and import them.

The first template file that we need to create is the driver, which tells unixODBC how to talk to FreeTDS. For this, open up your favorite text editor (it had better be vim) and create a file called driver.tpl with the contents listed below:

[FreeTDS]
Description = version 0.64
Driver = /usr/lib/libtdsodbc.so.0

Now save this file, and import it (as root) by running the command odbcinst -i -d -f driver.tpl. You should get some output similar to the following:

odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc

You can verify that everything was added properly by checking the contents of /etc/odbcinst.ini. Now what we need to do is set up the data portion for ODBC. This is done on a per-user basis and creates a DSN, or Data Source Name.

As your normal user (or whoever will be connecting to the database), create a text file called datasource.tpl with the following format, substituting your own information where appropriate:

[mymssql]
Driver = FreeTDS
Description = Sample Database
Trace = No
Server = my.mssql.server
Port = 1433
Database = puppies

Once you’ve got this file all set, we need to create the DSN. To do this run the command: idbcinst -i -s -f datasource.tpl. We should be able to test this now by using isql. You should see a prompt like the one below:

[user@statbox ~]$ isql -v mymssqll statuser statpass
+—————————————+
| Connected!
|
| sql-statement
| help [tablename]
| quit
|
+—————————————+
SQL>


That’s pretty much it. From here, you can simply use isql to run your mssql queries and grab the information that you need, just like you would with mysql or postgresql.

Getting apache to play nice

By default in CentOS and RHEL, apache does a very good job of handing out what you tell it. However if you deviate much from the usual html and php filetypes, you may find that some browsers try to render your files as text. Sometimes this is the desired behavior, and sometimes it’s not. I’ve been compiling a list of various extensions added to apache via the AddType directive for some time now, and enough folks have asked for it that it’s probably time to share it out. For those of you who are interested, here’s the filetype.conf I use. If something is missing, there are corrections to be made, or you’ve got questions, please ask in the comments.

Download it here