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

Status:Active» Fixed

fixed in git.

Status:Fixed» Closed (fixed)

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

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...

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).

Status:Needs work» Needs review

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

<?php
/**
* 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;
}
?>

Duplicate issue closed #1958898: Upgrade fatal error

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...

Title:Duplicate entry 0 for key PRIMARY in hosting_ip_addresses when installingDuplicate 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.

<?php
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.

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=&#039;127.0.0.1&#039;                                                                                                                                             [success]
UPDATE {hosting_ip_addresses} SET id=1 WHERE nid=2 AND ip_address=&#039;192.168.0.3&#039;                                                                                                                                           [success]
UPDATE {hosting_ip_addresses} SET id=2 WHERE nid=10 AND ip_address=&#039;192.168.0.3&#039;                                                                                                                                          [success]
ALTER TABLE {hosting_ip_addresses} ADD INDEX nid (nid)                                                                                                                                                                              [success]

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.

<?php
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:

<?php
db_query
("ALTER TABLE {hosting_ip_addresses} ENGINE=MyISAM");
?>

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.

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 &#039;1&#039; for key &#039;PRIMARY&#039;            [warning]
query: UPDATE hosting_ip_addresses SET id=1 WHERE nid=4 AND
ip_address=&#039;127.0.0.1&#039; database.mysqli.inc:134
Duplicate entry &#039;2&#039; for key &#039;PRIMARY&#039;            [warning]
query: UPDATE hosting_ip_addresses SET id=2 WHERE nid=4 AND
ip_address=&#039;127.0.0.1&#039; database.mysqli.inc:134
Duplicate entry &#039;3&#039; for key &#039;PRIMARY&#039;            [warning]
query: UPDATE hosting_ip_addresses SET id=3 WHERE nid=10 AND
ip_address=&#039;204.13.164.72&#039; 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=&#039;204.13.164.72&#039;
UPDATE {hosting_ip_addresses} SET id=1 WHERE nid=4 AND               [error]
ip_address=&#039;127.0.0.1&#039;
UPDATE {hosting_ip_addresses} SET id=2 WHERE nid=4 AND               [error]
ip_address=&#039;127.0.0.1&#039;
UPDATE {hosting_ip_addresses} SET id=3 WHERE nid=10 AND              [error]
ip_address=&#039;204.13.164.72&#039;
[...]
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;
}

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.