Bug 741782

Multiple wrong table names are used in Bootstrap.pm
Complete diff:


--- Bootstrap.pm.orig   2011-09-27 23:15:31.000000000 +0200
+++ Bootstrap.pm        2011-09-27 23:20:49.000000000 +0200
@@ -56,7 +56,7 @@
        my $satrec;

        if (defined($ssk)) {
-           $satrec  = SatNodeRecord->LoadOneFromSql("select recid,ip,sat_cluster_id,scout_shared_key from sat_node where scout_shared_key = '$ssk'");
+           $satrec  = SatNodeRecord->LoadOneFromSql("select recid,ip,sat_cluster_id,scout_shared_key from rhn_sat_node where scout_shared_key = '$ssk'");
            if (! defined($satrec)) {
                return &Reject($request, "Couldn't look up record for Scout Shared Key $ssk: $@");
            }
@@ -65,17 +65,17 @@
        my $clustid = $satrec->get_SAT_CLUSTER_ID;
        my $satid = $satrec->get_RECID;
        my $scout_shared_key = $satrec->get_SCOUT_SHARED_KEY;
-       my $clustrec = SatClusterRecord->LoadOneFromSql("select recid,description,customer_id from sat_cluster where recid = '$clustid'");
-       my $isll = CFDBRecord->LoadOneFromSql("select netsaint_id from ll_netsaint where netsaint_id = '$clustid'") ? 1 : 0;
+       my $clustrec = SatClusterRecord->LoadOneFromSql("select recid,description,customer_id from rhn_sat_cluster where recid = '$clustid'");
+       my $isll = CFDBRecord->LoadOneFromSql("select netsaint_id from rhn_ll_netsaint where netsaint_id = '$clustid'") ? 1 : 0;
        CFDBRecord->ReleaseAllInstances;

        # Store the satellite public key if one was provided
        if (defined($key)) {
-           CFDBRecord->DatabaseConnection->prepare("update sat_cluster set public_key = '$key' where recid = $clustid")->execute;
+           CFDBRecord->DatabaseConnection->prepare("update rhn_sat_cluster set public_key = '$key' where recid = $clustid")->execute;
            CFDBRecord->Commit;
        }
        if (defined($cert)) {
-           CFDBRecord->DatabaseConnection->prepare("update sat_cluster set pem_public_key = '$cert', pem_public_key_hash = '$certHash' where recid = $clustid")->execute;
+           CFDBRecord->DatabaseConnection->prepare("update rhn_sat_cluster set pem_public_key = '$cert', pem_public_key_hash = '$certHash' where recid = $clustid")->execute;
            CFDBRecord->Commit;
        }

This leads to more wrong table names in NOCpulse/SatConfig/GenerateConfig.pm


--- GenerateConfig.pm.orig      2011-09-27 23:31:41.000000000 +0200
+++ GenerateConfig.pm   2011-09-27 23:45:06.000000000 +0200
@@ -27,7 +27,7 @@
                    location_name as physical_location_name,
                    sat_node.max_concurrent_checks, sat_node.sched_log_level,
                    sat_node.sput_log_level, sat_node.dq_log_level
-            from   sat_cluster, sat_node, physical_location
+            from   rhn_sat_cluster as sat_cluster, rhn_sat_node as sat_node, rhn_physical_location as physical_location
             where  sat_cluster.recid = sat_node.sat_cluster_id
             and    sat_cluster.physical_location_id = physical_location.recid
             and    sat_cluster.recid = ?
@@ -46,7 +46,7 @@
         # Get the auto_update flag
         CFDBRecord->LoadFromSqlWithBind(q{
             select recid, auto_update
-            from   customer
+            from   rhn_customer_monitoring
             where  recid = ?
             },
             [$customerId], 'RECID');
@@ -112,7 +112,7 @@
     my $sth;
     eval {
         $sth = CSDBRecord->DoSql(q{
-            delete from deployed_probe
+            delete from rhn_deployed_probe
             where sat_cluster_id = ?
             or probe_type = 'url'
         },
@@ -133,11 +133,11 @@

     eval {
            my $sql = qq{
-             insert into deployed_probe($ins_probe_cols, sat_cluster_id, os_id)
+             insert into rhn_deployed_probe($ins_probe_cols, sat_cluster_id, os_id)
              select $ins_probe_cols, sat_cluster_id, os_id
              from (
                 select $sel_probe_cols, check_probe.sat_cluster_id, host.os_id
-                from probe, check_probe, host
+                from rhn_probe as probe, rhn_check_probe as check_probe, rhn_host as host
                 where check_probe.probe_id = probe.recid
                 and check_probe.sat_cluster_id = ?
                 and host.recid = check_probe.host_id

After this you get in trouble with NOCpulse/NPRecords.pm

Possible Fix:


--- NPRecords.pm.orig   2011-09-27 23:48:15.000000000 +0200
+++ NPRecords.pm        2011-09-28 00:14:20.000000000 +0200
@@ -117,7 +117,7 @@
           SELECT $probe_col_str, /* Checks */
                  check_probe.sat_cluster_id as netsaint_id,
                  $host_col_str
-         FROM   probe, check_probe, host
+         FROM   rhn_probe as probe, rhn_check_probe as check_probe, rhn_host_monitoring as host
           WHERE  check_probe.probe_id = probe.recid
           AND    check_probe.host_id = host.recid
           AND    check_probe.sat_cluster_id = ?
@@ -129,7 +129,7 @@
         $class->InstanceCount > 0 or return 0;

        # Fetch OS IDs and names for later mapping
-       CFDBRecord->LoadFromSql("SELECT recid, os_name FROM os", 'RECID');
+       CFDBRecord->LoadFromSql("SELECT recid, os_name FROM rhn_os", 'RECID');
        my $os = CFDBRecord->Instances;
        CFDBRecord->ReleaseAllInstances;

@@ -184,8 +184,8 @@
                                 m.snmp_host,
                                 m.snmp_port,
                                 m.sender_sat_cluster_id
-                      FROM      contact_groups g, contact_methods m,
-                                contact_group_members map
+                      FROM      rhn_contact_groups g, rhn_contact_methods m,
+                                rhn_contact_group_members map
                       WHERE     map.contact_group_id = g.recid
                       AND       map.member_contact_method_id = m.recid
                       AND       g.recid in (".join(', ', @placeholder).')';
@@ -211,7 +211,7 @@
        # Now %snmpdests contains a list of SNMP destinatios for each
        # contact group.
        $sql = "SELECT recid,contact_group_name,customer_id
-               FROM   contact_groups
+               FROM   rhn_contact_groups
                WHERE  recid in (".join(', ', @placeholder).")";
         CFDBRecord->LoadFromSqlWithBind($sql, \@contactGroupIds, 'RECID');
         $class->Map(
@@ -249,26 +249,26 @@
        # Fetch service probes and host probes for all sats/scouts ...
        my $probeSubquery = "
               SELECT probe_id
-              FROM   host_probe
+              FROM   rhn_host_probe
               WHERE  sat_cluster_id = ?
               UNION
               SELECT probe_id
-              FROM   check_probe
+              FROM   rhn_check_probe
               WHERE  sat_cluster_id = ?
               UNION
               SELECT probe_id
-              FROM   sat_cluster_probe
+              FROM   rhn_sat_cluster_probe
               WHERE  sat_cluster_id = ?
               UNION
               SELECT probe_id
-              FROM   url_probe
+              FROM   rhn_url_probe
               WHERE  sat_cluster_id = ?
        ";

        # Load up the commands table and add the command group name to the probe records
        CommandRecord->LoadFromSqlWithBind('
            select c.recid, c.name, c.description, c.command_class, c.group_name
-           from command c, probe p
+           from rhn_command c, rhn_probe p
            where p.command_id = c.recid
            and p.recid in ('.$probeSubquery.')',
           [$satClusterId, $satClusterId, $satClusterId, $satClusterId],
@@ -290,7 +290,7 @@
        CommandMetricRecord->LoadFromSqlWithBind("
           select m.command_class, m.metric_id, m.label, m.description,
                  u.unit_label, u.description as unit_description
-          from metrics m, units u, command c, probe p
+          from rhn_metrics m, rhn_units u, rhn_command c, rhn_probe p
           where m.command_class = c.command_class
           and m.storage_unit_id = u.unit_id
           and p.command_id = c.recid
@@ -303,7 +303,7 @@
           select cp.command_id, cp.param_name, cp.param_type, cp.mandatory, cp.description,
                  command.command_class,
                  'NA' as threshold_type_name, 'NA' as threshold_metric_id
-          from command_parameter cp, probe p, command
+          from rhn_command_parameter cp, rhn_probe p, rhn_command as command
           where cp.param_type = 'config'
           and p.command_id = cp.command_id
           and p.recid in ($probeSubquery)
@@ -312,7 +312,7 @@
           select cp.command_id, cp.param_name, cp.param_type, cp.mandatory, cp.description,
                  command.command_class,
                  ct.threshold_type_name, ct.threshold_metric_id
-          from command_parameter cp, command_parameter_threshold ct, probe p, command
+          from rhn_command_parameter cp, rhn_command_param_threshold ct, rhn_probe p, rhn_command as command
           where cp.param_type = 'threshold'
           and p.command_id = cp.command_id
           and ct.command_id = cp.command_id
@@ -326,7 +326,7 @@
        # Load the probe parameter values.
        ProbeParamValueRecord->LoadFromSqlWithBind('
           select probe_id, command_id, param_name, value
-          from probe_param_value
+          from rhn_probe_param_value
           where probe_id in ('.$probeSubquery.')',
           [$satClusterId, $satClusterId, $satClusterId, $satClusterId],
           ('PROBE_ID', 'PARAM_NAME'));
@@ -456,7 +456,7 @@
        my ($class) = @_;
        $class->LoadFromSql("
            select recid, name, description, command_class, group_name, for_host_probe
-           from command", 'RECID');
+           from rhn_command", 'RECID');
 }

 ############################################
@@ -504,7 +504,7 @@
        # Fetch service probes and host probes for all sats/scouts ...
        my $alertquery = "
        SELECT  *
-       FROM    snmp_alert
+       FROM    rhn_snmp_alert
        WHERE   sender_cluster_id = $satClusterId";

        # Just Do Me
@@ -530,7 +530,7 @@

        # Delete SNMP alerts for $satClusterId up to and including $lastRecid
        my $alertquery = "
-       DELETE  from snmp_alert
+       DELETE  from rhn_snmp_alert
        WHERE   sender_cluster_id = $satClusterId
        AND     recid <= $lastRecid";


---------
And now here we are back in trouble with GenerateConfig.pm ...

Query generation at starts at line 135 generates a query that can not executed by postgresql 8.1 ... i started around to fix the query generation ... last try:

my $sql = qq{
             insert into rhn_deployed_probe($ins_probe_cols, sat_cluster_id, os_id) 
		    	select $ins_probe_cols, sat_cluster_id, os_id
             		from (
                		select $sel_probe_cols, check_probe.sat_cluster_id, host.os_id
                		from rhn_probe as probe, rhn_check_probe as check_probe, rhn_host_monitoring as host
                		where check_probe.probe_id = probe.recid
                		and check_probe.sat_cluster_id = ?
                		and host.recid = check_probe.host_id
             		) AS SUB
           };

But that also fails with postgresql error:
"ERROR:  failed to find conversion function from unknown to numeric"

Statement:

STATEMENT:
                     insert into rhn_deployed_probe(recid, probe_type, description, customer_id, command_id, contact_group_id, notify_critical, notify_warning, notify_recovery, notify_unknown, notification_interval_minutes, check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user, last_update_date, sat_cluster_id, os_id)
                                select recid, probe_type, description, customer_id, command_id, contact_group_id, notify_critical, notify_warning, notify_recovery, notify_unknown, notification_interval_minutes, check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user, last_update_date, sat_cluster_id, os_id
                                from (
                                        select probe.recid, probe.probe_type, probe.description, probe.customer_id, probe.command_id, probe.contact_group_id, probe.notify_critical, probe.notify_warning, probe.notify_recovery, probe.notify_unknown, probe.notification_interval_minutes, probe.check_interval_minutes, probe.retry_interval_minutes, probe.max_attempts, probe.last_update_user, probe.last_update_date, check_probe.sat_cluster_id, host.os_id
                                        from rhn_probe as probe, rhn_check_probe as check_probe, rhn_host_monitoring as host
                                        where check_probe.probe_id = probe.recid
                                        and check_probe.sat_cluster_id = $1
                                        and host.recid = check_probe.host_id
                                ) AS SUB


But it should be:

insert into rhn_deployed_probe(recid, probe_type, description, customer_id, command_id, contact_group_id, notify_critical, notify_warning, notify_recovery, notify_unknown, notification_interval_minutes, check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user, last_update_date, sat_cluster_id, os_id)
                                select recid::numeric, probe_type::text, description::text, customer_id::numeric, command_id::numeric, contact_group_id::numeric, notify_critical::text, notify_warning::text, notify_recovery::text, notify_unknown::text, notification_interval_minutes::numeric, check_interval_minutes::numeric, retry_interval_minutes::numeric, max_attempts::numeric, last_update_user::text, last_update_date::timestamp, sat_cluster_id::numeric, os_id::numeric
                                from (
                                        select probe.recid::numeric, probe.probe_type::text, probe.description::text, probe.customer_id::numeric, probe.command_id::numeric, probe.contact_group_id::numeric, probe.notify_critical::text, probe.notify_warning::text, probe.notify_recovery::text, probe.notify_unknown::text, probe.notification_interval_minutes::numeric, probe.check_interval_minutes::numeric, probe.retry_interval_minutes::numeric, probe.max_attempts::numeric, probe.last_update_user::text, probe.last_update_date::timestamp, check_probe.sat_cluster_id::numeric, host.os_id::text
                                        from rhn_probe as probe, rhn_check_probe as check_probe, rhn_host_monitoring as host
                                        where check_probe.probe_id = probe.recid
                                        and check_probe.sat_cluster_id = 1
                                        and host.recid = check_probe.host_id
                                ) AS SUB


Haven't a clue, how to fix this ...

Created attachment 525403 [details]
My modification to Bootstrap.pm

Created attachment 525404 [details]
My modification to GenerateConfig.pm

Created attachment 525405 [details]
My modification to NPRecords.pm

Table aliases has been fixed in spacewalk master by
commit 4c9e1707d1add19b132229e959b2a657002c5308
    741782 - subquery in FROM must have an alias
    fixing
    ERROR:  subquery in FROM must have an alias
    LINE 3:              from (
                              ^
    HINT:  For example, FROM (SELECT ...) [AS] foo.
commit 475bd021f86a714d630401e66d10421b2e12dc6e
    741782 - replaced aliases with table names
commit b28339716a873ec082c6997202478c24b50bce31
    741782 - replaced aliases with table names
commit 7d3a88353f57ef9657853f69ab53cc499997dd7a
    741782 - replaced ll_netsaint alias with table name
commit 604e5374f8f31d0a1cb327ed5dc9f8eb5fd21756
    741782 - replaced sat_cluster alias with table name
commit ff8c6378fcfbf310270713eec30d881f7548aadd
    741782 - replaced alias with table name

'Unknown to numeric' issue from commant #3 has been fixed by
commit 025dbb54979af7a769d0edabc1aa6794486a18f4
    741782 - fixed data types in view
    fixing
    "ERROR:  failed to find conversion function from unknown to numeric"
    STATEMENT:
                         insert into rhn_deployed_probe(recid, probe_type,
    description, customer_id, command_id, contact_group_id, notify_critical,
    notify_warning, notify_recovery, notify_unknown, notification_interval_minutes,
    check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user,
    last_update_date, sat_cluster_id, os_id)
...

Thank you.
There is a new little error in NPRecords.pm 

ERROR:  relation "rhn_command_parameter_threshold" does not exist at character 1188

git created patch is attached as:
"0001-741782-relation-rhn_command_parameter_threshold-does.patch"

Created attachment 525838 [details]
fix rhn_command_parameter_threshold to rhn_command_param_threshold

During start on NPBootstrap this SQL Error occurs:


ERROR:  failed to find conversion function from unknown to numeric
STATEMENT:
                     insert into rhn_deployed_probe(recid, probe_type, description, customer_id, command_id, contact_group_id, notify_critical, notify_warning, notify_recovery, notify_unknown, notification_interval_minutes, check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user, last_update_date, sat_cluster_id, os_id)
                     select recid, probe_type, description, customer_id, command_id, contact_group_id, notify_critical, notify_warning, notify_recovery, notify_unknown, notification_interval_minutes, check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user, last_update_date, sat_cluster_id, os_id
                     from (
                        select probe.recid, probe.probe_type, probe.description, probe.customer_id, probe.command_id, probe.contact_group_id, probe.notify_critical, probe.notify_warning, probe.notify_recovery, probe.notify_unknown, probe.notification_interval_minutes, probe.check_interval_minutes, probe.retry_interval_minutes, probe.max_attempts, probe.last_update_user, probe.last_update_date, check_probe.sat_cluster_id, host.os_id
                        from rhn_probe probe,
                             rhn_check_probe check_probe,
                             rhn_host_monitoring host
                        where check_probe.probe_id = probe.recid
                        and check_probe.sat_cluster_id = $1
                        and host.recid = check_probe.host_id
                     ) X



Looks like my view is not changed. Must this be done manually? How it must be changed?

Before my last post i did a spacewalk-schema-upgrade. This didn´t fix the above, i must execute 095-rhn_host_monitoring.sql manually.

Is that the right way, or did i overlooked a detail?

(In reply to comment #12)
> Before my last post i did a spacewalk-schema-upgrade. This didn´t fix the
> above, i must execute 095-rhn_host_monitoring.sql manually.
> 
> Is that the right way, or did i overlooked a detail?

Yes, in the Spacewalk 1.6 it will be fixed automatically by spacewalk-upgrade-schema, but if you applied the changes to your 1.5 then it have to be upgraded manually.

Comment #9 issue fixed by 
commit d6e2a0da51299f0c4ccfaa2bca8c427da4aa67e0
    fixed misspelled table name
    fixing ERROR:  relation "rhn_command_parameter_threshold" does not exist at character 1188
    introduced by commit 475bd021f86a714d630401e66d10421b2e12dc6e

Spacewalk 1.6 has been released.