ElasticDog.com

I just upgraded the WordPress installation for ElasticDog to version 2.5.1 and noticed that the Manage Comments page in the administrative Dashboard was not displaying any comments. I only noticed there was a problem when the interface kept telling me there were 9 comments awaiting moderation, yet it would not let me see them. Everything was displaying correctly on the publicly viewable blog pages, but no comments showed up when trying to access them via the Dashboard.

After searching for a bit and checking my sever logs, I found that the upgrade script did not add the proper index to the “comment_date_gmt” column in the comments table for my WordPress database:

Apr 25 18:22:17 php-cgi: WordPress database error Key 'comment_date_gmt' doesn't exist in table 'wp_comment' for query...

The Solution

If you don’t want to wait until the upgrade script gets fixed in a later WordPress release, you’ll have to add the missing index to the database table yourself to get things working again. I’ll demonstrate how to do that using the MySQL command line interface, however, if you’re more comfortable with using phpMyAdmin, see the steps outlined here.

Check for Proper Privileges

First of all, you must have the proper privileges on your WordPress database in order to add an index. To check that, follow these steps:

1. Log in to MySQL with whatever account credentials you know.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 59202
   Server version: 5.0.51a Source distribution
   
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   
   mysql>

2. Specify that you want to access the “mysql” database, then ensure that you do have the privileges to add indexes.

   mysql> use mysql
   Database changed
   mysql> SELECT user, index_priv FROM user;
   +------------------+-----------------+
   | user       | index_priv |
   +------------------+-----------------+
   | root       | Y          |
   | elasticdog | N          |
   +------------------+-----------------+
   2 rows in set (0.00 sec)

As long as you see a ‘Y’ next to your username, then you should be good to go.

Add the Index to the Comments Table

Once you have access to an account that can add indexes, then follow these steps to add the missing index:

1. Specify that you want to access your WordPress database. If you don’t know the name of your WordPress database, run show databases; to see a list of databases you have access to.

   mysql> use elasticdog_wordpress
   Database changed

2. Add the index. The table names for WordPress are fairly standard, but if you don’t have one named “wp_comments”, run show tables; to see a list of contenders and it should be easy to figure out.

   mysql> CREATE INDEX comment_date_gmt ON wp_comments (comment_date_gmt);
   Query OK, 458 rows affected (0.14 sec)
   Records: 458  Duplicates: 0  Warnings: 0

3. If want to verify that the index was created, run
   SHOW INDEX FROM wp_comments; to list all indexes, and then you can exit out.

If everything went as planned, all will be right in the world and your comments should be viewable through the dashboard once again!