Can't add users in admin after copying database

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.

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.

• users
• admin

Share Improve this question edited Mar 4, 2019 at 21:57 BlueDogRanch asked Mar 4, 2019 at 16:54 BlueDogRanchBlueDogRanch 14055 silver badges2525 bronze badges 10

• 1 Can you add users programmatically either via CLI or wp_create_user? – MikeNGarrett Commented Mar 4, 2019 at 18:45
• Thanks, that's interesting; with wp-cli I get "Error: Unknown error creating new user." Does wp-cli use wp_create_user? – BlueDogRanch Commented Mar 4, 2019 at 21:21
• And if I run create user again with the same creds, I get a duplicate entry database error for the wp_users table, but searching the database doesn't show the user name or email. wp-cli debug doesn't show any fatal errors. – BlueDogRanch Commented Mar 4, 2019 at 21:44
• Are you connecting to the correct database? – MikeNGarrett Commented Mar 4, 2019 at 21:44
• I'm in public_html of the WP site using SSH. debug shows the correct database being called. – BlueDogRanch Commented Mar 4, 2019 at 21:46

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 4

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