| Sun, 11 May 2008 23:10:58 |
|
MySQL : Insightful |
|
|---|
| Being TRADITIONAL |
One of my hobbies that has taken a good bit of time lately has been to revamp my photo album. I originally wrote it a few years ago and, while it has served its purpose well, it lacks some features that I would like to see and represents less than ideal design decisions. This time around I have started from the ground up and, as a result, have the opportunity to apply some of the things I have learned n the past two years. For instance, this time around, all my main tables will be using InnoDB (or another transactional engine depending on when I finish the dern thing :) completely with foreign keys and, of course, making use of transactions where possible.
Recently while working on my little project, I discovered the value of TRADITIONAL. I am referring, of course, to to the SQL mode setting in MySQL that makes it behave more like, well, a "traditional" database. I tend to think of that as both somewhat unfair and vague. It might be better to call this mode PROPER or UBERMEGASTRICTMODE or something. Either way I have been aware of the it for some time, but have never really run into a situation where I thought to use it. Until now that is. So what exactly does TRADITIONAL mean? According to the documentation, it basically says that MySQL will bail out on an error when it would normally have simply flagged a warning. In actuality, TRADITIONAL is a meta mode that includes a number of other SQL modes, most notably being STRICT_TRANS_TABLES, although it also contains some numerical error constraints (such as division by zero). Why is this important? I think it best explained by example:
mysql> create table t1 (e1 enum('Yes', 'No') NOT NULL DEFAULT 'No');
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 (e1) VALUES ('this should not work');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'e1' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+----+
| e1 |
+----+
| |
+----+
1 row in set (0.00 sec)
Now, to me, this doesn't make any sense. The field was defined as NOT NULL, along with a default value of 'No'. MySQL actually did not insert a NULL, here, but it did insert an empty string:
mysql> select * from t1 where e1 IS NULL;
Empty set (0.00 sec)
mysql> select * from t1 where e1 = '';
+----+
| e1 |
+----+
| |
+----+
1 row in set (0.00 sec)
However, empty string was not one of the valid ENUM values that defined. In some cases this might be fine, but what if that field were something more tangible like whether a user on your site was male or female? What does the empty string even mean in that case? Neither? Both? N/A? Either situation isn't very flattering for those of us who are indeed male or female. Personally, I would prefer that MySQL would just set the field to the default value if not just flag an error. And this is where the TRADITIONAL mode comes into play:
mysql> SET SESSION sql_mode="TRADITIONAL";
Query OK, 0 rows affected (0.00 sec)
mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 (e1) VALUES ('this should not work');
ERROR 1265 (01000): Data truncated for column 'e1' at row 1
mysql> select * from t1;
Empty set (0.00 sec)
As you can see, where MySQL would normally simply flag a warning, it now flags an error and bails out. The value of this is obvious. With TRADITIONAL mode set, it is more difficult to screw up your data integrity because MySQL no longer allows sloppy data. Even if your application checks for these sorts of things, to me it still seems like a good idea. After all, humans make mistakes. Computers (usually) do not. And, either way, it is one less thing to worry about. After all, besides being relational, databases should generally be consistent.
Some might ask why is this mode not enabled by default, and in fact I initially asked the same thing. Trouble is, TRADITIONAL can actually be more trouble than it is worth in certain cases. Because MySQL is performing some additional checks, you might find this mode slightly slower than the default. Also, whole idea of not allowing sloppy data, for instance, can be thrown out the window if one is not using transactions. While InnoDB is fantastic, there are still great uses for MyISAM, however one of the caveats to MyISAM is that it is not transactional. The problem here is that TRADITIONAL is flagging errors on things that, to some, might be trivial. This could make things ugly in situations where multiple inserts are being issued where the data in the inserts relate to each other. If one such insert contains some dirty data, TRADITIONAL will bring down the hammer on it, thereby flagging an error, but potentially breaking referential integrity. Think of situations where one might have a Users table and than a UserPermissions or UserDetails table. If the INSERT on the Users table goes through without error, but the INSERT on UserPermissions fails, then we are left with a user that has no permissions! In my experience, this problem is more troublesome to deal with in the application than some dirty data.
That is assuming one is not using transactions. When transactions are being used (which, by the way, is generally a good idea!), this behavior is actually a good thing, since the entire transaction will be rolled back. This not only preserves referential integrity but also keeps bad data from ending up in your database. In the case of the sexes, MySQL won't assign a particular person to some sexually ambiguous title of empty string. Instead, it nixes the user altogether. Still just as rude, perhaps, but also much more consistent...
|
Last Modified: 2008-05-12 14:35:03
|
|
|
| Sat, 10 May 2008 17:47:57 |
|
Gamage : Insightful |
|
|---|
| The Rock Band Pedal Lives! |
Lately all of us have been spending the weekends playing Rock Band. It's a hella addicting game and while the fake guitars have little to do with playing a real guitar, the game is still quite fun. Needless to say, we have put some mileage on the instruments. We already replaced our first guitar with two new ones (so we could have a fake guitar and a fake bass) and last weekend our bass pedal for the drums finally gave out.
Before I go on, just to rank about Harmonix and Activision a bit. What the $%^& were you people thinking when you opted to release games without being able to pick up extra guitars? Seriously?! Better still why not have just kissed and made up, which would have pretty much solved the problem for both games, by letting us play each game with the other games' guitar? I mean really. Those were dick moves and while I will go ahead and blame Activision for the lack of fake guitar compatibility, both games should have offered the extra peripherals at day one. Score one point for being jackasses!
Anyways, the pedal breaking could not have come at a worse time since Chad, Mark, and Oliver drove all the way done from Austin just to play Rock Band with us. Heck, I even bought a new surround sound receiver that day so we could finally have awesome sound when we play (It's a Harman Kardon AVR-247 and I love it, but that's not the point of this post). About halfway or so into the night, the pedal finally snapped. Brandon thought he heard it crack earlier that evening and, in fact, we have been waiting for the drums to fail altogether for a good while now. As a result,it wasn't a surprise but really threw a wench into the Rock Band experience. Or, err, a broken pedal...
Well, a that wasn't about to stop us so I broke out some duct tape, wood screws, and various flat metal pieces that I could find to try and resurrect it. I didn't do a terrible job given the time and materials constraints. In fact I was a tad bit proud, although not proud enough to remember to take pictures of it. Either way, the pedal still ended up being squishy and not as fun, although it ended up being a really fun night just the same.
All was not lost for our pedal, however. Oliver quickly pointed me to an eBay shop called Pedal Metal, which specifically sells awesome looking metal pieces which can both strengthen working pedals as well as fixing most breaks in the old ones. So this last Monday I ordered it and, to my surprise, it cam to our house by like Wednesday. So I spent last Friday putting the thing together and have to say am quite please with the results:
It's not perfect but good enough. The top metal piece is slightly crooked which makes that bottom piece look a bit off. I did that in case I wanted to add the replacement hinge that comes with it. I opted not to install the hinge for two reasons: 1. The old one was impossible to get out no matter what the instructions say. 2. If it ain't broke, don't fix it. Really, it was more #1. I took it for a spin and it seems to play really well. I think one the drum pads die I might like to buy a new pedal and put the Pedal Metal on that first thing. Really, though, with as much as Harmonix seems to be making on this came you think they could have created a pedal to withstand Hard and Expert modes a bit better. I mean, the Hard or Expert mode of other games don't seem to cause things to break. It's not like playing Call of Duty on hard would make my controller break or anything. And since Harmonix wants to turn Rock Band into a platform, I think it wise to give us better quality fake instruments in the first place. On the other hand, Pedal Metal is pretty cool and well worth the modest price tag (something around $25 or so). It is also nice to see someone in the wild figuring out how the solve the problem and giving us a pretty phat solution to it, as well. For anyone with a Rock Band setup, I recommend getting it as soon as possible, hopefully before your pedal meets the same fate as mine (and Oliver's although their solution for fixing it was quite ingenious)...
|
Last Modified: 2008-05-11 12:03:28
|
|
|
| Fri, 02 May 2008 22:12:23 |
|
Music : Rambling |
|
|---|
| Christopher the Minister Rox! |
Christopher the Minister rocks! We were listening to him on the way home from having dinner with friends and got to hear him burp. I don't think enough DJs burp on air. It's awesome. Anyways, he's doing a 24 hour marathon on Alt Nation (the Alternative station on Sirius). Looks like it's getting rough but he's rockin' it hardcore!
|
| Wed, 23 Apr 2008 17:11:38 |
|
Computers : Rant |
|
|---|
| First Hand Experience With iTunes Rentals |
So recently I was lucky enough to attend the MySQL Conference. One of the caveats of going is a somewhat longer plane right than I am used to. No biggie, I have plenty of gadgets and gizmos to keep me occupied. This year, however, I thought I would try the iTunes Movie Rental service since, inevitably, I end up watching moving on the plane instead of doing something more productive (such as working on my website or something). So I rented two movies, Robocop and Aliens.
Look don't judge my selection. Both movies rock! But they are both from the mid to late-80's. That means if I rent these from my local video store, chances are I can probably rent them for 5-7 days. Granted, iTunes gives you 30 days to keep the movie, but a pathetic 24 hours to watch it. Unfortunately, I only got to watch about half of RoboCop on the way to the conference, but since I only had 24 hours to finish watching it, I never got a chance to finish it since I was busy getting my learn on at the conference itself. Now I tried to be clever. Since I knew the movie was going to expire, I intentionally left iTunes open with the movie paused the entire next day since I figured it already authenticated and wouldn't block me unless I stopped watching the movie altogether.
Turns out, Apple was smarter than that. At right around the 24 hour mark, I got a pop-up saying I could no longer watch the movie and iTunes subsequently closed it. Thanks Apple! I always enjoy paying $2.99 for a crappy 80's movie that I only get to watch half of. YAY! Fortunately, I haven't yet watched Aliens. When I do, you can bet I will do it in once showing.
The thing is. That is dumb. The only time I'm going to rent from iTunes is when I'm traveling. Say what you want about Blu-Ray but it's gorgeous. And by gorgeous I mean that iTunes' piddly HD wanna-be offering doesn't compare. At all. Not even close. I mean I could just compare audio quality and see a huge different, let alone the amazing visual quality of a Blu-Ray. So, sorry Apple, I am unlikely to be an AppleTV customer until the quality rivals that of Blu-Ray and, while I think that downloadable content may get there eventually, I don't think that day is very close. Well, alright, I'll admit that some movies I don't need on Blu-Ray. RoboCop is probably one of them. To be fair, I could envision renting a movie on a whim and needing instant gratification of being able to watch it immediately. Though, I think that my main use for iTunes Rentals would still be travel. Particularly when you consider I don't have a portable Blu-Ray player, so I can't really watch any of our new Blu-Ray's on the road. And when traveling, I don't need the brilliant picture and sound of a Blu-Ray anyway.
Now that said, when I do fly, I often find that it takes a few flights before I can finish watching a movie. After all, you have to take off, get settled, get your peanuts of whatever, go to the bathroom, and by the time I actually start watching a movie, the flight is something like half over. 24 hours to watch a movie is stupid. Who the hell thought that was a good idea? It goes against how I watch rentals when I do, and I bet I am not alone in that regards. Now for a first run film? Maybe. But for movies from the 80's? 24 hours to watch them? Really? How about a few days at least, if not up to the 7 that my local video store could match.
I mean for about the same price I could waltz into my video store, grab some DVDs to take with me would not have to worry about trying to watch an entire movie during one day of travel. Had I been able to watch the movie say within a week, I would have been able to finish RoboCop with time to spare. Instead I just blew away $2.99...
|
| Mon, 21 Apr 2008 19:53:29 |
|
Music : Rambling |
|
|---|
| T.K.O. Indeed |
So our policy at work is full disclosure and, in the spirit of this, I thought it important to admit something. Love T.K.O by Michael McDonald is awesome. It's friggin' awesome. And you know what, I'm good with admitting it...
|
| Tue, 15 Apr 2008 23:27:52 |
|
Personal : Rambling |
|
|---|
| MySQL Conference '08 - The Free Beer Edition |
So I am here at the MySQL Conference and while there is plenty to talk about, I've been too busy having fun to actually write about it. That said, one particular tidbit that I thought I would share is the free beer and cider available at one of the birds of a feather sessions. Woohoo! Free as in beer? Damn right! Thanks Wavemaker!
|
Last Modified: 2008-04-15 23:28:39
|
|
|
| Wed, 02 Apr 2008 14:00:39 |
|
MySQL : Rambling |
|
|---|
| MySQL + Secure Digital = Cheap SSDs? (UPDATED) |
With the advent of fast and reasonably sized SSD drives, I have to admit that even I, as one who still thinks the standard hard-drive tends to be a better choice, am warming up to solid-state. One crazy benchmark I have always wanted to do is how using consumer flash cards (SecureDigital, CompactFlash, etc.) stack up to their dedicated drive brethren. I just noticed that you can now buy off-brand 4GB High-Speed SD cards for around $10. That is amazingly impressive when you consider, even a few months ago, how much 4GB cards cost. After doing a bit of math, the economics work out pretty well. To match a 64GB SSD drive in capacity, I need 16 4GB SD cards. At $10 a piece, that's only $160. That's a pretty cheap way to match the capacity of an SSD drive without the cost.
Of course, there's a huge catch, or everyone would likely already be doing this. Actually there are quite a few. The numbers I ran don't include the cost of USB card readers and USB hubs, so the cost could easily be doubled. Second, USB is not that fast. In fact, The theoretical max throughput of USB 2.0 is around 60MB/sec, so if you wanted to get any performance beyond that, you would need to use multiple USB controllers. Most motherboards have at least 2 separate controllers, but even then, it's still 120MB/sec at best. So, you could drop in USB add-on cards, but then you could be running into saturating the PCI bus potentially. So, in practice, such a solution really may not provide much.
Except that it would be really cool and at a fraction of a cost. Even though I have no expectations that it would perform well, I still want to try it. And being that SD cards are now so cheap, seems like it might be a good time to uselessly invest in an useless benchmark project :) If nothing else, it's an interesting way to make use of the Linux Software RAID tools!
How does this relate to MySQL? Well, there's been a lot of buzz about how MySQL would run on SSD drives. Some of the benefits of SSD drives are also with SD, and other flash-based media. To what extent, it is hard to say since I am sure SSD drives have way more additional optimizations for handling data at higher speeds (and, again, the USB bus is a severe limitations), but both do throw the idea of disk seeks and rotational delays out the window. My benchmarks, if I ever do them, are to benchmark the standard engines, as well as up and coming (and very exciting) engines like PrimeBase XT to see how they perform on my kludgy SD card RAID array of doom! I have a feeling that some benchmarks may out perform some drive configurations, although I don't expect it to get near the performance of a true hardware RAID10. Still, it might be interesting to see what kind of numbers I do get. It could turn out that, at minimal cost, such a solution might outpace at least the single drive solutions. And, while recommending such a configuration in production environment might be madness (I would do it, but then again I don't make money off my websites!), it could potentially offer up an alternative to having to throw down a fair amount of money for a RAID of standard drives.
The same test could be run for most anything else, but I am most interested in the performance of MySQL on such a solution. It's been bugging me now for over a year. You would think I might have the gusto to, at some point, actually try it out. With SD card prices being what they are, I am reaching a point where I no longer have an excuse to try it!
P.S. Thanks to Matt for pointing out that I failed to mention anything about MySQL in my initial post. Ironic since the whole reason to set this up was to benchmark MySQL on it. Thanks Matt! What can I say...when I get excited, I forget small details like, you know, mentioning what I'm going to benchmark :)
|
Last Modified: 2008-04-02 16:41:42
|
|
|
| Tue, 01 Apr 2008 16:02:18 |
|
Personal : Humorous |
|
|---|
| My Favorite YouTube Video |
Is right here
|
|
| Latest Artwork |
|---|
 |
| Sun`s Acquisition of MySQL |
|---|
|
|
|
