User registration not working

  • Resolved van der Vaart

    (@vdvdesign)


    2 years, 11 months ago

    Hi everyone,

    I’ve encountered an issue where users are not able to register on our website. After some research and running WordPress in debug mode I’ve found out that this is the issue :

    WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO wp_users (user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, display_name, user_login) VALUES ('value', 'value', 'value', '', '2023-05-23 19:13:29', '', 'value', 'value') made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('wp_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Form_Handler::process_registration, wc_create_new_customer, wp_insert_user

    Please note; I have replaced some data with value because of privacy. After getting this error, I started investigating the database and found out that in the table wp_users there is no AUTO_INCREMENT setting at row ID. So this is probably causing this issue. After some more research I also discovered this AUTO_INCREMENT has disappeared when the website was moved in the past (10-04-2022) because that was when the last user was created. The website has been moved on that date and probably made a mistake with not adding the AUTO_INCREMENT on the user ID.

    When I tried to activate AUTO_INCREMENT for the row I got the following error:

    ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

    I hope someone here knows how this can be fixed.

    Thanks!

    • This topic was modified 2 years, 11 months ago by van der Vaart.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator bcworkz

    (@bcworkz)

    2 years, 11 months ago

    The users table must not have any duplicate IDs. Any duplicates will need an unused ID assigned. There should not be any IDs with 0 value either.

    Confirm the table’s ID column is set as primary. The login, nicename, and email columns should be indexed. Set the table’s auto_increment value to something larger than any existing ID value.

    I’m not sure, but the IDs may need to be in ascending order. Non-sequential is OK, but there should not be any smaller IDs listed after a larger one?

    Make a backup of the current DB before you start fiddling with ID values. Note that changing IDs will affect relations to usermeta and post authors. Any changes need to be carefully coordinated to avoid DB corruption.

    Thread Starter van der Vaart

    (@vdvdesign)

    2 years, 11 months ago

    Hi bcworkz,

    Thank you so much for replying to my issue. I’ve checked the database and there was indeed an user with the ID 0. I changed this to the last known ID + 1 and then set the auto_increment to the user ID column.

    I am now able to create/register users again.

    Thanks again!!

    Have a wonderful day.

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘User registration not working’ is closed to new replies.