How to prevent a PHP script from running while it’s still running

When you’re running cron jobs sometimes you’ll need to run things that use a lot of CPU or other resources. If you need it to run right away, but you don’t want to copies running at the same time, it’s pretty simple to setup.

The way I’ve been doing it is by creating a file at the top of the script, and deleting it at the end. When the script starts you just check if the file is there, and if it is, you quit.

Select Code
1
2
3
4
5
6
7
8
9
define('PIDFILE', '/home/username/public_html/file.pid');

if (file_exists(PIDFILE)) { EXIT(); }

file_put_contents(PIDFILE, posix_getpid());
function removePidFile() {
unlink(PIDFILE);
}
register_shutdown_function('removePidFile');

What I’ve done is used the register_shutdown_function so that way it deletes it on exit. The benefit is you don’t have to mess with all your code to ensure every area it could possibly stop running deletes the file.

Then I simply set the cron job to run every minute. It will check if there is something to do, if there isn’t, it exits, if there is it keeps going and no new jobs start till it’s finished. Real simple!

Using GROUP_CONCAT in a single to many JOIN query with MySQL

I’ve had this conundrum before, but I didn’t know how to do it until now. What if you are doing a MySQL query and pulling a bunch of rows. But then each of those rows has more data in another table, which can have multiple rows itself. Normally you’d have to loop through the first query with PHP and run the second query.

Well I figured it out how to do it using GROUP_CONCAT. But first, here is the “old way” of doing what I’m talking about:

Select Code
1
2
3
4
5
6
7
8
9
$query = "SELECT * FROM `posts`";
$result = mysql_query($query);
while ($POST = mysql_fetch_array($result)) {
$query2 = "SELECT `tag` FROM `tags` WHERE `PID`='{$POST["PID"]}'";
$result2 = mysql_query($query2);
while($TAG = mysql_fetch_array($result2)) {
.. code goes here ..
}
}

So what ends up happening is for each row in posts, you run that second query. So if there are 100 posts it ends up being 101 queries run each time. Using GROUP_CONCAT you can get the same info, but with only one query:

Select Code
1
2
3
4
5
6
7
$query = "SELECT *,
(SELECT GROUP_CONCAT(tag) FROM tags WHERE tags.PID = posts.PID) AS lists
FROM posts";
$result = mysql_query($query);
while ($POST = mysql_fetch_array($result)) {
... code goes here ...
}

Essentially GROUP_CONCAT is running that extra query in the background, grabbing all the tags, and combining them into one string separated by commas. So you’d get all the tags without having to run additional queries.

Why care? Well for one, speed and efficiency. Which is my favorite thing. By having only one query goto the database, the database can do the heavy lifting while it’s got everything going. Each query has an overhead and bandwidth, so anytime you can combine queries is good!

ON DUPLICATE KEY UPDATE

When you are working with high traffic sites it’s important to optimize every query you can. It’s also important to realize with so much traffic multiple people could be doing the same thing so data can messy.

Say you create a new table to track how many users login to your site every day. You create a table with the fields Date and Table. I used to do it this way:

Select Code
1
2
3
4
5
6
7
8
9
$query = "SELECT * FROM `logins` WHERE `Date`='09-17-2012'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$query = "INSERT INTO `logins` SET `Date`='09-17-2012', `Logins`=1";
$result = mysql_query($query);
} else {
$query = "UPDATE `logins` SET `Logins`=`Logins`+1 WHERE `Date`='09-17-2012'";
$result = mysql_query($query);
}

The problem is two fold: There are two queries for one task, and if you have a thousand people hitting that page at the same time there can be multiple inserts into the logins table.

Yes, you can make sure MySQL doesn’t allow multiple inserts, but it’ll result in an error and those logins won’t be counted.

What I found was a new way to do the same thing.. In one query, like so:

Select Code
1
2
$query = "INSERT INTO `logins` SET `Date`='09-17-2012', `Logins`=1 ON DUPLICATE KEY UPDATE `Logins`=`Logins`+1";
$result = mysql_query($query);

That’s it.. ON DUPLICATE KEY UPDATE tells MySQL to update the table if the key is a duplicate. Here the table structure would have the Key being “Date” and set to unique.

Pinterest Style Columns with CSS

This has always been one of my pet peeves with CSS and using Divs. I HATE having columns with different heights showing all weird floating every which way. Justin made a really cool looking div for the Harvest Party Winners List but when the different winners sections had different heights it looked terrible. I found this solution so I wanted to share it with the world:

Notice the second green div is a different height than the rest. Yet it’s still floating in there really nice? That’s the effect I’ve always thought floating should do but doesn’t do. So here you go.

Step 1: the container. This is the div that all those items are inside:

-moz-column-count: 3;
-moz-column-gap: 10px;
-moz-column-fill: auto;
-webkit-column-count: 3;
-webkit-column-gap: 10px;
-webkit-column-fill: auto;
column-count: 3;
column-gap: 10px;
column-fill: auto;

Notice that you specify the columns there? Suweeet! That simple. Just remember to make sure the container div width can fit 3 of those columns.

Step 2: the items. In the example the blue and green divs:

-moz-column-break-inside: avoid;
-webkit-column-break-inside: avoid;
column-break-inside: avoid;
display: inline-block;
width: 300px;

This fun stuff ensures that you don’t have the items going to the next column. If it were all text then it’d probably look better without that, but since we have chunks of divs and tables it’d not really work well. Don’t forget to put the width in there.

Step 3: marvel at the creation that should have been! Yeah, I got really excited. So excited that I had to write this blog post because these are one of those tricks that I don’t want to forget and if I don’t put it somewhere on my blog I’ll forget where or how I found it to do it again ;-)

How To Backup MySQL to Amazon S3

There are all sorts of ways to backup MySQL to Amazon S3 floating around the interent, but the only problem is that many of them require all sorts of things. I wanted to have my MySQL database on my WHM/cPanel server backed up automatically to Amazon S3, in the easiest way possible. Here’s what I did:

Get Amazon S3

It’s dead cheap and way easy to signup. There is a button to the right of this page. Once you have it all ready to go, hover over the “Your Web Services Account” and click the “AWS Access Identifiers” link. This is where you can get the secret code and access key needed later on.

Install Net::Amazon::S3

This you may or may not have to do, but it’s way easy with WHM. Just login, on the left menu find “Install a Perl Module”. Then enter Net::Amazon::S3 and click search. It found 3 items for me, just click on the one that says “Net::Amazon::S3”. Hard wasn’t it?

Install BackupManager

Note that I’m using the Devel 0.7.9 version because it supports S3. The commands below were via SSH logged in as root.

  1. # wget http://www.backup-manager.org/download/backup-manager-0.7.9.tar.gz
  2. # gzip -d backup-manager-0.7.9.tar.gz
  3. # tar -xvf backup-manager-0.7.9.tar
  4. # cd backup-manager-0.7.9
  5. # make install
  6. # cp /usr/share/backup-manager/backup-manager.conf.tpl /etc/backup-manager.conf

Configure BackupManager

There is a whole bunch of configuration options available, but I’ll only go through the options to *only* backup a MySQL database and *only* send it to Amazon S3. This should be enough to get you started.

  1. # pico -w /etc/backup-manager.conf
  2. Use CTRL + W to find the text below, and change accordingly. Bold text is what you want to change to your specific settings.
  • export BM_ARCHIVE_METHOD=”mysql”
  • export BM_MYSQL_DATABASES=”dbname1 dbname2 dbname3
  • export BM_MYSQL_ADMINLOGIN=”mysqlusername
  • export BM_MYSQL_ADMINPASS=”mysqlpassword
  • export BM_UPLOAD_METHOD=”s3″
  • export BM_UPLOAD_S3_DESTINATION=”s3bucketname
  • export BM_UPLOAD_S3_ACCESS_KEY=”s3accesskey
  • export BM_UPLOAD_S3_SECRET_KEY=”s3secretkey
  1. Press CTRL + X to close and “Y” to save

Sneaky Secret

This will only work if you have *all* InnoDB tables. If you do, and you want backups to not stall your server, let’s get your hands dirty:

  1. # pico /usr/share/backup-manager/backup-methods.sh
  2. CTRL+W (search for –opt)
  3. Replace “–opt” with “–opt –single-transaction”
  4. CTRL+X to close and “Y” to save

This will turn on snapshot backups. InnoDB is able to take a snapshot of the entire database and dump it without locking the tables. This is the difference between your server database holding up the entire server and things running as if no backup is running. Some day I’ll contact the backup-manager people to have them put it in..

Run Configure BackupManager

The first time you run it, you want to do it while you are there with verbose turned on. This way if any errors happen, you’ll know right away. To run it simply type:

  1. # /usr/sbin/backup-manager –verbose

Setup the Cron Job

If that runs without any errors, then the next step is to setup your cron jobs. Type the following, substituting the bold 45 for the minute you want it to run, and the bold 1 for the hour you want it to run. Remove –verbose if you don’t want to get emails with all the details.

  1. # crontab -e
  2. press o
  3. type 45 1 * * * /usr/sbin/backup-manager –verbose
  4. press esc
  5. type ZZ to save or :q! to quit if you make a mistake
  6. # crontab -l

The last command should list all your cron jobs. If it looks good, your golden. Just don’t forget that the quotes are not part of the command. You are typing what is inside the quotes.

Accessing your Files with Firefox

Well this is the kicker..¬† Accessing the files with S3 Firefox Organizer is way easy. It’s a plugin that sits in your bottom status bar of Firefox and you can just click it anytime and see your files. You can easily use it to synchronize folders on your computer too (so you can easily download those backups anytime!).

How To: Create a Blog with DreamHost

I use DreamHost to host my blog. It is incredibly easy to setup a WordPress blog, and it is incredibly easy to upgrade them too. For this tutorial, you’ll need an account with them. Use the coupon code TIMLINDEN to get $50 off a year!

Step 1: Login to the Web Panel, Goto Goodies then One-Click Installs.

Step 2: Enter the Options:

What Software..? WordPress

Install To? Select your domain. If you want it in a folder, enter the folder name. Leave it blank if you only want a blog on the domain. Note that it won’t install if the folder you enter has things in it.

Select Database? New

New Database Name? Make one up!

Existing Hostname? Create new

Create a New Hostname? Make something up, and select your domain. Most common is to use mysql but you don’t have to. Just make sure it’s not www, or a subdomain you’ll want to use later.

First User? Create new

New Username? Make one up! Preferably something hard to guess/never used before.

New Password? Make one up! Again, something hard to guess, never used before!

Step 3: Click install it for me!

Now within 10 minutes it’ll be installed, and you’ll get futher instructions via email. Basically that’ll be a link to your website saying to go create an admin username.

Upgrading WordPress: Simple. Go back to the One-Click install page. You’ll see your wordpress install listed. On the right under Actions it should say “Already vX.Y.Z” – If it doesn’t, click the link to upgrade it. It’ll do it within 10 minutes.

Note on Editing Templates: When you upgrade, it will overwrite the installed templates. If you want to tweak the template to your liking, I just rename the template folder. Then it leaves it alone!