Opened 4 years ago
Last modified 2 years ago
#38936 new defect (bug)
Alter Table Always Expects a COLUMN; Crashes on a CONSTRAINT
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | normal | Version: | |
Component: | Upgrade/Install | Keywords: | needs-testing |
Focuses: | Cc: |
Description
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.
Change History (4)
#2
@
4 years ago
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);
Note: See
TracTickets for help on using
tickets.
Thanks for the bug report, @philsown!
Could you post the CREATE TABLE statement that's causing this?