Basic string searching in MySQL table columns
29th April 2010
Last weekend, I ended up doing a spot of file structure reorganisation on the web server for my Assorted Explorations website and needed to correct some file pointers in entries on my outdoors blog. Rather than grabbing a plugin from somewhere, I decided to edit the posts table directly. First, I needed to select the affected observations and this is where I had to pick out the affected rows and edit them in MySQL Query Browser. To accomplish that, I needed basic string searching, so I opened up my MySQL e-book from Apress and constructed something like the following:
select * from posts_table where post_text like '%some_text%';
The % wildcard characters are required to pick out a search string in any part of a piece of text. There may be a more sophisticated method, but this did what I needed in a quick and dirty manner without further ado. Well, it was what I needed.
Shell swapping in Windows: PowerShell and the legacy command prompt
28th April 2010
Until the advent of PowerShell, Windows had been the poor relation when it came to working from the command line when compared with UNIX, Linux and so on. A recent bit of fiddling had me trying to run FTP from the legacy command prompt when I ran into problems with UNC address resolution (it's unsupported by the old technology) and mapping of network drives. It turned out that my error 85 was being caused by an unavailable drive letter that the net use command didn't reveal as being in use. Reassuringly, this wasn't a Vista issue that I couldn't circumvent.
During this spot of debugging, I tried running batch files in PowerShell and discovered that you cannot run them there like you would from the old command prompt. In fact, you need a line like the following:
cmd /c script.bat
In other words, you have to call cmd.exe like perl.exe, wscript.exe and cscript.exe for batch files to execute. If I had time, I might have got to exploring the use ps1 files for setting up PowerShell commandlets, but that is something that needs to wait until another time. What I discovered though is that UNC addressing can be used with PowerShell without the need for drive letter mappings, not a bad development at all. While on the subject of discoveries, I discovered that the following command opens up a command prompt shell from PowerShell without any need to resort to the Start Menu:
cmd /k
Entering the exit command returns you to the PowerShell command line again, and entering cmd /? reveals the available options for the command, so you need never be constrained by your own knowledge or its limitations.
Removing a column from a MySQL data table
19th April 2010
My trying out WordPress 3.0 in advance of its final release has brought me errors on the management page for website links. After a spot of poking around the TRAC, I found that the bug already has been reported and that the cause is an extraneous column in the *_links table called link_category. The change in taxonomy handling over the years seems to have made it redundant, so I removed the said column from the database using a command like the following from both the MySQL command line and MySQL Query Browser:
alter table database.wp_links drop link_category;
That seems to have made those errors go away, and I hope that their upgrade code takes care of this before WordPress 3.0 is let loose of the general blogging public. Taking out the coding brittleness would do too.
Relocating the Apache web server document root directory in Fedora 12
9th April 2010
So as not to deface anything that is available online on the web, I have a tendency to set up an offline Apache server on a home PC to do any tinkering away from the eyes of the unsuspecting public. Though Ubuntu is my mainstay for home computing, I do have a PC with Fedora installed, and I have been trying to get an Apache instance to start automatically on there unsuccessfully for a few months. While I can start it by running the following command as root, I'd rather not have more manual steps than is necessary.
httpd -k start
The command used by the system when it starts is different and, even when manually run as root, it failed with messages saying that it couldn't find the directory while the web server files are stored. Here it is:
service httpd start
The default document root location on any Linux distribution that I have seen is /var/www and all is very well with this, but it isn't a safe place to leave things if ever a re-installation is needed. Having needed to wipe /var after having it on a separate disk or partition for the sake of one installation, it doesn't look so persistent to me. In contrast, you can safeguard /home by having it on another disk or in a dedicated partition, which means that it can be retained even when you change the distro that you're using. Thus, I have got into the habit of having the root of the web server document root folder in my home area, and that is where I have been seeing the problem.
Because of the access message, I tried using chmod and chgrp, but to no avail. The remedy has to do with reassigning the security contexts used by SELinux. In Fedora, Apache will not work with the context user_home_t that is usually associated with home directories, but needs httpd_sys_content_t instead. To find out what contexts are associated with particular folders, issue the following command:
ls -Z
The final solution was to create a user account whose home directory hosts the root of the web server file system, called www in my case. Then, I executed the following command as root to get things going:
chcon -R -h -t httpd_sys_content_t /home/www
It appears that even the root of the home directory has to have an appropriate security context (/home has home_root_t so that might do the needful too). Without that, nothing will work, even if all is well at the next level down. The switches for chcon command translate as follows:
-R : recursive; applies changes to all files and folders within a directory.
-h : changes apply only to symbolic links and not to where they refer in the file system.
-t : alters context type.
It took a while for all of this stuff about SELinux security contexts to percolate through to the point where I was able to solve the problem. A spot of further inspiration was needed too and even guided my search for the information that I needed. It's well worth trying Linux Home Networking if you need further details. Though there are references to an earlier release of Fedora, the content still applies to later versions of Fedora to the current release, if my experience is typical.