Here is a super simple SQL query to determine the size of all tables in a given database:

SELECT TABLE_NAME AS "Table",
	round(((data_length + index_length) / 1024 / 1024), 2) AS Size_in_MB
FROM information_schema.TABLES 
WHERE table_schema = 'my_db_name'
ORDER BY Size_in_MB DESC

This can be very useful when debugging for instance in figuring out which rogue plugin/module on your site is dumping hundreds of megs of data into your database.

Read More »

I recently came across a tutorial on sitting Redis infront of WordPress allowing for insanely fast page generation. I gave it a try and it really works, in fact I’m now using it on this very site! The best part however is the fact that the script requires absolutely no modification to your existing WordPress site save for 1 line of htaccess. Truly amazing.

Below I’ll detail my slightly modified version of Jim’s script along with some metrics.

Firstly, What is Redis and what will it do for me?

The Redis website describes Redis as

… an open source, BSD licensed, advanced key-value store. It is often referred to as a data structure server since keys can contain strings, hashes, lists, sets and sorted sets.

What does this mean? Essentially it’s Memcached but more useful. Redis stores key-value pairs in memory and spits them out when requested. Unlike Memcached it has built in persistence but what’s most important to us is that it’s fast – very fast.

We’ll be using Redis to speed up our site by loading cached pages from it directly without even booting up WordPress. This will save a large amount of page generation time and get out site infront of our users’ eyeballs faster.

Exactly how much faster are we talking?

In my very unscientific tests, loading www.flynsarmy.com a bunch of times resulted in the following:

As you can see from the table above this equates to a 20x to 50x speed increase and that was WITH W3 Total Cache installed! Results of course may vary but I think you get the picture.

Read More »

Here’s a very quick set of SQL snippets for updating a WPMU domain. This might be useful when building a site on a development domain before moving to a production one later on.

Firstly update the wp_blogs table:

1

UPDATE wp_blogs SET domain='newdomain.com';

There will be a bunch of wp_options and wp_posts tables – one per site. Find all the tables:

1
2

SHOW TABLES LIKE "%_options";
SHOW TABLES LIKE "%_posts";

and for each table, perform the following query:

1
2
3
4

#_options tables
UPDATE <tablename> SET option_value=REPLACE(option_value, 'http://olddomain.com', 'http://newdomain.com');
#_posts tables
UPDATE <tablename> SET post_content=REPLACE(post_content, 'http://olddomain.com', 'http://newdomain.com');

This was enough to get the sites working for me. Additional tweaks are probably required afterwards – if you find anything let me know in the comments below.

Read More »

Tonight I had to move my WPMU install from my local development machine to the live server – this meant a change in installation path which is always a hassle with WordPress. Below I’ll detail my issues and the corresponding fixes in the hopes it will make life easier for others experiencing the same problems.

Redirection to wp-signup.php

First thing I noticed was that when loading the site on a live domain, I’d get instantly redirected to mydomain.com/wp-signup.php?new=mydomain.com. With a bit of Googling I came across this forum thread which recommended adding the following to my wp-config.php:

1
2

define( 'NOBLOGREDIRECT', '' );
define( 'SUBDOMAIN_INSTALL', false );

Don’t do this! It will get the site closer to working order but it’s not going to help you in the long run – especially when you need the other domains working too. Instead here’s what you should be doing:

1
2
3
4
5
6
7

#Dump your DB
mysqldump - u<username> -p<password> -h<host> <dbname> > test.sql
 
#Update folder path to that of the new servers
sed -i "s/path/to/install/new/path/to/install/g" test.sql 
#If you installed into localhost/foo/bar/mysite, change that to your live servers domain
sed -i "s/url/path/to/site/www.domain.com/g" test.sql

This is pretty standard behavior for moving WordPress sites, however if you load the above dump up on your live domain you’ll probably be greeted with the dreaded Error establishing a database connection.

Error establishing a database connection

Heading to www.domain.com/wp-admin will shed a little more light on the situation – you need to update your wp_blogs table for the main site. WPMU is currently using your development servers URL from this table and ignoring what’s in wp-config.php. To play things safe we’ll update any occurrances of our test servers domain in test.sql:

1
2

#Update your live servers subdomain in wp_blogs to your live servers domain
sed -i  "s/yoursite.localhost.com/www.yourdomain.com/g" test.sql

This should do the trick. Load that bad boy into your live server and you should be good to go!

Read More »

MySQL’s built in FROM_UNIXTIME() function only handles positive numbers however we can still convert negative integers to dates using its handy DATE_ADD() function like so:

1

SELECT DATE_ADD(FROM_UNIXTIME(0), INTERVAL -13391999 SECOND)

which gives:

1

1969-07-30 10:00:01

Note that this also works with positive numbers, so if your database contains a mixture of both it’s still safe to use.

Credit to user fat_kid for his tutorial here.

Read More »

Without any adieu what-so-ever, below is a MySQL implementation of PHP’s ucfirst function which capitalizes the first letter of each word in a string.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

DELIMITER $$
CREATE FUNCTION CAP_FIRST (INPUT VARCHAR(255))
 
RETURNS VARCHAR(255)
 
DETERMINISTIC
 
BEGIN
    DECLARE len INT;
    DECLARE i INT;
 
    SET len   = CHAR_LENGTH(INPUT);
    SET INPUT = LOWER(INPUT);
    SET i = 0;
 
    WHILE (i < len) DO
        IF (MID(INPUT,i,1) = ' ' OR i = 0) THEN
            IF (i < len) THEN
                SET INPUT = CONCAT(
                    LEFT(INPUT,i),
                    UPPER(MID(INPUT,i + 1,1)),
                    RIGHT(INPUT,len - i - 1)
                );
            END IF;
        END IF;
        SET i = i + 1;
    END WHILE;
 
    RETURN INPUT;
END$$
DELIMITER ;

Use it like so:

1

SELECT CAP_FIRST('my string of words');

and you should get:

My String Of Words

All credit and many thanks for this function to Joezack.

Read More »

I’ve been using my Cloud Database Backup script for a few months now for weekly scheduled backups of my MySQL databases to Google Docs. Everything has been going smoothly, however I’m starting to run low on quota. For this reason I decided to look into splitting the SQL dumps into chunks small enough to be convertible and doing an upload-convert rather than a zip upload which will result in literally unlimited, quote free database backups as frequently as I like! The focus of this post though is the actual splitting script which splits a given MySQL dump into chunks of x characters.

As always, download it here.

Read More »

Earlier today I completed my first project in Django and it came time to do some database optimization. I wanted to get a list of SQL queries executed for each page and a bit of Googling let me to this script on DjangoSnippets. It did everything I needed it to do, however I noticed it interfered with dynamically generated binary file outputs (such as the images made with django-simple-captcha). For this I needed to check if the output was in binary, and if so just return it without attempting to print the SQL log. I found what I was looking for here and after combining the two had the perfect SQL logger! Below is my finished code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

from django.db import connection
from django.template import Template, Context
import string 
#http://djangosnippets.org/snippets/161/class SQLLogMiddleware:
    def process_response ( self, request, response ):
        #Don't print SQL queries for binary outputs!        if istext(response.content) == 0:            return response 
        time = 0.0
        for q in connection.queries:
            time += float(q['time'])
 
        t = Template('''
            <p><em>Total query count:</em> {{ count }}<br/>
            <em>Total execution time:</em> {{ time }}</p>
            <ul class="sqllog">
                {% for sql in sqllog %}
                    <li>{{ sql.time }}: {{ sql.sql }}</li>
                {% endfor %}
            </ul>
        ''')
 
        response.content = "%s%s" % ( response.content, t.render(Context({'sqllog':connection.queries,'count':len(connection.queries),'time':time})))
        return response
 
#http://code.activestate.com/recipes/173220-test-if-a-file-or-string-is-text-or-binary/def istext(s):
    if "" in s:
        return 0
 
    if not s:  # Empty files are considered text
        return 1
 
    # Get the non-text characters (maps a character to itself then
    # use the 'remove' option to get rid of the text characters.)
    t = s.translate(string.maketrans("", ""), "".join(map(chr, range(32, 127)) + list("nrtb"))) 
    # If more than 30% non-text characters, then
    # this is considered a binary file
    if float(len(t))/len(s) >= 0.30:        return 0

To get this working on your site just add it to your MIDDLEWARE_CLASSES in settings.py and make DEBUG is set to True.

Read More »

Update 2011-10-12: More storage options! Google Storage, Local HDD, FTP, experimental unlimited Google Docs conversion

Have you ever wanted to ensure that even if your server dies in the most spectacular of ways, your DB is safe? Meet Cloud Database Backup. Cloud Database Backup is a little script I’ve written that allows you to quickly and easily back up your MySQL DBs to the cloud.

Currently you can back your database up to:

• Amazon S3
• FTP
• Google Docs
• Google Storage
• Local HDD

Download the script here.

Requirements:

• MySQL with access to INFORMATION_SCHEMA database
• php, php5-cli (for S3 backups)
• python2, python-gdata (for Google Docs backups)

This is an updated version of my old Automatic MySQL Database Backup script that adds support for Google Docs (which now allows uploads of any file type for non-enterprise users), the recently opened Google Storage, as well as local HDD and FTP servers. It does a MySQL dump of each DB separately, zips them all and uploads the zip to your cloud service of choice.

I’ve also added in an experimental ‘db chunker’ which uses this script to split SQL dumps into convertible sizes for Google Docs, allowing you to upload unlimited backups for free; owever due to a reproducible bug in Google Docs, I wouldn’t recommend its use (and it’s disabled by default).

To get started simply update your details in the appropriate places at the top of backup.sh and type ./backup.sh. Presto!

I’ve also included a crontab example to allow automation of backups.

Read More »

NOTE: If you’re looking for the Doctrine 2 modules for K3 see this post.

Download the module here.

I’ve previously written a module for Kohana 3 for Doctrine, as well as drivers for Kohana’s Auth and Session modules, however I’ve finally had the time to merge the three and fix up some longstanding issues present with them; namely the folder structure and lack of PDO support. To make my life a little easier I’ve moved the project to Google Code too.

You can find the project here. It’s got Doctrine 1.23 already in there, you should just be able to drop it in and add it to your bootstrap file. It also includes sample schema and data fixtures for the kohana auth and session modules – they can be found in /models/fixtures.

Good luck!

Read More »