PhotoDude.com

Sat. Sep 27, 2003

MT-MySQL 2, PhotoDude 0

MT-MySQL 2, PhotoDude 0 – That doesn’t begin to tell the score. It was a shutout, with the opponents able to score at will, resulting in a 7th inning “mercy call” to end the game.

Movable Type and MySQL have combined to thoroughly mop up the new web server with my tattered remains. I am humbled before mere text documents, though some of them be large and ornery.

So I’m starting over. I’m going to manually add the entries from September (only the first week so far), and hopefully most of the comments on those entries. And then I’m moving on. Damn it. Over the next few days, I’ll try to pick up the pieces and flesh out the content here. But not right now.

All the old content isn’t “lost,” and no links have been broken (that I know of). I was paranoid enough to do a full backup of the HTML files in addition to the MT export (though now they need some massive “Find and Replace” action). But there’s now over 2000 entries and over 3400 comments that are “out of the loop.” No longer searchable in one place, and not archived all together on their category pages. There’s now a dichotomy; the content built on the old server, now static and barred to further manipulation or cataloging, and the new content built on this server, represented mostly by this entry. It defeats the whole purpose of a “content management system.”

I feel a loss.

And, frankly, it makes me mad. So where do I aim my wrath? Should I be mad at Ben and Mena Trott for offering incredible software for me to use… for free? (well, maybe I can be mad at Anil, I think that’s his job now) Should I be mad at the folks who provide extensive knowledge and troubleshooting in the MT Forums …. for free? Should I be mad at the collective efforts that made MySQL an Open Source reality … for free?

Should I be mad at myself because I’m not fluent in every damn web language under the sun? I don’t know, I’m just mad. And even madder that I can’t even find a proper place to stake my victim claim.

But I’m moving on. You probably should, too. Web geeks will, as always, keep on clicking.

[Note: this entry also contains an ironic metaphor meant to poke fun at stereotypes in a futile effort to keep my head from exploding]

Basically, the process of moving to a new web host presented me with an entirely new problem, in a medium (MySQL) in which I have little experience. This problem first reared its ugly head a week ago Friday.

And for a week, I took the European Approach to solving this new problem.

I applied lots and lots of patience. I searched for existing and acceptable solutions from others who had successfully fought this battle before. I tried to check, analyse, and repair the problem on my own, using only conventional methods and tools. I sought consultations within the wider community, spelling out my problem and seeking any form of cooperation. But other than one old ally from previous battles, no attempts at help were forthcoming. This “Coalition of the Willing” was unfortunately unable to solve the problem via this method, and the European Approach garnered not a centimeter of progress.

So I moved to the American Approach. I nuked it.

Saturday morning, I made a pot of coffee, and at about 10am I sat down girded for a long battle (little did I know). No more trying to “repair” the elusive table defect, or attempts to “analyse” why the database hates me, I simply turned the database error into scorched Earth. In mere seconds, tens of thousands of words were wiped out. And then I went to work resurrecting the dead.

But despite my best efforts to wipe the slate clean, in the process of raising the dead, I also resurrected the demon, and it spoke in maroon words, “Statement has no result columns to bind (perhaps you need to successfully call execute first) at lib/MT/ObjectDriver/DBI.pm line 44.” Over and over again, just as it had before.

I tried repeated nuking. Three times I created virgin land to populate with my posts, yet each attempt at renewal would then yield the same beastly maroon words, yearning for a column with which to bind.

Three times my other four weblogs would prove they were still entirely functional, happy to comply with my every whim. Showing no sign of collateral damage at all despite the repeated nuking in nearby MySQL columns and rows, they seemed to mock me in their submissive compliance

So I escalated. I nuked the entire weblog. A final time. Then I set up a second MySQL database, and a second install of MT. Surely we were truly in virginal territory now. But there would be no joy. Three more times an import was tried, but no matter how many or how few entries it imported, attempts at the most minor rebuild would garner the same freakin’ error.

Like a cockroach, it had survived repeated nuking, and now even a move to a whole new planet.

A throughly beaten man, flummoxed and frazzled, I once again nuked the whole weblog, then I recreated it, and added … just … one … entry.

It worked!

So I went for two. By Gum, we might have something here!

What we had was the first success of any type whatsoever in, oh, about 12 hours. And this drowning man grasped at that rope. That’s why I’m starting over. Moving on. Not pouring more good weekend hours after bad (bad, about 14 of them).

But for the sake of any MT historians/technicians trying to work out the forensics of this, let me backtrack to that first nuke. My theory was that four weblogs were working perfectly before importing the Big Weblog, and even after it threw errors, they continued to hum and repsond to any and all requests. So I concluded the MySQL database error was somewhere within the 2000 entries and 3400 comments I’d imported for the Big Weblog.

So I nuked the weblog. Deleted it completely, then went back to the other four and completely rebuilt every single page of them. All hummed along with no errors, MT Nirvana.

So I then “created a new weblog,” redid the configuration and added the templates. Then, I pointed MT at the 8 megabyte text file containing all 2000 entries and 3400 comments. It began slowing chewing on that admittedly huge file. It sucked up the content bulge around and after September 11, 2001, and then gobbled down an equal bulge representing McKinney’s de-election in August, 2002.

It kept chewing until it got to content representing December, 2002, and then it choked. A detailed look at the entries around where it choked showed nothing unusual. But asking an operating web server (busy serving many sites other than mine) to simultaneously suck up an 8 MB file and process it is, admittedly, a tall order.

I assumed it had just run out of resources, or a connection glitch of some type had caused it to stop the import. Since it had properly imported about 5.5 MB of that 8 MB file, I decided to check what was there. A rebuild of the previously problematic Master Archive page worked fine, with no error. Up to that point, the database was working.

So I split out the remaining 2.5 MB of entires into a separate import file, and set MT to import those. It seemed to hum along fine, and took the whole 2.5 MB in one long bite. But when I tried to rebuild that Master Archive page, I get the old error sequence. Drat!

So I thought, at least I know the error is somewhere in the content after December 2002. So deleted the most recent 125 entries, and tried rebuilding again. Drat! I repeated this process, hoping I would eventually delete the offending content (and thus narrow the search for it), but soon I had deleted all ~550 entries in the second import. Faced with only the content that had been working before, I tried to rebuild it … and got the %$#@! error!

That which had worked fine (the first import up to December, 2002) was now corrupted by the second import. I reasoned that it wasn’t the conent of the second import, it was the attempt to do a second import itself that was screwing the database.

Thus, the second pot of coffee, and the second nuke. While watching it churn on the big file … again … I pretty much decided that if it made it to at least the beginning of 2003 before choking, and left a working database behind, I would just manually add to it from there and pick up the pieces over time.

While debating that ugly option, it chokes again. On the very same entry it choked on the first go-round, in December, 2002. Again I check that entry, very closely, and those around it. Again, I see no defect in the content. So, I excised the cancer and a good chunk of healthy tissue on either side (the entry it choked on, plus ten entries before and after). I literally “cut” those entries, pasted them in a new file so I could deal with them later, and then resaved the import file (now about a tenth of a megabyte smaller).

A third nuke (no third pot of coffee, my stomach can only take so much), another check that the other four weblogs still function, and then start all over … again. This time I import the big file, minus that seemingly cancerous section. At this point, I’m willing to accept anything that leaves me with a functional database that allows me to add new entries.

But it chokes again, about ten entries past the previous choke point, i.e., at the same point of volume. So I reasoned that having the four working weblogs and my somewhat massive main weblog in the same database might be a bit much, causing some resources overhead to kick in, or something.

That’s when I went to the fresh install of MT, and the fresh MySQL database. But it just gave me the same symptoms all over again, depsite three more attempts.

In the past week, I think I’ve made at least ten attempts to import this 8 MB text file. The first two times, I got legitimate chokes from MT, where I could go to the entry in question, and find the problem (for example, someone using a series of six or more dashes in a comment). In the course of these attempts, that file got parsed very throughly. I feel confident that it wasn’t defects in the file triggering this issue. Of those ten imports, it probably stopped in 8 different places.

Once the file had been parsed and corrected, it seemed like on the last half of the attempts, the choke would come somewhere in the range of 65-75% of the full file. 8 MB is a damn big text file, so I can only assume that might be part of it. However, early on, I tried splitting it in quaters, and, yes, the same error resulted.

I feel like I have tried it all. I know I haven’t, because my knowledge of MySQL is so limited, but what resources I have have been bested. So I’ll pick up the pieces from here over the next few days.

Not tonight. I’ve had more than my fill today.