Database errors when promoting WP 3.9 to multisite

[l3rady]

Developed a single site install in version 3.8 and kept it updated through to 3.8.3

Then I needed to promote the site to multisite but before doing so I first upgraded the website to 3.9.

Upon promoting the site to multisite I got this error:

WordPress database error: [Incorrect table definition; there can be only one auto column and it must be defined as a key]
ALTER TABLE wp_signups ADD COLUMN signup_id bigint(20) NOT NULL auto_increment

WordPress database error: [Key column 'signup_id' doesn't exist in table]
ALTER TABLE wp_signups ADD PRIMARY KEY (signup_id)

After the table upgrade it has left the signup table in the following state:

CREATE TABLE `wp_signups` (
  `domain` varchar(200) NOT NULL default '',
  `path` varchar(100) NOT NULL default '',
  `title` longtext NOT NULL,
  `user_login` varchar(60) NOT NULL default '',
  `user_email` varchar(100) NOT NULL default '',
  `registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `activated` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` tinyint(1) NOT NULL default '0',
  `activation_key` varchar(50) NOT NULL default '',
  `meta` longtext,
  KEY `activation_key` (`activation_key`),
  KEY `domain` (`domain`),
  KEY `user_email` (`user_email`),
  KEY `user_login_email` (`user_login`,`user_email`),
  KEY `domain_path` (`domain`,`path`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I'm not using the signup functionality so this broken table won't affect me but certainly needs investigating into why this has happened.

[Denis-de-Bernardy]

The ID was introduced in r25179

[leiascofield]

This has happened on one of our sites also. We are not running multisite, but are running Buddypress.

This affected both 3.8.3 and 3.9.

From the error log:

[Thu Apr 17 17:23:40 2014] [warn] [client XX.XX.XX.XX] mod_fcgid: stderr: WordPress database error Incorrect table definition; there can be only one auto column and it must be defined as a key for query ALTER TABLE wp_signups ADD COLUMN signup_id bigint(20) NOT NULL auto_increment made by do_action('admin_init'), call_user_func_array, bp_admin_init, do_action('bp_admin_init'), call_user_func_array, bp_core_admin_components_settings_handler, bp_core_install, bp_core_install_signups, dbDelta, referer: ​http://URL/wp-admin/admin.php?page=bp-components&updated=true
[Thu Apr 17 17:23:40 2014] [warn] [client XX.XX.XX.XX] mod_fcgid: stderr: WordPress database error Key column 'signup_id' doesn't exist in table for query ALTER TABLE wp_signups ADD PRIMARY KEY (signup_id) made by do_action('admin_init'), call_user_func_array, bp_admin_init, do_action('bp_admin_init'), call_user_func_array, bp_core_admin_components_settings_handler, bp_core_install, bp_core_install_signups, dbDelta, referer: ​http://URL/wp-admin/admin.php?page=bp-components&updated=true

[Denis-de-Bernardy]

Replying to leiascofield:

We are not running multisite, but are running Buddypress.

Aren't they now using the signups table now to streamline activations and such on single site installs or something like that?

[Denis-de-Bernardy]

I've cross-posted the bug to BP here:

​https://buddypress.trac.wordpress.org/ticket/5563

[boonebgorges]

It's true that BP is using the wp_signups table to store signups on non-MS now, but it's not clear to me how that fact alone would result in this problem. Maybe there is another plugin that was in use on each of these websites that had independently created a table with the name wp_signups, but created it with a schema that did not match WP core's schema exactly. Then, when upgrading (to MS in the one case, and BP 2.0 in the other), dbDelta() is getting confused when attempting to apply the core schema to the existing table. (BP pulls its signups schema dynamically from wp_get_db_schema(), so it's not possible for there to be this kind of mismatch in the case of BuddyPress alone. ​https://buddypress.trac.wordpress.org/browser/tags/2.0/bp-core/admin/bp-core-schema.php#L351)

l3rady and leiascofield - What other plugins are you running? In particular, what plugins are you using that may modify the way that WP registration works?

[l3rady]

Other plugins I'm using are:

ACF
Gravity Forms
Gravity Forms User Registration Add On.
Post 2 posts

GF User Registration creates the table wp_signups table. It is more than likely this plugin is the culprit

[l3rady]

I have reported this bug to Rocketgenius, Inc.

[leiascofield]

I can confirm that I also had Gravity Forms and Gravity Forms User Registration Add On installed before installing BuddyPress.

[johnjamesjacoby]

This is a bummer.

In BuddyPress, we've taken precautions to:

• Avoid creating a new wp_signups table if already multisite
• Avoid creating a new wp_signups table if it already exists is some other way
• Create exactly the same wp_signups table that WordPress Multisite would make
• Use dbDelta to alter any existing table to match WordPress core's schema if necessary

Is it possible dbDelta isn't capable of handling this particular difference (hence why it was added to pre_schema_upgrade() in r25179?) If so, we will need to add extra checks in BuddyPress and include a similar pre_schema_upgrade() routine to run this for single-site installations. (It's an odd bit of integration with a niche application, but if it's something we can check for, I believe we should be doing so.)

Other alternatives:

• Improve dbDelta (I'm only mildly afraid to stab that dragon)
• Remove multisite checks from pre_schema_upgrade() and instead check for $wpdb->signups and/or $wpdb->get_results( "DESCRIBE {$wpdb->base_prefix}signups};" ) along with another version bump to rerun those ALTERs.

Today, this falls in BuddyPress's court to fix; we're going outside-of-the-wp_box to improve sign-ups. Tomorrow, since 2 plugins are solving this problem the same way, WordPress single-site should reconsider how sign-ups happen, likely adopting multisite's approach. (Ideally, single-site gets phased out completely in favor of multisite as the default experience, but that's a 5 year plan that warrants its own ticket.)

I'm comfortable closing this here, and moving discussion on the fix next-door to ​BuddyPress's trac.

[johnjamesjacoby]

Currently fixed in the BuddyPress 2.0 branch (for 2.0.1) and trunk:

• ​https://buddypress.trac.wordpress.org/changeset/8301 (2.0 branch)
• ​https://buddypress.trac.wordpress.org/changeset/8302 (trunk)