Keeping it safe

Here’s a useful little site I found today – not sure how to create that strong password??  Give these guys a shot – and once you’ve picked something out, they even give you a way to test it!

Online Password Generator

Working with large MySQL tables

I’m working with Global Affiliate Network right now, and one of the big problems we’re starting to run into is how to work with the sheer volume of leads that we have to store.  It’s early days yet, so we have to find a solution to this as the volume of data is only going to grow, even with sensible archiving going on.  The good news is that if huge operations like Google and Yahoo can run their many billions of records on MySQL, I know we have the right underlying technology!
So – the plan is to take things operation by operation and optimize each one, both in terms of the table data and the way we set up the queries…
One of the things we do is to run programs from other networks through our network.  With this setup, we have to be able to synchronize our database to show which leads were accepted by the other network in order to show commissions due, etc.  So each click is tagged with a unique ID in our database and in the receiving network, then I periodically pull a report from the receiving network, as an XML or CSV, to update our database.
All of our transactions are in one big table, currently some 35,000 records (so not massive by MySQL standards) and the report from the outside network we’re working on is pulled into a second table (currently about 7,500 records).  So there’s a few steps here:

1. Pull the report from the receiving network
2. Parse that report and add it to a data table just for that network (each network returns different information, and there’s only a few to work with, so this is probably better than trying to normalise and combine all the data into one table) – making sure we only add the NEW records.
3. Run through our transaction table and update the status of each transaction matching the unique ID’s stored in that network table.

My first thought was that pulling the file was taking the time, but inserting a few timestamp echo statements into this file quickly showed that the first two steps were typically completed within 2-3 seconds.  The last step looked like this:
$sql = "update partners_transaction set piggyback_approved=1, transaction_status='pending' where transaction_status='held' AND unique_id in (select UID from net_trans WHERE NOT UID='');";
if (!$result = mysql_query($sql,$db)) die ("Could not update partners_transactions ".$sql);

Nice and simple, and all in one query – I even made sure that the main table was indexed on the key fields, but this single query was totally killing my database, even with only a few records to update.  After much work with the tables and googling MySQL experts, I finally ended up with the following solution – which seems totally counter intuitive to me:

1. Read the list of UID’s from net_trans into an array
2. Loop through the array, updating the matching record in partner_transaction as a single query each time

It seems to me that this requires far more queries, but apparently this is a MUCH quicker way to do things!  I’d appreciate any feedback from MySQL experts out there, but the final code looks like this:
//Get list of unique ID's:
$UID_list = array();
$sql = "select UID from trans;";
$result = mysql_query($sql, $db) or die(mysql_error());
while($row=mysql_fetch_assoc($result)) {array_push($UID_list, $row['TransactionId']);}

echo stamp()." Pulled list of UIDs";

$i=1; //Use as loop counter
foreach ($UID_list as $uid) {
$sql = "update partners_transaction set approved=1, transaction_status='pending' where unique_id='{$uid}' and transaction_type='sale' limit 1";
$result = mysql_query($sql,$db) or die ("Could not update partners_transactions ".$sql);
$i++;
}

echo stamp()." Transaction update complete - {$i} updates";

This solution processes almost 8,000 records in a total of 8 seconds, with negligible loading on the system. I’ll be limiting things more by selecting only the UID’s from the last few days – no need to really go back much more than 24 hours, but this solution already solves a huge load – and may give me the pointers to optimizing the other queries!

How to Find That Feed

OK – so my previous post showed how to create a feed on your website – but you’re missing out on a lot of the power if you don’t let the rest of the web know about it!  Luckily, it’s really easy to make your feed discoverable by any passing robot or web browser.  Just put the following between the <head></head> tags on your page:
<link rel="alternate" type="application/rss+xml" title="Name of Your Feed" href="http://your_rss_url" />
Thanks to Sean McGrath for refreshing my memory on this one!

Creating Your Own RSS Feed

There are many reasons to create an RSS feed – ranging from simply being a good way for people to keep up to date with what’s going on with your website, to a great interface for other applications such as Twitter.  If you have any sort of regularly updated content (game scores, news updates, blog, etc) then you should definitely consider creating an RSS feed.

I did just that for the Global Affiliate Network last night – we already had a MySQL driven News page, so this script pulls the last 10 articles and creates an RSS feed.  In my case, this was to publish on Twitter – but that’s a whole other article…


//Script to create RSS feed from our GAN news database
//Primarily for us to use in Twitter...

//Get our standard includes:
include "../includes/constants.php";

//Setup the opening info for the file:
$feed ="\r\n"
."\r\n"
."\r\n"
."\r\n"
."http://liveleads.org.uk/feed\r\n"
."This RSS feed keeps you up to date with what's going on at Global Affiliate Network.\r\n";

//Now open up our database, pull all the news articles (or the last 10) and create the body of the RSS
$mydb = mysql_connect($host, $user, $pass);

if (!mysql_select_db($db,$mydb)) { die("could not select database"); }

$sql = "select * from GAN_news order by news_date DESC limit 10;";
if (!$result = mysql_query($sql,$mydb)) die ("Could not retrieve news articles");

while ($article = mysql_fetch_array($result)) {
$feed .=''
.''
.'http://liveleads.org.uk/index.php?'
.'mode=news&article='.$article['news_id']
.''
.''.utf8_encode(html_entity_decode(stripslashes($article['news_content']))).''
.''.$article['news_date'].''
.'';
} // End while statement

//Close out the RSS feed:
$feed .= ''
.'';

header("Content-Type: rss+xml; charset=UTF-8");
echo $feed;

?>

This is a pretty tight piece of code – the only thing I will be working on is the encoding for the news_content – it took a lot of work to get this to display properly and pull into Twitter properly. I’m open to suggestions from any experts out there!

Surprise – Windows 7 DOESN’T boot faster!!

Study: Windows 7 doesn’t boot faster | Beyond Binary – CNET News

Although Windows 7 has been praised for loading and shutting down faster than prior versions of Windows, one software company says that, in many cases, the new operating system can take longer to get started than Windows Vista.

Iolo Technlogies, which sells PC tune-up software, said its lab unit found that a brand-new machine running Windows 7 takes a minute and 34 seconds to become usable, as compared to a minute and 6 seconds for Windows Vista. Iolo notes that it measured not the time it takes for the desktop to appear–which can be as little as 40 seconds on a fresh installation of Windows 7–but rather the time it takes to become fully usable “with CPU cycles no longer significantly high and a true idle state achieved.”

Going Dedicated for Global Affiliate Network

This is an exciting time for Global Affiliate Network – just 4 months after the official launch, there is already far too much traffic for the shared hosting plan we started with, so it’s time to make the move to a dedicated server.  In itself, that’s no big deal – the trick is in making that switch with the minimum interruption to service – we have leads flowing in all the time, so we really don’t want to lose any more traffic than we have to!  This entry will journal the steps I take to make that move…

Setting things up

At this point, the dedicated server is all setup with Apache, PHP and MySQL.  Further, I have already frozen the code on the old server and uploaded a copy to the new.  New databases have been created and loaded with a snapshot copy of the data from the old server.  Then the system has been tested out and any configuration changes (database server settings, etc) updated and checked.  It’s tough to check EVERYTHING, but if you’re not changing the code, then things tend to work as they always have or to fall over horribly – there’s not really any middle ground!

Right now – the new databases are empty and waiting.  Most of our traffic is UK based, so the upgrade is going to happen around 8pm Central, which is the early hours UK time, so therefore low traffic volumes.  Time to make the switch!

Making the switch

Letting the users know

About two days ago, we put out a notice that this upgrade was about to happen.  It’s amazing how upset some users can become at any interruption to service, no matter what the reason – so some advance warning is always a good idea and emphasizing the benefits to come.  Then tonight a 30 minute warning was sent out – loss of service should be short, but it never hurts to keep people informed.  I’m not going to be answering support desk tickets during this transition anyway…

Locking down the old system

We need to freeze the database as much as possible to make this switch.  It’s a MySQL database, so there will be some time while we download the old data and upload it to the new databases.  Old data will remain on the original server for the time being as a backup while we check out the new setup, but it will become increasingly out of date with each lead that passes through, so we need to get the new system working asap.

1. Switch the domain name over to the new server.  The old server no longer functions, so the sooner you start propagating the new server IP address, the better!
2. Change the database passwords in the old config files to “break” the website – that prevents any changes to the data from routine usage.
3. Download the MySQL databases (one for the email list, one for all our leads) as SQL files.
4. Setup a single, root directory .htaccess file to give a 301 redirect to the new server – shouldn’t be needed for long, but better than leaving visitors just hanging there.  It’s not ideal to redirect all traffic to one single page, but this is only while the new domain name settings propagate (probably 72 hours max, and usually a LOT quicker than that).  The file will need to include the line:
   

   redirect 301 /old/old.htm http://www.you.com/new.htm

Loading up the new system

With the above changes, the new system is already up and running – it just doesn’t have any data in it!  So as soon as we get the exported data uploaded, we should be in business…  Now, with databases of this size, phpmysqladmin is not going to handle it – you just cannot upload files this big, so it’s time to go command line!

1. Upload the exported files from above to the new server – put them somewhere you will be able to find them easily!
2. SSH into the new server.
3. Log in to mysql using mysql -u USERNAME -p – it will prompt you for your password
4. Select the database you want with source DATABASE
5. Upload the data with source /path/to/DATAFILE
6. Rinse and repeat for the other database

Now – if you’ve prepared everything correctly – that should be that!!

Calling PHP in dynamic pages

Here’s a great little piece of code I came across today – it gives us a way to access server-side data and functions from a live javascript page.  There are so many things that javascript cannot do for security reasons, but by using this code snippet, you can call any PHP script from your webpage, have them execute whatever you need and then use the output in your webpage.  So much of AJAX is way more complex than this – if you’re looking for a simple solution, this could be it!

Loading and Saving Files – CodingForums.com

function IO(U, V) {//LA MOD String Version. A tiny ajax library. by, DanDavis var X = !window.XMLHttpRequest ? new ActiveXObject(‘Microsoft.XMLHTTP’) : new XMLHttpRequest(); X.open(V ? ‘PUT’ : ‘GET’, U, false ); X.setRequestHeader(‘Content-Type’, ‘text/html’) X.send(V ? V : ”); return X.responseText;}

In my application, I have a list of transactions from a database, clicking on the delete image calls a javascript event handler which calls the above script. The IO url is deletrans.php and it returns a success message once the transaction has been deleted, then the last part of the javascript routine updates the transaction table.

So I have this onclick javascript:

function deletetrans(trans_id) {
var answer = confirm(“Are you sure you want to delete this transaction?”);
if (answer){
msgbox(IO(‘deltrans.php?transid=’ + trans_id), ‘red’);
setuppage();
} else {
msgbox(“Deletion cancelled”, ‘green’);
}
return null;
}

And my PHP file is:

<?php
include ‘modules/config.php’;
include ‘modules/db_open.php’;

$db = db_open();
$sql = “delete from `entries` where `entry_id` ='{$_GET[‘transid’]}’ limit 1;”;
if (!$result=mysql_query($sql,$db)) die (“Could not delete this transaction”);
echo “Transaction {$_GET[‘transid’]} deleted!”;
?>

Moving Windows XP around

So you’ve got a perfectly good Windows XP installation – customised JUST the way you like it, complete with desktop icons, program settings and multiple users, not to mention all of your data from your entire life.  Everything is good – until suddenly your motherboard fails…
Replacing the motherboard is an article in itself, but one of the problems with Windows XP has been that it would refuse to reboot with a new motherboard, meaning that you need to do a clean install and then go through recreating all of those settings and icons that you used to have.
However – with this article: http://forbiddenpc.com/unclean.htm – in many cases you will be able to switch motherboards and still keep the same Windows set – saving yourself a ton of work and not a little time in the process!

Moving your IP around

There are lots of reasons why you might want to seem to appear from a different IP address – some of the legitimate, others not so much!!  In my case, this was a project entering data into a webpage – the data is legitimate, but the webpage IP blocking (which was set up to stop spammers, quite rightly!) would only let me submit one set of data.

Enter the Tor project.  Take a look at their website and you’ll discover all sorts of uses for Tor but it was perfect for me right out of the box.  I’m running Debian Linux on my main computer anyway, so a quick edit to the sources.list file to add the Tor repository and apt-get install to do the work.

This installs two packages – Tor itself, and then Privoxy – between them they can route all your internet traffic through a network of random sites all over the world, making it much harder for anyone to see where you are connecting from.

One note of caution – you still have to configure your browser to use the proxy service – that part’s not automatic!  Full instructions are on the Tor website – surf in privacy and enjoy!

How’s your phone bill??

With recently relocating, it’s come home to me just how expensive it can be to use a cellphone for everything!  Even with all the included minutes you can get, trying to run a business from your cellphone just doesn’t make any sense if you have a fixed office to work from.  With a little help from your internet connection, you can DRAMATICALLY reduce your phone bills!

How?  Our friends over at Skype, that’s how!  You can download their software for free, install it on your computer and instantly start talking with other Skype users all over the world absolutely for free.  No big deal there – MSN messenger has been doing that for years, but Skype does have a nice look to it – and it’s available cross platform, so Linux and Mac users are on the same system too.

Where it really gets exciting though is with a paid subscription – check this out, for just $2.95 per month (no – that’s not a typo!) you can get UNLIMITED US and Canada calling, right from your PC!!  Then for little more you can add the rest of the world, either just some countries, or the whole globe.

Still sound a bit geeky?  Well – you can now buy Skype hardware from WalMart – which means you can just plug a regular phone into your internet router and you don’t even need to have your computer switched on to make or receive those calls.

If you’re looking to reduce your phone bills, you have to check out what Skype has to offer.  If nothing else, you could cut that AT&T bill down to size by getting just the basic local phone service and use Skype for long distance – the possibilities are endless.  Have fun – and you can find me on Skype at GaryBailey2008!