This happens when Jenkins installs 2.x from head:

[127.0.0.1:2200] out: Duplicate entry '0' for key 'PRIMARY'              [warning]
[127.0.0.1:2200] out: query: INSERT INTO hosting_ip_addresses (nid, ip_address) VALUES (4,
[127.0.0.1:2200] out: '127.0.0.1') database.mysqli.inc:134

http://ci.aegirproject.org/job/D%20aegir%206.x-2.x%20install/466/console...

Comments

anarcat’s picture

Status: Active » Fixed

fixed in git.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

helmo’s picture

Status: Closed (fixed) » Needs work

Sorry to re-open this, but this commit broke my upgrade. from 1.9 to 2.x.

I've had to change the db_add_field line in hosting_server_update_6200() to

-  db_add_field($ret, "hosting_ip_addresses", "id", array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE));
+  db_add_field($ret, "hosting_ip_addresses", "id", array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0));
Executing hosting_server_update_6200                                                                                                                                      [success]
Incorrect table definition; there can be only one auto column and it must be defined as a key                                                                             [warning]
query: ALTER TABLE hosting_ip_addresses ADD `id` INT unsigned auto_increment DEFAULT NULL database.mysqli.inc:134
Unknown column 'id' in 'hosting_ip_addresses'                                                                                                         [warning]

Hopefully we can get a Jenkins test up and running for this...

omega8cc’s picture

Here is a working/tested fix for this problem: http://drupal.org/node/1932616#comment-7212772

My patch fixes both install and upgrades (at least in my tests).

omega8cc’s picture

Status: Needs work » Needs review

Here is a working code which fixes all issues we have experienced:

/**
 * Add primary key for IPs, drop versionning
 */
function hosting_server_update_6200() {
  $ret = array();
  db_drop_index($ret, 'hosting_ip_addresses', 'vid');
  db_drop_field($ret, 'hosting_ip_addresses', 'vid');
  db_query("ALTER TABLE {hosting_ip_addresses} ENGINE=MyISAM");
  db_query("ALTER IGNORE TABLE {hosting_ip_addresses} ADD UNIQUE INDEX(nid)");
  db_add_field($ret, 'hosting_ip_addresses', 'id', array('type' => 'int', 'not null' => TRUE));
  db_change_field($ret, 'hosting_ip_addresses', 'id', 'id',
    array(
      'type' => 'serial',
      'unsigned' => TRUE,
      'not null' => TRUE,
    ),
    array(
      'primary key' => array('id'),
    )
  );
  $r = db_query("SELECT DISTINCT nid, ip_address FROM {hosting_ip_addresses} ORDER BY nid ASC");
  for ($i = 0; $row = db_fetch_object($r); $i++) {
    $ret[] = update_sql("UPDATE IGNORE {hosting_ip_addresses} SET id=$i WHERE nid={$row->nid} AND ip_address='{$row->ip_address}'");
  }
  db_query("ALTER TABLE {hosting_ip_addresses} ENGINE=InnoDB");
  return $ret;
}
omega8cc’s picture

Duplicate issue closed #1958898: Upgrade fatal error

anarcat’s picture

Status: Needs review » Needs work

We would need a patch here.. But anyways, why do we do a db_add_field() followed by a db_change_field()? helmo seemed to be saying he had a patch to that effect...

anarcat’s picture

Title: Duplicate entry 0 for key PRIMARY in hosting_ip_addresses when installing » Duplicate entry 0 for key PRIMARY in hosting_ip_addresses when installing / upgrading

Here's the patch I am working on in tandem with #1932616: IPs deleted from hosting_ip_addresses table on server verify.

diff --git a/modules/hosting/server/hosting_server.install b/modules/hosting/server/hosting_server.install
index 0dfe612..76fc0c1 100644
--- a/modules/hosting/server/hosting_server.install
+++ b/modules/hosting/server/hosting_server.install
@@ -215,13 +215,13 @@ function hosting_server_update_6005() {
  */
 function hosting_server_update_6200() {
   $ret = array();
-  db_add_field($ret, "hosting_ip_addresses", "id", array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE));
+  db_drop_index($ret, 'hosting_ip_addresses', 'vid');
   db_drop_field($ret, "hosting_ip_addresses", "vid");
+  db_add_field($ret, "hosting_ip_addresses", "id", array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE), array('primary key' => array('id')));
   $r = db_query("SELECT nid, ip_address FROM {hosting_ip_addresses}");
   for ($i = 0; $row = db_fetch_object($r); $i++) {
     $ret[] = update_sql("UPDATE {hosting_ip_addresses} SET id=$i WHERE nid={$row->nid} AND ip_address='{$row->ip_address}'");
   }
-  db_add_primary_key($ret, 'hosting_ip_addresses', array('id'));
   db_add_index($ret, 'hosting_ip_addresses', 'nid', array('nid'));
   return $ret;
 }

I'll test install and upgrade now.

anarcat’s picture

Status: Needs work » Needs review

It seems this patch works - at least for upgrades, i'll push and let jenkins test the install.

Executing hosting_server_update_6200                                                                                                                                                                                                [success]
ALTER TABLE {hosting_ip_addresses} DROP INDEX vid                                                                                                                                                                                   [success]
ALTER TABLE {hosting_ip_addresses} DROP vid                                                                                                                                                                                         [success]
ALTER TABLE {hosting_ip_addresses} ADD `id` INT unsigned auto_increment DEFAULT NULL, ADD PRIMARY KEY (id)                                                                                                                          [success]
ALTER TABLE {hosting_ip_addresses} CHANGE `id` `id` INT unsigned NOT NULL auto_increment                                                                                                                                            [success]
UPDATE {hosting_ip_addresses} SET id=0 WHERE nid=4 AND ip_address='127.0.0.1'                                                                                                                                             [success]
UPDATE {hosting_ip_addresses} SET id=1 WHERE nid=2 AND ip_address='192.168.0.3'                                                                                                                                           [success]
UPDATE {hosting_ip_addresses} SET id=2 WHERE nid=10 AND ip_address='192.168.0.3'                                                                                                                                          [success]
ALTER TABLE {hosting_ip_addresses} ADD INDEX nid (nid)                                                                                                                                                                              [success]
omega8cc’s picture

I have used this as a reference: http://api.drupal.org/comment/16549#comment-16549

It fixed the problem for me, so there was no error and upgrade reverted, because it was not possible to do it in a one step.

But how do you test upgrades? From 1.x or at least from 2.x before SSL refactoring?

Note also that this fixes otherwise critical issue with duplicate records - it just removes extra duplicates on the fly.

db_query("ALTER IGNORE TABLE {hosting_ip_addresses} ADD UNIQUE INDEX(nid)");

But, surprise, it doesn't work with InnoDB (at least the XtraDB used in Percona/MariaDB), so it was required to temporarily convert the table to MyISAM to get it working:

db_query("ALTER TABLE {hosting_ip_addresses} ENGINE=MyISAM");
anarcat’s picture

Status: Needs review » Fixed

There doesn't seem to be any warnings in jenkins: http://ci.aegirproject.org/job/D%20aegir%206.x-2.x%20install/479/console...

yay! thanks for your patience all..

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

lavamind’s picture

Issue summary: View changes
Status: Closed (fixed) » Needs work

Reopening this issue as I've hit the bug upon upgrading from 1.11 to 2.0 via Debian packages, and using a MySQL DB.

Executing hosting_platform_update_6208                               [success]
ALTER TABLE {hosting_platform} DROP release_id                       [success]
Executing hosting_server_update_6200                                 [success]
Duplicate entry '1' for key 'PRIMARY'            [warning]
query: UPDATE hosting_ip_addresses SET id=1 WHERE nid=4 AND
ip_address='127.0.0.1' database.mysqli.inc:134
Duplicate entry '2' for key 'PRIMARY'            [warning]
query: UPDATE hosting_ip_addresses SET id=2 WHERE nid=4 AND
ip_address='127.0.0.1' database.mysqli.inc:134
Duplicate entry '3' for key 'PRIMARY'            [warning]
query: UPDATE hosting_ip_addresses SET id=3 WHERE nid=10 AND
ip_address='204.13.164.72' database.mysqli.inc:134
[...]
ALTER TABLE {hosting_ip_addresses} DROP INDEX vid                    [success]
ALTER TABLE {hosting_ip_addresses} DROP vid                          [success]
ALTER TABLE {hosting_ip_addresses} ADD `id` INT unsigned             [success]
auto_increment DEFAULT NULL, ADD PRIMARY KEY (id)
ALTER TABLE {hosting_ip_addresses} CHANGE `id` `id` INT unsigned NOT [success]
NULL auto_increment
UPDATE {hosting_ip_addresses} SET id=0 WHERE nid=2 AND               [success]
ip_address='204.13.164.72'
UPDATE {hosting_ip_addresses} SET id=1 WHERE nid=4 AND               [error]
ip_address='127.0.0.1'
UPDATE {hosting_ip_addresses} SET id=2 WHERE nid=4 AND               [error]
ip_address='127.0.0.1'
UPDATE {hosting_ip_addresses} SET id=3 WHERE nid=10 AND              [error]
ip_address='204.13.164.72'
[...]
ALTER TABLE {hosting_ip_addresses} ADD INDEX nid (nid)               [success]
'all' cache was cleared in                                           [success]
/var/aegir/hostmaster-6.x-2.0#aegir.lib3.net
Finished performing updates.                                         [ok]
Drush was not able to start (bootstrap) the Drupal database.         [error]

Full error log here : http://paste.debian.net/80582/

It seems upon altering the table to use auto_increment (type change to serial), an id is immediately and automatically assigned to every row. When running the UPDATE statements (which do not seem to follow any particular ordering) following that, if those happen to match the automatically assigned numbers, no problem, but in my case they did not, hence the error.

After applying this patch, the error went away and the updated succeeded. I checked the hosting_ip_addresses table and id's were correctly assigned to every entry.

--- a/hosting_server.install	2014-02-06 13:51:51.960819128 -0500
+++ b/hosting_server.install	2014-02-06 15:12:04.216819609 -0500
@@ -216,10 +216,6 @@
   db_drop_index($ret, 'hosting_ip_addresses', 'vid');
   db_drop_field($ret, "hosting_ip_addresses", "vid");
   db_add_field($ret, "hosting_ip_addresses", "id", array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE), array('primary key' => array('id')));
-  $r = db_query("SELECT nid, ip_address FROM {hosting_ip_addresses}");
-  for ($i = 0; $row = db_fetch_object($r); $i++) {
-    $ret[] = update_sql("UPDATE {hosting_ip_addresses} SET id=$i WHERE nid={$row->nid} AND ip_address='{$row->ip_address}'");
-  }
   db_add_index($ret, 'hosting_ip_addresses', 'nid', array('nid'));
   return $ret;
 }
anarcat’s picture

Version: 6.x-2.x-dev » 6.x-2.0
Status: Needs work » Needs review

Sounds good, we should test on another host before shipping.

  • Commit 89ae01b on 6.x-2.x, 7.x-3.x, dev-588728-views-integration, dev-1403208-new_roles, dev-helmo-3.x by anarcat:
    fix the serial identifier for ip addresses, see #1930670
    
  • Commit d517efa on 6.x-2.x, 7.x-3.x, dev-588728-views-integration, dev-1403208-new_roles, dev-helmo-3.x by anarcat:
    fix serial field creation on upgrade, see #1930670
    

  • Commit 89ae01b on 6.x-2.x, 7.x-3.x, dev-588728-views-integration, dev-1403208-new_roles, dev-helmo-3.x by anarcat:
    fix the serial identifier for ip addresses, see #1930670
    
  • Commit d517efa on 6.x-2.x, 7.x-3.x, dev-588728-views-integration, dev-1403208-new_roles, dev-helmo-3.x by anarcat:
    fix serial field creation on upgrade, see #1930670
    
helmo’s picture

Status: Needs review » Fixed

This is already in ...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

thewilkybarkid’s picture

Status: Closed (fixed) » Needs work

I've just hit the same problem mentioned in #13. Applying that patch let it work the second time.

  • anarcat committed 89ae01b on 7.x-3.x-2345987
    fix the serial identifier for ip addresses, see #1930670
    
  • anarcat committed d517efa on 7.x-3.x-2345987
    fix serial field creation on upgrade, see #1930670
    
ergonlogic’s picture

Status: Needs work » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.