Today, I was giving a one-on-one PHP training course covering databases (we were trying to get mssql to work with PHP on Windows, but various factors seemed to conspire against us - possibly permissions related, as it seemed to refuse to allow us to select from a table that fricking well did exist.). Anyway, the amusing story was.....

This is just a quick note, incase it helps someone else in the future.

It seems that MySQL server, when using MyISAM tables doesn't truncate the index file when you run "ALTER TABLE x DISABLE KEYS", so when running "ALTER TABLE x ENABLE KEYS" you end up with an MyISAM index file (xxxx.myi) which keeps growing. In my case this hit 90gb on a customer's server.

Ways to fix this :

1. Upgrade to MySQL 5.0.52 (apparently ... see
   http://bugs.mysql.com/bug.php?id=4692 )
2. Run "optimize table xxxx"

The 2008 UKUUG Spring conference is coming up soon (well, 31st March to 2nd April). It's focus is large scale systems admin, but some of the talks look appealing to a wider audience (e.g. anti-spam). The good news is that it's in Birmingham, which seems to be attracting a few more technical events lately :)

This week, we are releasing our first full open source project.

It's basically a piece of web-based software, written in Python and PHP, that allows you to allocate tickets from multiple Trac projects to a simple week-based 'board' in order to organise work priority for developers.

We've scheduled some more PHP training courses up to June next year, running monthly.

People who are active members of Birmingham, Wolverhampton or Redditch and Bromsgrove LUGs or PHPWM can receive a discount of £25/day

See here for more details

Intro

Some notes on MySQL (v4.1.13) optimization where I managed to reduce the time of performing a task from 1 hour to 9.4 minutes.

Background

While working for one customer, a task I had was to consolidate (mergE) data from a number of separate databases into one. This involved importing a relatively large quantity of data into a central MySQL database.

While writing some PHP Training materials for Pale Purple, I thought I'd add an updated guide on PHP and database access. I've already done one on PEAR::DB, but PEAR::MDB2 is it's successor and has a slightly different API.... and as PEAR::DB is now deprecated, it's probably about time I rewrote it anyway.

Postfixadmin Squirrelmail plugin

I found a Squirrelmail plugin for Postfixadmin the other day on Sourceforge. It provides support for setting vacation/auto-response messages, adding aliases/forwards and changing your password.

Unfortunately it didn't support PostgreSQL, and as we later found out, it was full of SQL injection holes....

PHP5, Propel and Smarty

Currently we're working on a large (well, I think it is) project to build a web based events management system. We've chosen to use a combination of Propel, PHP5 and Smarty to build the application. Here's a really quick round up of what we've discovered so far:

• Propel rocks - not having to write an SQL database schema, or write SQL for simple actions is great (it does also allow you to write SQL if you need to for more complex queries).
• Propel rocks - it's database agnostic... so changing backend should be easy.
• Propel has a build phase where it reads schema.xml, creates a load of PHP5 object files (which you use to interact with the database) and then sets up the database structure for you... this on it's own removes a large chunk of my work.Propel allows you to define validation rules on fields - they're easy to check against, and after integrating them with Smarty we have automatic form field validation and good feedback for users.
• PHP5's reflection support has proved invaluable.
• Smarty was giving me problems with it's output modifiers - I hoped I could use the 'escape:"htmlall"' string to sanitise all output - unfortunately this didn't seem to work with objects (at least embedding {$myobject->getMethod()} within a template), so I've had to override the $smarty->assign() function to sanitise objects as well as other variables.
• I think I've got to the point where I can automate all the code necessary to add/update/view a particular record, to the extent that I'll need to write a Smarty template file, and about 3-10 lines of PHP behind the scenes to plug it into Propel (this does of course rely on libraries I've already written). This is good.

So full steam ahead! I'll probably write this up in far more depth either as a Linux User article, or on here, or both.

PHP5 and PDO

For some time now, we've been using PEAR::DB to do any database stuff from within PHP scripts. It's attraction was the ease of changing from one database to another. After I wrote a little about it here I had a few comments regarding performance. After the last phpwm user group meeting, I thought I'd better try and give PDO a go (thanks to Rob) as it appeared to provide a uniform cross platform, cross database method of accessing data. This details my initial findings. (PDO probably requires PHP5).