Title: Error in table Last modified: August 22, 2016 --- # Error in table * Resolved [Raradra](https://wordpress.org/support/users/raradra/) * (@raradra) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/) * First it told me that the log was unwwritable so I fixed that, now it’s saying error * “Could not SHOW INDEX on database table wp_comments.” * under the following section: * These unused user accounts are older than the age threshold you’ve set below. To remove them, either enable automatic deletion or click the “Remove spam/unused accounts now” button above. * [https://wordpress.org/plugins/user-spam-remover/](https://wordpress.org/plugins/user-spam-remover/) Viewing 8 replies - 1 through 8 (of 8 total) * Plugin Author [Joel](https://wordpress.org/support/users/joelhardi/) * (@joelhardi) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421505) * 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: [http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges](http://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.) * Thread Starter [Raradra](https://wordpress.org/support/users/raradra/) * (@raradra) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421517) * It was working until I updated to the new version and I haven’t changed any permissions. * Plugin Author [Joel](https://wordpress.org/support/users/joelhardi/) * (@joelhardi) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421519) * 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. * Thread Starter [Raradra](https://wordpress.org/support/users/raradra/) * (@raradra) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421522) * 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) * Plugin Author [Joel](https://wordpress.org/support/users/joelhardi/) * (@joelhardi) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421540) * 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) * Thread Starter [Raradra](https://wordpress.org/support/users/raradra/) * (@raradra) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421562) * 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. * Plugin Author [Joel](https://wordpress.org/support/users/joelhardi/) * (@joelhardi) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421563) * Yeah, sorry, the wordpress.org system is pretty primitive. You have to append a revision number, 0.9.1 (the last version to use original mysql extension) is [here](http://plugins.svn.wordpress.org/user-spam-remover/trunk/?p=358685). * 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. * Plugin Author [Joel](https://wordpress.org/support/users/joelhardi/) * (@joelhardi) * [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421566) * 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](https://wordpress.org/plugins/user-spam-remover/installation/). * 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. Viewing 8 replies - 1 through 8 (of 8 total) The topic ‘Error in table’ is closed to new replies. * ![](https://ps.w.org/user-spam-remover/assets/icon.svg?rev=1630376) * [User Spam Remover](https://wordpress.org/plugins/user-spam-remover/) * [Frequently Asked Questions](https://wordpress.org/plugins/user-spam-remover/#faq) * [Support Threads](https://wordpress.org/support/plugin/user-spam-remover/) * [Active Topics](https://wordpress.org/support/plugin/user-spam-remover/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/user-spam-remover/unresolved/) * [Reviews](https://wordpress.org/support/plugin/user-spam-remover/reviews/) * 8 replies * 2 participants * Last reply from: [Joel](https://wordpress.org/support/users/joelhardi/) * Last activity: [11 years, 7 months ago](https://wordpress.org/support/topic/error-in-table-1/#post-5421566) * Status: resolved