All done

finish01The move to a new server has now completed, and if you’re reading this then I assume everything worked as intended.

There are a few minor, known issues: anything I’ve ever linked to in my file repository at diznix.com is now broken, because it was simpler to create a new Owncloud file respository from scratch than to try to migrate the thing over. Fortunately, there are only a dozen or so such links: they should be sorted by the end of the day.

Otherwise, it’s all lovely, clean and fresh and working well!

(But if you do encounter glitches or problems with anything not working, feel free to let me know in the comments).

There was one nasty surprise that arose as a result of switching from ye olde, stable CentOS (MySQL version 5.1.something) to flash-harry Ubuntu (MySQL version 5.7.something). Between those two versions, MySQL invented something called “Strict Mode” -and in 5.7, this actually became the default operating mode. Specifically, the “STRICT TRANS TABLES” mode was enabled -and this governs the way MySQL reacts to things like implicit data conversions in a SQL statement. In non-strict mode, for example, an attempt to set a VARCHAR column to a value of 1 would raise a warning, but not fail. In strict mode, it fails. (As any fule knowe, if you want a varchar to contain the string value 1, you need to wrap your ‘1’ in quotation marks!)

The relevance to my server move? As per this article, I now re-compute the comment counts for my posts every time I publish a new one -and my query to do that is this:

UPDATE dizwp.wp_posts wpp
LEFT JOIN
(SELECT comment_post_id AS c_post_id, count(*) AS cnt FROM dizwp.wp_comments
 WHERE comment_approved = 1 
 GROUP BY comment_post_id) wpc
ON wpp.id=wpc.c_post_id
SET wpp.comment_count=wpc.cnt
WHERE wpp.post_type IN ('post', 'page')
      AND (wpp.comment_count!=wpc.cnt OR (wpp.comment_count != 0 AND wpc.cnt IS NULL));

Spot the error? Neither did I for a long time!! However:

mysql> desc wp_comments;
+----------------------+---------------------+
| Field                | Type                |
+----------------------+---------------------+
| comment_ID           | bigint(20) unsigned |
| comment_post_ID      | bigint(20) unsigned |
| comment_author       | tinytext            |
| comment_author_email | varchar(100)        |
| comment_author_url   | varchar(200)        |
| comment_author_IP    | varchar(100)        |
| comment_date         | datetime            |
| comment_date_gmt     | datetime            |
| comment_content      | text                |
| comment_karma        | int(11)             |
| comment_approved     | varchar(20)         |

Spot it now??

COMMENT_APPROVED is declared to be of type varchar(20).

My update statement says: WHERE comment_approved = 1

The WHERE clause is treating the number ‘1’ as a number, because it hasn’t any quotation marks around it. That is not good in strict mode when the column you are querying is a VARCHAR. 🙁

Two solutions, then: disable strict mode by editing /etc/mysql/mysqld.conf.d/mysqld.cnf and appending the line:

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

…after which you do a service mysql restart and the whole of MySQL is now running in non-strict mode and will do implicit data conversions without falling over.

Or you just re-write the original update statement to put quotation marks around the 1 in the WHERE clause:

WHERE comment_approved = '1'

I assume that WordPress knows all about strict mode and doesn’t need it disabled, therefore the addition of the quotes to my query seemed the more sensible option… but I imagine this is one of those niggly little issues that is quite likely to break an awful lot of MySQL applications if you’re not careful!

Anyway: apart from that, no major damage done, and I’ve edited the original article to add mention of this not-so-minor potential deal-breaker!

Un nouvel ordinateur

onlinelogoFor over a year now, this site and its sister have been hosted on a dedicated server hosted in a Parisian data centre, leased from online.net, whose technical support has been excellent the few times I’ve needed to contact them, and whose uptimes are similarly unimpeachable. They get a big thumbs-up from me and I’d recommend them to anyone who wanted well-specified and extremely economical dedicated servers.

For the princely sum of €16 a month (or about AU$25), I get 1TB of disk space, 8GB RAM, a 4-core hyperthreaded CPU, 1Gb/sec networking and unlimited bandwidth. It was (and remains) an exceptionally good deal, I think (as compared to, for example, Linode with whom I previously hosted). The French also have the advantage of not kow-towing to the demands of the NSA (so far as we know!), which makes my paranoid self a little happier than I was with Linode 🙂

It pays to keep an eye on their product offerings, however, because I just noticed that they are now offering 16GB RAM for exactly the same price as I was paying for my 8GB, plus they’ve more than doubled the networking to a 2.5Gb/sec connection. (The specific product offering is called a “Dedibox XC 2016“; I decided to stick with 1TB of spinning SATA disk instead of opting for their 120GB SSD near-equivalent offering).

I asked about the possibility of upgrading my current box to their new specifications, but it’s not possible (and given we’re talking about physical servers in a rack, I’m not surprised). If you want to upgrade, you have to buy a new server of the new specification, move everything across to it and then cancel the original, now poorly-specc’d server.

Which is what I’m currently in the middle of doing.

I’ve made life a little harder for myself by building the new server as an Ubuntu 16.04 server rather than the CentOS 6.7 that adorned the original. I was rather well-practised at hardening CentOS installations; now I have to learn how to do it for an entirely new distro. Let’s hope I get it right, eh?!

If I can get the new server built, hardened and all content transferred across by June 20th, I can cancel the old server without having to pay an extra month. If not, I have to continue paying for it until the end of July. Fingers crossed, then, and I may only have 20 days of overlap to pay for.

Naturally, there will be some DNS updates to point things at the new servers if I get it right in time, and accordingly access to this site might get a bit tricky from time to time. Bear with me please: I’ll post a final confirmation when everything is across OK, but no doubt I’ll manage to screw things up a bit before I finally get it all right!

Nightmares

wpress01I think I’ve used just about every blogging platform out there in my time. The remembered terrors of Drupal and Joomla still keep me awake at nights.

I keep coming back to WordPress, though. It’s pretty easy to install, administer and use… and the results, visually, are appealing.

Unfortunately, everyone else keeps coming back to WordPress, too. Including hackers, crackers and other assorted ne’er-do-wells. As probably the world’s most popular blogging platform, WordPress starts off as a big target. Given that it’s written in PHP, however, it is also a very vulnerable one: one bad extension can open you up to all manner of nasties. It’s happened a lot of late.

Which gives me nightmares: I like to think that being self-hosted, I’m able to administer a tighter ship than was true for some of those cases, but I’m probably kidding myself. Just as living where I live, one has to accept that one day, I’m likely to lose the house in a bush fire, so I suspect that I ought to resign myself to the fact that, running a WordPress blog, one day I’m going to get hacked.

In the case of the house, acceptance of the inevitable means making sure you’re fully insured; that all your passports and birth certificates and similar papers are immediately to hand to grab as you flee; that anything that has huge sentimental value is similarly ready to accompany you on a quick departure… and that everything else is just ‘stuff’ and material possessions can be re-purchased.

In the case of the blog, accepting the inevitable has (for me) meant making plans to host the blog at home as the primary site; it only gets to the public webhost when I’m ready to copy it there. Being a copy that’s not accessible to the Internet means when the hoodlums strike, I can hopefully re-install a fresh, clean operating system on my public web server and copy everything up to it afresh. It will be inconvenient, sure; but at least my work will be safe from total loss and the nightmares can subside a bit.

As an added bonus, my websites not only reside on my new RAIDZ zpool, but are copied separately to my two HP servers, which also run RAIDZ pools… so that’s at least three copies of my work handy in the house. And there are two offsite backups of those HP servers which get refreshed around every 3 months. So, if the worst came to the worst: should hackers wipe my web server, my home PC explode at the same time, and two HP servers both suffer catastrophic 2-disk failures that same day… well, I might want to end it all for other reasons (it sounds like it’s a bad hair day plus infinity!), but I’d stand to lose only a couple of months of blogging. Which I could probably deal with.

So, I wrote an article or three to explain how I did it; I thought it might be of interest to other WordPress users out there!

Sleep tight…

Twenty Steps to Perfect Ubuntu!

ubu01Finishing my recent flurry of Ubuntu-related activity, I’ve put together a new article explaining precisely how I tweak and twiddle with my fresh Ubuntu 16.04 installations to turn them into something I would call usable.

Perfection it certainly isn’t (thank you, right-hand windows controls!), but close enough for my purposes, I suppose.