[4.x]: When migrating columns, Craft is creating backup table names that are too long

[jaspertandy]

What happened?

Description

When Craft applies new project config, if the definition of a column has changed in any way, Craft backs up the old column values before migrating. This is done in the Fields service.

However, the Fields service does not have a check on the length of that backup table name, and if it exceeds 64 characters, MySQL does not like that.

Steps to reproduce

1. Create a new Matrix field with a long name: something like learnMoreItems worked for us!
2. Add a text field, call it text
3. Apply config changes
4. Change the text field definition to trigger a backup on next project-config/apply (it's very likely that the error happens here but I haven't seen that)
5. Run ./craft project-config/apply in a different environment

Expected behavior

The config applies with no error

Actual behavior

1059 Identifier name 'matrixcontent_learnmoreitems_field_block_text_zvjsmcfv_bak_1694160026' is too long

I have monkeypatched our instance with the following changes to the Fields service, but it's unlikely to be production-ready (it's fine for us for now though!):

        do {
            $suffix = $n === 1 ? '' : "_$n";
            $bakTable = "{{%{$shortTableName}_{$column}_bak_$timestamp$suffix}}";
            if (strlen($bakTable) > 64) {
                $bakTableDiff = strlen($bakTable) - 64;
                $shortColumn = substr($column, $bakTableDiff);
                $bakTable = "{{%{$shortTableName}_{$shortColumn}_bak_$timestamp$suffix}}";
            }
            $n++;
        } while ($db->tableExists($bakTable));

Craft CMS version

4.5.3

PHP version

8.2.10

Operating system and version

Docker php:8-fpm image

Database type and version

MySQL 5.7

Image driver and version

No response

Installed plugins and versions

No response

[brandonkelly]

Thanks for reporting that! This is now fixed for the next release. In your example, going forward that same table name would end up getting named:

matrixconte_learnmoreite_fie_blo_tex_frtgetp_bak_1694473659

[brandonkelly]

Craft 4.5.4 is out with that fix. Thanks again!

[jaspertandy]

Whoa thanks for sorting that so quickly @brandonkelly. Nice solution too

[CharlieGentle]

@brandonkelly We are still getting this error after updating to 4.5.4.

4.5.3 - craft_content_field_registeredUserPermissionMessage_bak_1694010052
4.5.4 - craft_conten_field_registeredUserPermissionMessage_bak_1694681794

[brandonkelly]

Whoops, didn’t account for table prefixes. Fixed now in 4.5.5.

[CharlieGentle]

Nice one, thanks