[wp-trac] [WordPress Trac] #34872: dbDelta Missing Index Name Creates Duplicate Indexes
WordPress Trac
noreply at wordpress.org
Mon Feb 29 22:02:30 UTC 2016
#34872: dbDelta Missing Index Name Creates Duplicate Indexes
--------------------------------------+------------------------------
Reporter: charlestonsw | Owner:
Type: defect (bug) | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Database | Version: 3.5.1
Severity: normal | Resolution:
Keywords: has-patch has-unit-tests | Focuses: performance
--------------------------------------+------------------------------
Comment (by charlestonsw):
As of 4.4.02 this is still an issue.
Correct Syntax
{{{
/**
* This is the approved WP 4.0 format.
*
* Double space after PRIMARY KEY
*
* Other indexes have KEY <keyname> (<key field>)
*
* Does NOT generate duplicate indexes.
* Does NOT generate database errors (see test 004 below)
*/
private function index_test_001() {
global $wpdb;
$table_name = $wpdb->prefix . 'dbdelta_test_001';
$wpdb_collate = $wpdb->collate;
$sql =
"CREATE TABLE {$table_name} (
id mediumint(8) unsigned NOT NULL auto_increment ,
first varchar(255) NULL,
PRIMARY KEY (id),
KEY first (first)
)
COLLATE {$wpdb_collate}";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta( $sql );
dbDelta( $sql );
}
}}}
Test That Fails
{{{
/**
* Skips index name on secondary index.
*
* AS OF WP 4.4.2 this produces TWO indexes named "first"
*/
private function index_test_003() {
global $wpdb;
$table_name = $wpdb->prefix . 'dbdelta_test_003';
$wpdb_collate = $wpdb->collate;
$sql =
"CREATE TABLE {$table_name} (
id mediumint(8) unsigned NOT NULL auto_increment ,
first varchar(255) NULL,
PRIMARY KEY (id),
KEY (first)
)
COLLATE {$wpdb_collate}";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta( $sql );
dbDelta( $sql );
}
}}}
Test That Works (using INDEX keyword) but generates WPDB duplicate key
notice
{{{
/**
* Uses INDEX instead of KEY on second index.
*
* AS OF WP 4.4.2 this generates and error in dbDelta
* DOES NOT create a duplicate index
* WordPress database error Duplicate key name 'first' for query ALTER
TABLE wp_dbdelta_test_004 ADD INDEX first (first)
*/
private function index_test_004() {
global $wpdb;
$table_name = $wpdb->prefix . 'dbdelta_test_004';
$wpdb_collate = $wpdb->collate;
$sql =
"CREATE TABLE {$table_name} (
id mediumint(8) unsigned NOT NULL auto_increment ,
first varchar(255) NULL,
PRIMARY KEY (id),
INDEX first (first)
)
COLLATE {$wpdb_collate}";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta( $sql );
dbDelta( $sql );
}
}}}
I have these tests (and others) in a private plugin I've created named
"db-delta-test". I'll post on Github if anyone is interested. Could be
useful for doing phpUnit testing if someone wants to help me convert it to
a proper phpUnit format.
--
Ticket URL: <https://core.trac.wordpress.org/ticket/34872#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list