Error in table

This is a message from the plugin telling you that your database user does not have the ability to run the “SHOW INDEX” query on the wp_comments table in your database.

The plugin adds an index to the comments table, on the user_id column. This is to make lookups by user_id fast (because the plugin needs to check whether each user has posted any comments or links to determine if it’s a candidate for deletion).

So, your database user must be configured with both INDEX and/or ALTER permissions for any of this to work. You could try granting just the INDEX permission (either on the full database or just on the comments and links tables) first, then add the ALTER permission as needed. (In either case you could apply the privilege to the whole database or just on the comments and links tables.)

More info on MySQL administration here: https://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges

(Note that if you don’t have these privileges granted, other things besides this plugin, like WordPress auto-upgrades, also will not work.)

It was working until I updated to the new version and I haven’t changed any permissions.

The new version uses the PHP mysqli extension instead of the older mysql one. It may be that these queries were failing on your database previously but just not raising an error. (The plugin does not technically need the table indexes to function, they just radically improve performance depending on the size of your comments table.)

To debug, I’d suggest connecting to your mysql database (be sure to use your WordPress database’s username and password), for instance using the mysql command-line client. Then try this query:

SHOW INDEX FROM wp_comments;

Please also provide the PHP and MySQL versions that you’re using.

Site1
mysql> SHOW INDEX FROM wp_comments;
+————-+————+—————————+————–+———————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+————-+————+—————————+————–+———————-+———–+————-+———-+——–+——+————+———+—————+
| wp_comments | 0 | PRIMARY | 1 | comment_ID | A | 7418 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_post_ID | 1 | comment_post_ID | A | 2472 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_approved_date_gmt | 1 | comment_approved | A | 4 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_approved_date_gmt | 2 | comment_date_gmt | A | 7418 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_date_gmt | 1 | comment_date_gmt | A | 7418 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_parent | 1 | comment_parent | A | 1854 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | user_id | 1 | user_id | A | 353 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_author_email | 1 | comment_author_email | A | 741 | 10 | NULL | | BTREE | | |
+————-+————+—————————+————–+———————-+———–+————-+———-+——–+——+————+———+—————+
8 rows in set (0.00 sec)

Site2 –
mysql> SHOW INDEX FROM wp_comments;
+————-+————+—————————+————–+———————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+————-+————+—————————+————–+———————-+———–+————-+———-+——–+——+————+———+—————+
| wp_comments | 0 | PRIMARY | 1 | comment_ID | A | 4737 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_post_ID | 1 | comment_post_ID | A | 1579 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_approved_date_gmt | 1 | comment_approved | A | 5 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_approved_date_gmt | 2 | comment_date_gmt | A | 4737 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_date_gmt | 1 | comment_date_gmt | A | 4737 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_parent | 1 | comment_parent | A | 1184 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | user_id | 1 | user_id | A | 169 | NULL | NULL | | BTREE | | |
| wp_comments | 1 | comment_author_email | 1 | comment_author_email | A | 526 | 10 | NULL | | BTREE | | |
+————-+————+—————————+————–+———————-+———–+————-+———-+——–+——+————+———+—————+
8 rows in set (0.00 sec)

# version numbers:

mysqld Ver 5.5.35-0+wheezy1 for debian-linux-gnu on i686 ((Debian))

PHP 5.4.32-1~dotdeb.1 (cli) (built: Aug 22 2014 17:09:53)

I just pushed a new version 0.9.8.1. No real change in functionality but if you upgrade it’ll at least tell you more explicitly whether your WordPress installation is configured to use mysqli or not.

I’ve been trying but am unable to replicate your issue so far. For instance I’ve been testing using a MySQL user with only these limited permissions:

MariaDB [(none)]> SHOW GRANTS FOR ‘wordpressuser’@’localhost’;
+——————————————————————————————————————+
| Grants for wordpressuser@localhost |
+——————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘wordpressuser’@’localhost’ IDENTIFIED BY PASSWORD ‘*175ECC8C35D7F9BF97A0C3AF85F765C26B23’ |
| GRANT SELECT, INSERT, UPDATE, DELETE ON wordpressdb.* TO ‘wordpressuser’@’localhost’ |
+——————————————————————————————————————+
2 rows in set (0.00 sec)

So how can I revert to a previous version that doesn’t use this mysqli? I’m not seeing any previous files on the dev page.

Yeah, sorry, the www.remarpro.com system is pretty primitive. You have to append a revision number, 0.9.1 (the last version to use original mysql extension) is here.

Did you get the new mysqli message in 0.9.8.1?

FYI PHP 5.5 deprecates the old mysql extension, so you will start getting PHP warnings if you upgrade PHP at some point and don’t switch to mysqli.

I’m going to close out this issue as resolved. I can’t replicate in any supported environments. Am assuming you’re either not using the PHP mysqli extension or haven’t assigned adequate MySQL privileges to your database user. We can reopen and keep going if you still think it’s something else.

I can confirm that the plugin does require the ALTER and CREATE privileges to create its indexes (happens only on initial activation, not on upgrade). For everyday use only SELECT, DELETE are required. Of course basic WordPress use is going to require all of these privileges anyway, but I added this detail to the readme.

Also, when adding the line:

define(‘WP_USE_EXT_MYSQL’, FALSE);

to wp-config.php to tell WordPress to use mysqli you can put it pretty much anywhere in the file except at the very bottom of the file where you aren’t supposed to edit anything. i.e. add it anywhere above this line:

/* That’s all, stop editing! Happy blogging. */

Yeah I know, WordPress probably should just have that line say:

/* DO NOT EDIT BELOW THIS LINE */
/* Seriously do not do it! */
/* After wp-settings.php is included it is game over for you! */

But it doesn’t. ??

Personally I just put WP_USE_EXT_MYSQL next to all the other DB settings near the top of the file.