Tag Archive for MySQL

Upgrading to Fedora 13

After having a spin of Fedora’s latest in a Virtualbox virtual machine on my main home PC, I decided to upgrade my Fedora box. First, I needed to battle imperfect Internet speeds to get an ISO image that I could burn to a DVD. Once that was in place, I rebooted the Fedora machine using the DVD and chose the upgrade option to avoid bringing a major upheaval upon myself. You need the full DVD for this because only a full installation is available from Live ISO images and CD’s.

All was graphical easiness and I got back into Fedora again without a hitch. Along with other bits and pieces, MySQL, PHP and Apache are working as before. If there was any glitch, it was with Netbeans 6.8 because the upgrade from the previous version didn’t seem to be a complete as hoped. However, it was nothing that an update of the open source variant of Java and Netbeans itself couldn’t resolve. There may have been untidy poking around before the solution was found but all has been well since then.

Basic string searching in MySQL table columns

Last weekend, I ended up doing a spot of file structure reorganisation on the web server for www.johnhennessy.co.uk 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 needed to pick out the affected rows and edit them in MySQL Query Browser. To do that, I needed basic string searching so I opened up my MySQL eBook from Apress and constructed something like the following:

select * from posts_table where post_text like ‘%some_text%’;

The % wildcard characters are needed to pick out a search string in any part of a piece of text. There may be more sophisticated method but this did what I needed in a quick and dirty manner without further ado. Well, it was what I needed.

Removing a column from a MySQL data table

My trying out WordPress 3.0 in advance of its final release has brought me errors on the links management page. 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 wordpress.wp_links drop link_category;

That seems to have made those errors go away and I hop 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.

Securing MySQL in Fedora

Ubuntu users must be spoilt because any MySQL installation asks you for a root password, a very good thing in my opinion. With Fedora, it just pops the thing on there with you needing to set up a service and setting the root password yourself; if I remember correctly, I think that openSUSE does the same thing. For the service management, I needed to grab system-config-services from the repositories because my Live CD installation left off a lot of stuff, OpenOffice and GIMP even. The following command line recipe addressed the service manager omission:

su – # Change to root, entering password when asked
yum -y install system-config-services # Installs the thing without a yes/no prompt
exit # Return to normal user shell

Thereafter, the Services item from the menus at System > Administration was pressed into service and the MySQL service enabled and started. The next step was to lock down root so the following sequence was used:

mysql # Enter MySQL prompt; no need for user or password because it still is unsecured!
UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’;
FLUSH PRIVILEGES;
quit # Exit the mysql prompt, leaving the bare mysql command unusable

For those occasions when password problems keep you of the MySQL shell, you’ll find password resetting advice on the MySQL website but I didn’t need to go the whole hog here. MySQL Administrator might be another option for this type of thing. That thought never struck me while I was using it to set up less privileged users and allowing them access to the system. For a while, I was well stymied in my attempts to access the MySQL using any of those extra accounts until I got the idea of associating them with a host, another thing that is not needed in Ubuntu if my experience is any guide. All in all, Fedora may make you work a little extra to get things like thing done but I am not complaining if it makes you understand a little more about what is going on in the background, something that is never a disadvantage.

Self-hosted web analytics tracking

It amazes me now to think how little tracking I used to do on my various web “experiments” only a few short years ago. However, there was a time when a mere web counter, perhaps displayed on web pages themselves, was enough to yield some level of satisfaction, or dissatisfaction in many a case. Things have come a long way since then and we now seem to have analytics packages all around us. In fact, we don’t even have to dig into our pockets to get our hands on the means to peruse this sort of information either.

At this point, I need to admit that I am known to make use of a few simultaneously but thoughts about reducing their number are coming to mind but there’ll be more on that later. Given that this site is hosted using WordPress software, it should come as no surprise that Automattic’s own plugin has been set into action to see how things are going. The main focus is on the total number of visits by day, week and month with a breakdown showing what pages are doing well as well as an indication of how people came to the site and what links they followed while there. Don’t go expecting details of your visitors like the software that they are using and the country where they are accessing the site with this minimalist option and satisfaction should head your way.

There is next to no way of discussing the subject of website analytics without mentioning Google’s comprehensive offering in the area. You have to admit that it’s comprehensive with perhaps the only bugbear being the lack of live tracking. That need has been addressed very effectively by Woopra, even if its WordPress plugin will not work with IE6. Otherwise, you need the desktop application (being written in Java, it’s a cross-platform affair and I have had it going in both Windows and Linux) but that works well too. Apart maybe from the lack of campaigns, Woopra supplies as good as all of the information that its main competitor provides. It certainly doe what I would need from it.

However, while they can be free as in beer, there are a some costs associated with using using external services like Google Analytics and Woopra. Their means of tracking your web pages for you is by executing a piece of JavaScript that needs to be added to every page. If you have everything set to use a common header or footer page, that shouldn’t be too laborious and there are plugins for publishing platforms like WordPress too. This way of working means that if anyone has JavaScript disabled or decides not to enable JavaScript for the requisite hosts while using the NoScript extension with Firefox, then your numbers are scuppered. Saying that, the same concerns probably any JavaScript code that you may want to execute but there’s another cost again: the calls to external websites can, even with the best attention in the world, slow down the loading of your own pages. Not only is additional JavaScript being run but there also is the latency caused by servers having to communicate across the web.

A self-hosted analytics package would avoid the latter and I found one recently through Lifehacker. Amazingly, it has been around for a while and I hadn’t known about it but I can’t say that I was actively looking for it either. Piwik, formerly known as PHPMyVisites, is the name of my discovery and it seems not too immature either. In fact, I’d venture that it does next to everything that Google Analytics does. While I’d prefer that it used PHP, JavaScript is its means of tracking web pages too. Nevertheless, page loading is still faster than with Google Analytics and/or Woopra and Firefox/NoScript users would only have to allow JavaScript for one site too. If you have had experience with installing PHP/MySQL powered publishing platforms like WordPress, Textpattern and such like, then putting Piwik in place is no ordeal. You may find yourself changing folder access but uploading of the required files, the specification of database credentials and adding an administration user is all fairly standard stuff. I have the thing tracking this edifice as well as my outdoor activities (hillwalking/cycling/photography) web presence and I cannot say that I have any complaints so we’ll see how it goes from here.

Ubuntu upgrades: do a clean installation or use Update Manager?

Part of some recent “fooling” brought on by the investigation of what turned out to be a duff DVD writer was a fresh installation of Ubuntu 8.10 on my main home PC. It might have brought on a certain amount of upheaval but it was nowhere near as severe as that following the same sort of thing with a Windows system. A few hours was all that was needed but the question as to whether it is better to do an upgrade every time a new Ubuntu release is unleashed on the world or to go for a complete virgin installation instead. With Ubuntu 9.04 in the offing, that question takes on a more immediate significance than it otherwise might do.

Various tricks make the whole reinstallation idea more palatable. For instance, many years of Windows usage have taught me the benefits of separating system and user files. The result is that my home directory lives on a different disk to my operating system files. Add to that the experience of being able to reuse that home drive across different Linux distros and even swapping from one distro to another becomes feasible. From various changes to my secondary machine, I can vouch that this works for Ubuntu, Fedora and Debian; the latter is what currently powers the said PC. You might have to user superuser powers to attend to ownership and access issues but the portability is certainly there and it applies anything kept on other disks too.

Naturally, there’s always the possibility of losing programs that you have had installed but losing the clutter can be liberating too. However, assembling a script made up up of one of more apt-get install commands can allow you to get many things back at a stroke. For example, I have a test web server (Apache/MySQL/PHP/Perl) set up so this would be how I’d get everything back in place before beginning further configuration. It might be no bad idea to back up your collection of software sources either; I have yet to add all of the ones that I have been using back into Synaptic. Then there are closed source packages such as VirtualBox (yes, I know that there is an open source edition) and Adobe Reader. After reinstating the former, all my virtual machines were available for me to use again without further ado. Restoring the latter allowed me to grab version 9.1 (probably more secure anyway) and it inveigles itself into Firefox now too so the number of times that I need to go through the download shuffle before seeing the contents of a PDF are much reduced, though not completely eliminated by the Windows-like ability to see a PDF loaded in a browser tab. Moving from software to hardware for a moment, it looks like any bespoke actions such as my activating an Epson Perfection 4490 Photo scanner need to be repeated but that was all that I needed to do. Getting things back into order is not so bad but you need to allow a modicum of time for this.

What I have discussed so far are what might be categorised as the common or garden aspects of a clean installation but I have seen some behaviours that make me wonder if the usual Ubuntu upgrade path is sufficiently complete in its refresh of your system. The counterpoint to all of this is that I may not have been looking for some of these things before now. That may apply to my noticing that DSLR support seems to be better with my Canon and Pentax cameras both being picked up and mounted for me as soon as they are connected to a PC, the caveat being that they are themselves powered on for this to happen. Another surprise that may be new is that the BBC iPlayer’s Listen Again works without further work from the user, a very useful development. It very clearly wasn’t that way before I carried out the invasive means. My previous tweaking might have prevented the in situ upgrade from doing its thing but I do see the point of not upsetting people’s systems with an overly aggressive update process, even if it means that some advances do not make themselves known.

So what’s my answer regarding which way to go once Ubuntu Jaunty Jackalope appears? For sake of avoiding initial disruption, I’d be inclined to go down the Update Manager route first while reserving the right to do a fresh installation later on. All in all, I am left with the gut feeling is that the jury is still out on this one.

  • As is commonly the case with places like these, all the views that you find expressed on here in postings and articles are mine alone and not those of any organisation with which I have any association, through work or otherwise. With regards to any comments left on the site, I reserve the right to reject any that are inappropriate. Otherwise, whatever is said is the sole responsibility of whoever is leaving the comment.