dbDelta do useless request when type case is not the same between query and describe result

[tristanleboss]

While debugging why a call to dbDelta was not working, I discovered in the function output (an array of performed changes) that it was doing some useless changes: "Changed type of wp_table.field from varchar(255) to VARCHAR(255)".

Indeed, on my MySQL 8.0.31 Windows x64 version (installed by Wampserver), if I do "DESCRIBE wp_table", the "Type" column of the result is always lowercase. If somehow, you wrote your "CREATE TABLE" statements with a different case for varchar columns, dbDelta will detect a change. It does it only for varchar. If I change my varchar to be lowercase in the SQL I use for dbDelta, it doesn't try to change the type.

<?php

$sql = 'CREATE TABLE `'.$wpdb->prefix.'table` (
                        `object_id` INT(10) NOT NULL,
                        `group_term_slug` VARCHAR(255) NOT NULL
) '.$wpdb->get_charset_collate().';';

$result = dbDelta($sql); // Will contain both "Created table wp_table" and "Changed type of wp_table.field from varchar(255) to VARCHAR(255)"

$sql = 'CREATE TABLE `'.$wpdb->prefix.'table` (
                        `object_id` INT(10) NOT NULL,
                        `group_term_slug` varchar(255) NOT NULL
) '.$wpdb->get_charset_collate().';';

$result = dbDelta($sql); // Will NOT contain "Changed type of wp_table.field from varchar(255) to VARCHAR(255)", only "Created table wp_table"

This will fix an issue (already reported on trac), where queries ran by calling dbDelta($sql) are not executed if they start in lowercase. For example: create table foo..., insert into ...

Trac ticket: https://core.trac.wordpress.org/ticket/59481

[lordspace]

yeah, this is annoying.

This also creates multiple indexes too!

The indexes license_plate_normalized and license_plate_normalized_5 seem to be equal and one of them could possibly be removed.
The indexes license_plate and license_plate_5 seem to be equal and one of them could possibly be removed.
....

[leewillis77]

The PR mentioned is unrelated to this issue. The problem reported in this ticket appears to be down to an incorrect comparison in line 3204 of upgrade.php: ​https://github.com/WordPress/WordPress/blob/master/wp-admin/includes/upgrade.php#L3204

In my specific case the comparison

$tablefield->Type !== $fieldtype

is comparing 'longtext' with 'LONGTEXT'. Clearly these are the same types and no table alteration is required, but this comparison fails and an alter statement is generated needlessly. I suspect that changing $fieldtype to $fieldtype_without_parentheses would resolve this. If that seems sensible then patch to follow.

[leewillis77]

patch

[leewillis77]

Previous patch is incorrect. Revised patch attached.

[johnbillion]

Thanks for the patch @leewillis77 ! Would you mind opening a pull request on ​https://github.com/WordPress/wordpress-develop so the test suite can run? It would be great to get test coverage of this change too.

Fixes https://core.trac.wordpress.org/ticket/59481

Amends dbDelta so that differences of case-only in a field's type will not generate unnecessary alter table statements.

Trac ticket: https://core.trac.wordpress.org/ticket/59481

[leewillis77]

Updated patch to add unit test

[SergeyBiryukov]

In 60789:

Database: Do not unnecessarily alter table in dbDelta() for field type case differences.

This aims to avoid extra changes to database structure when type case is the only difference:

Changed type of wp_table.field from varchar(255) to VARCHAR(255)

Follow-up to [1575], [37532].

Props leewillis77, tristanleboss, lordspace, johnbillion, SergeyBiryukov.
Fixes #59481.