Category: Sysadmin

System administration is a special are of IT. It also has a special place in my heart. It is an interesting mixture of all the other disciplines, both common across the whole industry, and at the same time unique for each person, company, and geographical location. When I have something to say or share about system administration, I use this category.

Fixing RT3 on Fedora 10

We upgraded our development server to Fedora 10 over the weekend.  Among other things, it runs RT3 – excellent support, issue management, and bug tracking tool.  Once the upgrade was over, we ended up with a semi-working setup of RT3.  The emails were going through just fine, but the web interface was giving out a blank screen with no content or errors or warnings.

Googled a bit, but that didn’t help a lot.   Went through server logs and found an out of memory shout from Storable.pm:

2325:Callback called exit at 
../../lib/Storable.pm (autosplit into ../../lib/auto/Storable/thaw.al) 
line 415.

Googled for that, but it turned out that quite a few people have the problem with this module running out of memory.  And not only in RT3.

So I left it where it was and had some good night sleep.  And it helped.  In the morning, englightment came in, and I tried reloading the page with cookies and cache cleaned.  It worked.  And then it didn’t work again.  Cleaning cookies was helping for a couple of page views.  So I dived back into the RT_Config.pm file to see my options.  There it was.

=item C<$WebSessionClass>
C<$WebSessionClass> is the class you wish to use for managing Sessions.
It defaults to use your SQL database, but if you are using MySQL 3.x and
plans to use non-ascii Queue names, uncomment and add this line to
F<RT_SiteConfig.pm> will prevent session corruption.
=cut
# Set($WebSessionClass , 'Apache::Session::File');

Once I enabled Apache::Session::File, the problem went away.  We are now back to work, enjoying the new web 2.0 round corners interface, pastel colors, and more.

On remote logging with syslog

We’ve been doing some interesting things at work, as always, with yet more people and Linux boxes.  And of the side effects of mixing people, Linux boxes, and several locations is this need for some sort of centralized logging.  Luckily we have either syslog-ng or rsyslog daemons installed on each machine, so the only two issues seemed to be reconfiguration of syslog services for remote logging and setup of some log reading/searching tool for everyone to enjoy.

As for log reading and searching, there seems to be no end of tools.  We picked php-syslog-ng, which has web interface, MySQL back-end, access control, and more.  There were a few minor issues during setup and configuration, but overall it seemed to be OK.  I also patched the source code a bit in a few places, just to make it work nicer with our setup and our needs  (both numerical and symbolic priorities, preference for include masks over excludes, and full functionality with disabled caching).  In case you are interested, here is a patch against php-syslog-ng 2.9.8f tarball.

Once everything was up and running and we started looking through logs from all our hosts in the same place, there was one thing that surprised me a lot.  Either I don’t understand the syslog facilities and priorites fully (and I don’t claim that I do), or there is just too many software authors who don’t care much.  Most of our logs are coming in at priority critical.  Even if there isn’t much critical about them.  Emergency is also used way too much.  And there is hardly anything at debug or info or notice levels.  (RT, SpamAssassin, and many other applications seem to be using critical as their default log level).  Luckily, that  almost always is trivial to fix using either the configuration files or applications’ source code directly.

Passwords are like women

I don’t know if this was posted by someone else somewhere else before (probably it was), but that’s what I came up with yesterday, while explaining our password policy to one of the (male) colleagues.

Passwords are like women:

  • you should have as many of them as you can
  • you should change them as often as you can
  • you should never share them with another man

Judging by reaction, I got the point across.

Oracle and PHP – the deadly mix

WI’ve spent most of the last week getting into, around, and out of the issues related to interoperability of Oracle and PHP.  Before you start laughing, cursing, and blaming, Oracle wasn’t my choice of the database for this specific project.  It’s just the company already had it installed and working for the background, and there needed to be some integration with the front, which is of course MySQL and PHP based.

First thing I do, obviously, is visit PHP.net to check for the prefix of the functions that I need for Oracle.  Through out my experience with PHP, that’s about the only thing I need to know to start working with the new database.  Oh, and the PHP module installed to provide those functions. Oracle interface for PHP is called is called OCI8.  All you need to do now is install the oci8 module.

Here comes the first trouble.  oci8 is not provided as a pre-compiled package for Fedora Linux.  There is an alternative yum repository – Remi, which has oci8 RPMs, but first of all, the oci8 module is compiled against somewhat outdated Oracle headers (version 10.2.0.4 instead of the latest 11.1.0.1), and it also needs to replace your native PHP and MySQL packages.  I tried that, and it sort of worked, but I wasn’t happy.  So I got my Fedora packages back and decided that I need to compile oci8 myself.

In order to compile oci8, one needs to download Oracle InstantClient (basic package) and some header files (devel package).  These can be downloaded from the Oracle web site, for free, minus the time for the registration.  The little trick here is that during oci8 compilation process, the includes are searched from locations which do not include the one from Oracle RPM.  I did a simple symlink of the includes folder to where Oracle headers were, and compilation went on just fine.  (Hint: otherwise you’ll get a whole lot of Zend related messages and a fatal error).  Gladly, I only had to do this path correction on the Fedora 9 machine.  My production server with Red Hat Enterprise Linux 5 compiled oci8 without any problems all by itself.

Update: more detailed instructions on the actual installation can be found here and here.

Now that oci8 installed and configured, I spent some time figuring the correct way to specify the DSN.   Oracle uses some weirdly name file (tnsnames.ora) in some weird location, but luckily there is a way to go around it.  More so, I recommend that you remove tnsnames.ora file altogether, since it can add to your troubles.  For example, if you mix spaces and tabs as whitespaces in that file, you are screwed.  So, just get rid of it.  The way you specify DSN is directly in the PHP script, and you use the syntax like so:  “//hostname.or.ip:port/dbname“.  Intuitive, I know.

Once you’ll get connected to the server, you have a whole bag of surprises waiting for you.  That is if you are too used to working with MySQL.  First is the syntax.  Oracle is using PL/SQL, so you wipe the dust of from that really old Pascal textbook that you have somewhere.  “begin :result := some.procedure.call(:param1, :param2); end;” – that sort of thing.  Secondly, you’ll be happy to know that prepared queries are supported.  So your workflow will slightly change.  Perl programmers will feel more at home here.  oci_bind_by_name() and oci_execute() are your friends here.  Oh, and while you are at, get familiar with the types of the parameters, because they are important.  And don’t forget that you’ll have to bind each and every variable in the query, or get a fatal error. And since you are learning something here, get ready for the oracle errors.  The most frequent one you’ll get would be something like “Failed to retreive the error message for ORA-12345”, where 12345 would be a number of the error.  So you’ll google for ORA-12345 and ORA-54321 and ORA-XYZZZ a lot.  But than you’ll have a wrapper library and you’ll be OK.

Update: as was noted in the comments, PL/SQL is just an option, not a requirement.  Also, most of the headaches of the above paragraph could be avoided by using one of the PHP frameworks.  I personally haven’t yet tried the framework yet, since I’d like to see things working directly first.  Especially since we are not in the test mode only.

The bigger surprise is still waiting for you though.  You are very likely to discover that OCI8 implementation for PHP is very slow.  And I do mean extremely very slow.  I couldn’t believe that it could be slow, so I went into the source code and OMG!  It is really slow.  The slow part is around fetch_all() against fetch_row().  Basically, it’s always row by row and never all, even if you tell it how many rows you need fetched.

In my case, I have the server a bit far away, and there is a possibility to get many rows back.  So even for a simple query with 140 rows in results I was getting 20 seconds execution time.  Oracle was serving results fast, the network was OK, machines on both sides were powerful and all, but it was still taking 20 seconds or more.

I am still trying to find the solution to this issue, but so far it seems that the current way I do it will be the way to do it.  And the way I do it now is the following.  Never ever run direct SQL queries.  Everything goes through a stored procedure.  The results are returned all in a single row.  And that single row has the BLOB (CLOB actually) with all results in one single XML.  Fetching works good enough to get it, and then parsing is done with one of the billion XML parsers for PHP.

In my case MiniXML worked pretty good until bigger results started coming in.  That’s when I learned an important lesson.  MiniXML parses XML with a regular expression.  PHP has a couple of settings in the configuration file that limits the size of the memory and recursion during regex parsing – pcre.backtrack_limit and pcre.recursion_limit.  If you really want to kill your server, set these to -1 (instead of default 100000) and try a regex against a 1 MB XML file.  Enjoy, cause it won’t be long before everything goes down. I didn’t feel like changing from MiniXML so we just implemented some limits in the queries and stored procedures on the Oracle side, and add a few checks in PHP fail rather than crash the system.

So, to some it up, here is my experience with Oracle and PHP from the last week:

  • I had to register on Oracle web site to download packages
  • I had to re-learn my long forgotten compilation skills
  • I had to go read some C
  • I had to step on the “re-inventing the wheel” path more than once
  • I am parsing XML when working with the database
  • I had a head ache more than twice
  • I didn’t have much fun
  • After all, it works.  Sort of.

One last point in this saga is about Googling.  Ask me any question, and I do mean any question, about MySQL.  Heck, even PostgreSQL.  And the answer is just there, on the first page of Google results.  In any human or programming language.  For any operating system.  You’ll be sorted out and working in less then a minute.   Then, try asking even the simplest of the simplest questions about Oracle and PHP.  Sometimes you’ll find something.  Some other times, you won’t.  The overall feeling I have is that not a lot of people are using Oracle with PHP, and those of them who do are in their majority not very happy.

Now I’ve joined the army.

mp3 collection maintenance

I have a rather large MP3 collection.  The directories and files are named correctly more or less, but ID3 tags used to be a mess until very recently.  Two applications helped me to bring some order in that mess.

EasyTag, a GUI application, that helped me to fix lots of broken and add lots of missing comments to my MP3 files.  The smart thing about this program is that it can figure out a lot of data from the names of the files and directories, and that it can grab and replicate partial data from within the albums.

The second program that I wanted to mention, I just found out about today (thanks to Michael Stepanov’s delicious bookmarks).  It’s called tag2utf.  It’s a little Python script that converts the encoding of ID3 tags from koi8 or cp1251 (two most widely used Russian encodings) to utf8.  It’s very easy to install (the only requirement my system needed was python-eye3d library, which exists in Fedora repository) and use.  Just run it from the command line with no parameters and it will recursively look in the current directory for any files that have ID3 tags in non-utf8 encoding.  It will then give you a choice of two encodings to select from (koi8 or cp1251), a “skip” option, and a “manual” option.  All you will have to do is take a quick look at the files, and chose to either convert them from one of the two options, skip them or convert manually one by one.  You will have to make this choice for every directory with non-utf8 files.  Optionally, you can specify on the command line which directories to scan.  In case you need to convert from some other non-Russian encoding to utf8, the script is trivial to modify.

Both tools are excellent pieces of software.  It took me practically no time at all to fix my mp3 collection.  Now I can search it better, and all files display nicely in any mp3 player.  Brilliant stuff!