How to reset user id

  • Hi ,

    Is there a way to reset the users id to start the next user from ?

    Lets say , i have few id’s for the registered users after the admin , and i deleted those users .. how to let wordpress start the next user id after the admin id ” 1 ” ..

    I tried this sql command , but it is not working :

    ALTER TABLE wp_users AUTO_INCREMENT = 2

    and this was the error : 

    #1067 - Invalid default value for 'user_registered'
Viewing 6 replies - 1 through 6 (of 6 total)
  • Moderator bcworkz

    (@bcworkz)

    It’s odd that the default value is incorrect. Get to the phpMyAdmin app, usually through your hosting account. Locate your WP DB and navigate to the users table and open the Structure tab. “Change” the user_registered field. The correct default value is as defined: 0000-00-00 00:00:00. Click Save.

    While in phpMyAdmin, you can set AUTO_INCREMENT directly through the UI. First go to the users table Browse tab and make note of the current highest user ID value, usually on the last listing page. Go to the Operations tab and set AUTO_INCREMENT to 1 or more higher than the currently highest ID value (under Table Options section). Don’t change anything else. Click the related Go button.

    FWIW, there is no real reason to adjust the auto increment value. The field is “bigint” type, you’ll never have so many users as to run out of IDs.

    Thread Starter alshizawi

    (@alshizawi)

    @bcworkz

    Still i am getting the same error 

    #1067 - Invalid default value for 'user_registered'
    Moderator bcworkz

    (@bcworkz)

    Verify the field type is datetime. If it is, I’m at a loss, you could try CURRENT_TIMESTAMP instead of 0000-00-00 00:00:00 I suppose. Select from the dropdpown. The second field will disappear. Then click Save of course.

    Thread Starter alshizawi

    (@alshizawi)

    @bcworkz

    Actually it is the same : user_registered datetime ?0000-00-00 00:00:00

    But when i am trying to change ” AUTO_INCREMENT ” to 1 , the error shows

    Moderator bcworkz

    (@bcworkz)

    Is your user table completely empty? That’s the only way setting it to 1 makes sense. If you only have user ID 1 as the initial admin user in the table, the lowest possible auto increment value is 2.

    Trying to set it to 1 when it already exists would certainly cause an error. The resulting error message does not make sense. It wouldn’t be the first time software generated a misleading error message.

    Thread Starter alshizawi

    (@alshizawi)

    Hi ,

    If you see my first post , i mentioned that i used : 

    ALTER TABLE 
wp_users
 AUTO_INCREMENT = 2

    But i got the same error .. i am really confused

    By the way , i changed wp_users to the actual table name in my database

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘How to reset user id’ is closed to new replies.