Alter Table Always Expects a COLUMN; Crashes on a CONSTRAINT

[philsown]

Hello,

I'm attempting to activate a plugin I'm developing. The database creation scripts have CONSTRAINTs on them. When I attempt to reactivate, I'm getting this error:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `mytable_mycol_foreign` FOREIGN KEY (`mycol' at line 1]
ALTER TABLE wp_mytable ADD COLUMN CONSTRAINT `mytable_mycol_foreign` FOREIGN KEY (`mycol`) REFERENCES `myothertable` (`myothercol`)

As you can see the SQL error lies in ADD COLUMN CONSTRAINT.

This is being generated in wp-admin/includes/upgrade.php around line 2392

<?php
// For every remaining field specified for the table.
foreach ($cfields as $fieldname => $fielddef) {
        // Push a query line into $cqueries that adds the field to that table.
        $cqueries[] = "ALTER TABLE {$table} ADD COLUMN $fielddef";
        $for_update[$table.'.'.$fieldname] = 'Added column '.$table.'.'.$fieldname;
}

ADD COLUMN is hardcoded and is creating this SQL error. I googled for a solution but didn't find anything.

I've tried it with the constraints being part of the full table creation statement, and also as a stand alone statement, with the same results.

[pento]

Thanks for the bug report, @philsown!

Could you post the CREATE TABLE statement that's causing this?

[philsown]

Here's the SQL I'm running

<?php
$sql = '
CREATE TABLE `wp_vendor_plugin_activities` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `activity_type_id` int(10) unsigned NOT NULL,
    `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT \'0000-00-00 00:00:00\',
    `updated_at` timestamp NOT NULL DEFAULT \'0000-00-00 00:00:00\',
    PRIMARY KEY (`id`),
    KEY `vendor_plugin_activities_activity_type_id_foreign` (`activity_type_id`),
    CONSTRAINT `vendor_plugin_activities_activity_type_id_foreign` FOREIGN KEY (`activity_type_id`) REFERENCES `wp_vendor_plugin_activity_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci';

dbDelta($sql);

[philsown]

The fix in the meantime has been, of course and unfortunately, to remove the CONSTRAINT.