How to import a large SQL file into MySQL

As a PHP/MySQL developer I’m regularly having to download backups of my databases, and occasionally I have to import them back into MySQL.

The problem is that phpMyAdmin has a file size limit for uploading an SQL file (usually 8MB, depending on your server configuration). Some MySQL backup files are in the hundreds of MegaBytes!

The solution? If you have access to the MySQL command line prompt:

1. Put your SQL file in a simple path (such as “C:\database.sql” on Windows, or “/home/username/database.sql” on Linux)
2. Login to MySQL
3. Create the database (if not already created, or if the SQL doesn’t have a “Create Database” statement)
4. Select the database (“USE databasename;”)
5. Run the “Source” command, like so:

SOURCE C:/database.sql
or
SOURCE /home/username/database.sql

This should import the SQL file into the database, it can take a few minutes depending on the size of the file.

I post this here just in case anyone else had trouble importing large database backups into their MySQL databases. Hope this has helped a few people!

Gmail FINALLY removes ‘on behalf of’ from headers!

Via the Official Gmail Blog

Until now, whenever you send an email from a custom address in Gmail, it would automatically add “From:” in the headers. This reveals your Gmail address to people who you may not wish to see it.

In Outlook and Hotmail, it shows your email as coming from:
myname@gmail.com on behalf of myname@mywebsite.com

Personally, this put me off consolidating my email addresses, because I didn’t want to give my Gmail address out to people, I’d rather they contact me via myname@mywebsite.com.

Well now, finally, Gmail has allowed us to send mail from a custom address without the “on behalf of”!

The way it works is instead of sending the mail out via the Gmail servers, it connects to your other SMTP server and sends it through there. This avoids any problems with spam filters thinking you’ve falsified your headers.

The way it worked before:

The way it works now:

For more info on how to enable this option, visit the Official Gmail Blog:
http://gmailblog.blogspot.com/2009/07/send-mail-from-another-address-without.html

How to use the new Amazon AWS Signature in PHP

I’ve just spent 3…no…4 hours messing about with this, until I finally got it to work.

The new Amazon Associates (or as it’s to be renamed, Amazon Product Advertising API) requires that by August all requests include a Signature.

Documentation here:

http://docs.amazonwebservices.com/AWSECommerceService/latest/DG/rest-signature.html

Here’s how I implemented it in PHP (I had to make some changes to support multiple keywords, such as ‘dan aykroyd’. The space was causing huge problems after being urlencoded and decoded, parsed, hashed, etc).

define('AMAZON_ID','YOUR AWS DEVELOPER CODE');
define('SECRET_KEY','YOUR AWS SECRET KEY');
$keyword = "steve martin"; // The keywords you're searching for
$aws = array();
$keyword = urlencode($keyword);
$url = "http://webservices.amazon.com/onca/xml?Service=AWSECommerceService";
$url .= "&AWSAccessKeyId=".AMAZON_ID;
$url .= "&Operation=ItemSearch";
$url .= "&SearchIndex=DVD";
$url .= "&Keywords=".$keyword."";
$url .= "&ResponseGroup=Small,OfferFull,Images,Reviews,ItemAttributes,SalesRank";
$url .= "&Timestamp=".gmdate('Y-m-d\TH:i:s\Z');
$url = str_replace("+",urlencode("+"),$url);
$url_a = parse_url($url);
$url_a['query'] = str_replace(',',urlencode(','),$url_a['query']);
$url_a['query'] = str_replace(';',urlencode(':'),$url_a['query']);
parse_str($url_a['query'],$params);
uksort($params, 'strnatcmp');
$qstr = '';
foreach ($params as $key => $val) {
$qstr .= "=".rawurlencode($val);
}
$qstr = substr($qstr, 1);
$qstr = str_replace('%20',urlencode('+'),$qstr);
$qstr = str_replace(',',urlencode(','),$qstr);
$qstr = str_replace(';',urlencode(':'),$qstr);
$sig = "GET\n"
. "webservices.amazon.com\n"
. "/onca/xml\n"
. $qstr;
$sig = base64_encode(hash_hmac('sha256', $sig, SECRET_KEY, true));
$sig = str_replace('+','%2B',$sig);
$sig = str_replace('=','%3D',$sig);
$params['Signature'] = $sig;
$p = array();
foreach($params as $k=>$v) {
$p[]=$k."=".$v;
}
$qstr = implode("&",$p);
$rebuilt_url = $url_a['scheme']."://".$url_a['host'].$url_a['path']."?".$qstr;
$aws['url'] = $url."&Signature=".$sig;
$xml = file_get_contents($aws['url']);
echo '';
var_dump($xml);
echo '';


I know what you’re going to say, why didn’t I do it ‘this way’ or ‘that way’?

The problem is that you don’t just encrypt the query string and tack it onto the end of the URL as a Signature. You need to split the query string up, re-sort them, and then join them back together again first. And if the query doesn’t come out looking exactly the same as the original, then the Signature won’t validate. I tried dozens of other ways and in the end I had to go for the most longwinded and seemingly illogical method.

I *could* probably waste time finding a way to condense this and make it simpler, but I’ve already lost my entire night because of this. And at least I know it works.

Update: In the end I used a modified version of Ulrich Mierendorff’s code from here:
http://mierendo.com/software/aws_signed_query/
It works with all the queries I can throw at it, including searches with single quotes.
View it in action here:
http://www.bluesbrotherscentral.com/profiles/willie-big-eyes-smith/
(The Amazon box at the bottom of the page)
It looks like I was on the right track, if I had the time I would have fixed the bugs myself (but why reinvent the wheel?)

Amazon.com can be so hopeless sometimes…

For the record, I love Amazon.com. I order dozens of CDs and DVDs through them all the time.

On my site I wrote a function to use Amazon Web Services to download product data for actors. For example, you visit Dan Aykroyd’s page and all his DVDs appear at the bottom. Simple enough.

Then today I get this confusing email from Amazon:

In addition to the new name, signatures will be necessary to authenticate each call to the Product Advertising API. This requirement will be phased in starting May 11, 2009, and by August 15, 2009, all calls to the Product Advertising API must be authenticated or they will not be processed. For pointers on how you can easily authenticate requests to the Product Advertising API, please refer to the developer guide, available here.

Sounds fair enough to me, I figure I just have to add a line of code to my script or something.

So I read the documentation:

http://docs.amazonwebservices.com/AWSECommerceService/latest/DG/rest-signature.html

TEN Extra Steps!!!

I’ve now spent an entire hour trying to update my code. It works fine without the Signature (and it will continue to do so until August), but as soon as I add the Signature it gives me a 400 Bad Request.

According to the documentation this is the crucial line:

base64_encode(hash_hmac('sha1', $sig, "1234567890", true)) 

Anyway I’ve had enough for now, I’m going to drink something.

Update: Well it turns out that the part of the documentation they don’t include is that I need to get  a Secret Key, they didn’t mention that “1234567890″ is a fake number.

Now I have to see if using my Secret Key will fix it…

Will you crazy people leave me alone?

So as you all know I built www.lcarsdeveloper.com back in 2002, when I used to be a bit of a Star Trek fan (something I lost interest in very quickly).

At the time I was teaching myself Flash, and I wanted to take on a big project to test my Flash skills and improve on them, so I started messing around with the computer interface from Star Trek, which is known as “LCARS”.

In 2005 my site merged with another site called STEngineer.com, a site that was trying to build an operating system called “LOSS” (LCARS Operating System Shell). I gave control over to the STEngineer owners, but after writing their name all over the site and redirecting their domain name to mine (as well as breaking half the pages) they left and never returned.

Eventually I closed down the forum on the site because it was being overrun by spammers and I couldn’t be bothered moderating it. I had offers from people who wanted to take over the forum, I was even prepared to give them all the posts that were made, but nobody had any serious offers (just amateurs who would have given up 2 weeks later).

In the end I just stopped touching the site, uploaded all the source code (Flash FLA files) and said “do what you want with them”.

A lot of people took me up on that offer, the best example is www.lcars.org.uk, which has essentially expanded on my work and built another site out of it. I’m glad to see there’s a decent credit in the footer of the site, although to just say “thanks for designing the menu” is a little bit of an understatement when he’s kept the original logo and all the animations too. I’m embarrassed to see he’s even posted my hideously inaccurate animation here, the one I only made as a placeholder and never got around to finish.  And these:
http://www.lcars.org.uk/lcars_m.htm
http://www.lcars.org.uk/lcars_o.htm
http://www.lcars.org.uk/lcars_p.htm
http://www.lcars.org.uk/lcars_r.htm
http://www.lcars.org.uk/lcars_x.htm (this one he finished)
http://www.lcars.org.uk/lcars_n.htm
http://www.lcars.org.uk/lcars_s.htm (I hated this one)
http://www.lcars.org.uk/lcars_aas.htm (not sure if this is based on mine)
http://www.lcars.org.uk/lcars_u.htm
http://www.lcars.org.uk/lcars_zh.htm (From my LOSS8 demo…I think?)
http://www.lcars.org.uk/lcars_w.htm (Never finished this one)
http://www.lcars.org.uk/lcars_t.htm
http://www.lcars.org.uk/Enterprise.htm (The big animation half way down with the scrolling text, although he finished it)

Don’t get me wrong, I’m thrilled that this guy has gotten so much out of my work, and it’s obvious from the hundreds of animations he’s made on his own that he has his own talent, he’s just used my work as a shortcut for his own. I think that’s fantastic, it’s exactly what I posted the files online for!

But I’m getting very tired of getting emails from…lets face it…morons who think I somehow stole his website.

Like this gem:

Why do you own ADGE’S lcars web site if you are not going to do anything with it.
Some of my friends wanted a new screen save of save trek. but so far there are no new changes. if you are not going to comtrubute to the star Trek then sale the web site to someone who will do something with it.

I mean come on, I don’t OWN his website! I have nothing to do with, aside from the fact that his menu system is 90% my work.
And since when did I make screensavers? I’ve NEVER made a screensaver, and I never even tried to!
If I’m not going to contribute to star trek I should sell my website to someone who will? Well I’ve already given the whole thing away for free, who would possibly want to pay for it when they can do what Adge did and click “DOWNLOAD”?

Anyway that’s my rant for the day. Stop emailing me.

Drunk Programming

Programming is more fun when you’re drunk. I’m actually enjoying it again for the first time in months! And all it took was 2 glasses of bourbon and 2 glasses of Irish Whiskey!

This function I just wrote is so awesome, seriously.

Now I have to fix someone else’s website before I can go back to working on my own. Stupid hosting company has messed up something. Now when you get a UNIX_TIMESTAMP() from the MySQL database it gives you the wrong timezone, so when I convert it back into a date it comes out as 1 day before I wanted!

So it goes into the database table as 2008-10-13, it comes out as 2008-10-14!

They probably changed the timezone on the servers or something stupid, so now I have to rewrite all the functions.

This is why I host my own sites in Texas instead of Australia…

 

Man…Firefly was one hell of a great show. I make sure to watch the whole series and movie at least once a year, half way through it again right now. There should be more sci-fi.

How to ‘Google’ Your Life!

This is something I’ve been thinking about for quite a while, how you can use Google and their associated products to get more out of your life online!

http://www.bluesbrotherscentral.com/forum/computers-video-games-technology/7452-how-google-your-life.html

Friendface.co.uk Text Deciphered!

If anyone was watching The IT Crowd last week, you would remember the whole plot revolved around a Facebook style website called “Friendface”.

Well there is a real official website at www.friendface.co.uk!

Except when you load the site you get a Reynholm Industries error message (I had nothing to do with this, I assure you!)

Well I’ve had nothing better to do tonight, so I  deciphered the blurry text on the page for everyone who was curious what it says. This is genuine, I did this by matching the symbols to where they appeared in the rest of the paragraph. The little upside down V with a dot above it is an ‘A’, and so on.

Here you go:

The text from www.friendface.co.uk, deciphered!

Update: I just found out that someone else beat me by 4 hours, it’s on Graham Linehan’s blog

Still, we’re both happy that someone else could confirm we got it right

Best Customer Service!

Well last week I made the decision to port my mobile phone across from Optus to Three.

(I should point out that I work for Optus, and I was on a staff plan with very cheap call rates).

I chose a $29 cap with $150 in calls, and a $5 data pack with 20MB included (plus unlimited access to some useful sites like YourGigs and YourBars).

Signing up was easy, I didn’t need a new handset so I just ordered a SIM online, which was delivered on the day it was promised. To port the number across I had to fill in a form and fax it back (standard procedure), I chose 15/12/08 as the date to port my number.

On that day at around lunchtime, my phone said “Inactive SIM”, so I took the Optus SIM out and put the Three SIM in. Immediately it started working, as expected.

I logged onto the website and tried to setup an account (for tracking usage, changing your settings, paying bills etc), but it couldn’t locate my number. I figured it was just because my number had only just ported across and it would take a day or two, but I called them anyway just in case.

No voice recognition menu, they have a simple “Press 1 for X, Press 2 for Y” menu system. I put my mobile number in, 5 seconds later I’m speaking to an Indian customer service rep (Three’s customer service is entirely outsourced, I already knew this). She asked me for my name and PIN. Every Three customer has to choose a PIN, it’s much faster to ID people that way! I said I was having trouble setting up my account, she doublechecked everything for me, reconfirmed my account number and said “it’s because your mobile has only just been activated, try again in 24 hours and it should work”. So I tried the next day, and it worked immediately!

A few days later, I received an email from Optus saying my mobile account is ready to view. I have online billing, I have to login to the website and download a PDF to view my bills. So I tried to login as I always do, and there were no mobile account numbers listed. I tried to put my number in and re-link the account, no luck! I know a bill’s been generated, but I can’t see it!

So I called Optus and went through the voice recognition menu (IVR), which I’m an expert on using now. I got an email a few weeks ago at work saying that customers can now use the IVR to check their account balance, it will read it out automatically. I figured this would be nice and easy so I tried it.

IVR: “Using just a few words, please explain the reason for your call”
ME: “Account Balance”
IVR: “So that’s a question about your account balance, is that correct?”
ME: “Yes”
IVR: “And is that about your mobile, home phone or internet service?”
ME: “Mobile”
IVR: “OK, to allow me to locate your account, please say your mobile number, or if you wish you can type it in using your keypad”
ME: “04XX-XXX-XXX”
IVR: “So that’s 04XX-XXX-XXX, is that correct?”
ME: “Yes”
IVR: “One moment while I transfer you to one of our general enquiries and billing staff”

(What the hell??? I thought this was meant to be automated! Anyway I get put through to some customer service rep, she was Australian)

REP: “Welcome to Optus, this is X, can I start with your mobile number?”
ME: “I put my number into the IVR, didn’t it come through?”
REP: “No it didn’t” (Optus has changed to a new caller ID system which doesn’t work most of the time, and most staff aren’t trained in how to fix it)
ME: “It’s 04XX-XXX-XXX”
REP: “And can I have your name, address and date of birth?”
ME: “Yes it’s …….” (What a pain compared to the PIN with Three)

Anyway she told me how much money was due, apparently I have some money overdue but seeing as I can’t view my bill I have no way of disputing the charges! Even though I’ve canceled my Optus service, I should be able to login and view my bills, at least for 6 months after disconnecting. What if I needed them for tax reasons? If I’m missing 12 months worth of bills, I’d have to get customer service to reprint them, a total cost of $60! I’m going to get my supervisor’s permission to view them at work on Monday before I pay anything. The customer service rep was insisting on getting details on when I’ll make a payment for the overdue money, I said:

ME: “I’ll pay when I receive a bill”
REP: “If you don’t pay soon, you may be subject to financial services action”
ME: “What does that mean?” (knowing the answer already)
REP: “You may have your service barred”
ME: “But I just said I’ve ported out my number to Three”
REP: “Yes, that’s right”
ME: “So Optus is going to call Three and tell them to bar my mobile service, just because I haven’t paid my Optus bill?”

I’d take a smart Indian rep over a dumb Australian one anyday…

I’ve only had one problem since porting, which is that Optus customers can’t send MMS (picture messages) to me anymore. I can send them to myself and they work, and I can send them to other people (including Optus), but when someone on Optus sends one to me it sits in my inbox unread, and I can’t download the attachment. I get a “Download Error. Communication Failed” message.

I tried different handsets with the same error message. Tried 2 different Optus people, same error. Even tried different images, same error.

I called Three tech support, the rep was friendly enough but it was a little difficult to get her to understand the problem. Eventually she knew what I meant and realised it wasn’t something she could fix, so she logged a support case for me with their technicians. I figured this was what they’d have to do, I assumed something hasn’t completed with the port process. I guessed if I was porting to Three from Vodafone, I wouldn’t be able to receive MMS from Vodafone customers. She promised me a callback within 48 hours.

Sure enough, 2 days later I received a callback from them, but she said “unfortunately we haven’t received a response back from Telstra yet (they control the numbers), but we’re working on it and someone should contact you within the next few days”. Fair enough, it’s outside their control, but I’m thrilled they kept their promise and kept me in the loop!

And then today, on a SUNDAY, I receive two MMS messages come through, from two numbers I don’t recognise. The second MMS said “test mms from vodafone handset, reply ‘ok’ if received”. So I replied “ok”. I then received a phone call from a technician at Three (He was Australian, working on a sunday!!) and he said he had sent me 4 MMS. One from a Three mobile (successful), one from Vodafone (successful), one from Telstra and one from Optus. After the call I received one more MMS but the attachment wouldn’t download, so I’d assume that one came from an Optus mobile. The Telstra one didn’t turn up at all for some reason. He said they’re going to keep working on it and he’ll call me back when he has an update, but he’s probably going to have to get in touch with Telstra and Optus to fix what appears to be a porting problem.

If I said I was impressed with the way Three treats their customers, it would be a complete understatement. I didn’t have to get transferred once, the staff were knowledgeable and friendly, and most importantly their computer systems work!

My vBulletin Rant

I’m just going through www.vbulletin.org reading through mods and template edits for the Soul Food Cafe forum, just to see if there’s anything useful to make my life easier.

There are some absolutely ridiculous mods there, to the point where I wonder why the author bothered to post it in the first place.

One mod replaces the post editor, which usually looks like this:

See that, nice and simple, completely not confusing for your members.

The mod turns it into this MySpace wannabe monstrosity:

It’s absolutely ghastly!

And what’s the deal with all these authors not bothering to include a screenshot? Is it seriously that difficult to press “Print Screen” and attach a JPEG to your post?

I’m sick of having to click “DEMO” and go to some hideous and painfully slow forum just to see what the mod does. It’s not like the description ever explains it, it’s usually in some terrible cross between English and who knows what language. And the mod titles are usually just as confusing.

One mod was called “Advanced Menu”. You’d think logically that it would give you some kind of more complex or more detailed menu option. What does it actually do? It disables right-clicking and selecting text, two of the most annoying and arrogant things a webmaster can do to their members.

Another mod adds pictures next to member usernames on the forum. Do you really want your members to have that kind of power over how your forum looks? Don’t give your members that ability, MySpace gave the general public the power to control a webpage and look what they all came up with!

There’s a reason why I would rather close my site down than give my members access to the MARQUEE and BLINK tags. Hell, I even made “posting in an unreadable text color” against the rules of my forum, I’ve even had to hand out the occasional infraction (my other members were very appreciative)!

Of course, you can’t tell an author how stupid, annoying and pointless their work is, and how they’re actually damaging the whole internet. They’ll just assume you’re personally attacking them and try to get your post removed.

So instead you have to vent on your blog.

Look at this mod description:

Basically this creats a button with whatever message you put into it,
and when a user clicks it a popup alert comes up with a message they
placed.

Yeah that makes perfect sense, and I totally didn’t need to read it 5 times to work out what the hell you were trying to say. And yes, those screenshots were completely unnecessary.