MySQL/ SQL and why I was a bad developer for not embracing it sooner

I can’t believe how little I understood MySQL. I’ve been using it for years, about 7 or 8, and even now I still only use a small fraction of its power. Let me be the angel sitting on your shoulder right now and tell you that you will be a better, more effective, more one-with-all developer if you spend time learning new skills in MySQL regularly. DBAs don’t sound like glamorous jobs but, my word, sometimes you’ve just got to dirtnap.

No, I don’t know what that means either…

Setting up WordPress on a CentOS server


This is a quick guide on setting up WordPress on a Linux server, I’m using CentOS and I’m running virtual hosts, this is fairly typical so you should find similar results. I’m running CentOS 5.x and I’m setting up WordPress.


Firstly, you’ll need to get your domain’s DNS settings: contact your domain provider for that, get them to point to the IP address of your server.

Setup virtualhosts

Secondly, setup virtualhosts. For that – you’ll probably need to edit httpd.conf file in /etc/httpd/httpd.conf but don’t just go changing that just yet – firstly we need to make a backup. So do this:

sudo cp httpd.conf httpd.conf.backup

Now edit the original `sudo vi httpd.conf`. Add your virtual host information (use your IP, not

DocumentRoot /var/www/html/yourfolder
ErrorLog logs/yourdomain-error_log
CustomLog logs/yourdomain-access_log common

Setup the site’s web root directory

Then create a folder in /var/www/html called yourfolder:

cd /var/www/html

then make a folder for your site:

sudo mkdir /yourfolder

Setup your file/dir ownership:

sudo chown -R yourname:apache yourfolder

And setup permissions:

sudo chmod -R 0750 yourfolder

Change directory into your folder:

cd yourfolder

Download WordPress files

Retrieve the latest version:


(If you have trouble with that then try this post, I had to run in circles updating wget).

Unpack that:

tar zxf latest.tar.gz

go into the directory you’ve just unpacked and copy it back up into your siteroot directory:
cd wordpress
cp -rpf * ../
cd ../

delete the other stuff we don’t need anymore:

rm -rf wordpress/
rm -f latest.tar.gz

Now restart your apache daemon:

sudo /etc/init.d/httpd restart

Go to your URL (assuming your DNS settings have propagated, if not give it up to 72 hours or contact your domain provider) and you should see the WordPress setup page showing you’re ready for the WordPress installation proper. For that, it’s probably best if you just do here:

Also, once you’re happy that your changes have taken, remove httpd.conf.backup

Fixing MySQL connection dropping

Every now and then I’ll come to write a post on my personal blog and I’ll find that the MySQL connection has broken. MySQL is essentially down and there’ll be no explanation on the webpage which didn’t load, there’ll be no HTTP status messages, Googlebot won’t be able to find robots.txt (as it sometimes emails me to say, rather alarmingly) and down I go in search engine rankings :( . I’ve finally got some spare time early on a Saturday morning in March (warmer than it has been, if you care…!) so I’m trying to work through this.

My first stop was a post on because, well, I wanted the problem to stop. A.S.A.P! As is the way though, a kind soul pointed out that actually, there’s a problem and it ought to be fixed. So my second stop was the mysql log – /var/log/mysql.log. After downloading that and looking through it (still feels more natural to me) I noticed that there were a ridiculous amount of the same warnings:

140524  1:59:10 [Warning] IP address 'x.x.x.x' could not be resolved: Temporary failure in name resolution
140526  3:28:09 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140526 21:38:09 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140527  4:47:42 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140529 14:24:36 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140531  8:33:25 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140531 16:19:40 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140531 21:52:34 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known
140601  5:10:03 [Warning] IP address 'x.x.x.x' could not be resolved: Temporary failure in name resolution
140601 10:44:19 [Warning] IP address 'x.x.x.x' could not be resolved: Name or service not known

… etc. etc.

That issue lead me to this post on ServerFault and on to Is DNS the Achilles Heel in your MySQL installation?

(note: before you do anything, BACKUP! you could use the command: sudo mysqldump –all-databases /home/YOUR_HOME_DIR/alldb.sql for example. If you get a warning about events see this post for more info)

I ended up adding the line


to the existing options under the [mysqld] to the my.cnf/my.ini configuration file for MySQL to completely block TCP/IP connections to MySQL. I had not been aware it was available – I had assumed (incorrectly as is now clear) that such a feature would not be available as default – perhaps my MySQL server was not ‘out-of-the-box’ new? I then restarted the MySQL daemon to include this new skip-networking option:

sudo service mysqld restart

(note: which is dependent on being able to use the command sudo – if you’re not familiar with that then get searching :) you’ll use it a lot)

I checked the log file and there has not been any new TCP/IP connections being attempted:

sudo tail /var/log/mysqld.log

(note: which is dependent on being able to use sudo and your Linux directory structure. I’m using CentOS as I usually do and its usually there)

I still had a feeling that although this DNS lookup (both forwards and in reverse) didn’t seem like it was going to be a problem with TCP/IP connections disabled I didn’t want a needless process enabled so I used vi to edit the my.cnf/my.ini file:

[me@myserver /]$ sudo vi /etc/my.cnf

I added the following line under the options for [mysqld]



socket=/var/li etc....




Then again I restarted the MySQL daemon.

Then I started thinking about caching and further optimisation, I mean – since we’re logged in and everything why not? This post on howtogeek is good – follow it, come back after! You might also like to follow this guide to work out the size you might like to set the value to.


from here:


Beginners guide to setting up a staging environment/ site on Linux, Apache, CentOS with password protection

So I need to setup a staging environment. I’m doing it on CentOS using the command line. This means I have a SSH connection to my server and have sudo privileges. But before that let’s begin by settings up a DNS entry in a control panel – if you don’t have that then you need to contact your web host.

This is what we want to create: a subdomain called “thestage” which will make the URL for access “”. 300 is the TTL(time to live) and I’m not sure whether it’s right. Since it’s inaccessible to the public I’m going to experiment with that value for a little while.

thestage 300 IN CNAME

If it’s not an IP address then you need a full stop at the end.

I created a directory on this server for the staging contents. This will make it a top level directory like /var /etc /usr etc. It felt appropriate.

mkdir /thestage

I setup a vhosts (virtual hosts) conf file. This essentially means we can relay different addresses through the httpd (HTTP daemon) like Apache to different folders. This is how a lot of web hosting is conducted. So if you have access to vhosts then you can do this, if you don’t you’re probably on a hosting package that limits your ability to do things like this. Talk to your hosting provider and probably don’t continue reading the rest of this until you can do the aforementioned.

Because my httpd.conf (httpd configuration file) includes all ‘.conf’ (configuration) files in a directory of vhosts configuration files I can make a single instance specific/ bespoke version by creating a new virtual host configuration file (see below). If I didn’t/ couldn’t do this I would have to include it in the httpd.conf file which is possible but  can get messy if you have a lot of information and a lot of virtual hosts. The httpd.conf file is probably somewhere like /etc/httpd/conf/httpd.conf. (if you can’t find it do this: “cd /” … “locate httpd.conf”. Instead I put one here /etc/httpd/vhosts.d/thestage.conf where “thestage.conf” is the name of the file and vhosts.d is a directory being included because of the following line in httpd.conf:

Include vhosts.d/*.conf

I put this inside it to just test everything works first off:

<VirtualHost *:80>
    DocumentRoot /thestage

    # Other directives here


Since we’ve made a change to httpd we need to restart it

If your user has it setup – not guaranteed (sorry, look elsewhere for that) – then use this:

service httpd restart

Otherwise (or if it doesn’t work) use the file path too:

/etc/init.d/httpd restart

now go to and you should see the default Apache page.

So go to /thestage and create index.html (this is the command line way, you could just use a program like WinSCP to create it and edit it in a GUI).

touch index.html

Then open that file to add contents

sudo vi index.html

(assuming you can and do use sudo, not discussed here)

Press [i] to enter “insert” module

Write anything, e.g.

Hello, world!

Press [Esc] to enter command mode

Type (they’re capitals btw):


and press enter, this saves and quits

Refresh your browser and you should then see:

Hello, world!

So since that works we can get on with password protecting Since it’s a staging site it’s essentially still testing and we don’t want the world seeing it!

So the password advice in this Apache link didn’t work when I first tried it presumably because the path or something wasn’t set for me to use it so I had to do use the following command: locate htpasswd

# locate htpasswd

The one I wanted is /usr/bin/htpasswd

So being in the directory with the program:

cd /usr/bin

we will almost be ready to use the command from the Apache link above. I have decided to not use /etc/.htpasswd so I will make a new file in my directory


If you want to put it somewhere else which doesn’t yet exist then you will need to use mkdir with the -p argument to make the parent directories. For example:

# mkdir -p /usr/local/apache/passwd

But, still being in the /usr/bin directory with the htpasswd program (enter the command “pwd” if you’re not sure if you’re in that dir) type the following to create the new .htpasswd file and add a password to be used with yourusername (your user name) as below. If you’ve already got a .htpasswd file as in /etc/.htpasswd then you don’t need the -c argument.

htpasswd -c /usr/local/apache/passwd/.htpasswd yourusername 

Then you’ll need to enter and re-enter your password. This should work, if it doesn’t you might want to try another directory.

Then you’ll need to change the file ownership:

chown apache:apache /usr/local/apache/passwd/.htpasswd

Then create a .htaccess file in your your directory /thestage and add the following lines:

AuthType Basic
AuthUserFile /etc/httpd/.htpasswd
AuthName "Enter password"
Require valid-user

Or wherever your file is in place of “/etc/httpd/.htpasswd” Save it and restart apache (httpd) again as before.

Refresh your browser and now you should have a login form.


et voilà!

Now, depending on your needs you have the ability to setup a new database (a staging version of your live one) and a new codebase (perhaps with Git, I’ll come to that later, if I go down that road…)

Thanks :)



Recommended resources:–net-29738


Drupalgeddon :(

Man, choose the worst week to go on holiday why don’t you?!

So this has happened and it’s a pain in the bum and there’s not even any sure fire why of knowing whether or not your site/server has been compromised. Even after updating the problem is that as is described in this post on Drupal Stackexchange patching or updating Drupal core does not mean you have rid yourself of the problem. So I’ve updated to Drupal 7.32 and run Drupalgeddon but now there’s every chance that the best thing to do is rebuild the server. Man, what a pain in the arse!

Good clear guide to get started on understanding Drupal’s AJAX form functionality

This is a good article posted by Randy Fay on the form API changes from AHAH to AJAX forms in Drupal. For some reason I find the AJAX form functionality of Drupal incredibly confusing. This might be because I’ve never really had a chance to get my fingers into AJAX in any capacity, let alone for Drupal. So the underlying concepts I know, loosely and on a high level, but I’ve not had a chance to practise. So if you’re really struggling, maybe you should ditch Drupal at this point and get some AJAX stuff working.


General error: 1005 Can’t create table Drupal error when creating foreign keys

I kept getting “General error: 1005 Can’t create table …” when trying to add foreign key integrity constraints to Drupal through MySQL and db_query(). I had the schema defined through hook_schema() and I was putting the db_query() which had the foreign key constraint definitions in hook_install(), I tried them in hook_update_N() too which is all by the book and correct (either way is legit).

It turns out my school boy error was that the types did not match…

Unavoidably helpful modules and resources for Drupal module developers

Form Builder module

  • install all the modules that come with Form Builder Example
  • then for a GUI visit:
  • build the form and export the code then add it to a form building function as in drupal_get_form(…)

Schema module

  • install
  • then visit:
  • or just click “configure” on the row with the schema module on the module listing page
  • click ‘inspect’
  • from there you can export a database table that you can make the traditional way in SQL or through PHPMyAdmin or similar
  • with that export you can

Web development testing process, methodology and checklist resources

This is a collection of resources on testing procedures, processes, methodologies, checklists or just related. In my experience, when you’re out of university one of the first things that ends up being dropped is the rigour of testing. Deadlines won’t wait and unlike when you’re studying, there’s no way you want to be up until two o’clock in the morning drinking energy drinks. Let me tell you now that dropping testing is a mistake.

Keep your testing procedures in place, keep your logging process in place. Take the time to keep your Gantt chart up-to-date and all the way along keep the methodology documented and tidy. If you do not do these things you will kick yourself later. Like sick leave or christmas parties it’s simply something that must be an overhead in business that you take into account. This is the job.

Resources: (please note some of these are rather old-skool development house style and therefore slower and heavier to assimilate, should you bother to read them at all)

Also, another for the pile – setup a staging server


Adding an update to a Drupal custom module during development


This is a post on how to write an update for a custom module that you have been developing on Drupal 7. To do this there are some prerequisites. Firstly – you must have used a ‘.install’ file (MY_MODULE.install) in your custom development (and you must have added that MY_MODULE.install file to the list of files in your file). You must have then installed your module by way of the typical /admin/modules route. If you’ve not done that then you need to – you’ll reap the rewards later as Views, Entities and a whole heap of other functionality will open up to you but you must use the code that declares your schema to Drupal, inside your MY_MODULE.install file.

Updating the existing schema definition

The first step in the update process proper is to add your new definition to the hook_schema() function

 * Implements hook_schema().
function MY_MODULE_schema() {


Hook update: the hook_update_N() function

Now you will need to add that same definition to the hook_update_N() function, but the manner in which you add it is different.

Firstly write the function declaration. This will be your module name replacing the word ‘hook’ and then your update identifier replacing the capital N. This to be honest still confuses me – I think the documentation on it is extremely confusing. So when the first number didn’t work (7001) I just used 7002 instead.

function MY_MODULE_update_7002(&$sandbox) {
    $schema['your_new_table'] = array(
        'description' => 'this is your new database table created through hook_update_N',
        'fields' => array(
            'id' => array(
                'description' => 'the ID of your_new_table',
                'type' => 'serial',
                'unsigned' => TRUE,
                'not null' => TRUE,
            'text' => array(
                'description' => 'some kind of text as an example',
                'type' => 'varchar',
                'length' => '128',
                'not null' => TRUE,
    db_create_table('your_new_table', $schema['your_new_table']);

Don’t forget to clear your cache (using drush, “cd” into the Drupal folder then type “drush cache-clear”, or …/admin/config/development/performance)

If that still hasn’t worked you might need to increment your update number.

Finally, making it happen on your system

So now all we need to do is backup our database and then visit /update.php and follow the instructions!

Recommended resource: this is a really good guide to the process (scroll down to the appropriate step that you’re on)

Recommended resources

Database functions to use in your update:!database!

hook_update_N() information: Community Documentation on Writing .install files (Drupal 7.x):