[wp-trac] [WordPress Trac] #22134: Update Network causes "WordPress database error Table 'wp_signups' already exists for query"

[WordPress Trac]

#22134: Update Network causes "WordPress database error Table 'wp_signups' already
exists for query"
---------------------------+------------------------------
 Reporter:  tomdxw         |       Owner:
     Type:  defect (bug)   |      Status:  new
 Priority:  normal         |   Milestone:  Awaiting Review
Component:  Network Admin  |     Version:
 Severity:  normal         |  Resolution:
 Keywords:  has-patch      |
---------------------------+------------------------------

Comment (by jamescollins):

 When upgrading our multisite install from v3.4.2 to v3.5.1, we encountered
 a lof of these errors too.

 The errors occurred when performing the network upgrade (/wp-
 admin/network/upgrade.php), and they occurred for each and every blog on
 the install that was upgraded (in our case 37 blogs).

 In other words, every time a sub blog's database was upgraded (by hitting
 /wp-admin/upgrade.php?step=upgrade_db), it generated the following
 database errors:


 {{{

 WordPress database error Table 'wp_users' already exists for query CREATE
 TABLE wp_users (\n  ID bigint(20) unsigned NOT NULL auto_increment,\n
 user_login varchar(60) NOT NULL default '',\n  user_pass varchar(64) NOT
 NULL default '',\n  user_nicename varchar(50) NOT NULL default '',\n
 user_email varchar(100) NOT NULL default '',\n  user_url varchar(100) NOT
 NULL default '',\n  user_registered datetime NOT NULL default '0000-00-00
 00:00:00',\n  user_activation_key varchar(60) NOT NULL default '',\n
 user_status int(11) NOT NULL default '0',\n  display_name varchar(250) NOT
 NULL default '',\n  spam tinyint(2) NOT NULL default '0',\n  deleted
 tinyint(2) NOT NULL default '0',\n  PRIMARY KEY  (ID),\n  KEY
 user_login_key (user_login),\n  KEY user_nicename (user_nicename)\n)
 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci made by wp_upgrade,
 make_db_current_silent, dbDelta

 WordPress database error Table 'wp_usermeta' already exists for query
 \nCREATE TABLE wp_usermeta (\n  umeta_id bigint(20) unsigned NOT NULL
 auto_increment,\n  user_id bigint(20) unsigned NOT NULL default '0',\n
 meta_key varchar(255) default NULL,\n  meta_value longtext,\n  PRIMARY KEY
 (umeta_id),\n  KEY user_id (user_id),\n  KEY meta_key (meta_key)\n)
 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci made by wp_upgrade,
 make_db_current_silent, dbDelta

 WordPress database error Table 'wp_blogs' already exists for query
 \nCREATE TABLE wp_blogs (\n  blog_id bigint(20) NOT NULL auto_increment,\n
 site_id bigint(20) NOT NULL default '0',\n  domain varchar(200) NOT NULL
 default '',\n  path varchar(100) NOT NULL default '',\n  registered
 datetime NOT NULL default '0000-00-00 00:00:00',\n  last_updated datetime
 NOT NULL default '0000-00-00 00:00:00',\n  public tinyint(2) NOT NULL
 default '1',\n  archived enum('0','1') NOT NULL default '0',\n  mature
 tinyint(2) NOT NULL default '0',\n  spam tinyint(2) NOT NULL default
 '0',\n  deleted tinyint(2) NOT NULL default '0',\n  lang_id int(11) NOT
 NULL default '0',\n  PRIMARY KEY  (blog_id),\n  KEY domain
 (domain(50),path(5)),\n  KEY lang_id (lang_id)\n) DEFAULT CHARACTER SET
 utf8 COLLATE utf8_general_ci made by wp_upgrade, make_db_current_silent,
 dbDelta

 WordPress database error Table 'wp_blog_versions' already exists for query
 \nCREATE TABLE wp_blog_versions (\n  blog_id bigint(20) NOT NULL default
 '0',\n  db_version varchar(20) NOT NULL default '',\n  last_updated
 datetime NOT NULL default '0000-00-00 00:00:00',\n  PRIMARY KEY
 (blog_id),\n  KEY db_version (db_version)\n) DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci made by wp_upgrade, make_db_current_silent,
 dbDelta

 WordPress database error Table 'wp_registration_log' already exists for
 query \nCREATE TABLE wp_registration_log (\n  ID bigint(20) NOT NULL
 auto_increment,\n  email varchar(255) NOT NULL default '',\n  IP
 varchar(30) NOT NULL default '',\n  blog_id bigint(20) NOT NULL default
 '0',\n  date_registered datetime NOT NULL default '0000-00-00 00:00:00',\n
 PRIMARY KEY  (ID),\n  KEY IP (IP)\n) DEFAULT CHARACTER SET utf8 COLLATE
 utf8_general_ci made by wp_upgrade, make_db_current_silent, dbDelta

 WordPress database error Table 'wp_site' already exists for query \nCREATE
 TABLE wp_site (\n  id bigint(20) NOT NULL auto_increment,\n  domain
 varchar(200) NOT NULL default '',\n  path varchar(100) NOT NULL default
 '',\n  PRIMARY KEY  (id),\n  KEY domain (domain,path)\n) DEFAULT CHARACTER
 SET utf8 COLLATE utf8_general_ci made by wp_upgrade,
 make_db_current_silent, dbDelta

 WordPress database error Table 'wp_sitemeta' already exists for query
 \nCREATE TABLE wp_sitemeta (\n  meta_id bigint(20) NOT NULL
 auto_increment,\n  site_id bigint(20) NOT NULL default '0',\n  meta_key
 varchar(255) default NULL,\n  meta_value longtext,\n  PRIMARY KEY
 (meta_id),\n  KEY meta_key (meta_key),\n  KEY site_id (site_id)\n) DEFAULT
 CHARACTER SET utf8 COLLATE utf8_general_ci made by wp_upgrade,
 make_db_current_silent, dbDelta

 WordPress database error Table 'wp_signups' already exists for query
 \nCREATE TABLE wp_signups (\n  domain varchar(200) NOT NULL default '',\n
 path varchar(100) NOT NULL default '',\n  title longtext NOT NULL,\n
 user_login varchar(60) NOT NULL default '',\n  user_email varchar(100) NOT
 NULL default '',\n  registered datetime NOT NULL default '0000-00-00
 00:00:00',\n  activated datetime NOT NULL default '0000-00-00 00:00:00',\n
 active tinyint(1) NOT NULL default '0',\n  activation_key varchar(50) NOT
 NULL default '',\n  meta longtext,\n  KEY activation_key
 (activation_key),\n  KEY domain (domain)\n) DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci made by wp_upgrade, make_db_current_silent,
 dbDelta
 }}}


 So it looks like each sub-blog database upgrade caused the multisite
 installation's global tables to be created even though they already
 existed.

 Our multisite install was originally a WPMU install which has been
 upgraded to multisite in 3.0 and upgraded to each version since then.


 For multisite installs that have 1000 of blogs, this would cause (8 x 1000
 =  8000) MySQL CREATE TABLE errors.

 It seems strange that dbDelta isn't detecting that those global multisite
 tables already exist.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/22134#comment:4>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software