Copying the last rows from a large remote table into the local database

I recently ran into a problem where I needed to copy a subset of the rows in a huge remote table to the local database.  This is the kind of thing best suited for mysqldump paired with mysql, but there's no parameter that lets you export only the last n rows of the table.  There is however a WHERE parameter (--where, -w) and apparently mysqldump does not protect against SQL injection.  So the solution looks like this:

Warning!  This will drop and re-create your local table, so if you have data in it you need to keep then you need to look into the flags to skip the drop and create statements.

mysqldump -u REMOTE_USER -pREMOTE_PW -h REMOTE_HOST --compress --where "true ORDER BY id DESC LIMIT 0, N" REMOTE_DB REMOTE_TABLE | mysql -u LOCAL_USER -pLOCAL_PW -h LOCAL_HOST LOCAL_DB

Make sure you replace the values in yellow with those appropriate for your situation.
Notes:

• N should be the numeric number of rows you want to copy (ex. 100000)
• There is no space between the -p and the password, but there is a space between most other options and their values
• If your password contains special characters, make sure to escape them
• If your local host is actually the same machine you are running the command on, you can skip -h LOCAL_HOST.  In my case it wasn't because my host operates separate MySQL and web servers (I was SSHed into the web server)
• You can skip the ORDER BY id DESC (or replace the sorting order with whatever fits you) as needed

Why does this work?  We're basically doing SQL injection by causing mysqldump to create select statements like:

SELECT ...
FROM ...
WHERE true
ORDER BY id DESC
LIMIT 0, 100000

The whole value of the yellow text is coming from our --where value.

Getting the most bang for your buck in Planetside 2

Here is a quick guide to XP/certs, weapons, and getting the most out of your dollars in the action-packed futuristic warfront of Planetside 2. If you know anything about the game or about SoE's F2P business model some of these may be obvious tips, but some may not be so obvious even for veterans. 

Milestone: 200,000 gold in World of Warcraft

If you're one of the folks on my BattleTag or in my guild, then you probably know that lately I've been spending quite a bit of my playtime in the pursuit of the almighty gold coin.  A couple days ago I passed my latest milestone: 200,000g.

So how did I come to reach this milestone?  Read on to find out...

Guild Wars 2 first beta weekend impressions/feedback

*GENERAL*
* *Positive*
** *THE GAME IS CHALLENGING, AND FUN, DON'T DUMB IT DOWN*
** Art / graphics are excellent
** The music is GREAT
** Very good performance even with 50+ people doing the same DE, or in WvW (even with custom-high settings I switched to)
** Save account AND password information is awesome to have!
** Game is fun and stable
** Charr are a very cool race concept

* *Negative*
** *Overflow is horrible. You need to identify each overflow (ie. I am in Overflow #3) and add the possibility to switch to your friend's / group members' / guildmates' overflow to play together.  Being unable to play in the same version of the zones as the people I want to play with is a dealbreaker*
** XP gain is too fast
** Better / more in-depth tutorials are needed.  Particularly on dodging, crafting, WvWvW
** Add a button to go to WvWvW directly without having to go through the PvP lobby
** Annoying to have to log out and back in to switch characters. Add a "Go to Character Select" option
** No 1-st person view
** Camera max distance is far too low
** Cannot bind multi-key actions (Ctrl + 1, Shift + PgUp, etc)
** Cannot bind additional mouse buttons
** Transferring worlds failed with cryptic or no error. Randomly worked and randomly didn't with no indication as to why.
** 5 character slots on a single server is unplesantly limiting.  Consider allowing 2-3 servers we can have 5 characters on each.  At LEAST allow 1 Euro and 1 America server per account.
** No /follow
** Windows need to remember the last position / panel they were opened to when reopening
** No Oceanic servers, no South-America servers


*PvE CONTENT*
* *Positive*
** Co-op play with everyone participating in an event is very fun. No kill stealing is nice.
** Heart tasks are awesome and I particularly liked the mix of combat and fluff (clean graffity, hit kids with snowballs, feed cows, etc)
** Dynamic Events are a lot of fun
** Death / revive system is awesome
** Weapons of opportunity are great

* *Negative*
** Hearts are too easily filled.  Make them require 25-50% more contribution
** NPC dialogue doesn't always show in chat when NPC dialogue is checked, very easy to miss instructions or story
** Selecting the right target to Revive / Search / Gather / whatever is very hard to do when you have a bunch of them stacked.
** Combat is very adversely affected by the slightest lag
** Obstruction on abilities can be very wonky in a lot of cases where it blocks an ability that did have line of sight and allows others that didn't.  Sometimes without moving (or the target moving) some abilities will pass and others fail
** Dynamic Event scaling needs to better adapt to massive numbers of players without becoming an exercise in frustration as mobs 1-shot dozens of players (only increase damage so far, if more scaling is needed spawn some sidekicks to keep everyone engaged)
** DE bosses easily get lost in the swarm of players / particles when there's 30+ fighting them. Make them scale in size.
** Dynamic Event chains should be longer with more steps and less repetitive.  Fork DE outcomes more into different events
** Melee combat is far more risky than ranged without being more rewarding
** Camera is too close-in when 'dead' making it impossible to use certain abilities well (ex. Teleport to target area)
** Events in a zone should be announced / marked at a greater distance not only when you're right on top of them
** Personal story has too few variables and will get old fast
** Pets need some improvements to AI or mechanics as they do not dodge and get splattered. Every. Single. Fight.
** Spawn rate is too quick when solo, too slow when in a 30+ player blob
** Difficulty is all over the place. Some mobs and PQs are trivial and others are almost impossible.

*PVP CONTENT*
* *Positive*
** Loved the WvWvW!!
** Good architecture on the keeps / towers

* *Negative*
** Add 'hotspots' on the map based on where a large number of players are engaged in fighting each other
** WvWvW needs a lot more tutorials (for ex. building siege machines, supply carry limit, co-op nature of supply, how to capture supply)
** Battlegrounds are boring and very much been there, done that
** WvWvW is very much lacking in personal rewards
** No dueling
** WvWvW groups need a squad system with multiple levels of command (take a look at EVE's fleet system)
** No option to leave battleground at the end of a fight, leads to a lot of players dropping after the next game starts
** Trebuchet produces too much smoke

*CLASSES / SKILLS*
* *Positive*
** I like not being swamped with a million abilities on my hotkeys
** Underwater combat with its own weapons and abilities
** Skillchains

* *Negative*
** A lot of buffs should last longer (paired with longer cooldown on their casting) to add more strategy to casting / dispelling / stealing them
** A class's weapon options and the skills they provide should be shown on the info screen at class selection
** Weapons change gameplay significantly - give the player a choice in their starting weapon
** Low camera max distance makes it hard to effectively use the range of many ground target abilities
** The ground target preview shape does not appear always when preparing to place an ability (the ability places OK when clicked, but you can't preview where it'll be)
** AoE ground targets can be very hard to see in a variety of environments
** Mesmer illusions and engineer turrets do not attack keep doors. Particularly mesmer illusions get summoned by the abilities used then stand there being completely useless.
** No skill reset
** All skills disabled when swimming on the surface of water.  Make either ground or underwater skills active don't disable everything as soon as we touch the surface


*ITEMS*
* *Positive*
** Starting with a set of armor dyes (and customizing color of starter armor) is great
** Unlimited use armor dyes
** I like salvage kits and being able to break items into all kind of mats
** Skimpy female outfits

* *Negative*
** Armor and weapon appearance needs more variety in shape
** Offer a choice of starting armor appearance.  Even with the custom color schemes it looked like the zones were full of a million clones running around since everyone looked the same from 1-10.
** Many of the dye shades are almost indistinguishably close.  Feels like a gimmick to sell more dye packs on the market

*CRAFTING*
* *Positive*
** Discovery is FUN!
** I like salvage kits and being able to break items into all kind of mats
** Being limited to two crafting skills is frustrating but good
** Liked being able to harvest everything without needing to learn gathering skills

* *Negative*
** Blood needed for the basic sub-10 craftable armor is too rare.  In the time I got 3-4 blood I not only levelled to 10+ but also discovered and crafted more than a full set of level 10 armor.


*UI*
* *Positive*
** Intuitive and clutter-free

* *Negative*
** Chat system is not up to snuff for a 2012 MMO - Need /say, need overhead chat bubbles for nearby speakers, need more categories of chat when controlling on/off (ex combat should be split into far more detail)
** The auction house is VERY slow and buggy (a lot of times it stalls into infinite loading, other times it fails with no error message given)
** No minimap filter
** No face-to-face trading
** Player name font too small (allow to select font size)
** UI size other than default causes ugly pixellation and unreadable fonts
** UI size scaling should have a broader range and more steps in between
** UI elements should be movable and resizable at least
** No guild recruitment window

MySQL dump to .gz file in 1 line

Came across this neat little 1-line linux script to dump a mysql database to an zipped sql file.  Auto-named based on current date/time too!

mysqldump -u <username> -p<password> <database> | gzip -c > <database>_$(date +%Y%m%d_%H%M%S).sql.gz

I love snippets that speed up things I do dozens of times a day sometimes!

Slow response from mysql server

I've been working with one of our older applications the last couple days and for some reason my test server database was very slow to respond to each operation.  Pages would take upwards of 6 seconds to generate even though the actual queries ran for less than 0.05 seconds.

I also noticed that the slow-down only happened if the application ran on a different machine than the sql server.  For example I was browsing from my machine ( 192.168.0 .10) to the Apache server on the same machine (localhost) which in turn connected to a MySQL database on another machine in the same LAN (192.168.0.99).  When I connected to Apache on  192.168.0 .99 and it used localhost to connect to mysql everything was smooth.

The solution - as I found out after several hours of Google-ing - is a little configuration option in mysql's my.cnf (or my.ini as your case may be):

skip-name-resolve

This should go in your [mysqld] section, so I just added mine at the bottom.  Here's the what the whole section looks like:

[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
skip-name-resolve

While I am not exactly clear on what is causing the slowdown, I believe it is due to the MySQL server attempting to resolve a host name for the server connecting to it if the connection comes through the network and not a local socket.  As such it wastes some time attempting to resolve 192.168.0.10 which would fail.

Five Things EverQuest 2 Gets Right

Us MMO gamers tend to be a fickle and malcontent bunch, always complaining about this and that and how X game or Y new feature sucks.  You can hardly view any MMORPG forum without finding threads complaining about it, or its competition, or the next expansion.  I'm no exception either and many times in the past I've found myself often engaging in General Chat arguments over the failures of this or that game.  What sparked the idea of this article was EverQuest II, a game I had played very little since its release in 2004 but one that sucked me in recently with its free-to-play move in December 2011.

In the time I spent in EQ2 these last few months I found myself genuinely surprised and delighted by the way it approaches certain features.  It's nothing revolutionary, but after over 6 years spent mostly in World of Warcraft it can be easy to take WoW's ways as standard.

With these things in mind I decided that, for a change, I'm going to post about the positives of various MMOs I play or have played and focus on things they did right, things that added enjoyment (sometimes very subtly), instead of bashing what they did wrong.  So to cut to the chase, here are 5 positive things I found in EQ2:

Mentoring
Depending on what MMO lands you've been traipsing through this feature might be something you're very familiar with or completely new to you.  Mentoring, as implemented in EQ2, allows you to temporarily reduce your level to that of a groupmate.  You keep all your abilities but they are scaled down in power and you incur a penalty to your XP gain (your mentored groupmate gets a bonus), but you can still earn XP based on how mobs con to you at your reduced level.  For example if I want to play my lvl 60 conjurer and my wife wants to play her 25 inquisitor we can group, I can reduce my conjurer to level 25 and we can do level 25 mobs together.

Mentoring may not seem to be a big thing, but it's huge boon both to content and teamwork.  On the content side it allows players to experience content they missed as they levelled or revisit content they enjoy in a way that retains some of its challenge.

The social aspect is of even bigger importance.  Have you ever rolled a new level 1 toon you weren't too interested in just to help a friend / girlfriend / relative who was just starting the game?  Have you ever hated playing catch-up because your jobless friends got 10 levels ahead of you?  Did you ever get tired of slaughtering grey mobs 30 levels below you because your guildmate asked for help?  Of course.  With mentoring you can do all those things and still be playing the toon you love while getting some xp and challenge out of it.

And to make it all even better, there are chronomage NPCs that allow you to reduce your level without having someone to mentor.  So you can even enjoy lower level content solo!

Mounts

One of those little things that just work and just subtly improve your enjoyment is the way EQ2 handles mount buffs.  You get a mount.  You equip your mount.  You summon your mount.  You ride your awesome new mount around.  And then you zone into a dungeon.  Your mount disappears.  Not a big deal, it's standard fare of most MMOs for mounts to not be used indoors.  The difference is in EQ2 the mount buff is simply suppressed not lost.  When you get back into a rideable area you're immediately back on your mount and all its speed enhancing properties resume.  It's a tiny thing, and one that I didn't even notice until I had been playing for a couple months.  And then I noticed it and couldn't help but think "Holy shit, this is a great idea! This is so much more convenient than having to recast my mount each time I exit a house or fall in the water."

Appearance Slots
In addition to the standard inventory slots where you equip your rusty short sword or epic lootz (depending on how addicted to the game you are), EQ2 also gives you a set of appearance slots.  These are only available for those slots visible on your 3D model (head, chest, main-hand, etc) and you can place any item you can equip in them.  If you leave them empty your looks will be based on your stat item (so you could leave all of them empty and things would work as in any other game), but if you place something in the appearance slot its looks will be visible instead.  Meanwhile you still get all the stats of your regular item.

Did you just loot a Super-Ultra-Mega-Epic Mace of the Apocalypse but realize its looks fall somewhere between a caveman's club and a turd-on-a-stick?  No problem.  Pop that sucker into your regular main-hand slot so you can visit doom and destructions on anything able to yeld more phat lewtz and put your Not-very-efficient Mace of Awesome Texturing in the appearance slot.  You look like your're swinging the cool looking weapon but the mobs you fight are in for epic pain.

This is not a unique feature but EQ2 gets it right:  The custom look isn't tied to a specific item so when you upgrade your stat items your appearance stays the same.  It doesn't cost any gold or a trip to an NPC.  The items do not have to match - for example my tank uses an axe and shield but her appearance slot is filled with a badass looking two-handed sword.  And there's oodles of appearance-only items (which have no stats but look awesome) to be found / quested / bought / crafted.  All in all an RPers dream.

The Marketplace
Definetely an odd one for me to place on this list because I'm not at all a fan of micro-transactions.  I'd much rather shell out $60 for a boxed expansion and $15 a month and get everything there is to get through gameplay than spend on micro-transactions, but in EQ2 I ended up spending quite a bit on fluff and I didn't even need it for gameplay.

First off, nothing in EQ2's marketplace is required to be competitive.  There are no epic loots, no buffs, no combat pets, no potions, no mercenaries.  There are a few things in there have some effect on gameplay, but they're all minor.  For example there are XP potions but they cap at 100% bonus for 4 hours for a whooping $18.  Keeping in mind that everyone can get a 200% rested bonus and that levelling is very easy already, they aren't at all necessary.  There's also a few stat items but they can all be bought with Station Cash OR Dungeon Marks (farmed in game).  All in all I spent over $250 and I haven't felt driven to buy a single item that affects gameplay.

Secondly, the EQ2 Marketplace is packed with a huge variety of stuff.  It doesn't matter what you like doing in the game, there's something cool you could buy.  From appearance weapons and armor, to non-combat pets, to houses, to mounts, to dungeon layouts and spawners for creating your own dungeons (also buyable with dungeon marks).

Last but not least, you can use your Station Cash balance to pay for services such as server transfers (As an aside, EQ2 server transfers take less than 5 minutes to complete!!), character renames or even to add prepaid subscription time to your account.

Player and Guild Housing
This feature is awesome enough to deserve its own blog post (and maybe one day I will dedicate it one), but to make a long story short in EQ2 you can own houses.  The downside is they are instanced.  The upsides, however, are many!  You can allow others to visit your house and you can even designate friends or alts who can make use of its amenities, place items inside it or redecorate.  There's also a huge amount of items that can be placed in houses (in fact a whole tradeskill profession is dedicated to creating furniture and decorations).  There's also a huge variety of house types and layouts. I've seen everything from a 2 room shack, to a mansion, to a firey draconic lair, to your own flying island.  And just so you aren't too torn by the choices, each of your characters can own 10 of them.

And for a final treat here is a video tour of a player-owned house.  The layout is premade, but all the furniture, paintings, statues and other decorations are placed by the owner.  One of my characters owns one of these too, but I have to admit mine is not nearly as cool as this one.  So for your enjoyment here's the video (made and posted by thesawolf):

cURL on Ubuntu 11.10

One of the new features I'm working on for a client requires libcurl to connect to a remote server from PHP.  I've dealt with enabling this on Windows installs of PHP before, but since I've lately had to use a linux (Ubuntu 11.10) server for testing I had no idea how to get it up and running (my forays into using a linux-based server are still new).  After some searching and trial and error it turned out there's a very simple and elegant solution:

sudo apt-get install php5-curl

Nifty bit of sofware: Oracle VirtualBox

Upgrading my computer at the end of December was a perfect time to start fresh with all my installs.  The first couple days were the usual whirlwind of installs for all the most used pieces of software (OS obviously, browsers, IDE, Skype, and so on).  Unfortunately I'm far from the most organized guy around so a lot of the things I used to have, but didn't immediately need, slipped my mind.  A couple days ago though I ran into the need for a virtual machine and realized I hadn't re-installed Windows Virtual PC yet.

In 2011 I used Virtual PC on and off as a platform for testing projects in a few browser/OS configurations that are common to our clients but that I don't personally use at home.  It's worked well, but as impatient as I am I always found it taking too long to start up and shut down VMs (not the guest OS itself, but rather the loading time before the guest started 'booting').  Since I was faced with a fresh installed anyway I looked around for some alternatives.  One of the first things that popped up on my Google search was Oracle's VirtualBox software.  Between it free price tag, the well-known Oracle as its developer and the fact that it's open source software I decided it's worth a shot.

Installation (I use Win 7 x64 as the host) went smoothly.  Configuration was also very straightforward and covered all the usual things I've come to expect from a desktop VM solution.  The list of supported guest OSs (as far as I can see from the options when setting up a VM) is quite extensive.  The Windows selection covers everything from 3.1 to 8.  The Linux list covers big names like Ubuntu, Red Hat, Fedora, Gentoo, Debian and several others.  The other lists are more limited, but some names stand out: DOS, Max OS X (and its Server pair), Solaris 10, Free BSD (and a handful of other flavors), several OS/2 versions and more.  Each list also tends to have an Other option which may let you install even things unlisted.  Also, most OSs are accompanied by their 64-bit counterpart as well.

My first attempt was to import my Windows XP virtual disk I had from my previous Virtual PC install and basically re-build my WinXP VM.  This didn't pan-out as neither the built-in Virtual Media Manager nor several command line tricks I found on Google were able to turn this virtual disk into something usable.  This wasn't a huge issue as I had pretty much expected to start fresh when I chose to try different VM software.

Windows 7 and Ubuntu VMs in Oracle Virtual Box

Starting from scratch I created two VMs, one with Ubuntu 11.10 and one with Windows 7, both in 64 bit version.  Both of them installed very smoothly.  Absolutely no problems with Windows 7, and while Ubuntu threw a couple error lines due to some feature the virtual bios didn't support it installed and runs perfectly.

One thing I really liked is that once the guest OSes were up and running everything else was also working without any additional drivers or tweaking.  Both VMs' sound system played properly through the host's speakers, both of them were connected to the internet and picked up the USB devices I enabled on them.  A very quick "Install Guest Additions..." on each VM also provided them with video drivers (after a reboot of the guest OS) that allowed them to go beyond 1024x768.

What I really like (beyond the painless setup of the VMs) is that start-up / shutdown or save / restore of the VM state is very very quick compared to Virtual PC.  No, I didn't run benchmarks on all that, but it definitely satisfies my impatience.

All-in-all Oracle's VirtualBox addresses my VM needs so I can see myself using it long term.  If you're looking for a VM solution then this one is definitely worth a look-see.

SoE will unlock all pre-existing characters the morning of 12/09

Sonly Online Entertainment is listening to the issue experienced by veteran players who tried to return to EQ2 in the new free-2-play model only to find themselves unable to roll a new character on top of having all their characters locked.  Earlier this evening, EQ2 Executive Producer `SmokeJumper` posted that characters which existed before the F2P switch will be unlocked.  This change should happen sometime is happening during downtime on Friday, Dec 9th at 7:00 AM PST (patch notes here).  You can find his original post here, but you can read below for the highlights.