Bob's World

Evolution of a Home Server System

2009-08-26T10:27:00.000-07:00

My concept of a home server system has evolved over time.

I got in to a home network early on, long before I had broadband internet or even a modem connection shared via a proxy server. The purpose was sharing files and printers, playing the occasional networked game. The network was peer to peer.

I originally decided that I needed a server right after I lost a hard disk on my home computer system containing irreplaceable information, and decided right then that one fatal hard disk crash was too many, and I needed to be fault-tolerant in the future. My initial implementation of a server was with the oldest, slowest system that I had, a 486 with 20 meg of ram, to which I attached a SCSI disk farm featuring three 6 gig hard drives made into a RAID-5 array. Windows NT Server 4.0 was my choice of operating system, influenced in no small way by similar systems I was working with professionally. This served me well for quite some time, until the point where I outgrew spreadsheets as a repository for data and moved into databases.

I had dabbled in Microsoft Access for a time, but all of a sudden my databases proliferated and multiplied. My choice of a file server with ample (at the time) storage capacity but minimal computing power became quickly overwhelmed, frequently maxed out, and especially at a time when I began encouraging the whole family to take advantage of fault-tolerant storage. So it became necessary create a new server system.

I built my next one from scratch also, a barebones system with state-of-the-art CPU and disks, along with Windows Server 2000. This one fared better at first, however my databases continued to expand.

At this time I began dabbling with Linux, Slackware to be specific. I had purchased Slackware 2.2 a number of years ago, only to set it aside when I could not get X-Windows to work correctly. I have had rather extensive experience with X-Windows, DEC Windows to be specific, and always thought it would be fun to dabble with at home. So now, having been laid off and plenty of time on my hands, I decided to dabble with it again, having about the same amount of luck. I decided an upgrade was in order, and purchased the latest Slackware, 10.2. Bundled with this was MySQL, which I quickly discovered and began to dabble in this as well. It took time, but eventually all my Access databases were ported into MySQL, and I began to explore Apache and PHP as well.

My web server was hosted on a laptop, an old Thinkpad initially, and it did quite well at first. I also ran MySQL on this laptop, this being my Slackware playground. I generated a number of testimonials as to the small footprint a LAMP stack could work within, even as I overwhelmed it eventually. My main MySQL server moved over to the Windows Server system, but even that eventually became overwhelmed as I tested the limits of the system, first with test scenarios and then hosting my ever-growing databases.

A new server system was in order, especially as another desktop computer had crashed, this time with a fried motherboard. My existing server was gutted to make into the new desktop, and I once again bought new hardware. This time I did not even use a barebones platform but completely from scratch this time. I upgraded to Slackware 12.0, used Samba to share the data to the Windows desktops, and put in a Core2 Quad CPU that runs most apps like greased lightning. I consolidated my LAMP stack on this system, and it is working very well. One of my slower pages, a PDF rendering of one of my novels that took three-plus minutes to load via the laptop Apache server, now loads in ten seconds or so. With some of my tables having over a million rows, this computing power is serving me well.

So you can see how my concept of a server has changed over time, from computing power being irrelevant to essential.

Further RSS issues

2007-12-05T13:15:00.000-08:00

My saga with RSS continues. As I stated before, changes to MySQL's forum site led me to pursue using an RSS reader as my main interface to the forums. That worked quite well until recently. My only issue had been that the various replies to a post showed as separate entries to the reader, with no connection between them. Coming back from a self-imposed hiatus, I find that this problem has been fixed, at the expense of creating another one. Now, each thread shows up once only in the reader, along with an indication of how many replies have been posted. This is fine, except that the thread is not marked as updated, and so threads that get replied to are never highlighted as unread by the reader. This of course makes it extremely difficult to conduct timely and efficient interactions with someone on the board. I now have to peruse the forum web page with the thread list looking for new posts, an error-prone procedure supposedly eliminated by the reader, which had been popping up little notes on my screen when someone replied.

I have enjoyed helping out on the boards in the past, and intend to do so again, however without a foolproof automatic means of being notified of new replies, this rather puts a crimp on my activities.

Reaction to the iPhone concept

2007-01-16T18:09:00.000-08:00

I find the new iPhone to be conceptually very interesting. Now, I am very new to the world of PDAs— I have only just started using one, a cast-off Palm Pilot from someone, and already I am thinking of things that it should be able to do but doesn't, or I haven't learned how yet. I suspect that very shortly I will be wondering how I got along without a PDA all these years.

Now, this new iPhone from Apple (I hope that they win the name from Cisco, as I have never ever heard of a Cisco iPhone but the name seems to naturally fit alongside iPod and other Apple products) will apparently be running a version of OSX. I find this very intriguing, because it opens up all sorts of interesting possibilities, including perhaps being able to run MySQL on it, or at least a client, perhaps a small database replicated from a fixed or not-as-mobile source.

At first glance and after hearing various reports, it sounds like a very capable device even without getting under the hood and finding out what makes it tick. Of interest will be what CPU it uses, getting stuff to compile for it, etc.

A Fork In Progress

2007-01-14T12:02:00.000-08:00

It looks like MySQL AB is going about the code fork in a very methodical, gradual, and thoughtful fashion. The latest Community release seems evidence of this. It was released as source-only, as promised, however it was announced that since no community contributions have been made to the community tree, the release was made from the Enterprise tree. In effect, the actual fork has not taken place yet. There is a community tree in evidence in BitKeeper, but it appears to be a placeholder only so far. The changes to date have been with regard to policy and procedure, not code.

I see this as a good thing. This gives MySQL time to evaluate reactions to these changes while implementing them in a gradual fashion. The changes appear well thought out and sound, and reflect changes in the community, the marketplace, and the customer base. It's important to balance all of these factors since they all play a part in the life of the product.

Knowing who the community user is, and who the enterprise user is, is important too. The focus should be on prioritizing catering to the needs of larger groups first, with the knowledge that some may not see their needs met directly. But this is an open-source product, so surely someone can rise to the occasion and fulfill a niche need.

New Bitkeeper client, old one broke

2007-01-08T18:00:00.000-08:00

As has been pointed out, there is a new version (2.0) of the free BitKeeper client, and furthermore the old one (version 1.1) appears not to work anymore. At first I suspected my daughter's hogging of available bandwidth as the culprit when all of my daily pulls of MySQL source trees failed, but that soon proved not to be the case. I am once again pulling source code successfully.

Now that it's forked ...

2006-12-20T10:02:00.000-08:00

Now that the MySQL code base has been forked into Enterprise and Community editions, and things have had time to sort themselves out somewhat, it looks to be pretty much business as usual. The community is as lively as ever in the forums, code fixes are still being pushed to the community tree, etc. There are a few complainers but it seems that you can never please everyone and someone will always find something to beef about. I have been watching the BitKeeper sources, pulling and building nearly every day. Version 5.0 jumps two or sometimes three revisions at a time, I have the strong impression that the skipped numbers have to do with the Enterprise edition. I have not seen any Enterprise source, and I am curious as to when and where this will be available. I also see a lot of activity in 5.1 and some work on 4.1.

Those who need frequent and timely bug fixes basically have two options: subscribe to the Enterprise edition or learn to build from source. Building from source is not rocket science, at least in the Linux world, and I feel it's worthwhile learning how to do this if one wants to be truly proficient and knowledgeable with MySQL.

From my perspective it's pretty much business as usual.

Selecting a Database

2006-12-08T14:00:00.000-08:00

When people are comparing databases and trying to select the correct one for their application, it is a very complex and confusing process. There are many factors, price being not the least of these. Some would opt for a closed-source product because of the extra bells and whistles it offers. I have to say that some of these extra features may represent security risks, or simply a database trying to take on functionality better performed at the application layer. There is a certain argument to have all of your business logic embedded in the database as it certainly eases the burden on the application programmers. But it can allow them to become sloppy, or to allow the database to be exposed directly to the users without even an application layer to protect it, counting on the database to protect itself. In some cases this comes at a price, sacrificing speed for security, when some fairly simple application coding could have implemented the same security and retained the speed. Granted some things are better done in stored procedures than application logic, and it's worth running some tests to see which is better, or considering moving the logic to the database if it is performing poorly in the application.

Price, too is a consideration. With open source, you can test the functionality without an initial investment before opting for a support package. Then, when the decision is made to purchase support, MySQL sweetens the deal by offering the Enterprise package, with some extra functionality just for the paying customer. True, it is new, and has some issues to be worked out, but it is a marvellous concept, and I am curious to see some indication of how well it is selling. As I have stated before, perceived value is an important aspect to some people, the 'bang for the buck' principle, and this new offering caters to that.

Sometimes it is a mindset issue, and I have to say I have been guilty of this at times. It is very easy to move into a new situation and recommend the familiar tools that one is familiar with, even if this means a capital outlay for the new employer. I have done this, and also had this done to me by others, so I can't be too hard on them. Open source requires an open mind in a lot of ways. I see many ingenious solutions to problems. I also see these rejected at times by those of narrow perspective continuing to try to develop solutions in this new environment using guidelines and experience drawn from elsewhere.

Not that experience isn't valuable, but the spirit of innovation must prevail, and a willingness to accept change. Also sometimes one must consider that perhaps the familiar closed-source product has rather bent the standard and changed the rules, so that suddenly finding an environment where these rules are enforced, and with increasing strictness, can be a shock to some. Some complain of this behavior, calling it wrong if familiar code won't execute because it took advantage of an extension previously available.

Sometimes this failure leads to a decision to return to the closed-source world, despite all the good advice on how to adapt.

Inaugural Phoenix Meetup

2006-11-29T21:35:00.000-08:00

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

2006-11-22T09:06:00.000-08:00

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

2006-11-19T14:16:00.000-08:00

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

2006-11-15T10:02:00.000-08:00

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

2006-11-09T13:45:00.000-08:00

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

2006-11-01T06:37:00.000-08:00

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.

A Fork in the Code

2006-10-26T15:56:00.000-07:00

I am waiting and seeing how this new MySQL Enterprise Edition shapes up and how it affects the Community Edition, the Community, and MySQL users as a whole, but everything I have heard about it so far, from official press releases, initial public feedback, and information given to me, I am very impressed with it. This move shows every sign of being well-planned, designed to work positively for all parties concerned, from MySQL staff to paying customers to community members. What I see is added value for paying customers, better reasons for purchasing the paid edition, a focusing of MySQL's resources to better develop the product as well as to devote support to their customers, and importantly, an enabling of the community to be even greater participants than before. Some things taken for granted by some community members such as frequent binary updates may cause some disappointment, but I view this as a small price to pay, and an encouragement to become more literate with the source code, more involved in the process. Something for nothing is an illusion, it is so important in the long run that the community puts back in as much or more as it gets out.

To echo the sentiments of others, it is an honor to be part of this community, to help it along as we are able. MySQL shows no signs of fading away despite naysayers along the way, and its continued existence and popularity attest to its success.

I can't see my databases!

2006-10-12T10:54:00.000-07:00

This common complaint uttered fairly frequently on the forums and some similar related issues appears to stem from some basic misunderstandings of the MySQL account structure. The most common culprit is the "anonymous guest" account set up by default coupled with an initial lack of knowledge of how to connect to MySQL. Personally I have no use for such accounts and remove them when I find them. Presumably the intent is to allow anonymous users minimal access to MySQL, where they are limited to using the `test` database which is also set up by default. Compounding this issue is the Command Line Interface start-menu shortcut created by the installer versions of the Windows packages, which connects users as 'root' automatically and oftentimes unbeknownst to them. Then when the user goes to connect via some other means and presents incorrect credentials out of ignorance, then there is a chance that this guest account may be inadvertantly invoked.

So there they are, connected to MySQL, yet nothing is right. All they can see is the `test` database, and for 5.0 or better, the `information_schema` database. This might also happen if, in order to connect from a remote node, an account such as 'root' is creating with the remote node's name, but not granted any actual privileges.

So therefore the first thing to do when this situation occurs is to execute the following statement:

SELECT user(), current_user();

to find out exactly how one is connected, particularly if there is any doubt. Then one could go back to a known method for connecting as 'root' or otherwise correct the credentials.

Phoenix MySQL Meetup

2006-10-04T15:41:00.000-07:00

It looks like there's going to be a MySQL Meetup forming in the Phoenix area. A few of us are planning to get together next month for an inaugural meeting. There seems to be any number of people in the Valley of the Sun who are interested in MySQL or related topics, so it will be interesting to see if this meetup gets off the ground and attracts any sort of meaningful membership.

Not Able To Connect

2006-10-03T17:07:00.000-07:00

There has a been and continues to be a lot of discussion on the forums for people who are not able to connect to MySQL for one reason or another. Some aspects of this issue have been dealt with, particularly Bill Karwin's article which he published on the MySQLForge Wiki, which I have linked into any number of MySQL Forum threads by now.

I should like to conduct a broader discussion, as to some users, not able to connect does not mean strictly that an initial handshake of any sort could be established with the server, but includes a far broader scope of problems ranging from the server not even being installed let alone running, scripting languages, e.g. PHP, being misconfigured, invalid credentials, or even false assumptions about such things as access from remote computers. When the user is not able to connect, they are oftentimes unable to express the manner in which they are unable to connect, or unaware that there are many ways in which one is unable to connect, and so stating that they are unable to connect and asking for assistance without further explanation is a common occurence.

Client misconfiguration, particularly PHP, is a major issue, but again a Forge article adequately covers, for PHP at least, the possible causes and solutions, and the necessary steps, to enable MySQL support properly. I was able to configure PHP prior to this article being available, but I have prior knowledge and experience, not to mention the patience to sift through a lot of extraneous material to find the specific instructions needed.

Assuming that the client is properly configured, then the problem boils down to three possibilities:

The client can't connect
The client's host is not allowed to connect
Access is denied

Bill's article adequately discusses this first possibility, and the latter two are in reality two sides of the same coin. At this point it is necessary to explain to people the fact that the MySQL user authorization model treats users connecting from different client hosts to be completely different users, with, potentially, completely different passwords and privileges.

Somewhere along in here, also, someone is bound to discover the anonymous guest account which is created by default. This seems to create more problems and confusion, to be more confusing than helpful, since it allows the user to connect as any otherwise unknown username, from the local host, but not to actually accomplish anything except with the test database. Usually the first things I do are to remove this account and the test database.

So the only default account of any real substance is the root account, and this account, again, is only for access from the local host. People have these concepts that the remote host should be added to some list, or some option should be enabled, that all accounts suddenly work from all hosts, and so forth. Personally I should like to see the ability to copy all authorization records for one host to another, or copy one username to another. I know it's easy enough to do this manually using SQL statements, but showing a first-time user how to do this is not the easiest thing in the world.

Another issue in here is when MySQL sees the connecting node as something other than what the user thinks of it as. MySQL either sees the remote host as an IP address, or some name derived either from the hosts file or some other mechanism. Sometimes MySQL sees a domain name, particularly in the case of a Windows client connecting to a server running on Windows. I have a machine that boots either to Linux or to Windows, and MySQL on a Windows Server system sees it differently depending on how it's booted necessitating multiple accounts. That's why it's important to pay attention to the complete error message text. Oftentimes a user will post on the forums only the number or a short phrase, leaving insufficient information to give a correct diagnosis and solution.

Finally there is the scenario where the user did not present the credentials he thought he did. Particularly in PHP it's possible to badly code a call to mysql_connect, such that it doesn't connect, maybe bad credentials, but then not check for errors. Then the next call will attempt to connect using some default set of credentials which is probably wrong. Proper error checking is the answer here, but sometimes incomplete error checking is even more confusing since the user might see the default credentials mentioned, and have no clue where they originated.

Advanced Features as Crutches

2006-09-24T12:40:00.000-07:00

I am glad to see all of the advanced features in MySQL. They all have their place, whether it's making things possible that weren't before, or improving ease-of-use, or improving thru-put and performance. However I find that just because you can use an advanced feature in place of the old way doesn't necessarily mean that you should.

If there are several ways to accomplish the same thing, my first inclination is to go with the one that is simplest to code. My philosophy is that if I arrange the syntax in a logical order with each link in the chain in its proper place, that this will make it that much easier for the server to optimally execute it.

I think that some people will code a subquery in preference to the equivalent join syntax mostly from a basic lack of understanding of how joins work. My personal preference is to code something as a join and avoid use of subqueries wherever possible. If the subquery functionality did not exist (and it still doesn't for a lot of users, but that's an entirely different issue) then people would perforce use joins.

Subqueries are not bad things. They do make things possible that are not possible with joins alone. They can eliminate the need for temporary tables, although a properly-defined temporary table may out-perform a straight subquery. People may code using all the bells and whistles available, saying that it's the software's job to properly optimize the execution. But until the optimizer rivals the human brain in terms of complexity, nothing can beat some good old-fashioned skull sweat in terms of convincing a computer to put out its best performance.

Plus, once you've coded the same sort of problem a time or two, it gets easier to wrap your brain around a scenario and crank out the code using the optimal correct aproach. Trying to convince a newbie that there is a method to the madness, there is a reason for doing things a certain way, that it's not a bug that one approach will outperform another— now there's a challenge.

Some features I see as going further to enhance security than to increase performance; views seem to fall into that category, and to a certain extent, stored routines. Triggers and cursors make many things possible, yet it's also possible to use these to code stuff that looks perfectly nice on paper, yet there is a perfectly simple solution using only basic SQL statements.

It's certainly worth exploring all the functionality, to find out what it can and cannot do, but also to find out what it does well, and what is better done the 'old way'.

Too Many RSS Feeds

2006-09-23T07:52:00.000-07:00

Okay, so maybe I went a little overboard. But having switched to RSS in order to better interact with the MySQL forums, I seem to have embraced this new way of having content delivered to me whole-heartedly. Of course I added all of the available forum feeds immediately, but then also I added feeds to Planet MySQL as well as some of the constituent blogs directly, also a few NASA blogs and some from family&friends, well, before I knew it the list of feeds has stretched the entire height of my monitor, and I have a 21.3" monitor turned into portrait orientation.

I have to admit, I like staying on top of things and having my feeds polled at 15 minute intervals. I may have to figure out how to subcategorize my feeds so I don't have to look at the entire list always but roll up portion of it. I have already trimmed the list a little, removing some feeds that I had more of curiosity than anything else. Some things are outside of my area of expertise, but it is interesting sometimes to try and follow along, I might learn something.

PHP and MySQL Stored Procedures

2006-09-18T15:06:00.000-07:00

Amid recent discussions about calling MySQL stored procedures from PHP or similar API-type interfaces, and mentions of difficulties in doing so, I thought I would post some notes about my experiences. When I first tried to use stored procedures from PHP, I discovered that it worked some of the time, but I did not take the time to delve into the whys or the wherefores. It was also mentioned by some that the mysqli interface was necessary in order to properly interact with stored procedures, but I have since determined that that is in fact not the case. I now conclude that the mysql interface works with stored procedures just fine, and that is fine with me since I am perfectly at home with this interface.

The only issue appears to be when returning a resultset. It's not at all intuitive, but in order to return a resultset from a stored procedure to PHP, one must have either the multiple-statements connect option or the multiple-results option (or both) enabled. If the routine does not return a resultset, neither option is required.

Since the defined names are not available from PHP, one must use the numeric equivalents. You can grep these from mysql_com.h. I used option masks of 65536, 131072, and 199608 with equal success. I confess I do not understand why both these options perform equally well, and why a single resultset from a stored procedure evidently falls into the 'multi' category, but no matter, it works.

MySQL Error Checking in PHP

2006-09-03T10:49:00.000-07:00

So often it seems that I am seeing someone struggling to find out what is wrong with their PHP script. They see the following error message:

Warning: mysql_fetch_assoc():
supplied argument is not a valid MySQL result resource
in /var/www/htdocs/somefile.php on line 18

and have no clue what this means. The line number has no bearing on where the real problem is, and there may be a whole cascade of these messages. Let's assume the code looks something like this:

mysql_connect ("server", "user", "password");
mysql_select_db ("dbname");
$result = mysql_query ("SELECT something FROM mytable");
while ($row = mysql_fetch_assoc ($result))
{
...
}

Where did the error occur? The answer is it could be any number of places. Let's make a list:

The server name is wrong.
MySQL is not running on the server
The script is being run on a host that is not allowed to connect to the MySQL server
Incorrect username
Incorrect password
The database does not exist
The username does not have permission to access the database at all

And finally:
There is an error in the query

So we can play the guessing game trying to figure out where the error is. Or we can do error checking. The next mistake is to do some error checking, but not in all potential places for error. This can be more confusing than no error checking at all. Let's say that we checked for errors after the call to mysql_query but nowhere else, and the connect call failed. We put in the password wrong perhaps. So the call to mysql_connect fails. Then the call to mysql_select_db fails. Finally the call to mysql_query fails. We actually do check the error message here and what we get is the following:

Access denied for user 'nobody'@'localhost' (using password: NO)

At this point our user is scratching his head, because he knows he put in the right credentials, he's double-checked. This error message gives a complete different set of credentials: wrong username, no password, maybe even a diffrent server name. The reason for this, which is not always obvious, is that a connection did not get established via mysql_connect, so PHP reverts to a default set that is in its config file. Chances are nobody has edited it to put valid credentials in, and I wouldn't personally recommend it for security reasons.

So we can see how it is essential to perform error checking every step of the way to eliminate any guesswork as to what went wrong, where it went wrong, and why. The remaining item to discuss is how to check for errors. There are numerous different styles and techniques, each with their own pros and cons. My personal preference is to do several things:

Store the credentials in a separate script. This might even be outside of the htdocs directory tree entirely, so there is no chance of someone referencing it maliciously.
Write a small script that references the credentials script, connects to the database and selects the database, using proper error checking of course.

(At this point we have eliminated the possibility of any connection error being mistaken for a faulty query)
Display a compound error message with detailed information about a failing query.

I prefer a compound error message since it provides complete details, but any of the following will work. I recommend aborting the page load by calling die since it can print a message and do the abort in a single call. Plus it can be piggy-backed onto the mysql function calls using the short-circuit boolean logic which says that if the left-hand operand of an or is true, the right-hand side is ignored. So simply append one of the following forms:

or die()
or die("Query failed!")
or die ("Query failed: " . mysql_error())
or die ("Query failed: " . mysql_error() . " Actual query: " . $query)

I prefer the latter with its complete display of pertinent information. If you have more than one query in a particular document, it may be helpful to add some indication of which on failed to further eliminate confusion.

I go away for three days and ...

2006-08-28T08:18:00.000-07:00

So I've set up this cast-aside laptop as a machine to do pulls from BitKeeper and do builds and installs and it's working pretty well. I was able to do pulls of six different versions of MySQL and build them. They are now happily running simultaneously.

Then I went away for three days, taking my younger daughter to boarding school where she is starting as a freshman in high school. At the same time my older daughter is off to college so the nest is now basically empty. But I digress.

I said 'was' because shortly after that a couple bugs manifested, one as noted before and one new one which I was pleased to report and have verified. I have been following the development of the MySQL Build Farm Initiative and on Sheeri Kritzer's recommendation, I added this machine to the inventory of available machines. Bug #21642, now fixed in the 5.1 tree, continues to be an issue in the 5.2 tree, and now the 5.1 tree is affected by Bug #21855, and I'll see if I can't do some poking and prodding into what's causing that one. I've taken the opportunity when these issues arise to get under the hood and look into what's going on. In the case of Bug #21642 I had my own workaround patch in place almost immediately while waiting for the official fix, and I'll be doing some research into Bug #21855 as well.

The Build Farm idea sounds like a good one, and while I wait to see how it will develop, I'm going ahead with building and installing more or less daily pulls of the available source trees.

Effect of Perceived Values

2006-08-20T16:33:00.000-07:00

In my experience there has been a driving factor, a mindset of those who make the purchasing decisions and are not necessarily technically knowledgeable, that the value of an item somehow equates to the money spent acquiring it. A company I worked for years ago catered to this mindset by way of pricing their product according to factors such as speed, capacity, and price of the target computer. The same software was delivered to all customers, but the understanding was that owners of computers would somehow expect software to be valued in relation to their computer. Owners of expensive computers would expect to have to purchase expensive software for it, and distrust something viewed as too cheap, whereas owners of lower priced systems would not want to pay as much for the software by the same token.

Times have changed but the value/price mindset continues. One place I was at a few years ago seemed caught up in the mindset. They hired a young DBA whose expertise was mostly in Visual Basic and SQL Server, and on his recommendation spent a lot of money on hardware and software, dedicated high-end server system, workstation systems, etc. I didn't know then some of the things I know now, and I'd have come up with a rather less pricey solution if asked today. Back then I probably would have come up with yet a third solution involving mostly development on my part and little if any
hardware investment. Shortly after that they started going down the open-source path with Apache and Perl. They probably would have arrived at MySQL or something similar in time, but a corporate mindset is a hard thing to break.

The idea of switching to an open source database for a web application has a lot to do with highering a web experts and then acquiring for him the tools he requests to fulfill his job. But still the web system is viewed as separate from the 'real' data processing, somehow inferior. The OS database is no less efficient or reliable, of course, but unless that is understood by managers or decision makers it matters little. There may be a slow increase over time, the occasional rogue department that switches over, but it's counter to the corporate culture of volume license deals with large software vendors. A lot remains to be seen how the landscape continues to unfold, but there are signs that times are changing, mindsets are changing.

A busy afternoon

2006-08-14T21:08:00.000-07:00

Having gotten my daughter a new laptop to take off to college where she starts as a freshman in a couple of weeks, I now have her old laptop, still quite serviceable albeit older, larger, heavier, etc. So I set about setting it up as a system where I can do BK pulls of various MySQL versions, be continuously running and testing new pulls from the 5.1 and 5.2 trees.

The first thing I did was to take the laptop, a Toshiba Satellite with a 30GB hard drive, Celeron processor and 256MB RAM, reformat it and install Slackware 10.2. This is my preferred operating system except for a couple of systems that are rather low on my priority to convert, where it's vital to keep them running as Windows because of a number of applications. I set up the following partitions:

hda1  6.6GB /
hda2  6.6GB /usr
hda3  512MB (swap)
hda4 13.3GB /usr/local

The swap partititon is my standard formula, double the size of the RAM, and located approximately in the center of the drive. This is a bit of a departure from my earlier configurations in which I typically created two data partitions, with hda1 incorporating /, /usr, and /usr/local, but latter partition containing directories symbolically linked into the structure on hda1.

Slackware came up okay. I was a little concerned having hda4's mountpoint being on hda3 having in turn it's mountpoint on hda1, that the mount process might somehow mount the partitions out of order for some reason, or have some other chicken&egg sort of problem.

Then I started the bundled MySQL. This is version 4.1.14 of MySQL, and as has been pointed out elsewhere, it does not start automatically out of the box owing to the fact that the Slackware installation does not run mysql_install_db and also it is required to set the permissions on the data directory to correct the error 13 'permission denied' abort when trying to start MySQL.

I then downloaded and installed bk_client-1.1 and pulled the 5.1 and 5.2 sources. I installed Bison 2.3 to correct the sql_yacc.yy compile error I noted previously, and promptly ran into Bug #21642, reported hours earlier, of the undefined reference to `srv_flush_log_at_trx_commit' error that happens when configure is run with no arguments.

Now I just need to figure out what arguments it is necessary to apply in order to make it build properly.

Using Triggers with FEDERATED Tables

2006-08-03T12:02:00.000-07:00

In a recent blog entry I discussed some research I had done into the behavior of MySQL FEDERATED tables. Since then the question arose in the forums regarding the use of triggers with FEDERATED tables, so I performed some additional tests to see how triggers would behave.

The question was asked if triggers could be used with FEDERATED tables, and I verified that triggers could indeed be created on FEDERATED tables. With the assertion that the a trigger on a FEDERATED table did not actually fire, I set up a base table and an associated FEDERATED tables with triggers attached to both tables and verified they did in fact get called.

A 'before insert' trigger on a FEDERATED table gets called before the corresponding 'before insert' trigger on the base table, and this is rather what I would expect. If the FEDERATED table's trigger makes an alteration to the row, the altered version is what the base table's trigger is presented with.

An 'after insert' trigger on a FEDERATED table gets called after the corresponding 'after insert' trigger on the base, and this is again what I would expect. However, there is an anomaly here. If the base table's 'before insert' trigger made alterations to the row which subsequently were written to the table, the base table's 'after insert' trigger sees these changes, but the FEDERATED table's 'after insert' trigger does not. It only sees any alterations made by the FEDERATED table's trigger.

So I can summarize my testing of before and after insert triggers by concluding that information flows during an insert into a FEDERATED table from the server with the FEDERATED table to the server with the base table, but not vice versa.

MySQL 5.2

2006-07-29T18:10:00.000-07:00

I installed MySQL 5.2 today a la Markus Popp although I note his comment that it may not in fact be any different from 5.1 as yet. I have installed MySQL from source before, in the form of the 5.1 source download package. I did BK pulls of both the 5.1 and 5.2 sources, noticing that they required me to upgrade my Bison compiler, where previous builds of 5.1 worked okay with the version of Bison bundled with Slackware 10.2.

Then I went to install it on my aging Thinkpad only to have the hard drive choose this particular moment to give up the ghost. I happen to like this particular system because when people ask about what sort of footprint to expect, I point out my Thinkpad with the 300 MHz Pentium II, 64MB ram and 10GB harddrive running Slackware, Apache, MySQL and PHP. I've been using it steadily for five years and it was refurbished at that, so I can't be too hard on it. I plan to buy a 60GB replacement drive and possibly some more RAM since it has a slot open, and thereby breathe new life into it.

Federated Tables

2006-07-27T17:40:00.000-07:00

A number of people have been recently been inquiring about MySQL FEDERATED tables or are having requirements that seem ideally met using FEDERATED tables, so I thought I would perform some testing and better acquaint myself with the behavior of this storage engine. The first thing I noticed was the spot in the documentation that said:

The structure of this table must be exactly the same as that of the remote table, except that the ENGINE table option should be FEDERATED and the CONNECTION table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.

So I wrote a little PHP script that does a SHOW CREATE TABLE on the base table, alters the ENGINE clause, and adds the appropriate CONNECTION clause. After all if it has to be exactly the same, I may as well automate it and eliminate typos as a source of error.

Then I wrote another PHP script which created a series of base tables, and connected to each of these a series of FEDERATED tables with obvious differences. Lo and behold, it works! The only actual requirements I have been able to discern are:

The base table has to exist. The FEDERATED engine checks this at table creation time.

Columns in the FEDERATED table have to have columns by the same name in the base table.

Column types don't have to match. I haven't tried all the combinations, but I have gotten it to convert dates to strings, numbers to strings, strings to numbers, etc. I have also successfully connected to views.

The question was posed in the forums as to what connections are made and how long they persist. A connection is made briefly, obviously, during table creation since the base table is checked for existence. A persistent connection is made when the table is queried, and hangs about for a period of time controlled by wait_timeout on the remote server. On my system this is set to 28,800 or exactly 8 hours.

So all in all this engine seems like a fairly flexible and useful tool, and beyond the obvious function of making tables availabe to remote servers, can be used to do some data type-casting as well.

Switching to RSS

2006-07-03T18:11:00.000-07:00

I have finally decided to start using an RSS reader. Recent changes in the MySQL forums' behavior, layout and features have made it hard for me to see what forums have new posts have been made— so I will give the reader a try for a while and see if I like it better. I have subscribed to most of the forums, not that I am knowledgeable in all areas, but I find it interesting to read posts on subjects outside of my direct bailiwick.

So far so good, though I notice the feeds for a couple of MySQL forums aren't working, Database Adminstration and .Net. None of the foreign language feeds are working either, but I expect that is because they are brand new and haven't been posted in yet. I like the format of my chosen RSS client, similar to that of an email program for example the one I am using to peruse the news groups. Ultimately it would be nice to have one client that pulls in everything, but this works for me right now.

I can also follow Planet MySQL in the reader too, though that has been working well enough from the web page.

Normalizing a Column

2006-05-04T11:12:00.000-07:00

Recently I joined a networking group, and I received a spreadsheet containing their membership rolls. Of course the first thing I wanted to do was import it into MySQL. Then someone in the forums asked about the very same thing, so I thought I'd document this process here.

The first challenge was the fact that the membership list was on three separate worksheets, and each had a different column layout. I saved the worksheets into text files and imported them into Microsoft Access since it has the facility to create tables using columns derived from text in the first row of the file. I then imported these tables into a MySQL database.

From there I was able to juxtapose the columns correctly and merge these tables into one, with the addition of a new column indicating which worksheet each row originally came from. Then the fun began. There were two columns, one for current employer and another with a list of past employers.

Present employer was pretty easy. I created two additional tables, `companies` and `people_companies`, using the following two statements to initially populate them:

INSERT INTO companies (name)
SELECT company FROM people GROUP BY company;

INSERT INTO people_companies (personid, companyid)
SELECT p.id, c.id
FROM people AS p
JOIN companies AS c ON p.company = c.name;

I then created a stored function with a cursor to process the PriorCompanies column from the People table. This field presented a real challenge. People had entered data into this field any old way, by-and-large using commas as separators, with the occasional slash for good measure. So I replaced these characters with vertical bars, did some special handling of ", Inc" and then looked in the docs for a good element extracting function.

What I was looking for was something like the STR$ELEMENT or F$ELEMENT function I remember from my VMS days. F$ELEMENT is a DCL lexical function, F$ELEMENT(index,delimiter,string) which parses string into pieces based on delimiter as a separator, and returns the index'th part of it. I couldn't find one so I wrote my own:

create function element (idx int unsigned, del char(1),
                str varchar(255)) returns varchar(255)
loop begin
 declare pos int default 1;

 set pos = locate(del, str, pos);
 if idx = 0 then
  if pos > 0 then
   set str = left(str, pos-1);
  end if;
  return str;
 end if;
 if pos > 0 then
  set str = mid(str, pos+1);
 else
  set str = NULL;
 end if;
 set idx = idx - 1;
end; end loop;;

Then all that remained was to create a stored procedure with a cursor and a couple of loops to pull out each prior company name and insert rows as necessary to the companies and people_companies tables:

create procedure normalize_prior () begin
 declare done tinyint default 0;
 declare c1 cursor for
   SELECT Id, PriorCompanies
   FROM people
   WHERE PriorCompanies IS NOT NULL;

 open c1;
loop1: repeat begin
   declare pc varchar(255);
   declare idx int default 0;
   declare co varchar(255);
   declare cid int;
   declare pid int;
   declare continue handler for sqlstate '02000'
                  set done = 1;

   fetch c1 into pid, pc;
   set pc = trim('"' FROM pc);
   set pc = replace(pc, ', ', ',');
   set pc = trim(',' FROM pc);
   set pc = replace(pc, ',', '|');
   set pc = replace(pc, '/', '|');
   set pc = replace(pc, '|Inc', ', Inc');
loop2:   loop
     set co = element(idx, '|', pc);
     if co IS NULL then
       leave loop2;
     end if;
     set co = trim(' ' FROM co);
     INSERT IGNORE INTO companies (name)
                VALUES (co);
     set cid = last_insert_id();
     if cid = 0 then
       SELECT Id INTO cid
                FROM companies WHERE name = co;
     end if;
     if cid != 0 AND cid IS NOT NULL then
       INSERT IGNORE INTO people_companies
                (personid, companyid)
                VALUES (pid, cid);
     end if;
     set idx = idx + 1;
   end loop;
 end; until done end repeat;
 close c1;
end;;

Of course the real fun now was going over all the tables and cleaning up numerous issues, but the main work of organizing the lists was accomplished automatically.

Microsoft Access Undelete or Lack Thereof

2006-03-27T06:56:00.000-08:00

Recently I had occasion to delete a couple of rows from a MySQL table using the Access interface. This is not something I make a habit of doing. Normally with an Access database you highlight some rows, press Delete, the rows disappear, it asks you do you really want to do that, and if you say "No" the rows magically reappear again.

The first thing that happened was an error. I did not have Delete privilege granted to me on this database. I quickly corrected that, noting that the error messages appeared before it asked me for confirmation that I really meant to do this operation.

So, again selecting and electing to delete these rows, as it prompted me with it's readback of what I am about to do to my database that I cannot undo, and do I really want to do it? message, I elected to say "No" just for grins. My rows came back— sort of. They had the #Deleted text in every column, and I had this feeling that they hadn't really come back. Good thing I really meant to delete them!

This got me to thinking. Obviously Access had actually gone ahead and deleted them before asking if I really wanted to delete them. It was expecting to bring these rows back via some mechanism evidently. I checked the table definition and it was a MyISAM table, not a suprise since I had set it that way on purpose. So I did the following:

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ENGINE InnoDB;
INSERT INTO table2 SELECT * FROM table;

and linked this table into Access via ODBC just like the first table. I then did the 'delete a couple rows, change my mind' thing again.

It worked.

So my conclusion is that the Access front end is counting on transactions being supported in order to roll back the operation if the user elects not to perform it.

My real concern here is the "You are about to delete 1 record(s)" message, the key words being "about to"— it has already done so! Especially since it has blithely begun a transaction and deleted your rows, confident in it's ability to roll this transaction back, and completly unaware that the underlying storage engine does not support transactions and has already irrevocably deleted them.

This gives rise to a whole slew of further questions. First of all I don't exactly like Access's style of first deleting the records and then telling me I am about delete them and do I really want to, etc. I really feel it should ask me first.

That said, obviously it sends some command to initiate a transaction. At some point deep in the bowels of MySQL this command performs a null action since the underlying storage engine does not support transactions. However, it returns as if it did. The question is, since I am using an engine that is not transaction-safe, should this command return an error? Should there be an option to enable or disabling returning an error? What were the design factors leading to the current way it is implemented?

A Million Tables

2006-03-08T12:20:00.000-08:00

The question was asked if one could create a million tables in a MySQL database. So I put together a small php script to do just that. It ran all afternoon, all evening, and into the night. It hung during the creation of the 262,731st table. I was running Windows Server 2003, using the NTFS file system on a mirrored volume. Each table was an InnoDB table with a single tinyint field and no indexes. The ibdata1 file grew by more than 3GB during the process although no rows were ever inserterd. Windows file compression is enabled, however and the 4.4GB ibdata1 file actually occupies only 295 MB.

I posted back to the fellow after my script had run for several minutes my calculation that it would take about 27 hours just to create the tables and he might want to rethink his design. I never heard back from him.

Recently another forum post asked a question in a similar vein, about creating "more than a couple thousand" databases. I suspect a rather similar answer would be obtained.

People ask too, about how many columns they can create. People always want database to be like spreadsheets, and keep thinking in the wrong terms. The power of a well-tuned database lies in the simplicity of the schema; the fewer databases, tables or columns the better, and instead efficiently storing as many millions of rows as necessary.