Bus error: Jake Billo’s weblog

I nearly burned up my entire afternoon today troubleshooting a few annoying inconveniences with the ADODB database layer, plus the design decisions of MySQL. Here’s what I’ve found out in a few ‘fun’ facts. This post is reasonably heavy on the technical content, so be warned.

You may be tempted to use the MySQL “REPLACE INTO” function for your own code. A sort of hybrid between INSERT or UPDATE operations, MySQL will seek the primary key in your query and check if it matches a database row. A match results in an UPDATE operation, while no match results in an INSERT query being run. Unfortunately, reading the documentation reveals an enraging drawback:

…except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

This is a poor showing from an optimization perspective, but consider a typical business application: we’re not going for any kind of ridiculous efficiency, and the overhead of a DELETE operation is minimal. The real gain achieved by going through a REPLACE statement is on the development side, where no longer are two code paths or separate queries required for add and edit operations.

Except this isn’t the case.

My specific condition was the standard implementation of a users table: a PRIMARY KEY with an auto-incrementing user identifier, username, a few other fields, and a password storage column. Unlike the chumps at MySpace, I don’t store passwords in reversible encryption/plaintext or make a habit of sending the field contents for account reset emails.

By default, an UPDATE operation only changes the requested fields in a table - so if I left out the password column, the contents would stay the same for that user. Yet somehow, even when I didn’t specify a password change for a user, their password field became blank.

Lo and behold: because of the ridiculous DELETE behaviour present behind the scenes in a REPLACE query, you lose all other contents of that row if you don’t store and specify their values again.

Strike one for REPLACE. The fact that silent data loss can occur is what I’d consider a severity one problem, and if you’re planning on using REPLACE in one of your own programs, ensure that you’ve retrieved all columns from that table first. Not only is it weak from a performance standpoint, but its ability to nuke perfectly legitimate hashed data makes it dangerous.

The second such entertaining point was what happened when I tried to use ADODB’s “AutoExecute” function. This method provides a database-agnostic call to run an insert or update statement based simply on a key/value array:

AutoExecute($table, $arrFields, $mode, $where=false, $forceUpdate=true,$magicq=false)

AutoExecute() inserts or updates $table given an array of $arrFields, where the keys are the field names and the array values are the field values to store. Note that there is some overhead because the table is first queried to extract key information before the SQL is generated. We generate an INSERT or UPDATE based on $mode (see below).

This seems like an incredibly lazy and awesome feature when you think about it: the advantages of the REPLACE INTO statement, with only one minor if/then code block based on insertion or update. However, in the infinite wisdom of ADODB, all strings end up being quoted automatically. This means that you can’t use CURRENT_TIMESTAMP to update columns, nor can you easily use DBTimeStamp since the result of that function has already been escaped. For some reason, ”2008-09-24 20:36” won’t parse inside SQL.

As a result, I’ve dropped back to writing my own parameterized queries for this application. This provides the sanitization features of escaping potentially malicious user input, while preserving the ability to run timestamp updates for rows.

Thoughts? How would you go about ensuring an optimal database interaction?

Just checked out the Eclipse SVN site and apparently the issue with dashes in new PHP filenames has been resolved. I upgraded my work copy to the latest nightly build and things seem to be going well. (The October 1st integration build might be better stability-wise, but I’ve had good luck with the nightly builds.)

For the record, after the upgrade, loading my workspace took about five minutes (Core 2 Duo @ 2.16GHz, 1GB RAM) with no discernable progress bar or CPU activity. Eclipse effectively looks like it’s frozen. Be patient; it’ll load eventually.

PDT, though, is about the single most useful tool I find for PHP development at work. The next front-runner is WinSCP, which has a really neat feature, Keep remote directory up to date:

This feature lets you have complete synchronization between a remote SFTP server and your local Eclipse workspace. It also supports private key encryption.

As a pair to WinSCP, I also use PuTTY, with anywhere from one to five sessions open at a time. PuTTY offers a neat tunnelling option, which I’ve mentioned before.

You can also access internal servers using one of these tunnels; for example, if I want to connect to a remote desktop session on host 192.168.1.101:3389 on my internal network, I’d use the following syntax:

Source port: 127.0.0.2:3389
Destination: 192.168.1.101:3389

Then, after establishing the SSH connection, using the 127.0.0.2 alias in Remote Desktop lets me connect to the machine behind the NAT traversal firewall.

Notepad++ is another program I highly recommend as a replacement for Notepad. If you can’t use TextMate because you’re not on a Mac, Notepad++ is an acceptable substitute. It offers some neat syntax highlighting features, and saves session history even when you exit.

Paint.NET is also a decent image editor, allowing you to verify hex values for colours and perform some minor tweaks to images without incurring the load time and aggravations in Photoshop.

It must be a banner day for Eclipse projects, since the primary IDE I use for both work and personal projects has been released as a 1.0 version at eclipse.org.

The primary download mirror from eclipse.org is fairly slow (I’m pulling about 50-60K/s from my work connection from it, when normally I get much speedier downloads), at least for the full-sized Win32 large package. I’d suggest you use an alternate mirror - the OSU Open Source Lab mirror seems to be pulling in the ZIP file fairly well. It also hasn’t percolated to all mirrors yet, so try a different one if you’re unlucky with the first.

I’m going to check and see if the minor file naming bug in RC1 made its way out to the final release; stay tuned.

Update: Wow, we’ve got a UI and a pain-in-the-rear bug when trying to create filenames with multiple periods:

I might decide to report this to the Bugzilla site, but last time I did, apparently the description and sample code I provided wasn’t really needed.

Update 2: Apparently this will be fixed in the next integration build, but it’ll still probably have an ‘illigal’ spelling error.

Update 3: The typo should be picked up in the next nightly.

Update 4: The typo and bug has been fixed in the N20070920 build, but still doesn’t allow the creation of filenames with dashes. Might have to report this one.

There’s been a lot of whinging and amateur-lawyering over the past few days (including a massive dupe on Slashdot), about a controversy surrounding an app called TestDriven.NET. As far as I understand the situation, the primary developer is currently in a whine-fest with some people from Microsoft about licensing for a Visual Studio 2005 Express add-in. Basically, you can’t extend Express without a mad UI hack, and according to Microsoft, said extensions violate Express Edition’s license.

This sort of patent, licensing or “you’re violating the product’s ethos” crap irks me in a different way. At work, I have nearly free reign to develop applications in whatever language I feel like. As long as it suits the business case, I could crank out Java, Python, VB6 - most of the existing stuff I’m updating is a combination of Visual Basic 2005 and VBA automation. I use all of the languages in VS2005, as well as some optional components (managing an SQL Server 2005 installation is somewhat interesting.)

As part of an organization, generally “getting a license” is preferred to snagging some copy of an application off BitTorrent. At IBM, the software requisition process could take a week or two to get rolling, unless your manager put a “please expedite this” note along with it. With the license, you’d have to renew it after a certain period of time - although I was probably subject to this more than most people, since I’d request six months for each term, and end up needing to extend that three times over the course of my time there. Aside from that, I can recall a certain internally licensed program had to be updated with a license file nearly every two weeks.

My current experiences have been the opposite. Most software is available on the public network share, and it’s an average time of one day between my request for a program and its receipt.

The crux of all this is that if I wanted Visual Studio 2005 Team Suite, SQL Server 2005, whatever the newest Exchange Server is, and any other ridiculously expensive application (for an individual, at least) and asked for it tomorrow, I’d likely have it by Friday. So why, instead of using Windows Server 2003, IIS 6.0 with ASP.NET 2.0, and Visual Studio Expression Web Designer, would I pick up a copy of Eclipse 3.2 with the PHP Development Tools extensions and crank away on my latest project with that?

Partially because I know PHP very well and am aware of its idiosyncracies and language features.

Partially because this Microsoft nonsense has left me wondering where the hell I stand using Express editions of their products.

Partially because Eclipse actively encourages UI extensions - in fact, you can’t even really consider Eclipse an IDE first of all, because it’s more of a Java UI framework.

Formal unit testing is not something I’ve done much of in my line of work. I’ve written and executed testcases, and contributed code/XML/documentation to fix deficiencies in said testcases. But when I move into needing formal testing, I don’t want to be beholden to someone’s mad licensing whims.

I’ve said my piece, in any event.

I’ve decided to take a look at my Webalizer statistics for the months of April and May to figure out what people are searching for. It also helps me tailor the content on this ugly green and beige page to what people might be interested in.

If you’ve got a question about something I’ve written, or want to know more details about a particular subject, leave a comment! I’ll get back to you as soon as I check my email, which, now that I have a Blackberry, is more frequent than ever.

ml-2010 errors: Sorry, can’t help you there. My Samsung ML-2010 has been working really well, even from a Windows box networked to OS X. You also may want to specify the error message that you’re receiving.

svchost cpu usage 99%: Sadly, there haven’t been many updates to this situation since I last updated the post on the matter. My solution is still to disable Microsoft Update from the Windows Update / Change settings / Disable Microsoft Update software page, since I haven’t seen much of a tangible benefit from systems using MU versus standard WU.

It’s more likely to cause problems than it solves, especially with respect to drivers. I wonder if they’ve fixed that issue with Dell boxes and GeForce2 cards that causes WU to deliver a 640×480-limited resolution… (the solution is to tell Windows Update to never install the faulty driver, and get the official nVidia one.)

because nothing can fly with this broken wing music lyrics: This one’s easy to recall - the post was about OMGLOLEMO music, and the specific artist is Story of the Year. The relevant song was Burning Years off the Page Avenue album - which I actually own a legal copy of (but somehow have misplaced it!)

Speaking of emo bands, here are just some of the top artists on my playlist of angst, which is also coincidentally called “4+ Stars”:

• A Day to Remember (pretty much screamo, but the non-screaming parts have fairly good vocals, or at least pretty good Pro Tools work)
• The Academy Is
• Anberlin
• Boys Like Girls (more mellowed sound)
• Chiodos
• Linkin Park (the new album is super power pop angst IMO)
• Saosin
• The Used (specifically the new promo track The Bird And The Worm)

I do indeed listen to other music, by the way. My latest artist of hilarity is Mims, whose concert posters a few friends and myself spotted in Toronto the other week. He’s clearly an absolute genius with “This Is Why I’m Hot” and continues his vocal prowess with “Like This.”

bus error: Yes, that’s the title of this weblog. It stems from several cryptic OS X error messages. What else do you want from me?

As an aside, “bus error php” and MySQL bus errors mean that you’ve seriously ruined something with your configuration, or your hardware is toasted. I’ve never seen these apps throw the standard POSIX error messages, and frankly, you should rethink your purpose if you’re getting that kind of nonsense out of those tools.

custom resolutions nvidia not available vista: Run XP. Vista is indeed pretty, but XP has not exhibited one of the many symptoms I experienced with Vista’s installation. Like I’ve said, it’s fairly standard hardware for a new system: Asus P5B Deluxe board, eVGA GeForce 7800GT, Core 2 Duo E6600, 2GB of OCZ RAM… you’d think there’d be few issues with this sort of thing.

download convection font: Sure, you can find the Convection font right here.

driver for samsung ml2010 printer for windows: Oh, honestly. Learn to search and type in addresses: Samsung Product Downloads, type in the model number, and you’re golden.

driver vista viewsonic vx2025wm: Again! No Vista! Ruins lives!

is it safe to update a modified xbox 360: Safe? Depends how modified it is. Check Xbox-Scene for all your 360 modding needs, you filthy pirates.

kf42e200a: Good TV, I got it for $999 at Future Shop. Don’t pay any more if you can help it.

msdnaa vista number of installs: 2, then activation hell.

phpmyadmin futurepoint: Ooh, an interesting one! Look up the IP address of your server by running an “nslookup” command to your domain. Then, go to http:///MyAdmin/ and sign in with your MySQL credentials. You can also use the “nsxx-y.futurepoint.com” URL instead of the IP address, depending which nameserver you’re connected on.

All for now. Going to crash and think about proper normalized form database design, since it’s actually practical in my job.

I’ve escalated the issue with the ev98.net DNS resolution problem. To reiterate the problem, the community blogging/random video/insult site at ev98.net is currently unavailable for public access - it’s been replaced with some spammy search site that apparently is trying to flog DJ equipment or something else useless.

Things from the administration end seem to be fine; I can still log into the administrative panel and phpMyAdmin instances that Future Point provides. I’m also able to access my account using SSH to upload and change files. Right now, I’m currently creating a copy of everything originally hosted on ev98, as well as a backup of the site’s text content, in the event that I have to permanently move things offsite.

For now, I’ve redirected all content on the site to the “hosting preview” address, which still should work. (You might have to log in again, though.)

http://ns11-1.futurepoint.com/ev98.net/

I’ll update in the near future with what ends up happening. At worst case, the site moves over to a new domain and I have to tell everyone to update their email address information for me. At best case, the site comes back up and I change a few Ethanol settings back.

I also managed to push the WordPress 2.1.1 update here, which apparently fixes some XSS exploit issue. I’m still trying to determine if there’s a legitimate bug with the “custom fields” functionality, since I’d like to exploit that capability for a new project. That’ll have to wait until I can verify things though.

I’ve finally gotten the chance to deploy the new security-related fixes for WordPress. I’m not a huge fan of any mass-market weblog packages, or for that matter any heavily popularized software for managing a website. In fact, I plan to use a stable version of Ethanol or Aerosol to maintain this site once I get some time to hammer out some of the installation bugs that have crept in since its first release. It’s difficult enough maintaining “stable” and “development” branches as an sole, independent developer, let alone backporting usability and security fixes.

One of the reasons I feel so strongly about managing my own content is due to some of the security problems that have plagued phpBB; the popular bulletin board system had attracted a significant number of exploits and remote abuse abilities. The exploits were to such an extent that the developers removed the version number of the software from the default footer, since many scripts keyed on this version number in order to facilitate the crack.

I’m not sure of the current state of security for the phpBB project, but frankly I wouldn’t trust it running on a server without mod_security and Hardened PHP. Ideally, you’d run the package in a BSD jail, but that’s not an option for many low-cost web hosts.

While I was in the upgrading mood, I also managed to download the latest Subversion build of Xbox Media Center. For those of you who might not know, XBMC is an open-source application that runs on modified Xbox consoles, allowing it to effectively act as a media library for your TV. The best part about XBMC is its native networking support, so you can stream music and video files from your home network and play them on your TV. It also offers native archive support, so you can view the contents of RAR and ZIP files and play media stored within them.

Unfortunately, the incredible features and functionality of this software (I’ve personally not seen an open-source project as useful as XBMC since Firefox) are all muddled up in the great debate about console modification. Ozymandias (Andre Vrignaud) of the Xbox team goes over the three main reasons to modifying a console in his post, and attempts to debunk them:

• To play pirated games (Andre’s reasoning: “[A]t the end of the day every game not legally purchased is simply stealing money from the creators.”)
• To play imported/region-locked games (Andre says: “[S]ometimes companies have good reasons to either not release a title into a region or release it at different dates. It may be because of the time and cost of localization, marketing plans, ad buys, cultural considerations, or perhaps even because of the impact of piracy in the region. Whatever the case, it’s safe to assume the publisher has thought about it.”
• The desire to run “homebrew”, or non-licensed applications on the console. Effectively, console manufacturers like Microsoft and Sony sell their boxes at a loss, hoping that (over time) their attach rate, or number of games sold per console, will be high enough that they can recoup profit from the games. Andre indicates that he can’t condone running homebrew applications on consoles, because “[a]t the end of the day, the cost difference needs to be made up somewhere, and that’s why we need to you buy those razor blades.”

I personally can’t agree with Andre’s third argument against homebrew modifications. In my experience with marketing, I fit into one of the key demographics for being an early adopter of game consoles and peripherals. I have a significant amount of disposable income (well, as much as a student can have - but I’m not supporting anybody other than myself); I’m interested in the latest games, peripherals and systems that come out; and I understand the business model.
For the original Xbox, which was effectively a set of commodity PC parts in a black box, some attach rates for 2003 were quoted at 5.3 and 5.8 games sold per console. Dean Takahashi mentioned in his book Opening the Xbox that the expected attach rate for profitability on Xbox would have been 9.0 games sold per console, with at least three of those games as first-party, Microsoft-produced titles.

Personally, I can’t feel guilty about running a software modification on my system, because I’m well over the attach rate myself. I have purchased over twelve Xbox titles at full retail value, as well as several controllers, an Xbox Live subscription ($80/year) with headset, and some Halo 2-branded merchandise. I no longer even use my original Xbox to play games or sign on to Xbox Live; that’s reserved for my Xbox 360, which has fairly compelling media features of its own.

I understand Andre’s perspective - that console manufacturers are out to make a profit, and modifying systems cuts into their bottom line - but looking at the required attach rate, you can calculate this out: assuming a break even point on the system and software:

Xbox Console: Assume $299 (first price drop, Canadian funds)
First-Party Games: Assuming three required for break even point at $60 apiece: $180
Third-Party Games: Assuming six required for break even point at $7-$9 licensing fee: $42 - $54
Total Cost: $299 + $180 + $54 = $533

I would gladly pay $600 for a completely “unlocked” system, which allowed me to play games as well as run homebrew software. However, this idea doesn’t hold water, as it has no mass-market appeal. What’s more, the unlocked system would effectively be a development kit - which sells to developers for about a $10,000 US licensing fee. No thanks.
I think what Microsoft is doing with the XNA Game Studio and Creator’s Club on the Xbox 360 is interesting, but it doesn’t offer anything close to what the XBMC team is capable of.