Paddy

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!

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!

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