Where did my blog go? And why didn’t I have backups?

Earlier this week I wrote a blog post about SQL Server. I scheduled it to go live the following lunchtime (as I often do) and then checked to see why IFTTT hadn’t created a new link in bit.ly for me to tweet (I do this manually since the demise of TwitterFeed).

To my horror, my blog had no posts. Not one. Nothing. Nada. All gone.

Where did all the blog posts go?

As I’m sure you can imagine, the loss of 13 years’ of work invoked mild panic. But never mind, I have regular backups to Dropbox using the WordPress Backup to Dropbox plugin. Don’t I? Oh. It seems the last one ran in March.

History: Backup completed on Saturday March 11, 2017 at 02:24:40.


Now, having done some research, my backups have probably been failing since my hosting provider updated PHP on the server. My bad for not checking them more carefully. I probably hadn’t noticed because the backup was partially running (so I was seeing the odd file written to Dropbox) and I didn’t realise the job was crashing part way through.

Luckily for me, the story has a happy ending*. My hosting provider (ascomi) kept backups (thank you Simon!). Although they found that a WordPress restore failed (maybe the source of the data loss was a database corruption), they were able to restore from a separate SQL backup. All back in a couple of hours, except for the most recent post, which I can write again one evening.

So, what about fixing those backups, Mark

Tonight, before writing another post, I decided to have a look at the broken backups.

I’m using WordPress to Dropbox v4.7.1 on WordPress 4.8, both of which are the latest versions at the time of writing. The Backup Monitor log for my last (manual) attempt at backing up read:

22:55:57: A fatal error occured: The backup is having trouble uploading files to Dropbox, it has failed 10 times and is aborting the backup.
22:55:57: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/banner/video-seo.png’ to Dropbox: unexpected parameter ‘overwrite’
22:55:56: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/banner/configuration-service.png’ to Dropbox: unexpected parameter ‘overwrite’
22:55:56: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/banner/news-seo.png’ to Dropbox: unexpected parameter ‘overwrite’
22:55:55: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/editicon.png’ to Dropbox: unexpected parameter ‘overwrite’
22:55:54: Processed 736 files. Approximately 14% complete.
22:55:54: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/link-out-icon.svg’ to Dropbox: unexpected parameter ‘overwrite’
22:55:53: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/extensions-local.png’ to Dropbox: unexpected parameter ‘overwrite’
22:55:53: Error uploading ‘/home/mwilson/public_html/blog/wp-content/plugins/wordpress-seo/images/question-mark.png’ to Dropbox: unexpected parameter ‘overwrite’
22:55:48: Processed 706 files. Approximately 13% complete.
22:55:42: Processed 654 files. Approximately 12% complete.
22:55:36: Processed 541 files. Approximately 10% complete.
22:55:30: Processed 416 files. Approximately 8% complete.
22:55:24: Processed 302 files. Approximately 6% complete.
22:55:18: Processed 170 files. Approximately 3% complete.
22:55:12: Processed 56 files. Approximately 1% complete.
22:55:10: Error uploading ‘/home/mwilson/public_html/blog/wp-content/languages/plugins/redirection-en_GB.mo’ to Dropbox: unexpected parameter ‘overwrite’
22:55:10: Error uploading ‘/home/mwilson/public_html/blog/wp-content/languages/plugins/widget-logic-en_GB.mo’ to Dropbox: unexpected parameter ‘overwrite’
22:55:09: Error uploading ‘/home/mwilson/public_html/blog/wp-content/languages/plugins/widget-logic-en_GB.po’ to Dropbox: unexpected parameter ‘overwrite’
22:55:09: Error uploading ‘/home/mwilson/public_html/blog/wp-content/languages/plugins/redirection-en_GB.po’ to Dropbox: unexpected parameter ‘overwrite’
22:55:06: SQL backup complete. Starting file backup.
22:55:06: Processed table ‘wp_yoast_seo_meta’.
22:55:06: Processed table ‘wp_yoast_seo_links’.
22:55:06: Processed table ‘wp_wpb2d_processed_files’.
22:55:06: Processed table ‘wp_wpb2d_processed_dbtables’.
22:55:06: Processed table ‘wp_wpb2d_premium_extensions’.
22:55:06: Processed table ‘wp_wpb2d_options’.
22:55:06: Processed table ‘wp_wpb2d_excluded_files’.
22:55:06: Processed table ‘wp_users’.
22:55:06: Processed table ‘wp_usermeta’.
22:55:06: Processed table ‘wp_terms’.
22:55:06: Processed table ‘wp_termmeta’.
22:55:06: Processed table ‘wp_term_taxonomy’.
22:55:06: Processed table ‘wp_term_relationships’.
22:55:05: Processed table ‘wp_redirection_logs’.
22:55:05: Processed table ‘wp_redirection_items’.
22:55:05: Processed table ‘wp_redirection_groups’.
22:55:05: Processed table ‘wp_redirection_404’.
22:55:03: Processed table ‘wp_ratings’.
22:55:03: Processed table ‘wp_posts’.
22:54:54: Processed table ‘wp_postmeta’.
22:54:49: Processed table ‘wp_options’.
22:54:48: Processed table ‘wp_links’.
22:54:48: Processed table ‘wp_feedfooter_rss_map’.
22:54:48: Processed table ‘wp_dynamic_widgets’.
22:54:48: Processed table ‘wp_comments’.
22:54:45: Processed table ‘wp_commentmeta’.
22:54:43: Processed table ‘wp_bad_behavior’.
22:54:43: Processed table ‘wp_auth0_user’.
22:54:43: Processed table ‘wp_auth0_log’.
22:54:43: Processed table ‘wp_auth0_error_logs’.
22:54:43: Starting SQL backup.
22:54:41: Your time limit is 90 seconds and your memory limit is 128M
22:54:41: Backup started on Tuesday July 25, 2017.

Hmm, fatal error caused by overwriting files in Dropbox… I can’t be the only one having this issue, surely?

Indeed not, as a quick Google search led me to a WordPress.org support forum post on how to tweak the WordPress Backup to Dropbox plugin for PHP 7. And, after making the following edits, I ran a successful backup:

“All paths are relative to $YOUR_SITE_DIRECTORY/wp-content/plugins/wordpress-backup-to-dropbox.

In file Dropbox/Dropbox/OAuth/Consumer/Curl.php: comment out the line:
$options[CURLOPT_SAFE_UPLOAD] = false;
(this option is no longer valid in PHP 7)

In file Dropbox/Dropbox/OAuth/Consumer/ConsumerAbstract.php: replace the test if (isset($value[0]) && $value[0] === '@') with if ($value instanceof CURLFile)

In file Dropbox/Dropbox/API.php: replace 'file' => '@' . str_replace('\\', '/', $file) . ';filename=' . $filenamewith 'file' => new CURLFile(str_replace('\\', '/', $file), "application/octet-stream", $filename)

(actually, a comment further down the post highlights there’s a missing comma after $filename) on that last edit so it should be 'file' => new CURLFile(str_replace('\\', '/', $file), "application/octet-stream", $filename),)

So, that’s the backups fixed (thank you @smowton on WordPress.org). I just need to improve my monitoring of them to keep my blog online, and my blood pressure at sensible levels…


*I still have some concerns, because the data loss occurred the night after a suspected hacking attempt on my LastPass account; which seems to have been thwarted by second-factor authentication… at least LastPass say it was…

WordPress Backup to Dropbox column offset errors filling up web space

My website has been chewing through disk space recently. I couldn’t work out why and the largest files a few weeks ago were some webstat logs, so I cleared them down. Tonight, as I couldn’t upload anything (or apply any updates) I hunted around and found a couple of error_log files in my webspace.  The first related to a PHP file that was reading my Twitter feed using an old API and so was repeatedly failing. I removed the log and the offending PHP but that wasn’t the biggest problem. In my blog’s home folder was a 1.2GB error_log file – loo big to even read properly in Notepad, Word, or anything else I tried.

I managed to download a partial copy of the file (using Filezilla, then cancelling the transfer after a few seconds and saw lots of lines that contained the following error:

WordPress database error Column ‘offset’ cannot be null for query INSERT INTO wp_wpb2d_processed_files (file, uploadid, offset)

That told me it was the (very useful) WordPress Backup to Dropbox plugin, by Michael de Wildt. I don’t really want to disable that but luckily I found a fix on WordPress.org, posted by Rich Helms:

The issue is file wordpress-backup-to-dropbox/Classes/Processed/Files.php

Toward the bottom of the program change from

'file' => $file,
'uploadid' => null,
'offset' => null,


'file' => $file,
'uploadid' => null,
'offset' => 0,

so change the offset default from null to 0 and the issue goes away

Sure enough, that change seems to have fixed the problem and whilst the edit to the plugin will be over-written with the next release, hopefully that release will also include a permanent fix!

Short takes: search for new lines in Word; fix HTML <code> text wrapping in CSS; hidden elements in a WordPress theme

Another mini-blog post under the “short takes” banner…

Search for new lines when reformatting text in Word

Unix admins will probably scoff at me as they can probably cat, awk and sed this (or something like that) but I needed to take a list of values from a web page and convert them to a list in a single command earlier this week. The basic steps I used were:

  1. Copy text from table on HTML page
  2. Paste into Excel
  3. Delete unrequired columns
  4. Save as text

That gave me a file with a list of values (in this case a list of audio or video file formats) but it was one column and I wanted a row to include within some very long PowerShell commands.

  1. Open in Word
  2. Find ^p and replace with ,

The way that this works is that ^p will search for new lines in Word (actually, it’s looking for new paragraphs, and ^l will find a new line). This worked for me in Word, but not in WordPad.

Wrapping text in HTML code snippets

For years (ever since Garry Martin wrote a one of his guest posts on this blog), I’ve been using a WordPress plug-in called DirtyCode to format code snippets that wrap to multiple lines.

The plug-in is no longer maintained though, and WordPress’s visual editor strips out the <dirtycode> tags so I’ve been wanting to fall back to the standard HTML <code> tag. Unfortunately that doesn’t text wrap in my theme, so I had to find a way to stop long lines of code running out of the frame.

The fix (or maybe it’s a fudge – if I could work out how to make custom CSS stick on theme changes, I would) was to edit my WordPress theme’s stylesheet (style.css) to include the following inside the existing code { } line:

word-break: break-all; white-space: pre-wrap;

Hidden elements in a WordPress theme

On a related note, I had some issues with elements not displaying properly in my new theme either. The WordPress forums came to my rescue though – it seems the tag line that I couldn’t see was there but hidden, until I added the following code to the custom CSS:

.site-description { color: #CCCCCC; display: block; }

Remembering the changes made to my blog’s theme

Every year I need to update the copyright notice on my blog, and every year I forget where to do it in the theme for the site.

I should use a dynamic copyright date, as suggested by James Bavington (@jamesbavington):

Or perhaps this more elegant solution highlighted to me by Garry Martin (@garrymartin) but my late night coding changes just broke my blog, so I did it the old way.  Whilst I was at it, I applied an updated version of the theme, so this post is more of a “note to self” of the things I need to change when theme updates come along:

  • Include the various items in the header.php file that are needed for Google (and others) site verification, Google Analytics, Facebook Like button, Google+ badge, better search results, etc.
  • Put back in the style.css stylesheet amendments that adjust colours, etc.
  • Edit the functions.php file to make sure that the footer_link() function has the correct attribution and copyright notice…

Displaying a widget on a single WordPress post

I’ve used WordPress’ conditional tags in the past, for example to make sure that something only shows on a certain type of page or post but, a few months ago, I wanted to limit some code to only run on a specific post – and that was proving to be a challenge.  Then I found a useful plugin called Widget Logic.  As the name suggests, it adds some logic to widgets such that they only appear according to the rules specified using conditional tags.

All I needed to add was is_single('postnumber') to limit the widget to appearing only on the post I specified.  It’s also been handy to limit my warning that older content may be out of date so that it’s not shown on pages, only on posts (using !is_page()).

Presenting a warning for potentially outdated blog content

My older blog posts still seem to generate the odd bit of interest with people posting and asking for advice on potentially out-of-date topics (although one about Photoshop 7 crashing on startup is still one of my most popular posts!)

Some time ago, I decided to add a warning to these pages, to say something like “This post was originally published in 2011 and its contents may now be out-of-date.”

It’s implemented as a bit of PHP code in a WordPress widget:

1 ) { ?>


This post was originally published in and its contents may now be out-of-date.

Nothing particularly clever here, but it struck me that this might be useful for other WordPress site owners…

Some thoughts on modern technology: email, gadgets (and how children view them)

I haven’t found much time to blog recently, but this post pulls together a few loosely related streams of consciousness on technology – how we use it (or does it use us?), how it’s sold to us, and how the next generation view the current generation’s tech.

on Email…

Driving up to and back from Manchester last Friday night gave me a great opportunity to catch up on my podcast backlog – including listening to an entire series of Aleks Krotoski’s The Digital Human (#digihuman). The “Influence” and “Augment” episodes are particularly interesting but I also found that some parts of “Intent” sparked some thoughts in my mind. That episode featured comments by Douglas Rushkoff (@rushkoff) of Program or be Programmed fame, which I’ve paraphrased here.

Email can be seen as a [broken] game with many unintended consequences coded into it. For many of us, our working life is a game called “empty the inbox” (in the process, filling the inboxes of others). Email has a bias to generate more email – even when we’re away we auto-generate messages. In effect, all problems become a “nail” for which email is the “hammer”.

We’re almost entirely reactive – and we need to understand that it’s a person on the other side, not a computer – someone who is expecting something of some other person. So, standing up to your Blackberry is really standing up to your boss/colleague/whoever, not to the technology. It takes a brave person to send an out of office response that says something to the effect of “I’m deleting your message, if it was urgent, send it again after I’m back”. But that is starting to happen, as people realise that they are the humans here, with finite lifespans, and that a line needs to be drawn “in the digital sand” to show their limits.

I was also fascinated to learn that the average US teenager sends 3000 texts (SMS messages) a month – a stark contrast with ten years ago, when I had to explain to American colleagues what SMS was. At that time, the USA still seemed to be hooked on pagers, whilst SMS was really taking off over here in Europe.

on gadgets…

I spent a chunk of this weekend shopping for a (smart) television and a smart phone [why does everything have to be “smart” – what next, “neat”?].

The experience confirmed to me that a) I’m officially “a grumpy old man” who doesn’t appreciate the ambient noise in John Lewis’ audio visual department (nor, I suspect, do many others in the department store’s target demographic) b) John Lewis’ TV sales guys do not deliver the “well-trained and knowledgeable” confidence I associate with other departments in the store (i.e. they don’t really know their stuff) c) Samsung reps attached to consumer electronics stores are trained to up-sell (no surprise) d) Even John Lewis’ under-trained TV sales guys are better than Carphone Warehouse’s staff (who told my wife that the difference between the iPhone 4, 4S, and 5 starts off with the operating system… at which point I bit my tongue and left the conversation).

Incidentally, Stephen Fry’s new series, Gadget Man, starts tonight on Channel 4 – might be worth a look…

on the way children see gadgets…

Of course, the shopping experience had another angle introduced by my kids, who decided that it would be a good idea to change the channel on as many TVs as possible to show CBeebies (it kept them amused whilst we talked about the merits of different models with the Samsung rep who was in store) but I was fascinated to see how my boys (aged 6 and 8) reacted in Carphone Warehouse:

  • The switch from “oh phone shopping – that will be boooooring” to “oh, look, shiny things with touch screens” was rapid.
  • They liked using a stylus to write on a Galaxy Note.
  • All tablets are “iPads” (in fairness, my wife pointed out that that’s all they’ve ever known in our house).
  • An e-ink Kindle is a “proper Kindle” and the Kindle HD (which they had been happily playing games on – it took my six-year-old about 30 seconds to find “Cut The Rope”) was “the iPad Kindle”.

The irony…

After slating email as a “broken game”, I posted this by email using the new post by email functionality in the WordPress Jetpack plugin. I guess it still has its uses then…

Reducing website errors with HTTP 301 redirects

A couple of weeks ago, I wrote about a WordPress plugin called Redirection. I mentioned that I’ve been using this to highlight HTTP 404 errors on my site but I’ve also been using the crawl errors logged by Google’s Webmaster Tools to track down a number of issues resulting from the various changes that have been made to the site over the  years, then creating HTTP 301 redirects to patch them.

Redirections as a result of other people’s mistakes

One thing that struck me was how other people’s content can affect my site – for example, many forums seem to abbreviate long URLs with … in the middle. That’s fine until the HTML anchor gets lost (e.g. in a cut/paste operation) and so I was seeing 404 errors from incomplete URLs like http://www.markwilson.co.uk/blog/2008/12/netboo…-file-systems.htm. These were relatively easy for me to track down and create a redirect to the correct target.

Unfortunately, there is still one inbound link that includes an errant apostrophe that I’ve not been able to trap – even using %27 in the redirect rule seems to fail. I guess that one will just have to remain.

Locating Post IDs

Some 404s needed a little more detective work – for example http://www.markwilson.co.uk/blog/2012/05/3899.htm is a post where I forgot to add a title before publishing and, even though I updated the WordPress slug afterwards, someone is linking to the old URL.  I used PHPMyAdmin to search for post ID 3899 in the wp_content table of the database, from which I could identify the post and  create a redirect.

Pattern matching with regular expressions

Many of the 404s were being generated based on old URL structures from either the Blogger version of this site (which I left behind several years ago) or changes in the WordPress configuration (mostly after last year’s website crash). For these I needed to do some pattern matching, which meant an encounter with regular expressions, which I find immensely powerful, fascinating and intimidating all at once.

Many of my tags were invalid as, at some point I obviously changed the tags from /blog/tags/tagname to /blog/tag/tagname but I also had a hierarchy of tags in the past (possibly when I was still mis-using categories) which was creating some invalid URLs (like http://www.markwilson.co.uk/blog/tag/apple/ipad).  The hierachy had to be dealt with on a case by case basis, but the RegEx for dealing with the change in URL for the tags was fairly simple:

  • Source RegEx: (\/tags\/)
  • Target RegEx: (\/tag\/)

Using the Rubular Ruby RegEx Editor (thanks to Kristian Brimble for the suggestion – there were other tools suggested but this was one I could actually understand), I was able to test the RegEx on an example URL and, once I was happy with it, that was another redirection created.  Similarly, I redirected (\/category\/) to (\/topic\/).

I also created a redirection for legacy .html extensions, rewriting them to .htm:

  • Source RegEx: (.*).html
  • Target  RegEx: $1.htm

Unfortunately, my use of a “greedy” wildcard meant this also sustituted html in the middle of a URL (e.g. http://www.markwilson.co.uk/blog/2008/09/creating-html-signatures-in-apple-mail.htm became http://www.markwilson.co.uk/blog/2008/09/creating-.htm-signatures-in-apple-mail.htm) , so I edited the source RegEx to (.*).html$.

More complex regular expressions

The trickiest pattern I needed to match was for archive pages using the old Blogger structure.  For this, I needed some help, so I reached out to Twitter:

Any RegEx gurus out there who fancy a challenge, please can you help me convert /blog/archive/yyyy_mm_01_archive.htm to /blog/yyyy/mm ?
Mark Wilson

and was very grateful to receive some responses, including one from Dan Delaney that let me to create this rule:

Source RegEx: /blog\/([a-zA-Z\/]+)([\d]+)(\D)(\d+)(\w.+)
Target RegEx: /blog/$2/$4/

Dan’s example helped me to understand a bit more about how match groups are used, taking the second and fourth matches here to use in the target, but I later found a tutorial that might help (most RegEx tuturials are quite difficult to follow but this one is very well illustrated).

A never-ending task

It’s an ongoing task – the presensce of failing inbound links due to incorrect URLs means that I’ll have to keep an eye on Google’s crawl errors but, over time, I should see the number of 404s drop on my site. That in itself won’t improve my search placement but it will help to signpost users who would otherwise have been turned away – and every little bit of traffic helps.

Redirection – an essential plug-in for WordPress users

Last year, a combination of a loss of service from my hosting provider and my appalling backups meant that this website was temporarily wiped off the face of the Internet. It’s never recovered – at least not in terms of revenue – and it taught me an important lesson about backups (it’s all too easy to forget the hours of effort that go into a “hobby” site like this one…).

Whilst the blog posts were restored, and I took the opportunity to apply a new theme to the site (it’s probably due another one now…) but some of the images had got AWOL along the way. I’ve been ignoring that (mostly) but decided I really should do something about it when an old post was picked up by a journalist today and I realised it had a missing graphic.

I remembered a WordPress plugin that I used on another site recently, for managing redirects when access to the .htaccess file is not available. The plug-in, written by John Godley, is called Redirection, and one of its modules will report on HTTP 404 errors, like the ones that my missing graphics will create. I know there are other tools that can do this for me (Google’s Webmaster Tools, for example, or trawling through the web logs) but it’s an easy way to see when a 404 has been returned in order to investigate accordingly.  So far this afternoon, I’ve tracked down and replaced around 8 missing graphics and one broken permalink using the logs from Redirection.  I’m now scanning through the rest of John’s plugins to see what else I’m missing and will certainly be donating later…

Disabling comments for all posts on a WordPress blog

Long-time readers of my blog will know that I used to manage the Fujitsu UK and Ireland CTO Blog (which we’ve recently closed, but have left the content in place for posterity) and I’m still getting the comment notifications (mostly spam).  Many of the posts have HTTP 301 redirects to either mine or David Smith‘s blogs (I found a great WordPress plugin for that – Redirection) but, for those that remain, I wanted to turn off comments.  Doing this individually for each post seemed unnecessarily clunky but there is, apparently, no way to do this from the WordPress user interface (with database access it would have been straightforward but I don’t have that level of access).

There is a plug-in that globally disables all comments – named, rather aptly, Disable Comments – except that the blog is part of a multi-site (network) install and I’m not sure what the broader impact would be…

No bother, I found a workaround – simply set all of the posts to close comments after a certain number of days. The theme that someone has applied to the site (since I stopped working with it) doesn’t seem to respect that, and still leaves a comment button visible, but anyone with a well-developed theme should be OK…