0

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 user@yahoo.com --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.

  • 1
    Can you add users programmatically either via CLI or wp_create_user? – MikeNGarrett Mar 4 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 Mar 4 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 Mar 4 at 21:44
  • Are you connecting to the correct database? – MikeNGarrett Mar 4 at 21:44
  • I'm in public_html of the WP site using SSH. debug shows the correct database being called. – BlueDogRanch Mar 4 at 21:46
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.