Erik Gillespie's Homepage

	Recreating a MySQL System Table without Reinstalling	Sunday March 25, 6:30 PM
I made quite a large blunder on my local installation of MySQL 5.0 the other day. I'm working on a web app that has a User table and I logged into MySQL to drop and recreate the table and forgot to change the database. Actually, that wasn't my first mistake. My first mistake was logging in as root, which in turn uses the default database 'mysql'. After slapping myself for being such an idiot I poked around on Google to see if anyone had done something similar but I couldn't find anything that seemed to work so I then had to turn to my own brain to figure out how to get a workable system. I figured the task would be easy enough, I figured all I would need is to find the DDL for the user table and recreate it but this didn't turn out to be such an easy and straight-forward task. My first step was to figure out how to start MySQL so that I could login and execute SQL without any actual users. Turns out that one's not so bad. All I had to do was run the following two commands as root: `$ killall mysqld $ mysqld --user=root --skip-grant-tables &` I could then use `mysql -u root` to get to a SQL editor line. Great, next the obvious step: run the SQL to create the user table. The hardest part here was finding the SQL. I tried talking to a friend to see if they had a MySQL 5.0 installation that we could extract the DDL from but to no avail. Nothing on the web seemed to provide any help either (I stopped just short of downloading the source code for MySQL to see if I could find it in there somewhere). I decided to poke around the /usr/local/mysql/bin directory to see if there might be any useful tools there. I saw a program called `mysql_create_system_tables` that looked promising. I first ran it without any arguments to figure out how to use the program. It turns out that the program takes only one argument: 'test' or 'real'. I ran the program with the 'test' argument: `$ mysql_create_system_tables test` The output I got was amazing. It dumped out all of the SQL needed to create each and every MySQL system table! I searched through the mess of SQL and found the 'create table user' statement and was on my way. I ran this SQL and got my table created. I also noticed the output of `mysql_create_system_tables` also spit out the SQL to recreate the root user so I tossed that in there as well. At this point I figured I would restart MySQL in its usual way and one of two things would happen: I still don't have a working server or everything would be back to normal. Unfortunately what happened was somewhere in the middle. When I tried to launch mysqld it would return without any output and wouldn't actually start. I found the error log file (/usr/local/mysql/data/tachikoma.local.err in my case) had some useful information at the tail end of it. It told me that MySQL could not read the file /usr/local/mysql/data/mysql/user.frm. I went out and looked to see if the file was even there and it was, the only issue was that it and two other files (user.MYD and user.MYI) were owned by the user root. I ran the following command to change the owner to mysql: `$ chown mysql /usr/local/mysql/data/mysql/user.*` I tried to start MySQL again and VOILA! MySQL came up like a charm and everything seemed to be okay. The only remaining task was that I had to recreate all of the other users on my system. What did I learn? Never login as root unless you truly have an administrative task to take care of that no other user is capable of.

	Things I dislike about Hibernate after designing one table	Sunday March 11, 1:57 PM
I'm working on a project at home and I've decided to learn Hibernate. If you're not familiar with Hibernate, in short it's an Object-Relational Mapping framework that is quickly becoming the tool of choice for handling database persistence in large enterprise applications. At first glance it's easy to see why it's becoming popular too. It allows people who know a lot about Java but little about databases to be able to create mapping XML that associates records in the database with Java classes. At its most abstract level you don't need to know any SQL to be able to design a database and start using it in your application. Of course the recommended approach is to use HQL, which is to the extent of my knowledge, the subset of SQL features that all common database engines support (select, from, where, group by, order by, joins, basic aggregate functions). I've also read that you can use Hibernate to execute custom SQL tied to a specific DB vendor but a key features of Hibernate is that it's database agnostic so many argue that you lose that advantage by tying yourself to a specific vendor. Don't get me wrong, so far I still see a lot of value in using a tool like Hibernate. It's just that I consider myself to know quite a bit more than the average Joe about databases and there are a few things I am annoyed with after designing only one table with Hibernate. I'm writing here to vent my frustrations. First, anyone who tries to use Hibernate in an enterprise application and also tries to use the "Hibernate is database-agnostic" argument needs a serious reality check. Enterprise applications are almost by definition applications with large data sets. Unfortunately with large data sets a RDBMS does not always choose the best indexes and join order. Not only that but sometimes in order to take full advantage of an index you need to add additional criteria to your WHERE or FROM clause that no ORM tool could possibly be smart enough to do for you. To get around these you can't always let your ORM tool generate SQL for you and even beyond that you can't always use a database-agnostic language such as HQL to write efficient queries. I understand that Hibernate gives you much of the flexibility you need to add in vendor-specific queries as you need them but after much discussion with colleagues and reading around on the web I've come to the conclusion that there are a lot of naive developers out there who go around selling Hibernate without so much as a thought about the impact to your application's performance. My first argument can be worked around using the built-in flexibility of Hibernate and it's not so much an argument against Hibernate than it is against using a tool without putting some research and thought into your decision first. My other complaints focus more on limitations within the implementation of Hibernate and its packaged tools. My second argument is mostly a minor annoyance. Hibernate supports and recommends the use of versioning on records to handle concurrent modification to database records over a period of time. This is a pretty nice feature but the way the DTD for Hibernate's mapping XML was written, the version column must exist after your primary key and before all other data columns in your table. You get no choice in the matter. This isn't a big deal I just hate it when an API puts unnecessary constraints on me like this. The computer should do what I tell it to, not the other way around. Third annoyance. In the mapping file you can create a column that maps to a String and you can specify the maximum length of that string even but the Hibernate tool that generates the SQL and creates the table in the database always creates the column as VARCHAR. Suppose I have a column that is a string but will always be nine characters long. If I were to create the column in DDL I would specify `mycolumn char(9)`. The DDL that Hibernate generates though will always produce `mycolumn varchar(9)`. There's not way around this. Your only option is to generate the SQL and then modify it. Then pray that amongst a team of developers no one else makes the mistake of generating the DDL and blowing your changes away. I've read that Hibernate does not allow fixed-length strings because there's no vendor-agnostic way to do this. That argument is a total cop-out and has no truth to it. It would be very easy using JDBC to determine whether a database supports char(n) columns. If it doesn't, then you can fall back to using varchar(n). Don't tell me there's no vendor-agnostic way to do it, char(n) is in the ANSI SQL-92 grammar! In some databases char(n) can give you up to 50% improvement on lookup speed than varchar(n) so it would be well worth the extra effort to implement this feature. My final annoyance with Hibernate, which I'm still working my way through, is that when you use the Hibernate tools to generate a container object (POJO in the Java world) it creates all of your members, each of which represents a column in the database, as Java helper types instead of primitives. The exception is in primary keys. For some reason Hibernate will create a primary key as a primitive type but not any other column. To further explain what's happening here let's say we want to keep track of the number of bad logins since a user last logged in. In Hibernate's mapping XML we would specify `<property name="failedLoginCount" type="integer" not-null="true" />`. The DDL that Hibernate would produce for this column would be `failedLoginCount int not null`. So far so good. Then when we try to generate the class to contain a record from this table we get a member variable declared as `private Integer failedLoginCount`. In my opinion this is no good. What this means is that any time we want to change the value of this number we need to perform a method invocation on failedLoginCount. For example to increment the value we would have to write the following code: `failedLoginCount = new Integer(failedLoginCount.intValue() + 1);`! Can you believe that?! Not only do we have to invoke intValue() but we also have to create a whole new object! That can't be good for performance. It would be nice if I could instead simply do a `failedLoginCount++;` and be done with it. From what I've read, Hibernate creates objects using Java helper types instead of primitive types because databases allow nullable columns and trying to set a primitive value to null is not allowed. But in the mapping file we can specify whether the column allows nulls or not so why not give us the benefit of the doubt and if the column does not allow nulls use a primitive type. Another alternative would be to specify in the mapping file itself whether we want to try and use a primitive type instead of a helper object. Then all of the decision is on the developer and if they mess up it's their fault. Again, Hibernate is adding totally unnecessary restrictions at the cost of performance and good design.

	Goodbye Credit Card Debt!	Friday February 23, 9:53 PM
I got my bonus check today and what a bonus it was! Instead of spending my reward on ale and whores I decided to play the role of a responsible adult and deposited the check into a money market account. I then cancelled my automatic payments for my credit card and paid off the entire amount with one gigantic payment. Not only am I now totally out of credit card debt, I will also be making money each month from the money market account. HA, take that Discover! There is still the matter of my student loans... and my truck payment... and my mortgage payments. I'm working hard to try and have my student loans paid off this year too, we'll see how that goes. I saw The Number 23 tonight. What a trippy movie. I've caught myself looking for the number in my own life since watching it. My birthday is December 11. 12 + 11 = 23. The number 23 appears in my social security number. And my drivers license number. And my bank account number. Both of my posts to my site this year were on the 23^rd of the month. Spooky.

	10 Months Later, Yes I'm Still Alive	Tuesday January 23, 11:22 PM
Wow, I think I've set a new record, it's been over ten months since my last post! I see I've been getting a lot of junk posted in the comments as well. :( Well hopefully I'll be able to start posting more frequently than once or twice a year but who knows. There have been a handful of reasons I haven't posted. I've been working heavily on tax projects at work and overall things have been very successful. I've been putting in a LOT of hours lately since tax season has arrived but I'm definitely learning a lot. Some of it technical but most of it has been business-oriented. I got to fly out to New York City last fall for a business conference and I paid to stay out for a few extra days to do some sight-seeing. I got to run my first Production deployment, I almost felt like a real project manager. I also spent the summer and fall roofing my house. My friend Kevin helped quite a bit, but for one or two people to tear off and roof a two story house is pretty time consuming as I find out. It did save me a lot of money though (save being relative since I didn't really have the money to spend to begin with) and it was a lot of free exercise. I got a tablesaw and a truck out of the deal too. This winter I'm planning a few minor projects inside but overall I'm going to avoid doing any major projects to save more money (I've got to pay back that student loan some day!) and give myself some more free time. Katie and I broke up last fall too. We don't talk too much but we're not really on bad terms. It's already been several months since the break up, I haven't really dated anyone since but I've been plenty busy with work and I'm also keeping myself busy at home as much as I can too even though this month I haven't been having much energy or motivation left over when I get home from work. Part of it I believe is that winter has finally arrived. I always complain more in winter and become more sluggish. Maybe I should take up hibernating. :) I should talk about resolutions. It is a new year after all even though I've never really celebrated New Years. I've set a range of goals for myself this year. I'm sure some of these resolutions won't pan out, that seems to be how resolutions work. I'd like to start exercising again. In the spring I'd like to get back on my bike, maybe even try riding it to work but I'd like to start with exercises that use your own body weight as resistance (think push-ups, pull-ups, lunges, crunches, dips, etc.). I got a punching bag for Christmas, I'd like to start using that as well, I just have to make room in my garage to hang it up. I'd like to pick up woodworking as a hobby and maybe even a side business to make a little extra money. Ever since I bought my tablesaw I've been aching to try and make some furniture for myself. Right now I'm designing a table in Google SketchUp (awesome software btw). I'll need some more tools, maybe a nice bonus or tax returns will help with that hobby. In the spring/summer I'd like to start a garden in my backyard. I've been starting to cook again, it's something I really enjoy but eating out is so damn convenient. I want to start my own business as well. I may have mentioned that in a prior post but I'm still committed to the idea, I just need to get off my ass and do it. So many ideas and so little time to pursue them! Finally, I want to be a little less hermit-like. This definitely won't be something that happens during winter but it could start there. Since I've been talking with business users a lot more and coding less I'm seeing that the whole idea of being anti-social has nothing to do with personality. Being social and personable is a skill just like anything else, it just has to be developed and if you don't practice then you don't become good at it. I feel like an idiot for taking so long to learn this but it's something I want to tackle. I want to be able to start and maintain a conversation with a total stranger and make it not feel forced or awkward. I want to spend more time with family and friends. Let's see how far I can take these ideas.

	It's Been a Long Time	Sunday March 19, 11:05 PM
Actually, it's been a long, long time since my last post and as I think about it there isn't anything drastically new going on. Katie and I have been continuing our work on the house. We're working on remodeling the kitchen and this spring a friend and I will be reroofing my house. I've been playing a lot of board games lately with a group of friends (Runebound, Descent: Journeys in the Dark, Betrayal, Twilight Imperium, etc. etc.) and I'm really digging it. I'm trying to make some 3D terrain and paint some of the figurines for the games in my spare time. Since I have very little spare time though it's been slow going. I got a raise and a really good bonus this year at work and the company's doing great. We had our first profitable month in February. I've been put on a project where I have the opportunity to do some really cool design work and have written some nice code for easing our burden on handling database persistence. I'm also pushing for upgrading our EJBs from version 1.1 to 2.0 so we can start taking advantage of Container-Managed Persistence which would be even better for cutting down the amount of code we need to write both in Java and in SQL. The project will hopefully be one of the first in our system to use AJAX as well. I'm really having fun with this project so far and am looking forward to seeing it through to completion. Outside of work I've got a bunch of projects and ideas that I am either currently working on or hoping to start working on soon. As I mentioned before, a friend and I are going to be reroofing my house. Since the house is more than two stories tall we decided to take on the task of inventing a cheap but safe and stable system for transporting bundles of shingles to the roof without us having to manually carry them up. The job almost necessitated this invention. Bundles of shingles weight 80 lbs. each which makes it very difficult to carry up a ladder, more difficult yet since my house is on a hill so the ladder won't be on level ground and at 20 feet in the air the ladder doesn't feel very safe unless you have both hands on the rungs. We may try to sell the plans for our invention after we're done. We're putting a lot of thought into the project and are even drafting plans for the system before we build it. I've been trying to think of a name for a company that I would like to start. Pretty much anything I can think of has been taken and I have so many ideas right now that I would like to work on that I'm going to explode if I can't come up with a name so I can start doing some business for myself. Only about half of my ideas are software-related so I'm trying to find a name that's broad enough to cover a vast range of products. I'm sure I'll come up with something soon enough, it just bugs me that I have all kinds of ideas popping into my head during random bouts of daydreaming but when I want to think of a creative solution to a very specific question I'm coming up short. Katie is plugging away at school. She should be able to get her Bachelor's Degree in Computer Science from MSU next year. We're both really looking forward to it. She had her hair cut short (it used to be very very long). We've been carpooling and doing what we can to pay off our credit cards and save money. We've recently got to the point where bills are actually going away permanenly. I'm glad our cost-cutting has paid off. After this year we'll be doing a lot better as far as debt goes. The last thing I'll mention is that our dog Jack has matured quite a bit in just the past few weeks. He still has a very active personality but he hasn't been doing a lot of the annoying puppy things he used to. I've been trying to teach him some new tricks. His current list includes sitting, laying down, getting up, sitting pretty, giving big hugs, giving small hugs, standing up on two legs from a sitting position, shutting the back door when it's open (either because he pushed it open from letting himself in or because me or Katie opened it), and staying. The closing the door trick is particularly nice. We can let him outside and close the door but not latch it and he can come inside whenever he's ready and close the door behind him without us having to wait around for him or having to constantly check on him. He also recognizes a lot of words such as the common ones like "come here," squirrel, kittie cat, outside, and pottie. He also understands inside, upstairs, downstairs, "Who's here?," and more. He's a great dog, and the brainiac he is makes him fit perfectly into our household. :)