Hello again, world.
Usually, WordPress updates work without a hitch. This time, however, the update somehow thought it had to install a new blog, create new blog database tables within my blog database, and show a lousy “Hello World” post instead of the videos I had just painstakingly uploaded. And within two minutes I was getting roughly an e-mail a minute notifying me of my Chinese spammer friends.
Fixing it wasn’t that obvious, so I’m taking notes here as a reminder for future muck-ups. First, I exported my database before updating – a good idea for my peace of mind, but, if I’m not mistaken, not necessary for saving/restoring my blog. (20/20 Hindsight Tip™: empty spam before exporting, that’ll save a lot of MB.) I had to do three things to restore my database to visibility:
- In wp-config.php, change the value for $table_prefix from “wp_” to “myprefix_”. (Insert real prefix for “myprefix”.)
- In wp-config.php, change the character set to latin1.
- In the newly restored admin panel, change the permalink settings first to default and save the change, then change back to my custom permalink structure (I was smart enough to copy-paste the custom bit beforehand).
Finally, I also deleted the new tables that the new installation had created, which took care of the spam comments in a hurry. And WordPress had to update my database when logging in after step 2, which made my heart skip a beat, but that worked fine.
I do wonder if the update hiccup came from being logged in to the admin panel and the hosting service at the same time, but that seems strange. It’s the only distinction to previous updates, however, that comes to mind.
Why it happened, I still do not know, nor why it killed the comment function. (Anyone who tried to post got an error message, “ERROR: The comment could not be saved. Please try again later.”)
What tech support did to fix the problem was to add an Auto_Increment to the column “comment_ID” in the “myprefix_comments” table and to the column “meta_id” in the “myprefix_commentmeta” table. In phpMyAdmin that means checking the box under A_I when you’ve drilled down to the structure of your column in question. (Replace “myprefix_” with your own prefix, “wp_” by default.)
He knew the problem lay there from checking the error_log file. After the new installation, there came a ton of “file not found” errors; then (probably after I fiddled with the wordpress installation as described above) the comment trouble started with errors like this one:
[Mon Dec 22 00:09:19 2014] [warn] [client aaa.bbb.ccc.ddd] mod_fcgid: stderr: WordPress database error Duplicate entry ‘0’ for key ‘PRIMARY’ for query INSERT INTO `myprefix_comments` etc. etc.
The primary key isn’t immediately obvious in the phpMyAdmin view, but you can sort your table according to the primary key, which should let you deduce which one it is.
This time, the update to 4.1.1 seems to have worked okay, as usual.