Limit the index column size for queue_name

[piotrbak]

Before submitting an issue please check that you’ve completed the following steps:

• Made sure you’re on the latest version
• Used the search feature to ensure that the bug hasn’t been reported before

Describe the bug For specific table encoding we might encounter the following error:

[08-Sep-2022 18:02:15 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query CREATE TABLE wp_wpr_rucss_used_css ( 
            id               bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            url              varchar(2000)       NOT NULL default '',
            css              longtext                     default NULL,
            hash             varchar(32)                  default '',
            unprocessedcss   longtext                NULL,
            retries          tinyint(1)          NOT NULL default 1,
            is_mobile        tinyint(1)          NOT NULL default 0,
            job_id           varchar(255)        NOT NULL default '',
            queue_name       varchar(255)        NOT NULL default '',
            status           varchar(255)        NOT NULL default '',
            modified         timestamp           NOT NULL default '0000-00-00 00:00:00',
            last_accessed    timestamp           NOT NULL default '0000-00-00 00:00:00',
            PRIMARY KEY (id),
            KEY url (url(150), is_mobile),
            KEY modified (modified),
            KEY last_accessed (last_accessed),
            INDEX `queue_name_index` (`queue_name`),
            KEY hash (hash) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by require_once('wp-admin/admin.php'), do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Rocket\Dependencies\Database\Table->maybe_upgrade, WP_Rocket\Dependencies\Database\Table->install, WP_Rocket\Dependencies\Database\Table->create

Expected behavior We should limit INDEX 'queue_name_index' ('queue_name'), to be maximum 191 characters.

Additional context We need to decide what's maximum expected size there and alter tables for existing installations.

Backlog Grooming (for WP Media dev team use only)

• [ ] Reproduce the problem
• [ ] Identify the root cause
• [ ] Scope a solution
• [ ] Estimate the effort

[CrochetFeve0251]

Identify the root cause

The issue comes from the configuration of the msql server that are a maximum size of key inferior to what we expected.

Scope a solution

The solution will be to reduce the size of the index to 191. For that we can modify the following line in the UsedCSS to :

INDEX `queue_name_index` (`queue_name`(191)),

Estimate the effort

Effort XS