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: |
|
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
#2
@
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.
....
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