cPanel Question posted in
The official documentation can be seen here.

Reset WordPress 'next User ID' number – CPanel

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search