Chris Hope's LAMP Blog - The Electric Toolbox

Linux Apache MySQL and PHP articles by Chris Hope

This is Chris Hope's blog for Linux, Apache, MySQL and PHP (known as LAMP) and Javascript/jQuery. I started this website several years ago with articles about web programming, Linux and Windows tips and tricks, howtos etc.

The ten most recent articles can be found below in their entirety. Navigating the sections in the right navigation (under Categories) will bring up all the other posts, and you can also use the search box at the top of the page to find what you might be looking for.

Setting a column as a UUID/GIUD in MySQL - replication safe

Posted December 2nd, 2015 in MySql

I recently looked at how to default a column as a UUID/GUID in MySQL using a trigger, but it's not replication safe when using statement based replication. This post looks at an alternative to using triggers by using a variable instead.

Example table

Let's use the same example table as in the previous post. This is a very basic example table; replace 'tablename' with your actual tablename and add appropriate fields, primary key, change the uuid fieldname, etc.

CREATE TABLE `tablename` (
    `uuid` char(36) NULL,
    `reference` varchar(100) NOT NULL

Using a trigger is not statement based replication safe

As noted in the previous post, if you are using statement based replication between a master & slave (or master & master), the UUID will not be replicated across to the slave server(s) and different UUID(s) will be created on the slave(s).

Why not just use the UUID() function directly in the insert/update query?

You could run this query, for example:

INSERT INTO tablename (uuid, name) VALUES (uuid(), 'example');

but again, it's not statement based replication safe and the end result will most likely be different UUID values on the master and slave(s).

Set a variable instead

If you set a variable and use that in the insert or update query, then it does appear to be statement based replication safe, which I have tested myself using master-master replication with MySQL 5.5.

So, do this:

SET @uuid = UUID();
INSERT INTO tablename (uuid, name) VALUES (@uuid, 'example');

Now both the master and slave server(s) will have the same uuid value.

Testing and warnings

These are all the tests I ran:

mysql> insert into tablename (name) values ('automatically set by trigger');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into tablename (uuid, name) values (uuid(), 'set using the uuid() function');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> set @uuid=UUID();
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tablename (uuid, name) values (@uuid, 'set using set @uuid variable');
Query OK, 1 row affected, 1 warning (0.01 sec)

Using statement based replication, all warnings were the same: "Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave."

However, the last example worked fine, despite this warning:

On the master server:

mysql> select * from tablename;
| uuid                                 | name                          |
| 0a118942-97b2-11e5-b6ee-f23c916e6be0 | automatically set by trigger  |
| 46b800a4-97b2-11e5-b6ee-f23c916e6be0 | set using the uuid() function |
| 711d2c09-97b2-11e5-b6ee-f23c916e6be0 | set using set @uuid variable  |
3 rows in set (0.00 sec)

On the slave server:

mysql> select * from tablename;
| uuid                                 | name                          |
| 0a151316-97b2-11e5-b867-f23c916e6b8f | automatically set by trigger  |
| 46bba1aa-97b2-11e5-b867-f23c916e6b8f | set using the uuid() function |
| 711d2c09-97b2-11e5-b6ee-f23c916e6be0 | set using set @uuid variable  |
3 rows in set (0.00 sec)

How to show a MySQL warning that just happened

Posted December 1st, 2015 in MySql

You've just run a query in MySQL and it tells you "Query OK, 1 row affected, 1 warning (0.01 sec)" but not what the warning is, so how do you know what the warning was for the query that was just run?

Use show warnings

Directly after you see the warning result, run this:

show warnings;

And you'll get something like this:

| Level | Code | Message              |
| Note  | 1592 | ... message here ... |
1 row in set (0.00 sec)

When you run another query after this (e.g. another insert, update or select), it will clear the warning buffer and running show warnings again will not show anything if your subsequent query ran fine.

How to default a column as a UUID/GUID in MySQL

Posted November 30th, 2015 in MySql

MySQL does not yet support setting a column's default value using a function (at least not yet as of version 5.6) but you can use a trigger instead. This post shows how to set a column to a UUID/GUID by default in MySQL using a trigger.

A warning about replication before we start

If you are using statement based replication between a master & slave (or master & master), the examples on this page will not replicate the UUID across to the slave server(s) and different UUID(s) will be created on the slave(s). I cover another method which is statement based replication safe here.

Example table

This is a very basic example table; replace 'tablename' with your actual tablename and add appropriate fields, primary key, change the uuid fieldname, etc.

CREATE TABLE `tablename` (
    `uuid` char(36) NULL,
    `reference` varchar(100) NOT NULL

The trigger

The following trigger will set the uuid field to a UUID if it hasn't already been set by your insert query whenever a new record is inserted:

CREATE TRIGGER before_insert_tablename
  IF new.uuid IS NULL THEN
    SET new.uuid = uuid();

Note that the SET and END IF lines need to be terminated with ; otherwise you'll get a SQL error. However, these would define the end of the query, hence the DELIMITER ;; statement at the very start of the trigger creation and then the ;; at the end to finish it.

Remember to reset you delimiter back to ; if you are running this from the MySQL CLI:


Example insert and resulting data

We'll insert a value into the reference column only, and the trigger will automatically set the uuid field to a UUID:

INSERT INTO tablename (reference) VALUES ('example');

Now we'll select the data to see the automatically populated UUID:

SELECT * FROM tablename;

And the resulting data:

| uuid                                 | reference |
| 79c9e1ac-96f1-11e5-85a6-000c29f1f6c4 | example   |

This method can be used for any functions you want to automatically populate a column with in MySQL, by using triggers. Note that as mentioned at the start, this won't work as expected when using statement based replication, because the UUID generated will be different on each server.

Check the Advantages and Disadvantages of Statement-Based and Row-Based Replication section of the MySQL manual for more information about the functions which won't be replicated correctly, and I cover another method which is statement based replication safe here.

Enable & disable WordPress plugins from the command line

Posted November 27th, 2015 in WordPress

Sometimes installing or upgrading a WordPress plugin breaks both the frontend of your website and the WordPress admin too, making it impossible to disable the plugin with the user interface. If you have command line access, WP-CLI to the rescue!

Downland and install WP-CLI

Go to to download and install the command line tool for managing WordPress.

List plugins

To list the WordPress plugins you have available, both active and inactive, run this:

wp plugin list

You'll get something like this:

| name                            | status   | update    | version |
| akismet                         | active   | none      | 3.1.5   |
| wp-conditional-captcha          | active   | none      | 3.7.1   |
| feedburner_feedsmith_plugin_2.3 | active   | none      | 2.3.1   |
| gd-star-rating                  | inactive | none      | 1.9.17  |
| outbound-link-manager           | active   | none      | 1.11    |
| sexybookmarks                   | active   | available | |
| tinymce-advanced                | active   | none      | 4.2.5   |
| wptouch-pro                     | active   | none      | 2.7     |
| wordpress-seo                   | active   | available | 2.3.5   |

Deactivate a plugin

Let's say you wanted to deactivate the wp-conditional-captcha plugin:

wp plugin deactivate wp-conditional-captcha

And the output:

Success: Plugin 'wp-conditional-captcha' deactivated.

To deactivate other plugins, replace wp-conditional-captcha in the command above with the plugin's name.

Activate a plugin

And if you wanted to enable it again:

wp plugin activate wp-conditional-captcha

And the output:

Success: Plugin 'wp-conditional-captcha' activated.

Doing it directly in the database

It's also possible to deactivate plugins directly in the database if you don't have command line access, which I'll cover in a future post.

Update domains & paths in WordPress with WP-CLI

Posted November 26th, 2015 in WordPress

If you've ever had to take a copy of a live WordPress install and use it on a development/test server, you'll know it can be difficult updating all the domains and paths that are riddled throughout the database. The WP-CLI command line tool helps to easily solve this problem.

Downland and install the tool

Go to to download and install the tool.

Updating domains & paths

Say you've copied a WordPress database and downloaded all the installation files, and need to change the domain from to You can run this command to easily accomplish this:

wp search-replace --recurse-objects --precise

This will give you output like this:

| Table            | Column                | Replacements | Type |
| wp_commentmeta   | meta_key              | 0            | PHP  |
| wp_commentmeta   | meta_value            | 5            | PHP  |
| wp_comments      | comment_author        | 0            | PHP  |
| wp_comments      | comment_author_email  | 0            | PHP  |
| wp_comments      | comment_author_url    | 0            | PHP  |
| wp_comments      | comment_author_IP     | 0            | PHP  |
| wp_comments      | comment_content       | 1            | PHP  |
| wp_comments      | comment_approved      | 0            | PHP  |
| wp_comments      | comment_agent         | 0            | PHP  |
| wp_comments      | comment_type          | 0            | PHP  |
| wp_links         | link_url              | 0            | PHP  |
| wp_links         | link_name             | 0            | PHP  |
| wp_links         | link_image            | 0            | PHP  |
| wp_links         | link_target           | 0            | PHP  |
| wp_links         | link_description      | 0            | PHP  |
| wp_links         | link_visible          | 0            | PHP  |
| wp_links         | link_rel              | 0            | PHP  |
| wp_links         | link_notes            | 0            | PHP  |
| wp_links         | link_rss              | 0            | PHP  |
| wp_options       | option_name           | 0            | PHP  |
| wp_options       | option_value          | 14           | PHP  |
| wp_options       | autoload              | 0            | PHP  |
| wp_postmeta      | meta_key              | 0            | PHP  |
| wp_postmeta      | meta_value            | 129          | PHP  |
| wp_posts         | post_content          | 2349         | PHP  |
| wp_posts         | post_title            | 0            | PHP  |
| wp_posts         | post_excerpt          | 0            | PHP  |
| wp_posts         | post_status           | 0            | PHP  |
| wp_posts         | comment_status        | 0            | PHP  |
| wp_posts         | ping_status           | 0            | PHP  |
| wp_posts         | post_password         | 0            | PHP  |
| wp_posts         | post_name             | 0            | PHP  |
| wp_posts         | to_ping               | 0            | PHP  |
| wp_posts         | pinged                | 0            | PHP  |
| wp_posts         | post_content_filtered | 0            | PHP  |
| wp_posts         | guid                  | 4106         | PHP  |
| wp_posts         | post_type             | 0            | PHP  |
| wp_posts         | post_mime_type        | 0            | PHP  |
| wp_term_taxonomy | taxonomy              | 0            | PHP  |
| wp_term_taxonomy | description           | 0            | PHP  |
| wp_terms         | name                  | 0            | PHP  |
| wp_terms         | slug                  | 0            | PHP  |
| wp_usermeta      | meta_key              | 0            | PHP  |
| wp_usermeta      | meta_value            | 0            | PHP  |
| wp_users         | user_login            | 0            | PHP  |
| wp_users         | user_nicename         | 0            | PHP  |
| wp_users         | user_email            | 0            | PHP  |
| wp_users         | user_url              | 0            | PHP  |
| wp_users         | user_activation_key   | 0            | PHP  |
| wp_users         | display_name          | 0            | PHP  |
Success: Made 6604 replacements.

And let's say the path you have WordPress installed on the production server is /var/www/wordpress but on your local test machine it's at /Users/chris/Websites/wordpress, you can again use the tool to update all paths in the database:

wp search-replace /var/www/wordpress /Users/chris/Websites/wordpress --recurse-objects --precise

This one will probably only need to update one configuration option in the {prefix}options table, but depending what plugins etc you have installed, there may be other places that can catch you out.

Big databases

I need to do this periodically on a database which has over 500,000 records in the posts table and 1.7 million records in post meta (most of these "posts" are bpress topics & replies). This is by no means a particularly large database, but it takes a long time to run the domain change update, due to the guid field being constructed from the domain name. e.g.

You could possibly change the guid to some sort of guid that doesn't contain the domain name, although I don't know what other effects this would have on WordPress, or you can do this little solution instead.

Before running the wp cli command, run this SQL query first, obviously substituting the domains for the ones you need to replace:

UPDATE wp_posts SET guid = REPLACE(guid, ', '');

And then run the wp-cli command as usual. Much faster!

Browser responses to the X-Frame-Options response header

Posted November 25th, 2015 in Apache and Nginx Web Server

I discovered the X-Frame-Options response header this morning; it can be used to prevent your website being rendered within a <frame>, <iframe> or <object>. There are different options to either deny access to all websites, allow from the same origin only, and to allow from a specific origin.

Documentation at Mozilla

I won't document how it's used here, because it's already done here at Mozilla, which covers how it works and how to configure the header with Apache, nginx, IIS and HAProxy.

What I am going to look at is how the browser responds to the blocked content in various browsers.

Browser response - Firefox

Firefox loads about:blank into the frame instead of rendering anything, so the frame/object will quite simply be blank. There is no error message at all, unless you happen to dive into the developer console where you will see the message "Load denied by X-Frame-Options: does not permit cross-origin framing."

This is accurate as of the current version of Firefox at the time of writing this post, version 42 on OSX Yosemite. According to the Mozilla documentation page: "At some point, an error message of some kind will be displayed in the frame instead."

Browser response - Chrome & Safari

A blank page as per Firefox. Again, if you look in the console you'll see an error message which is currently the same for both browsers: "Refused to display '' in a frame because it set 'X-Frame-Options' to 'SAMEORIGIN'."

Versions tested: Chrome 46 & Safari 9 on OSX Yosemite.

Browser response - IE8 to IE11

Internet Explorer is (unusually!) much better than the other browsers, and displays an error message where the framed content would normally have been rendered, with a link to open the framed content into a new window:

"This content cannot be displayed in a frame. To help protect the security of the information you enter into this website, the publisher of this content does not allow it to be displayed in a frame. What you can try: open this content in a new window."

Browser response - Edge

And Microsoft's Edge browser works in the same way as the older Internet Explorer versions, but with slightly re-worded content:

"This content can't be shown in a frame. There is supposed to be some content here, but the publisher doesn't allow it to be displayed in a frame. This is to help protect the security of any information you might enter into this site. Try this: open this in a new window"


I've always busted my sites out of frames using Javascript, but the X-Frame-Options response header gives the ability to prevent your site being framed without the need for scripting. The downside is that the majority of browsers don't show any sort of obvious error which then makes it easy for the user to click through to the actual website. A shame that in this instance the other browsers aren't more like Internet Explorer / Edge.

How to generate a SAN CSR from the command line

Posted November 24th, 2015 in Linux/Unix/BSD

SAN Certificates (Subject Alternative Names Certificates) allow you to have multiple domain names on a secure single certificate, which means you can serve up multiple secured domains on a single IP address without using SNI (Server Name Indication). This post shows how to generate the CSR (Certificate Signing Request) for a SAN Certificate.

The openssl command to generate the SAN certificate

I found plenty of tutorials about how to generate SAN certificates, but they were all overly complicated and some required creating a copy of the /etc/ssl/openssl.cnf, which then made it difficult/not possible to make some of the optional inputs optional. All I wanted was a single command that I could use on the command line to generate the CSR file.

After some messing around, I managed to come up with this:

openssl req -new -newkey rsa:2048 -nodes -keyout filename.key -out filename.csr -reqexts SAN -config <(cat /etc/ssl/openssl.cnf <(printf "[SAN]\,"))

Replace "filename.key" & "filename.csr" with the actual filenames you want to use for the key and CSR and replace & with the additional domain names you want on the certificate.

If you only need one additional domain, then remove the , part, and if you need more than two additional domains, add additional ",DNS:domain" parts to the end.

You do not need to specify the primary domain name for the certificate with this command; you'll specify that in the normal CSR creation dialog when it asks for the common name. After hitting <enter> you'll get the normal CSR generation dialog.

The command above worked for me on Debian 7 and Debian 8.

Use a different rsa identity file when using ssh

Posted November 23rd, 2015 in Linux/Unix/BSD

You may need to use different identity files when logging into ssh/sftp servers, so this post shows how to specify a different one to use from the command line, and then always using that key by default in the config file.

ssh -i command line flag

Normally your default identity file will be at ~/.ssh/id_rsa, but let's say we've created another rsa file at ~/.ssh/id_rsa2 and need to use that when connecting to

The -i flag allows you to use a different keyfile:

ssh -i ~/.ssh/id_rsa2

Using the config file

Now you don't want to have to specify that -i flag every time you connect, and if you are using e.g. git to pull updates over ssh, you won't be able to specify it anyway. So a much better way to specify the file to use is in the config file.

If you don't already have a file at ~/.ssh/config, then create it, and then add this for our example:

  IdentityFile ~/.ssh/id_rsa2

Much easier! Above I talked about how when you're doing a "git pull" you won't be able to specify the identity file, and this is when I first needed to implement this. I have an account at Bitbucket and a separate deployment key which I use for it. So this is what I added to the config file:

  IdentityFile ~/.ssh/id_rsa_bitbucket

Setting a user's supplementary groups on Linux

Posted November 22nd, 2015 in Linux/Unix/BSD

A user on Linux belongs to a primary group, which is specified in the /etc/passwd file, and can be assigned to multiple supplementary groups, which are specific in the /etc/group file. The usermod command can be used after creating to user to assign them to additional groups(s).

Replace existing supplementary groups

Running usermod with the -G flag will replace all existing supplementary groups for the user with the ones specified. To assign "chris" to the "adm" group only, do this:

usermod -G adm chris

To assign "chris" to "adm" and "sudo", do this where multiple groups are separated with commas:

usermod -G adm,sudo chris

Adding a supplementary group to existing ones

If you want to add a supplementary group and not interfere with the other ones already set, add the -a flag (append) as part of the command.

To add "chris" to the "adm" group, leaving the other group assignments as-is, do this:

usermod -a -G adm chris

Setting the groups when adding a user

It's the same flag to set the supplementary groups when adding a user, -G:

useradd -G adm,sudo chris


The flags & commands should be the same on all Debian based distros (and others too); when writing this post I was using Debian 8.1.

How to change the remote url for your local git repository

Posted November 21st, 2015 in Applications

If you've moved a git repository from one git hosting service to another, transferred from one account to another at the same service (here's how to transfer between accounts at Bitbucket) or moved your own repository servers around the place, you'll need to change the remote url for you local repository to be able to continue pushing and pulling changes, etc.

Check the existing remotes

Use this command to check what you have configured as the current remote:

git remote -v

If the git repository is at e.g. Bitbucket, the login name MyUsername and the repository coolproject, then running the above command would output something like this:

origin	git@bitbucket.MyUsername/coolproject.git (fetch)
origin (push)

Change the remote url

To change your local repository's origin to e.g. another accout within Bitbucket (YourUsername), use the set-url command like so:

git remote set-url origin

Done! You might want to run "git remote -v" again to check it's all looking good.