Changing the starting value for AUTO_INCREMENT on wp_users table

Support
1

What issue or error are you experiencing?

Hey guys, I typically run the ALTER TABLE wp_users AUTO_INCREMENT = SQL Query to set a custom starting value for new User ID#'s. I’ve run hundreds of these queries in a previous Desktop Server environment with phpMyAdmin and no issues.

I just tried it on a handful of new site builds with Adminer and getting an error => “Error in query (1067): Invalid default value for ‘user_registered’”.

Could somebody please let me know what I’m missing?

Kind regards,
Paul

2

Hi @krackedkreative

This might be helpful:

3

Thanks Nick, unfortunately setting the sql_mode globally using -

set global sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

doesn’t work…

It does enable you to run the query ALTER TABLE wp_users AUTO_INCREMENT = ‘integer of choice’ without throwing a SQL error.

However, removing 'NO_ZERO_IN_DATE,NO_ZERO_DATE’from the global sql_mode caused other issues, including locking me out of all of my sites WP Admin.

For anyone else following this thread =>

Running set global sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’

In an effort to restore the DB to it’s original values; failed.

I ended up binning the app for the third time this month and reinstalling a fresh instance of Local…

Kind regards,

Paul

4

Did reinstalling the app resolve the issue?

5

Hey Nick, reinstalling the app appears to have given me a clean slate again. However I binned all of the DBs from my existing site installations and installed fresh ‘blank’ DBs before importing them into Local; just to be sure…

I don’t believe that this is a Local issue, rather a “newer version” of SQL issue, than I was using with DesktopServer. I ran some queries to change User ID#s; no problem, but setting the auto-increment using that particular query doesn’t work. Further research hasn’t uncovered any definitive solutions yet…

Regards,
Paul

6

Thank you for following up and sharing your findings with the community. We appreciate all your attention to detail. If you have any other questions or concerns don’t hesitate to reach back out. We are always happy to help!

7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.