You are currently viewing archive for August 2011

August 22, 2011: Other Payment Methods

Let me start off by saying The Undermine Journal is financially stable. Month after month, the donations of generous visitors meet the costs of keeping the service open, so we're in no immediate danger of closing, at least for financial reasons. So don't take this post to mean that we need money. Donations are great, and I appreciate them and hope they continue, and I'd like them to grow. ;) But we're not in danger by any means.

I've talked about subscription services in the past. They're kind-of a pain to set up, especially if I want to provide actual service differences instead of calling subscriptions glorified "recurring donations." So they never got off the ground. Plus, every time I mention them, there's a lot of grumbling from the masses about "hey Blizz won't let you do that" and "hey that's copyright infringement" and so on. How kind of them to worry about my legal concerns. I'm sure it has nothing to do with not wanting to pay.

Anyway, another way to increase the money coming in is to have more ways to receive it, beyond Paypal. I'm not a big fan of Paypal, never have been, but their policies and standard payments API are very polished. Paypal's business is moving money, and they know that and do it well. Their stuff just works. It's just a shame that, in cases of dispute, Paypal's behavior is a bit drastic and unpredictable. So folks avoid it. There are two other big players worth mentioning: Google Checkout and Amazon Payments.

I've been messing with Amazon Payments off and on over the past few weeks, and not getting very far. They have a method that's similar to Paypal's, but I can't get it to work. Here's how Paypal does it:

  • User clicks a Donate button
  • User gets sent to the Paypal site
  • User types in the amount they'd like to give
  • User confirms transaction
  • User gets sent back to original site
  • Paypal sends Instant Payment Notification (IPN) data to original site
  • Original site verifies IPN data with Paypal
  • Original site records transaction
Simple. And to test this stuff, Paypal has a "sandbox" for development use. You create fake seller and buyer accounts in the sandbox site, and you put fake sandbox buttons in your pages during development. You generate transactions as if it were production, but since everything bounces off the sandbox site, no money is actually moved. Once you have your site ready, you remove/change all references to the sandbox so things go to production, and you're off and running.

Back to Amazon. They don't let you call it a "donation" since donations must go to registered non-profits. Everyone else needs to sell goods or services. And goods/services must have a set price, so there's no interface to let the user type in how much to give. You can get around this by having the box on your own site and dynamically generating the button, but that's a little messy. The backend has the same idea as Paypal: they have a sandbox, and they send IPNs that you can verify. I've been working in the sandbox. I generate a transaction, and the buyer sees the transaction in the Amazon interface, as does the seller. So far so good. But no IPN gets sent out from the sandbox. This happens a lot. Without IPNs, I can't automatically record the transaction in my database. I prefer IPNs and database inserts to see how we're doing without manually adding up amounts from emails. There are other inconsistencies in the confusing Amazon interface and documentation, but they're a bit too vague for me to put into words here. Basically, I don't like it. And since I can't test IPNs in the sandbox, I can't be sure it'll work in production.

And then there's Google Checkout. That has a similar donation restriction, and a similar lack of interface for buyers to specify their own price. It also looks even more complicated than Amazon's. Amazon and Google are very excited about being the shopping cart engine for retailers, and while they have resources for small single-product shops, it's a lot more involved than with Paypal. Paypal just makes more sense, and it's more straightforward. And it works. Maybe one of these days I'll get the energy to dig into Google Checkout again, and see if it's worth it. But I think my patience with Amazon has run out.

Finally, Flattr. Flattr is big(ger) in Europe. I had a Flattr button on TUJ for a while, early on, but removed it when it just sat there gathering dust. It might be time to bring it back, since we now support English EU realms, but as a USian who never sees Flattr buttons on any other sites, I don't have many avenues to pay it forward as Flattr encourages. Chicken-and-egg, I know.

Category: Money | Posted by: admin | 4 Comments

August 19, 2011: API Forum Squick

I simply can't post in the API forum anymore. Don't let me. Even better, don't let me even look in there anymore. I already have the blue posts in that forum via RSS, with links that go right to the page with just the blue posts. (Thanks, MMO-Champ!) I even have the API documentation commits feed for those rare times with details hit official documentation before the forums. I don't need to browse the full API forum. Don't let me. It bothers me.

You know why? Because of three big things: developers that can't write English, developers that ask really dumb questions, and developers that offer really bad advice. Glass houses, throwing stones, I know. Hear me out.

I can't stand to read forum posts with horrid forum-speak, with no punctuation, no capitalization, and awful spelling. These people are asking (or answering) technical questions, and 12-year-olds with broken keyboards and lolcats make more sense. What must their code look like? I shudder to think. Not everyone needs an English degree, but such fundamental errors and apathy in communication come across as disrespect towards other forum visitors and the resources available. This isn't the class forum where people whine and bitch about PVP nerfs. This is a forum where we discuss resources available, and have unparalleled access to the developers at Blizzard who try their best to implement our suggestions.

Over and over, people whine, asking for spell information, or achievement criteria, or other stuff available in many other places. Thankfully, the devs at Blizzard know what information is hard to get because it isn't included in the client. Important live stuff like character and auction information was taken care of, along with item information that isn't built-in to the client. For the other stuff people are asking about, they can either pull it off of Wowhead/MMO-Champion easily enough, or even better, mine it out of the DBCs that come with every client. That's what WH/MMOC do, and that's what I had to do two years ago, when the Armory was new and I built an armory clone site that needed more information that I could get from other sources. You can get achievement and criteria data, you can get spell names, icons and reagents, you can get all that stuff off your hard drive. Do some Google searches and put on your thinking cap and do it yourself. Stop whining and be grateful that Blizzard is supplying the stuff we can't already get for ourselves.

Finally, I see suggestions and answers to problems that are sloppy, and either don't solve the original problem or would cause many more. It's the easy-way-out answer set. Someone says, "Hey, I can't parse the auction JSON via PHP, it says I run out of memory." Someone then replies, "Oh, just set your memory limit to -1 and it'll work." Well, yeah, that'll let the script run, but it lets you do dangerous things when you might actually want a memory limit. Choose a sensible limit and explain why you chose it. Someone whines that the realm status API doesn't allow spaces when requesting specific realms. Well, the API lets you get the status for all realms, and includes the slug (a normalized name) for each realm for future calls. Request those realms by the slug, not by the display name. And I said as much. Then someone comes in with "Oh, just urlencode it, then replaces all %20's with +'s." Except that neither %20's nor +'s actually give you what you want for the API in question.

So much misinformation and laziness in that forum, like the rest of the WoW forums, I guess. Don't let me in there again.

Category: General | Posted by: admin | 3 Comments

August 18, 2011: Bugs Under Logs

I've come to appreciate the usefulness of error logs, for errors reported and unreported.

I've been saving all the PHP errors that occur in a log for a while now. Since I have the most control over how the PHP is run, I would hope to virtually eliminate any PHP errors. Nobody likes it when a page doesn't return, or when it gets cut off due to weird data or some other condition. So, logging the PHP errors is obvious.

I've also been logging all page requests that take longer than 10 seconds to return. This will help me to find those pages with huge datasets, so I can tune the queries better from pages that people actually load. I can't fix them all, but it's still good to know where they are. Oddly enough, recently the user page has been taking long for some folks so I'll have to check that out.

With the recent database issues, I also have a script that kills all queries that take longer than 90 seconds, and logs them. I used to get a bunch of seller queries locking up before the reindexing, but since the weekend I haven't had any. Very good news.

Finally, I've received an issue that IE users weren't seeing heat maps for sellers. They worked fine on Firefox, and since I develop on Linux I don't have access to IE. I discovered the issue was related to a profile addition I had made a week or two prior. It bothered me so much that a part of the site was broken for a lot of users and I had no idea about it until someone finally spoke up. I looked into and implemented a JavaScript error logging function. On most pages on the site, it'll now log all JavaScript errors by reporting them back to the server via an Ajax call. That way I can see specific browser information related to the page whenever errors occur, regardless of the browser used.

This automatic logging is so very handy. Often I'll get a message on the forum or via the contact page from a user saying "Hey, I can't see this item" or some other vague problem. Luckily, with every contact form submission, I record the IP, browser user agent string, and if available, the realm and user ID. With this extra info I log automatically, I can fill in the blanks myself instead of having to ask the user to be more specific. Users never know just how much information is required to debug an issue. They state the problem in the vaguest terms and expect you to know exactly what's wrong. To abuse another car analogy, it's like calling a car garage and saying, "I hear grinding noises from the passenger front wheel" and expecting a response. They don't volunteer whether it's a car or truck, what make, model and year, if it happens during braking, during steering, at city or highway speeds, or any other diagnostic information that's helpful to diagnose the problem. It's like all grinding noises from the wheels are the same.

"Since about 2 days, 20 hours ago, the item notifications have stopped working for me. I see items on my list that are below the price thresholds I set, but notifications are not being generated and emailed to me."

No realm supplied, no item supplied. No idea how to duplicate the issue. I asked the user to contact me via the form, and then I looked up the account with the attached user ID to see what items he was watching.

"I'm trying to look up a competitor on TUJ who has (part of) the same name as an ingame item. So if I search for his name, I only getting this item."

No realm, no seller name, no item name. No idea how to help. When I get a bug report, my first impulse is to repeat the problem. I need to see it happen, to know what's coming up incorrectly. Then I can do it again, but by reading through the code to see where the logic is wrong. Being vague helps nobody.

But you can't complain, because then your users don't bother to tell you when things are wrong at all. So you cope, and put on your Sherlock hat.

Category: General | Posted by: admin | 4 Comments

August 17, 2011: So I heard you like outfits..

Apparently, the way gear looks will be important soon.

Choose a weapon or armor that you'd find on the Auction House. Look it up on The Undermine Journal. Click the icon next to the tooltip display on the item's page. "Ooh" and "aah" at the rotate-able 3D representation.

Code shamelessly yoinked from It's just an Easter Egg for now, but I'll find some tasteful way to point it out on the interface soon.

Category: General | Posted by: admin |

August 16, 2011: Lots of little fixes

After last week's database shakeup, things have been going very smoothly since the weekend. I've been making a lot of small fixes and additions over the past few days. Here's most of `em:
  • Heat maps now display for sellers in IE and Webkit (Safari/Chrome).
  • The new combined price/quantity charts driven by the Highcharts script should be more robust and fail to appear less often.
  • On the Category pages, most items that are crafted will show the crafting tooltip instead of the item tooltip, to show the materials used to create the item.
  • Seller links have tooltips that show their trained professions.
  • Seller pages now show profile images and character information, including profession skills, for IE and Webkit.
  • Seller tooltips for characters under level 10 should time out more quickly for Firefox and Webkit.
  • The XML API page for EU realms now shows the correct Market XML API URL.
  • The user control panel page now links to EU items properly in the Market Notifications section.
  • Market and Seller Notification messages now have the descriptive text ("The above notification(s) have reached thresholds..") at the end instead of the beginning, for RSS and EMail previews.

Category: General | Posted by: admin |

August 11, 2011: Curses, InnoDB!

My index "optimizations" to save space last week are causing more issues, despite my research. Decided to reindex the main auction table yet again, to be even more like how it was before I broke it. Hope I have enough space.

This time, I'll leave the site open, since reads will still be supported during reindexing. Updates will be queued until the reindexing is done.

Found out that the InnoDB Plugin is a drop-in replacement to the built-in InnoDB engine in MySQL, and supports fast index creation. Great, so this time I don't have to do that stupid table copy! For some reason, my version of MySQL (5.1.56 on Fedora 13) doesn't have the plugin included, even though it should be there. Shiny. Had to track down an appropriate RPM for the MySQL I'm using, and extract the plugin file. I was smart enough to try this on my dev server first. Worked there. Did the same method in production. Worked again. Now I'm using the snazzy InnoDB plugin. Hooray.

So I go about dropping the item index and creating it anew. Oh, what's this? It's doing a table copy anyway. Yay, thanks for wasting 2 hours with promises that can't be kept! Ugh. I cancel that, realizing I don't have enough disk space to do an entire table copy. Now I've scripted deleting the auction data older than 9 days ago. Hopefully that'll give me enough space for the new copy.

Site should stay up during the change. Updates will be iffy. End result is that this morning's laggy searches, causing max_user_connections overflows, should stop.

I also wrote and am running a script that checks every minute for web queries that last longer than 90 seconds, and kills them. Usually one query will get stuck, and lock enough tables/records (on a read? shrug.) to make everyone else wait. Killing those queries is not the nicest thing to do, but at least it doesn't cause problems for everyone else for hours.

Category: Technical | Posted by: admin |

August 09, 2011: Over the weekend, parser maintenance

Over the weekend I did a lot of work on the bits that move the data from (undermine) to the main auction database at my apartment (kezan). Here's the updated overview: updates AH API lists for every realm every hour
undermine pulls the AH API lists, sends out any required market notifications, then packages the data for kezan.
kezan pulls the packaged auction data from undermine and updates its history tables

(It used to be that the crawlers would pull the auction data from wowarmory, push it to undermine, and undermine would send out market notifications and package the data for kezan.. but with the APIs I don't need crawlers anymore.)

This means that my home DSL would constantly be downloading auction data from undermine to put into kezan. When supporting the US realms, this would be a constant 25-30kBps, which is like streaming a high-quality MP3 radio station all the time. No big deal. When I added the English EU realms, it bumped me up to 50kBps, which was starting to feel more noticable.

The packaged data would include every auction seen in each scan, and kezan would perform an upsert for each line. It was a bit lazy, but it worked. Now, the code does a number of things to bring down the data size. It saves the auction IDs seen in the previous scan on undermine. Then when undermine packages the auction data in a new scan, it goes like this:

Was this auction in the last scan?
  • Yes - Remove this auction from the last scan list. Is this auction still shown as "very long" time remaining?
    • Yes - send nothing to kezan
    • No - send only the time remaining for this auction
  • No - send all pertinent data about this auction.
Then, for any auctions left in the last scan list that weren't seen in this scan list, send the auction IDs as a list of "missing" auctions.

Kezan then parses this data appropriately, as a list of changes instead of as a complete state. This saves lots of bandwidth, as the previous 50kBps average is now down to about 15kBps average. Downloads to kezan are quicker, and some update queries are now quicker (since there's no finding and updating rows that haven't changed), so the whole realm parse is quicker.

The parse log now looks something like this.

23:59:10 Getting URL for frostmourne
23:59:10 Reusing socket to
23:59:10 Allowing gzip on this GET
23:59:10 Starting reads, path: /api/wow/auction/data/frostmourne
23:59:10 Got header:
HTTP/1.1 200 OK
Date: Mon, 08 Aug 2011 23:59:10 GMT
Server: Apache
X-Frame-Options: SAMEORIGIN
Last-Modified: Mon, 08 Aug 2011 23:59:07 GMT
Content-Language: en-US
Vary: Accept-Encoding
Content-Encoding: gzip
Content-Length: 140
Keep-Alive: timeout=10, max=3983
Connection: Keep-Alive
Content-Type: application/json;charset=utf-8

23:59:10 Reads complete. 140 bytes at 0.66kBps
23:59:10 Saving handle to
23:59:10 Good header
23:59:10 Was gzipped, extracting
23:59:10 Ungzip successful. 140 -> 148, 94.59999999999999%
23:59:10 Last modified is 1312847947000, greater than our last check of 1312844347000
23:59:10 Pulling
23:59:10 Reusing socket to
23:59:10 Allowing gzip on this GET
23:59:10 Starting reads, path: /auction-data/frostmourne/auctions.json
23:59:11 Got header:
HTTP/1.1 200 OK
Date: Mon, 08 Aug 2011 23:59:10 GMT
Server: Apache
X-Frame-Options: SAMEORIGIN
Last-Modified: Mon, 08 Aug 2011 23:59:07 GMT
Accept-Ranges: bytes
Cache-Control: max-age=86400
Expires: Tue, 09 Aug 2011 23:59:10 GMT
Vary: Accept-Encoding
Content-Encoding: gzip
Keep-Alive: timeout=10, max=3982
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: application/json; charset=utf-8

23:59:12 Reads complete. 1179232 bytes at 548.99kBps
23:59:12 Saving handle to
23:59:12 Good header
23:59:12 Was gzipped, extracting
23:59:12 Ungzip successful. 1179232 -> 6660731, 17.7%
23:59:12 Time lag: -5 seconds
23:59:12 Starting parse for alliance
23:59:15 26412 auctions seen. New max ID: 1757701325
23:59:15 Storing results for kezan..
23:59:15 1353 brand new, 11406 sent in short form, 13653 skipped altogether.
23:59:15 1319 auctions missing since last scan.
23:59:15 Starting market watch notifications..
23:59:17 Done for alliance.
23:59:17 Starting parse for horde
23:59:20 30645 auctions seen. New max ID: 1757700953
23:59:20 Storing results for kezan..
23:59:20 1530 brand new, 12788 sent in short form, 16327 skipped altogether.
23:59:20 1294 auctions missing since last scan.
23:59:20 Starting market watch notifications..
23:59:22 Done for horde.
23:59:22 Starting parse for neutral
23:59:22 51 auctions seen. New max ID: 1757430072
23:59:22 Storing results for kezan..
23:59:22 0 brand new, 22 sent in short form, 29 skipped altogether.
23:59:22 3 auctions missing since last scan.
23:59:22 Starting market watch notifications..
23:59:22 Done for neutral.
23:59:22 Scan complete.

2011-08-08 23:59:27 - Starting scan from less than 1 minute ago. US-Frostmourne-1. (74kb in 0.37 seconds, 199.76kBps)
2011-08-08 23:59:28 - Old live auctions read after 1 seconds.
2011-08-08 23:59:28 - Live attribute removal complete for 1294 auctions after 1 seconds.
2011-08-08 23:59:28 - Auctions table update complete after 1 seconds. (1530 new, 12788 already seen)
2011-08-08 23:59:29 - Skipped auction info complete after 2 seconds.
2011-08-08 23:59:30 - Sold update (657 auctions) complete after 3 seconds.
2011-08-08 23:59:30 - Complete scan with 30645 Auctions done in 3.37 seconds on Frostmourne-1
That means there were 1,294 auctions missing between that scan and the one before. There were 1,530 new auctions in that time. There were 12,788 auctions that are listed as having time "Short", "Medium" or "Long" and the time left was updated. 657 auctions were marked as sold by the algorithm, and there were 30,645 auctions in total posted on US Frostmourne Horde.

Still have some work to do on the client side for EU, changing a few hardcoded "US"es to a region variable, and fixing some bits on the control panel page. I'm doing lots of actual coding work at my day job lately, so some days when I get home I don't want to code even more. :) It'll get there.

Category: Technical | Posted by: admin | 4 Comments

August 05, 2011: Sand Castle

I had to un-optimize some of my optimizations from earlier this week. Humbling. I'm still watching those queries that take a long time to return data, but for the most part, pages are as quick as they were, if not quicker.

I've made a bunch of small changes on many pages to support additional realm sets.. specifically, EU realms. Those changes are live since testing in production is what all the cool kids do. Will probably start getting EU data soon for my own alpha testing. The plan is to support English-speaking EU realms within a few weeks. If anything is broken on the US side now, let me know.

Category: General | Posted by: admin | 2 Comments

August 03, 2011: What's goin' on

So on Monday evening, Blizzard released their Auction House APIs. Things went relatively smoothly on this end with the change. My coding from the specs paid off. Blizz had some hiccups and I had some hiccups but we're each sorting them out and it'll be pretty cool. I don't need those crawler accounts anymore, and updates actually process even quicker than before, since there's less downloading nonsense.

So I used this excuse to mull over EU support again. There's a few changes already done in the database to separate the regions, but last night I was thinking about having enough space for it all. My enterprise-class SSD (which they don't make anymore and whose replacement isn't yet released) is only 64GB. Between the auction table itself and a few other smaller tables and things I need, I was using about 48GB of that 64GB total. That's comfortable for just supporting the US, but not enough if I want to pack EU on the same drive.

To support all auctions for the US over two weeks, the actual data was about 12GB. The indexes to allow for quick searching, however, were another 14GB on top of that. I thought, hey, why are those indexes so huge, and can I cut down the data size as well? I ran a few test queries that disabled the item and seller indexes, and they came back just as fast as without indexing. Sure, they do a scan across all auctions for a realm on that table, but I guess a fast SSD with a large memory cache would be enough, right? So last night I go about removing the indexes to reclaim some space.

Removing an index on an InnoDB table in MySQL means writing a brand new copy of the table and its data, then removing the old one, and pointing to the new one. So it set about copying about 12GB in place. Fine. Takes about a half hour. I have a few other ideas about removing columns (like removing the "first_seen_date" column, which is 8 bytes per row, since I can calculate an auction's start close enough based on the auction ID) so I do that at the same time. While the DB is chugging, I change the website code to match the new table schema. DB is done, site comes back up, and it's way too damn slow. Apparently calculating the auction start time instead of using a stored first_seen_date column takes too long. Whoops. Bring the site back down, put the column back in (another half hour table copy), update the column with dates that make sense (40 minutes?), bring the site back up. It's past my bedtime, and the site is up, so off to bed I go.

Wake up this morning, DB is very unhappy, and everything is taking forever. Note to self: don't come up with table change ideas late at night, you fool. I bring the site back down. Looks like those indexes are useful after all. I figure the item index gets hit a lot more than the seller index, so I just bring that one back. Half hour table copy again. Bring site back up. Any seller page query can lag the DB enough that fast item lookups aren't fast anymore. Damnit. Site goes back down. At this moment I'm adding the seller index (now about an hour-long process, to calculate the new indexes) and hoping things get back to normal. For those paying attention, I'm kinda back where I started yesterday before I started screwing everything up. Oops.

However, all is not for naught. I have ideas to improve the speed and space used for finding current auctions, flagging sold auctions, and handling auctions with bids/buyouts over 429k gold. Those changes are going in, too, and I don't see how they'd keep the DB slow, but my track record isn't that great today, so who knows.

Sorry for all the downtime today, folks. My fault, most of it. I don't have a dev environment that can model the number of auctions used in production in the same way, so some of these changes had to go into prod before I can see how they helped (or hurt). I really hope this afternoon's current downtime is the last of it for a while. And I hope I find the space I was looking for to do some EU support someday.

Totally Not Fun Update I'm almost 2 hours into this last table indexing thing, and it's crawling. I can't exactly tell how far along it is, but I've been watching the directory where it does the dirty and this run through is much slower than the last few. Before, a 12GB table copy took about a half hour. Adding an index brought me to 16GB and took about an hour. Er, whatever. Now I'm adding a second similar index, and I think it'll be another 4GB, but after two hours I'm only at 17GB out of the expected 20GB. Come on you slow dog.

Edit 2 Looks like I should complain more often. Just got done.. gonna do some quick checks before opening it up.
Query OK, 191250219 rows affected (2 hours 3 min 8.76 sec)
Records: 191250219 Duplicates: 0 Warnings: 0

Category: Technical | Posted by: admin | 21 Comments