On a fresh install of HEAD with only one 6.14 platform, I've duplicate rows of packages in hosting_package. The most obvious case of this is in the site form, as there are two 'default' install profiles listed (and listed because > 1 profile means the profile form item isn't hidden). Closer inspection reveals 2 of every package in hosting_package.

mysql> SELECT * FROM hosting_package LIMIT 20;
+-----+-----+--------------+------------+-------------+
| vid | nid | package_type | short_name | description |
+-----+-----+--------------+------------+-------------+
|   4 |   4 | platform     | drupal     |             | 
|  10 |   8 | module       | aggregator |             | 
|  11 |   9 | module       | user       |             | 
|  12 |  10 | module       | user       |             | 
|  13 |  11 | module       | system     |             | 
|  14 |  12 | module       | system     |             | 
|  15 |  13 | module       | tracker    |             | 
|  16 |  14 | module       | tracker    |             | 
|  17 |  15 | module       | comment    |             | 
|  18 |  16 | module       | comment    |             | 
|  19 |  17 | module       | help       |             | 
|  20 |  18 | module       | help       |             | 
|  21 |  19 | module       | profile    |             | 
|  22 |  20 | module       | profile    |             | 
|  23 |  21 | module       | forum      |             | 
|  24 |  22 | module       | forum      |             | 
|  25 |  23 | module       | menu       |             | 
|  26 |  24 | module       | menu       |             | 
|  27 |  25 | module       | search     |             | 
|  28 |  26 | module       | search     |             | 
+-----+-----+--------------+------------+-------------+
20 rows in set (0.01 sec)

This might not be in Hosting, but I have no idea where this is being caused and I haven't look too closely yet, just getting it down before I forget.

Comments

Anonymous’s picture

It looks like 2 Verify tasks are being created for the platform on fresh install!?

The second one actually throws error but the task is 'successful'

< snip >
Can't create database 'provision_test'; database exists query: CREATE DATABASE provision_test
Can't drop database 'provision_test'; database doesn't exist query: DROP DATABASE `provision_test`
Failed to drop database provision_test
Mysql can create new databases.
Found existing drushrc.php file
Changed permissions of drushrc.php to 0600
Drushrc file (/var/aegir/drupal-6.14/drushrc.php) was written successfully
Changed permissions of drushrc.php to 0400
Command dispatch complete
Removing task from hosting queue
Command dispatch complete
Anonymous’s picture

Further testing: it is definitely instigated on the initial 'hosting setup' command or something spawned from it. Getting closer

Anonymous’s picture

Still haven't worked out why it looks like a verify is occuring twice. Here's an excerpt from a mysql log http://drupalbin.com/11477

I just installed an 0.4 alpha1 system and the problem exists there too, so it's not a recent HEAD commit

Anonymous’s picture

Title: Duplicate packages in hosting_package » Duplicate rows in node, hosting_package tables
adrian’s picture

commited the fix in both hostmaster and hosting

i don't know how to fix this for existing installs yet.

adrian’s picture

Status: Active » Needs work
Issue tags: +Upgrade path

putting this to needs work now till we sort out the upgrade path

Anonymous’s picture

Status: Needs work » Fixed
Issue tags: -Upgrade path

So I did an install of 0.3 which did not have this problem.

Then I patched the 0.3 install with the Ports patches from #515052: Customizable port field per site and did a re-install and the duplication occurs.

Adrian has a fix to prevent two node_save()'s by adding (I think) a no_verify flag or somesuch.

Meanwhile I have been doggedly trying to understand how this could happen and I think I might've found it:

  function hosting_web_server_update($node) {
  // if this is a new node or we're adding a new revision,
  if ($node->revision) {
    hosting_web_server_insert($node);
  }
  else {
    $oldports = db_result(db_query("SELECT ports FROM {hosting_web_server} WHERE vid = %d", $node->vid));
    if ($oldports != $node->ports) {
      $query = db_query("SELECT {nid} FROM hosting_platform WHERE web_server = %d", $node->nid);
      while($nid = db_fetch_object($query)) {
        hosting_add_task($nid->nid, 'verify');
      }
    }
...

This query from just after submitting the Web Server and File System tasks in the Hostmaster install:

mysql> select * from hosting_task;
 +-----+-----+-----------+-----+-------------+----------+
 | vid | nid | task_type | rid | task_status | executed |
 +-----+-----+-----------+-----+-------------+----------+
 |   6 |   6 | verify    |   5 |           0 |        0 |
 |   7 |   7 | verify    |   5 |           0 |        0 |
 +-----+-----+-----------+-----+-------------+----------+

Maybe somehow we are triggering that hosting_add_task($nid->nid, 'verify');, it causes two Verify tasks as seen on a fresh install, and I assume what branches from this is the duplication of packages in the node and hosting_package tables. Strange because the hosting_web_server table shows 'ports' field as being 80 (I didn't add any others)

Adrian notes that this could occur in the frontend as well, in that one can create a site and then immediately create a new verify task. He's committed a fix for the hostmaster profile and to Hosting to stop the duplicate verify task, along with the complete refactoring of the UI as well

Anonymous’s picture

Status: Fixed » Needs work
Issue tags: +Upgrade path

Adrian beat me to the comment submission and I wiped his metachanges to the ticket.

anarcat’s picture

Status: Needs work » Needs review

I feel this is fixed: if adrian fixed the duplicate task stuff, then it's fixed. Otherwise I have worked on the task interface so that tasks are revisionned properly (#422970: have one task type per site node) and that should also fix the problem.

Anonymous’s picture

@anarcat: The issue is that though the bug is fixed, anyone who's installed 0.4 alpha1 or running off HEAD has duplicate rows for every package in the node and hosting_package table. We need to write a hook_update() for the next release that somehow fixes these tables.

jonhattan’s picture

In my fresh 0.4 alpha 1 install those packages are not duplicated:

+-----+-----+--------------+---------------------+-------------+
| vid | nid | package_type | short_name          | description |
+-----+-----+--------------+---------------------+-------------+
|   4 |   4 | platform     | drupal              |             | 
|  10 |   8 | module       | user                |             | 
|  11 |   9 | module       | upload              |             | 
|  12 |  10 | module       | update              |             | 
|  13 |  11 | module       | trigger             |             | 
|  14 |  12 | module       | translation         |             | 
|  15 |  13 | module       | tracker             |             | 
|  16 |  14 | module       | throttle            |             | 
|  17 |  15 | module       | taxonomy            |             | 
|  18 |  16 | module       | system              |             | 
|  19 |  17 | module       | syslog              |             | 
|  20 |  18 | module       | statistics          |             | 
|  21 |  19 | module       | search              |             | 
|  22 |  20 | module       | profile             |             | 
|  23 |  21 | module       | poll                |             | 
|  24 |  22 | module       | ping                |             | 
|  25 |  23 | module       | php                 |             | 
|  26 |  24 | module       | path                |             | 
|  27 |  25 | module       | openid              |             | 
|  28 |  26 | module       | node                |             | 
|  29 |  27 | module       | menu                |             | 
|  30 |  28 | module       | locale              |             | 
|  31 |  29 | module       | help                |             | 
|  32 |  30 | module       | forum               |             | 
|  69 |  67 | module       | admin_menu          |             | 
|  70 |  68 | module       | install_profile_api |             | 
|  71 |  69 | module       | hosting_web_server  |             | 
|  72 |  70 | module       | hosting_task        |             | 
|  73 |  71 | module       | hosting_ssl         |             | 
|  74 |  72 | module       | hosting_site        |             | 
|  75 |  73 | module       | hosting_signup      |             | 
|  76 |  74 | module       | hosting_platform    |             | 
|  77 |  75 | module       | hosting_package     |             | 
|  78 |  76 | module       | hosting_migrate     |             | 
|  79 |  77 | module       | hosting_db_server   |             | 
|  80 |  78 | module       | hosting_cron        |             | 
|  81 |  79 | module       | hosting_clone       |             | 
|  82 |  80 | module       | hosting_client      |             | 
|  83 |  81 | module       | hosting_alias       |             | 
|  84 |  82 | module       | hosting             |             | 
|  85 |  83 | theme        | eldir               |             | 
+-----+-----+--------------+---------------------+-------------+

Those one are the duplicates:


+-----+-----+--------------+---------------------+-------------+
| vid | nid | package_type | short_name          | description |
+-----+-----+--------------+---------------------+-------------+
|  33 |  31 | module       | filter              |             | 
|  34 |  32 | module       | filter              |             | 
|  35 |  33 | module       | dblog               |             | 
|  36 |  34 | module       | dblog               |             | 
|  37 |  35 | module       | contact             |             | 
|  38 |  36 | module       | contact             |             | 
|  39 |  37 | module       | comment             |             | 
|  40 |  38 | module       | comment             |             | 
|  41 |  39 | module       | color               |             | 
|  42 |  40 | module       | color               |             | 
|  43 |  41 | module       | book                |             | 
|  44 |  42 | module       | book                |             | 
|  45 |  43 | module       | blogapi             |             | 
|  46 |  44 | module       | blogapi             |             | 
|  47 |  45 | module       | blog                |             | 
|  48 |  46 | module       | blog                |             | 
|  49 |  47 | module       | block               |             | 
|  50 |  48 | module       | block               |             | 
|  51 |  49 | module       | aggregator          |             | 
|  52 |  50 | module       | aggregator          |             | 
|  53 |  51 | theme        | pushbutton          |             | 
|  54 |  52 | theme        | pushbutton          |             | 
|  55 |  53 | theme        | minnelli            |             | 
|  56 |  54 | theme        | minnelli            |             | 
|  57 |  55 | theme        | garland             |             | 
|  58 |  56 | theme        | garland             |             | 
|  59 |  57 | theme        | marvin              |             | 
|  60 |  58 | theme        | marvin              |             | 
|  62 |  60 | theme        | chameleon           |             | 
|  61 |  59 | theme        | chameleon           |             | 
|  63 |  61 | theme        | bluemarine          |             | 
|  64 |  62 | theme        | bluemarine          |             | 
|  65 |  63 | profile      | default             |             | 
|  66 |  64 | profile      | default             |             | 
|  67 |  65 | profile      | hostmaster          |             | 
|  68 |  66 | profile      | hostmaster          |             | 
+-----+-----+--------------+---------------------+-------------+

This is distinct from mig5's dump. At least `forum`is duplicate for mig5's and not for me.

Those are all tasks that have run out here:

Verify aegir.example.com
Verify aegir.example.com (Drupal 6.14)
Verify aegir.example.com
Import aegir.example.com
Verify aegir.example.com (Drupal 6.14)
jonhattan’s picture

Probably I miss something but following query results in all packages that are alone in hosting_package and are the ones to be deleted:

SELECT nid FROM hosting_package WHERE nid NOT IN (SELECT package_id FROM hosting_package_instance);

if that is true, following update should do the work:

// Remove duplicate packages introduced by a double validation of platform in 0.4 alpha 1
function hosting_package_update_11() {
  $ret = array();
  $result = db_query("DELETE FROM hosting_package WHERE nid NOT IN (SELECT package_id FROM hosting_package_instance)");

  return $ret;
}

Anyway this is so simple that probably I'm missing some big thing :)

jonhattan’s picture

StatusFileSize
new1.04 KB

In addition to `hosting_package` duplication is also in `node` and `node_revisions`.

jonhattan’s picture

StatusFileSize
new1.08 KB

Previous patch was wrong.

I do use raw sql to delete the nodes in order to use update_sql() and show a pretty message but probably usage of node_delete() will be better option.

there's also duplication in hosting_package_instance:

mysql> select iid, rid, package_id, filename from hosting_package_instance where filename like '%contact%';
+-----+-----+------------+-------------------------------------------------------+
| iid | rid | package_id | filename                                              |
+-----+-----+------------+-------------------------------------------------------+
|  39 |   5 |         35 | /var/aegir/drupal-6.14/modules/contact/contact.module | 
| 105 |  48 |         35 | /var/aegir/drupal-6.14/modules/contact/contact.module | 
| 166 |  51 |         35 | /var/aegir/drupal-6.14/modules/contact/contact.module | 
| 242 |  84 |         35 | /var/aegir/drupal-6.14/modules/contact/contact.module | 
+-----+-----+------------+-------------------------------------------------------+

I think `rid` is the platform nid. Nodes 48, 51 and 84 are packages, not platforms. All of those package instances are also to be removed, isn't it?

SELECT iid FROM hosting_package_instance WHERE rid NOT IN (SELECT nid FROM hosting_platform);
Anonymous’s picture

Status: Needs review » Needs work

Quick note

Just tested this on a alpha 0.1 release where I could reproduce the bug. I get sql errors on update.php



    * user warning: 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 ')' at line 1 query: DELETE FROM hosting_package WHERE nid IN () in /var/aegir/drupal-6.14/profiles/hostmaster/modules/hosting/package/hosting_package.install on line 256.
    * user warning: 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 ')' at line 1 query: DELETE FROM node WHERE nid IN () in /var/aegir/drupal-6.14/profiles/hostmaster/modules/hosting/package/hosting_package.install on line 256.
    * user warning: 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 ')' at line 1 query: DELETE FROM node_revisions WHERE nid IN () in /var/aegir/drupal-6.14/profiles/hostmaster/modules/hosting/package/hosting_package.install on line 256.

Updates were attempted. If you see no failures below, you may proceed happily to the administration pages. Otherwise, you may need to update your database manually. All errors have been logged.

    * Main page
    * Administration pages

The following queries were executed
hosting_package module
Update #11

    * Failed: DELETE FROM {hosting_package} WHERE nid IN ()
    * Failed: DELETE FROM {node} WHERE nid IN ()
    * Failed: DELETE FROM {node_revisions} WHERE nid IN (

mysql> SELECT nid FROM hosting_package WHERE nid NOT IN (SELECT package_id FROM hosting_package_instance);
Empty set (0.01 sec)

jonhattan’s picture

StatusFileSize
new1.13 KB

mig5: your failure is because there is no duplication... I've added a condition to avoid that.

Still pending code to remove those SELECT iid FROM hosting_package_instance WHERE rid NOT IN (SELECT nid FROM hosting_platform); as I said in #14. I still dont understand the underlying logic on how did all of this happen.

Anonymous’s picture

The problem being I *do* have duplication, I'm looking at it :)

Here's my hosting_package and node tables.

Yes I had the two verify tasks for the first platform etc too.

adrian’s picture

just deleting the nodes should delete the instances.

the issue is the existing nodes with wrong references, which is basically all sites pointing to the wrong profile node.

jonhattan’s picture

StatusFileSize
new1.64 KB

current patch does node_delete to remove all packages, truncate tables hosting_package_{instance,languages} and schedule a reverify for platforms.

After verification duplication still exists, but in a diferente flavour (note not all of us has the same duplication issue): now I have no duplicates in hosting_package but hosting_package_languages content is:

+-----+----------+
| iid | language |
+-----+----------+
|  45 | en       | 
|  46 | en       | 
|  92 | en       | 
|  93 | en       | 
| 156 | en       | 
| 157 | en       | 
| 203 | en       | 
| 204 | en       | 
| 250 | en       | 
| 251 | en       | 
+-----+----------+

those iid are in fact duplicates in hosting_package_instance.

mysql> select iid,rid,package_id, filename from hosting_package_instance where iid in (select iid from hosting_package_languages);
+-----+-----+------------+---------------------------------------------------------------+
| iid | rid | package_id | filename                                                      |
+-----+-----+------------+---------------------------------------------------------------+
|  45 |   5 |        135 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
|  46 |   5 |        136 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
|  92 |  45 |        135 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
|  93 |  45 |        136 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
| 156 |  46 |        135 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
| 157 |  46 |        136 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
| 203 |  84 |        135 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
| 204 |  84 |        136 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
| 250 | 154 |        135 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
+-----+-----+------------+---------------------------------------------------------------+

I updated to HEAD prior to running update.php and result is the same.

univate’s picture

StatusFileSize
new1.15 KB

Here is a db dumps of what I see with one package that is duplicated from apha1:
http://drupalbin.com/11650

The attached patch fixed the issue for me of duplicate packages appearing in site and platform lists on the site as well as duplicates in the node and hosting_package db tables although there appears to still be issues in the database records that don't make sense to me in hosting_package_instances:

Take for example package_id#35 which is the filter module

mysql> select * from hosting_package_instance where package_id=35;
+-----+-----+------------+-----------------------------------------------------+----------------+---------+--------+
| iid | rid | package_id | filename                                            | schema_version | version | status |
+-----+-----+------------+-----------------------------------------------------+----------------+---------+--------+
|  34 |   5 |         35 | /var/aegir/drupal-6.14/modules/filter/filter.module |              1 | 6.14    |      0 | 
| 108 |  44 |         35 | /var/aegir/drupal-6.14/modules/filter/filter.module |              1 | 6.14    |      0 | 
| 224 |  80 |         35 | /var/aegir/drupal-6.14/modules/filter/filter.module |              1 | 6.14    |      0 | 
+-----+-----+------------+-----------------------------------------------------+----------------+---------+--------+

nid#5 - platform
nid#44 - Minnelli theme
nid#80 - site

I don't understand the Minnelli theme being in this list?

univate’s picture

From testing #19:

It removes the duplicates from the hosting frontend site/platform package tables.

After this is run there are a couple of extra records in hosting_package_instance (which means there are also these records in the language table)

mysql> select i.iid,i.rid,i.package_id, i.filename, n.title, n.type from hosting_package_instance i LEFT JOIN node n ON n.nid=i.rid where i.iid in (select iid from hosting_package_languages);
+-----+-----+------------+---------------------------------------------------------------+------------------------------------+----------+
| iid | rid | package_id | filename                                                      | title                              | type     |
+-----+-----+------------+---------------------------------------------------------------+------------------------------------+----------+
|  40 |   5 |        110 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | aegir2.univate.local (Drupal 6.14) | platform | 
|  41 |   5 |        111 | /var/aegir/drupal-6.14/profiles/default/default.profile       | aegir2.univate.local (Drupal 6.14) | platform | 
|  99 |  40 |        110 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | NULL                               | NULL     | 
| 100 |  40 |        111 | /var/aegir/drupal-6.14/profiles/default/default.profile       | NULL                               | NULL     | 
| 141 |  41 |        110 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | NULL                               | NULL     | 
| 142 |  41 |        111 | /var/aegir/drupal-6.14/profiles/default/default.profile       | NULL                               | NULL     | 
| 183 |  66 |        110 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | aegir2.univate.local               | site     | 
| 245 |  66 |        111 | /var/aegir/drupal-6.14/profiles/default/default.profile       | aegir2.univate.local               | site     | 
| 242 | 130 |        111 | /var/aegir/drupal-6.14/profiles/default/default.profile       | site10.univate.local               | site     | 
| 244 | 130 |        110 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | site10.univate.local               | site     | 
+-----+-----+------------+---------------------------------------------------------------+------------------------------------+----------+
10 rows in set (0.01 sec)

You can see the records above that don't match up with any sites or platforms.

jonhattan’s picture

I've realized that in alpha1 the content of hosting_package_languages is 16 rows instead of 8 after patching, updating and reverifying.

mysql> select count(*) from hosting_package_languages;
+----------+
| count(*) |
+----------+
|       16 | 
+----------+

So I could spect 20 results insted of 10 in

mysql> select count(*) from hosting_package_instance i LEFT JOIN node n ON n.nid=i.rid where i.iid in (select iid from hosting_package_languages);
+----------+
| count(*) |
+----------+
|       13 | 
+----------+

no luck. If there are 13... where are the rest? Those ones seems orphaned rows in hosting_package_languages:

mysql> select * from hosting_package_languages where iid not in (select iid from hosting_package_instance);
+-----+----------+
| iid | language |
+-----+----------+
| 317 | en       | 
| 322 | en       | 
| 323 | en       | 
+-----+----------+

So so so, what I want to conclude is that this is a separate issue. I've just installed HEAD and it's confirmed that hosting_package_languages has 8 rows that match 7 packages:

mysql> select iid, rid, package_id, filename from hosting_package_instance where iid in (select iid from hosting_package_languages);
+-----+-----+------------+---------------------------------------------------------------+
| iid | rid | package_id | filename                                                      |
+-----+-----+------------+---------------------------------------------------------------+
|  41 |   5 |         46 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
|  42 |   5 |         47 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
|  98 |  41 |         46 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
|  99 |  41 |         47 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
| 140 |  42 |         46 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
| 141 |  42 |         47 | /var/aegir/drupal-6.14/profiles/default/default.profile       | 
| 182 |  64 |         46 | /var/aegir/drupal-6.14/profiles/hostmaster/hostmaster.profile | 
+-----+-----+------------+---------------------------------------------------------------+
7 rows in set (0.00 sec)

and the 8th row, you guess, is an orphan:

mysql> select * from hosting_package_languages where iid not in (select iid from hosting_package_instance);
+-----+----------+
| iid | language |
+-----+----------+
| 183 | en       | 
+-----+----------+
1 row in set (0.00 sec)
univate’s picture

Status: Needs work » Needs review
StatusFileSize
new1.46 KB

Here is a combined patch discussed in IRC

It finds all duplicates where there are mupltiple package_instance's for each platform or site (#20) and where there are is no package_instance for a package (#16).

If there are any nid that match these:
* call node_delete() on each nid
* re-run verify on each platform.

I wonder if we should also re-run verify on each site as well.

Anonymous’s picture

I applied this patch (with some minor {} bracket corrections and some extra code documentation) and it applied fine.. It said that it deleted a whole bunch of nodes, that's great.

But both before and after the update, I ran this:

mysql> SELECT iid FROM hosting_package_instance WHERE rid NOT IN (SELECT nid FROM hosting_platform);

And got 193 rows returned. So even after the update, this happened.

When the re-verify went through of the platform, I ran the query again, and now i get 282 rows in set. Is this expected behaviour?

on the other hand, everything else seems normal (i.e during duplication, or at least in one of the cases, I see two 'default' profiles in the site node form when adding a site, and after this update, this has gone.)

anarcat’s picture

There is a separate issue about orphaned items in #596714: orphaned entries in hosting_package_instance, please disregard that issue (which is also critical as it affects 0.3) when resolving the bug here.

Anonymous’s picture

Status: Needs review » Fixed

Thanks anarcat, yes I confirm this update fixes the duplication issue (orphans are for that other ticket), so I've committed this to HEAD.

Thanks most of all to jonhattan and univate who knuckled down and sorted this one out for us :) mega gratitude to you both.

Status: Fixed » Closed (fixed)
Issue tags: -Upgrade path

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