Description of problem: SatConfig-bootstrap-server-1.13.3-1.el5 Package uses sat_node in file NOCpulse/SatConfig/Bootstrap.pm. Should be rhn_sat_node. Additional info: @@ -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: $@"); }
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.