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:
…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!