Inaugural Phoenix Meetup

So the first ever Phoenix MySQL Meetup finally happened tonight. Four of us went to The Yardhouse to sit around and talk geek talk about MySQL and related topics. Hopefully more will come to future meetings. The focus seemed more social than technical, comparing notes on individual backgrounds and occupations. We'll have to see how it evolves as it grows. It'll be interesting seeing what the core group is and what the common interests, an opportunity to compare notes and do some networking of the social variety.

Upgrading PHP

Since the recent demise of my Thinkpad, and the musical hard drive game leading the Thinkpad's hard drive being installed in the Toshiba, the Toshiba has been operating with an insufficiently sized swap partition. This has led to severe performance problems, particularly when doing MySQL builds. So I enlarged the swap partition, deleting the operating system partition and in fact replacing it with two equally sized partitions, similar to the partition layout on the Toshiba's original hard drive.

So of course this required me to reinstall Slackware, MySQL, and PHP. I have found the Apache bundled with Slackware 10.2 to be ample for my needs so far. So first I ran my pulls script to pull and build all the different MySQL source trees, and installed the six instances I normally operate. So far so good. Then I turned my attention to installing PHP.

The first time I installed PHP (5.1.4) I must have had an extraordinary case of beginner's luck since the install went smoothly and without and problems. That has not been the case since then. The next time I reinstalled PHP 5.1.4 I found that the new MySQL client libraries didn't agree with the PHP build scripts, resulting in unresolved symbols. I had to get MySQL 5.0.16 out of the archive in order to build PHP. And now, hearing that PHP 5.2.0 is out, and resolves several issues, I decided to go with that. I immediately began to encounter issues.

First it doesn't seem to want to generate the libphp5.so file. I have managed to persuade it to, but not reliably or repeatably. The next issue is how it works with MySQL. Linked to the 5.0.32 client libraries, it doesn't seem to want to connect to a 5.0.18 server on Windows 2003, my production server at the moment. So I opted to link it with the 5.1.14 libraries. This connects okay, but gets the "Commands out of sync; you can't run this command now" error when trying to perform more than one query from a connection to MySQL. This is annoying but can be worked around by closing the connection and reopening for each subsequent query.

Then there's the issue of PHP not loading GD, and not being able to recognize any of the image functions. I make a certain usage of these, and it's annoying, not a showstopper but causing cosmetic issues.

I suppose I can fall back to a known working configuration but I like to stay reasonably up-to-date so I am continuing to work these issues. Also it's interesting to see how PHP fares with different versions of the MySQL client library.

PHP, MySQL Stored Procedures, and Recursion

A current project which now includes the use of PHP, MySQL Stored Procedures, recursion, temporary tables, etc., and some of the issues encountered along the way, leads me to write up these notes to share with others. Hierarchical datasets have been discussed before, and the means to traverse them in SQL. This current dataset is not hierarchical, not even a partial ordering, but a network, somewhat along the lines of a social network. In this instance it is musicians who play for bands, and creating an extended network by following up on other bands that musicians played for, and also researching all members of a particular band that a musician is a member of, ad infinitum or at least to maximum recursion depth, currently set at 25.

Right now the stored procedures consist of two: a non-recursive function that creates a temporary table and makes the initial call to the recursive procedure. No resultsets are returned; the PHP script that calls the procedures performs a query against the temporary table. I wanted to write a stored function which would call the stored procedures, query the number of rows in the temporary table and return that value, but I ran into all sorts of issues there and am currently stymied. More on that in a moment.

Anyway the current set of procedures is performing nicely, creating a temporary table (engine=Memory) and populating it with all of the bands and musicians along with degree of separation as indicated by recursion depth. My notion is ultimately when researching a musician, a group, or a song, to be able to display related groups, musicians, works, etc. Currently I am not crawling the network created by linking songs to artists but I hope to add that feature in a future version.

The idea of creating a function to return the row count in the temporary table was to then run a query of all artists and this number, ordered by the count, thus to find the largest networks in order to explore them. The major issue that is blocking this is that functions can be called from statements, and statements can be part of transactions, and therefore stored functions execute in a different environment than stored procedures call directly. This environment is then imposed on any stored procedures that may be called, rather like the restrictions imposed by calling stored routines from triggers. I can understand the rationale for conducting execution in this fashion, but it is not less frustrating to be so stymied.

MySQL allows the CREATE TABLE function to be executed from this limited environment, however either the actual creation of the table is delayed until the function completes, or the function is not allowed to do the COMMIT, implicitly or explicitly, required to make the new table visible during the remainder of the execution of the function. So the CREATE TABLE completes successfully, but the new table either does not exist yet or is not permitted to be visible, thus guaranteeing an error when the table is subsequently referenced.

Future attempts at implementing this function will undoubtedly center around having the table already exist, and possibly doing a TRUNCATE operation, but these solutions are not as elegant in my mind, necessitating a prior query from PHP to perform the setup work.

R. I. P. Thinkpad

It is not worth salvaging at this point. I got a lot of use out of it in the time I've had it, and it's been very dependable, considering that I bought it used. It had failed before, and I replaced the hard drive, but now the display has failed. In retrospect it's probably been giving me warnings for a good six months or so. These flat-panel LCD displays have a fluorescent light in them, and it's been doing what aging fluorescent lights do— flickering at first, taking time to get to full brightness, etc. Now it lights up dimly for a few seconds and goes out. I can faintly see the image still on the display. I've never had a display fail like this, but as CRTs continue to go by the wayside, and flat-panel displays are pretty much the norm now, I expect we'll see more of this as time goes on. I opened up the display, hoping maybe I'd find a bulb I could replace, but no such luck. It actually turns out to be a Samsung monitor tucked in there, and certainly not worth the cost of replacing it even though replacements are still available.

So I played musical hard drive. Into the Toshiba laptop, the one I've been doing BK pulls from, have my six instances MySQL, various versions, set up on it, and potential Build Farm machine, I put the new hard drive from the Thinkpad. I copied what data I had from the Toshiba's old hard drive. Actually it's Hitachi, I've been see a lot of Hitachi hard drives lately. My old Thinkpad hard drive was a Hitachi, the new one I bought was also, and my father-in-law's HP laptop had a Hitachi drive that died. I gave him the old drive from the Toshiba since I didn't need it any more.

I miss the Thinkpad. I found its keyboard layout very easy to use, mimicing the standard computer keyboards by maintaining the familiar layout of key groups albeit placing them where they'd fit. By contrast the Toshiba's non-alphanumeric keys seem placed arbitrarily. Also I much prefer the track-point eraser-head mouse of the Thinkpad to the track-pad type of mouse control. I've gotten used to using it, but I'm always inadvertantly touching it when I don't mean to, and having that quick touch interpreted as a mouse click. It's not a terrible big issue since I don't go into X Windows that often, and do most of my work from the console.

Editing a database with SED

As I had the occasion to take an existing database and make some wholesale changes to it and reload it under a new name, and particularly wanted to do this possibly numerous times while I tweaked the changes I'm making, the thought had crossed my mind to use sed or something similar to take a database dump, modify a few of its particulars, and load it under a new name. With that in mind I created a bash script rather like the following:

#!/bin/bash

mysqldump -h server -u root -ppassword \
      --database olddatabasename \
      --routines \
      --ignore-table=olddatabasename.page_content \
      --add-drop-database \
      --add-drop-table \
\
| sed -e "s/\`//g" \
      -e "s/olddatabasename/newdatabasename/g" \
      -e "s/Song/Work/g" \
      -e "s/song/work/g" \
      -e "s/artists_works/artist2work/g" \
\
| mysql -h server -u root -ppassword

mysql -h server -u root -ppassword newdatabasename << '$$'
      grant select on newdatabasename.* to PhpUser@boblap;
      alter table artists add column MiddleName varchar(20) after FirstName;
      alter table artists add column Suffix varchar(10) after MiddleName;
$$

As you can see I piped the output of mysqldump into sed, made a few blanket changes, then piped it into mysql to load the new database. Then I ran mysql again to make some additional changes. All this in the context of a bash script that I can run repeatedly as needed until I'm satisfied with the changes.

The thought also occurred to me, especially for large databases, of breaking the operating into several parts, e.g. first the schema only, then the data, table by table if necessary. Obviously this is meant more for development than production, but this could be used for some complex scenarios or blanket changes potentially tedious and time-consuming if done by hand, not to mention the likelihood of error.

Meetup Next Week

The first ever Phoenix MySQL Meetup is less than a week away. While there are a few more members now, the inaugural event will most likely be just a few people. I am curious to see how this develops and how many other MySQL types there are in the Valley, especially as more companies are coming here that make use of or partner with MySQL in some way.