I met this morning with unusual confidence and decided to upgrade MySQL to version 5.0.51b. It went smoothly for once, and it appears after testing that all is well. I also ran the troublesome mysql_upgrade, which ran just like intended. This in turn gives me the ability to modify the privileges in phpmyadmin (and it will add them correctly elsewhere as well, I assume). No more struggling with manually having to add in user privileges from our GUI app.

So I am finding that even after a restart of our MySQL server, we still have a myisam.log at 14 gigs. My search for resources regarding this particular log show very little in the way of help. I suppose this log in particular, as with the other general query logs, are only there for debugging purposes, or perhaps as a time based recovery system, and thus only minorly necessary in our world, but still necessary. The problem then is the large size. Even if we wanted to recover data using this file, it’s 14 gigs. It would take quite some time, and chances are, it’s been there for quite a while, so most of it is already covered by our backups. I believe our solution will have to be to get that log’s data down to something more manageable by doing a weekly backup of it and clearing it out. We could run a weekly batch script to do just that thing. At least then it will be more manageable.

So today I was doing some thinking about my next project, and I decided that I should not be deleting records willy nilly and instead using a flag for deletion.  I know, I’m a little behind the times, this is certainly basic database management, but you know how things are.  We do what we do because we learned our bad habits from programmers before us.  The bigger picture is, experience and reading peer materials (best practices blogs and websites) teach us how to think differently, and how to correct some of our bad habits.  As one who is always interested in correcting those bad habits, I came across a nifty solution.

Using the mysql Show Tables syntax in a ColdFusion cfquery, I am able to pull down a query result set of all of the tables within the database that I am working on.  Using that query is very simple then to loop over and conduct a new query that alters the tables, adding on the new column that I wanted.

<CFQUERY NAME=”test” datasource=”#dsn#” username=”#dbuser#” password=”#dbpass#”>   Â
  show tables;
</CFQUERY>

The query will output with a column named TABLES_IN_xxxxx where xxxxx is the name of your database.   Using this as my new variable, I can loop over the results and use the alter table to add my little ‘deleted’ switch.

<cfoutput query=”test”>
 <CFQUERY NAME=”testUpd” datasource=”#dsn#” username=”#dbuser#” password=”#dbpass#”>   Â
  ALTER TABLE #test.tables_in_xxxxx#
  ADD Deleted TINYINT(1) UNSIGNED NOT NULL DEFAULT 0;
 </CFQUERY>
</cfoutput>

So there you have it.  A nice tidy way to make a mass update to all tables in the database.  This can be modified in all sorts of ways.  Say I wanted to change the datatype of one of my fields, and it has foreign key references all over the place.  Well, I can do the same thing with the CHANGE column syntax.  Of course, as always, be wary of housing this online anywhere where it could be accessible to those who would have some ill intent, having database permission to alter tables is a dangerous set of permissions to be floating around.