Make WordPress Core

Opened 17 months ago

Last modified 6 months ago

#59481 new defect (bug)

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

Reported by: tristanleboss's profile tristanleboss Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 6.3
Component: Database Keywords: has-patch
Focuses: Cc:

Description

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"




Change History (2)

This ticket was mentioned in PR #5521 on WordPress/wordpress-develop by r-martins.


16 months ago
#1

  • Keywords has-patch added

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

#2 @lordspace
6 months ago

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.
....

Note: See TracTickets for help on using tickets.