Can't add users in admin after copying database

Question

This is a really strange issue. I'm testing a large production Woocommerce site with a 2 gig database. The production site needs updates to Woocommerce and plugins, so I want to test on staging. I can add users on the production site, and Woocommerce subscriptions work.

I copy the database to a testing subdomain using mysqldump and change all URLs in the database using wp-cli. Everything on the staging site works - sandbox checkout of current users, etc - except adding users via New User in admin. This also means Woocommerce subscriptions don't work, as that process adds a user as part of the checkout. (Yes, Woocommerce subscriptions are correctly enabled as a sandbox for testing on staging.)

When I try adding a new user in admin, the page loads for a few seconds and I see the green flagged WP alert that the user has been added. But when I search for the user in admin and in PHPMyAdmin, the user hasn't been added. The site admin gets an email that a user has been added, but the username and email are blank.

There are no errors in debug, no errors when using a query monitor, no errors in the general server php error log or in the MySQL or slow query log.

On staging, I've tried manually replacing all WP core files/folders, deactivating all plugins and using the 2019 theme.

I've raised max_input_vars and post_max_size in PHP7.2.

Server is Maria 10.2. I've repaired all database tables.

I can add users using a query directly in PHPMyAdmin.

Trying to add a user with wp-cli wp user create username [email protected] --role=subscriber results in an "Error: Unknown error creating new user"

When I create a new database on staging and install WP as a "new" site with the same WP files/folders and plugins, I can add new users in admin. But as soon as I go back to the working database copied from production, I can't add users.

Answer 1

The key turned out to be that the wp_users table lost its AUTO_INCREMENT setting when it was imported, for unknown reasons; the server may have timed out, etc.

I found this out from the wp-cli error in the debug log that said "WordPress database error Duplicate entry '1000075' for key 'PRIMARY' for query INSERT INTO 'wp_users'and in PHPMyAdmin, I could see that the ID 1000075 was already assigned to a user.

Even though the wp_users table showed AUTO_INCREMENTset in PHPMyAdmin, I ran this query to set it anyway:

ALTER TABLE pw_users AUTO_INCREMENT = last-ID-plus-1

where last-ID-plus-1 is the highest ID that exists in wp_users, plus 1, i.e. in my case was

ALTER TABLE pw_users AUTO_INCREMENT = 1003000

After setting AUTO_INCREMENT, I can add users.

Reference: @N00b's answer for Wordpress Database lost auto increment