Heart Internet :: wp_options table creates lots of transient records

One of my clients received an email from Heart Internet recently stating that a database on their account had exceeded limit of 500MB and had ballooned to 3,561MB. This was a bit of a shock as the database holds a WordPress install which only has 82 posts.

My initial thought was that the comments hadn't been disabled and were most likely filling up the database, but when I checked phpMyAdmin I found that everything appeared to reasonably small in table size except for `wp_options` which although only had 370 records was somehow using up 3.4GB by itself.

When I looked into the table I found the usual `transient` records at the end of the table, these look to be temporary records with a short ttl. So the space which is being used appears to be overhead, although it doesn't show as such. But rebuilding the table via `OPTIMIZE TABLE` brings down the table size to a mere 1.5MB.

As this was the second time this client got an email from Heart with this issue I put together a scheduled task which optimises the `wp_options` table each day. Hopefully this will be useful for you.

I placed the scheduled task file above the `public_html` folder, within a folder called `crons` and called the file `optimise-db.php`. You can change the location and name of the file but you'll need to update the paths used in the code and control panel.

File: ~/crons/optimise-db.php

<?php

require_once $_SERVER['HOME'] . '/public_html/wp-config.php';

// Connect to Database
//
try {
$dbh = new PDO( 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET, DB_USER, DB_PASSWORD );
$dbh->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ );
} catch( PDOException $e ) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}

// Optimise wp_options table
//
try {
print_r( $result = $dbh->query( 'OPTIMIZE TABLE `' . $table_prefix . 'options`' )->fetch() );
} catch( PDOException $e ) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}

The file will use the table prefix as set within `wp-config.php` so this should be a case of just copy and paste. You can use an include file within `public_html` if you want to test this file works.

File ~/public_html/test.php

<?php

require_once '../crons/optimise-db.php';

Next you'll need to add the `optimise-db.php` file to a scheduled task.

The command you use should be similar to the below but you'll need to update the path to the file.

/usr/bin/php56 -f /home/sites/example.org/crons/optimise-db.php

Here I'm using PHP 5.6 as I know Heart will continue to support this for sometime and something as simple as running a single MySQL query won't require PHP 7.

You can set the task to run once per week or once per day (usually at midnight). I opted for each midnight.

With this set, you can click on the `Test Command` button and you should receive the following output:

And that's it. This scheduled task will keep your transient records from filling up your database table.

If you have any questions I might be able to help, so tweet me @WilliamIsted.

How to CHMOD all directories to 644 and all files to 755 via SSH

Sometimes we need to adjust access for multiple files or folders, this can be the case if using composer as often the files created will be 0664 which might upset Apache or PHP. To correct this you can use the following commands.

This will recursively chmod all files or folders from the current directory unless you change the directory in the command; it is advised you just run this from the appropriate directory.

Directories:

find . -type d -print0 | xargs -0 chmod 0755 # For directories

Files:

find . -type f -print0 | xargs -0 chmod 0644 # For files

GET hash fragments with JavaScript

function getHashParameters() {
var prmstr = window.location.hash.substr(1);
return prmstr != null && prmstr != "" ? transformToAssocArray(prmstr) : {};
}

function transformToAssocArray( prmstr ) {
var hash = {};
var prmarr = prmstr.split("&");
for ( var i = 0; i < prmarr.length; i++) {
var tmparr = prmarr[i].split("=");
hash[tmparr[0]] = tmparr[1];
}
return hash;
}

var hash = getHashParameters();

Teaching SpamAssassin with sa-learn (works with cPanel)

Training SpamAssassin is super easy to do with `sa-learn`,  but working out the best way to get all the spam emails to it can be a bit time consuming if done manually. The below line of code tells `sa-learn` to get all emails from all spam folders for all mail accounts.

/usr/local/cpanel/3rdparty/bin/sa-learn --progress --spam /home*/*/mail/*/*/.spam/*

The following figure shows the path components.

  Use common home directory names 'home', 'home2', 'home3'.
┌─┴─┐
/home*/*/mail/*/*/.spam/* └┬┘ Include all user folders within the home directories. Mail directory, nothing special going on here. ┌─┴─┐ /home*/*/mail/*/*/.spam/* └┬┘ Wildcard to include all domain folders. Wildcard to include all user folders. ┌┴┐ /home*/*/mail/*/*/.spam/* – Wildcard to include 'cur', 'new', 'tmp' folders. └─┬─┘ Go for spam, that's what we're here for.


Slick carousel - Fix broken height / appearance within tabs

Fix for when a slick slider has broken positioning from being previously hidden.

jQuery(function($) {

$(document).on( 'click', '.tab', function() {
    $('.slick')
            .find('.news-item')
            .css({ height: '' }) // Remove the height calculated when hidden
            .find('.news-item__title')
            .css({ height: '' }); // Remove the height calculated when hidden
        $('.slick').slick('getSlick').checkResponsive(); // Trigger same internal functions as `orientationchange` event.
        $('.slick').slick('getSlick').setPosition(); // Trigger same internal functions as `orientationchange` event.
    });

});

Mount remote FTP drive on CentOS 7

Recently I needed to mount a remote backup drive via FTP in order to recover an account within WHM, I found the following package which is an FTP filesystem based in cURL and FUSE.

This worked effortlessly and is now my "goto" for FTP mounting.

The package is called CurlFtpFS and is available via Yum:

yum install curlftpfs

Mounting an FTP account is as easy as:

curlftpfs ftp://username:password123@example.org /mnt/ftp-example
Note: CurlFtpFS hasn't been updated since 2008 and is presumed to be a dead project.

Composer install suggestions

With Craft CMS 3 Beta, there's a fair amount of packages to install via Composer, there's also a sizeable list of suggested packages (mostly for use with sources). Usually I'd just include whichever suggestions I'd want to install by copying and pasting each package into a `composer require X`, but there's a quicker way to go from suggestion to require; the following line will add all suggested packages as a requirement to your Composer project.

composer suggests | xargs -i composer require {}

This may be overkill for some (most?) projects but for Craft CMS 3, I want to ensure these packages are available should a user or plugin later require it, or if a suggested package is just more efficient! Like the `ext-gmagick` package for use with P&T's Imagine editor.

Open Google Chrome in Kiosk Mode on Mac

set strUrl to "https://example.org"
do shell script "/Applications/Google\\ Chrome.app/Contents/MacOS/Google\\ Chrome --app=" & strUrl tell application "Google Chrome" to activate
tell application "System Events"
keystroke "f" using {command down, control down}
end tell