WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 3 years ago

Last modified 18 months ago

#10693 closed defect (bug) (maybelater)

Bug in dbDelta

Reported by: arena Owned by: ryan
Milestone: Priority: normal
Severity: major Version: 2.8.5
Component: Database Keywords: dev-feedback
Focuses: Cc:

Description

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)

Attachments (5)

dbDelta_bug.txt (7.4 KB) - added by arena 6 years ago.
Log_debug_20091114a.txt (55.3 KB) - added by arena 6 years ago.
first test of dbDelta
Log_debug_20091114b.txt (53.4 KB) - added by arena 6 years ago.
adding the trim
Log_debug_20091114c.txt (44.0 KB) - added by arena 6 years ago.
replacing tab by space at the end of column names
Log_debug_20091114d.txt (43.4 KB) - added by arena 6 years ago.
removing blank lines before index declarations

Download all attachments as: .zip

Change History (12)

@arena6 years ago

comment:1 @scribu6 years ago

  • Keywords needs-patch added
  • Milestone changed from 2.8.5 to 2.9
  • Priority changed from high to normal
  • Version set to 2.8.4

@arena6 years ago

first test of dbDelta

@arena6 years ago

adding the trim

@arena6 years ago

replacing tab by space at the end of column names

@arena6 years ago

removing blank lines before index declarations

comment:2 @arena6 years ago

  • Keywords dev-feedback added; needs-patch removed
  • Version changed from 2.8.4 to 2.8.5

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

comment:3 @ryan6 years ago

  • Milestone changed from 2.9 to Future Release

comment:4 @arena4 years ago

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>

comment:5 @arena3 years ago

  • Resolution set to maybelater
  • Status changed from new to closed

comment:6 @ocean903 years ago

  • Milestone Future Release deleted
Note: See TracTickets for help on using tickets.