0

I've been setting up a WordPress-based website with a membership system that uses the User ID as a membership number.

During testing I've made many users then deleted them. Now that testing is complete, when I make a new user, they get an ID of 25, 26, 27, etc…

How I can reset the 'next User ID' number back down to 2 (or whatever) so the new users become 2 then 3 then 4 etc.

Is it something I need to do in CPanel in phpMyAdmin / SQL?

jimbolgs
  • 3
  • 1
  • 2
  • Possible duplicate of [this](http://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql). – forrestmid Apr 01 '17 at 21:50

2 Answers2

1

Yes you can do it with phpMyAdmin or SQL.

First go into the wp_users table and delete any users you need to.

Then go into the wp_usermeta table and delete any meta entries that correspond do the IDs of those deleted users.

Then you need to alter the wp_users table to reset its auto increment value. So you can do that in phpMyAdmin, or use SQL like this:

ALTER TABLE `wp_users` AUTO_INCREMENT={ID you want to be used next}

So if you want the next created user to have the id number 4 it would look like this:

ALTER TABLE `wp_users` AUTO_INCREMENT=4
jqlee85
  • 81
  • 6
0

If you try to alter wp_users with MySQL 5.7.5+ you'll get the following error:

Invalid default value for 'user_registered'

This is because the NO_ZERO_DATE mode was added as default in MySQL 5.7.5 which forbids the date 0000-00-00. WordPress sets the default value for user_registered to 0000-00-00 00:00:00, thus this error.

I'm not sure what the best solution is, but because changing user IDs is pretty hacky in the first place this was my quick fix.

I queried the default (session) sql_mode:

SELECT @@SESSION.sql_mode;

(for me on 5.7.28 this was ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION)

I removed NO_ZERO_IN_DATE and NO_ZERO_DATE from this, and then set sql_mode to it before altering the table:

SET SESSION sql_mode = '(mode string from above without NO_ZERO_DATE)';
ALTER TABLE `wp_users` AUTO_INCREMENT = 2;
WD40
  • 429
  • 1
  • 7
  • 19