Process fails and doesn’t recover

Thanks for the question, and my apologies for not noticing it sooner.

You’re getting the database error Failed to read auto-increment value from storage engine from your DBMS. That can happen when your current autoincrementing meta_id value gets bigger than the space allocated for it in your table. There are also a couple of old MySQL bugs that can cause it too.

If you could do something like this:

wp db query "show create table wp_usermeta;"

and reply to this topic with the table definition you get back I might be able to troubleshoot further.

Thanks for getting back to me!

Here’s the table structure:

 wp_usermeta | CREATE TABLE wp_usermeta (
  umeta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  meta_key varchar(255) NOT NULL,
  meta_value longtext DEFAULT NULL,
  PRIMARY KEY (user_id,meta_key,umeta_id),
  UNIQUE KEY umeta_id (umeta_id),
  KEY meta_key (meta_key,meta_value(32),user_id,umeta_id),
  KEY meta_value (meta_value(32),umeta_id)
) ENGINE=InnoDB AUTO_INCREMENT=37520662 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

Well, this is quite strange. The usual reason for this error — your autoincrement number growing too large for its column — isn’t in play. Try restarting your MySQL or MariaDB database server.

It looks like you’re using my other plugin, Index WP MySQL for Speed. Could you go to Tools -> Index MySQL and then to the About tab. Then upload your metadata and reply to this topic with the upload id. And I’ll take another look.

Sorry for the inconvenience, and thanks for your patience.

I’ve restarted the database and attempted to restart the user indexing. However, it only progressed to 41% before stalling, with no errors appearing in the debug log.

My metadata upload ID is:

YGe5KMsc

Thanks for persevering with this, I really appreciate your help!

Weird. If you still have the patience for this, please try deactivating the plugin.

Then, if you’re willing, try editing .../wp-content/plugins/index-wp-users-for-speed/index-wp-users-for-speed.php.

Look for this line

const INDEX_WP_USERS_FOR_SPEED_BATCHSIZE = 5000;

and change 5000 to 1000.

Then reactivate it. Maybe that will help. Thanks again for working with me on this.

Tried reducing the batch size to 1000, stalled again at 45%.
Debug log is showing both a deadlock and auto-increment errors this time.

[05-Nov-2024 23:55:05 UTC] WordPress database error Deadlock found when trying to get lock; try restarting transaction for query INSERT INTO wp_usermeta (user_id, meta_key) SELECT user_id, meta_key FROM (SELECT a.user_id, 'wp_iufsr:administrator' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:administrator'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'administrator', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:editor' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:editor'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'editor', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:author' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:author'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'author', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:contributor' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:contributor'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'contributor', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:subscriber' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:subscriber'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'subscriber', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:customer' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:customer'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'customer', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:shop_manager' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:shop_manager'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'shop\\_manager', '%')
          AND b.user_id IS NULL) a WHERE a.user_id >= 251999 AND a.user_id < 252999 made by do_action_ref_array('index_wp_users_for_speed_task'), WP_Hook->do_action, WP_Hook->apply_filters, IndexWpUsersForSpeed\index_wp_users_for_speed_do_task, IndexWpUsersForSpeed\Task->doTaskStep, IndexWpUsersForSpeed\PopulateMetaIndexRoles->doChunk, IndexWpUsersForSpeed\Task->doQuery
[05-Nov-2024 23:55:05 UTC] WordPress database error Failed to read auto-increment value from storage engine for query INSERT INTO wp_usermeta (user_id, meta_key) SELECT user_id, meta_key FROM (SELECT a.user_id, 'wp_iufsr:administrator' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:administrator'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'administrator', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:editor' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:editor'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'editor', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:author' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:author'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'author', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:contributor' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:contributor'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'contributor', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:subscriber' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:subscriber'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'subscriber', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:customer' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:customer'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'customer', '%')
          AND b.user_id IS NULL UNION SELECT a.user_id, 'wp_iufsr:shop_manager' meta_key
         FROM wp_usermeta a
         LEFT JOIN wp_usermeta b
                ON a.user_id = b.user_id 
               AND b.meta_key = 'wp_iufsr:shop_manager'
        WHERE a.meta_key = 'wp_capabilities'
          AND a.meta_value LIKE CONCAT('%', 'shop\\_manager', '%')
          AND b.user_id IS NULL) a WHERE a.user_id >= 251999 AND a.user_id < 252999 made by do_action_ref_array('index_wp_users_for_speed_task'), WP_Hook->do_action, WP_Hook->apply_filters, IndexWpUsersForSpeed\index_wp_users_for_speed_do_task, IndexWpUsersForSpeed\Task->doTaskStep, IndexWpUsersForSpeed\PopulateMetaIndexRoles->doChunk, IndexWpUsersForSpeed\Task->doQuery

Let me know if you want me to try anything else. Thanks!

Ah, thanks for your kindness trying that. Deadlock, huh? I bet your site is quite busy!

I’m going to push out a new version of the plugin with some transaction management in it, and some restarting capability. I’ll let you know.

I just released v 1.1.8 with, hopefully, some database deadlock protection in it.

I hope it works for you!