Bug in dbDelta

[arena]

Once i created a table for a plugin.

Then i updated the plugin, modifying, adding and changing the order of the columns (just for code readability, as no effect on how the table is created) in the CREATE TABLE.

This last point resulted in a misinterpretation of what i intended to do by dbDelta code.

When the columns of the table to update are not in the same order than the original table,
dbDelta :

• considers it is a new column,
• generates a (ALTER TABLE ... ADD COLUMN ...) instead of a (ALTER TABLE ... CHANGE COLUMN ...)
• should return an error but can't get the caller (2.8.4)

[arena]

first test of dbDelta

[arena]

adding the trim

[arena]

replacing tab by space at the end of column names

[arena]

removing blank lines before index declarations

[arena]

Hi ryan,
I investigated a bit on that ticket, to notice the following :

• it is not a problem of column sequence. (Log_debug_20091114a.txt)

• columns name should be separated by at least a space from the other declarations (type, etc...). (some of my column names were ended by a tab, space is better)

• during my tests i changed following lines in dbDelta (Log_debug_20091114b.txt)

					if($validfield) {
						$cfields[strtolower($fieldname)] = trim($fld, ", \n");
					}

into

					if($validfield) {
						$cfields[trim(strtolower($fieldname))] = trim($fld, ", \n");
					}

• HOWEVER it seems that the multi index tables are not handled properly. (Log_debug_20091114c.txt & Log_debug_20091114d.txt)

i attach the logs of my different tests for future use if needed

[arena]

This code :

$queries = array();

$queries[] =
"CREATE TABLE $wpdb->mp_tracks (
 id                bigint(20)      UNSIGNED NOT NULL AUTO_INCREMENT,
 user_id           bigint(20)      NOT NULL default '0',
 mail_id           bigint(20)      NOT NULL default '0',
 tmstp             timestamp       NOT NULL default '0000-00-00 00:00:00',
 mmeta_id          bigint(20)      NOT NULL default '0',
 context           varchar(20)     NOT NULL default 'html',
 ip                varchar(100)    NOT NULL default '',
 agent             varchar(255)    NOT NULL default '',
 track             longtext,
 referrer          longtext,
 PRIMARY KEY (id),
 UNIQUE KEY id (id),
 KEY user_id  (user_id),
 KEY mail_id  (mail_id),
 KEY mmeta_id (mmeta_id)
) $charset_collate;";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($queries);

gives this :

<p class='wpdberror'><strong>WordPress database error:</strong> [Multiple primary key defined]<br />
<code>ALTER TABLE wp_mailpress_tracks ADD PRIMARY KEY (id)</code></p>
</div><div id='error'>
<p class='wpdberror'><strong>WordPress database error:</strong> [Duplicate key name &#039;user_id&#039;]<br />
<code>ALTER TABLE wp_mailpress_tracks ADD KEY user_id  (user_id)</code></p>
</div><div id='error'>
<p class='wpdberror'><strong>WordPress database error:</strong> [Duplicate key name &#039;mail_id&#039;]<br />
<code>ALTER TABLE wp_mailpress_tracks ADD KEY mail_id  (mail_id)</code></p>

[SergeyBiryukov]

Related: #10404, #27062.