[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]

Re: [Spacewalk-devel] function nvl(evr_t, evr_t) does not exist



On Thu, Jan 12, 2012 at 04:55:52PM +0000, Parsons, Aron wrote:
> The following exception is generated when attempting to install a package on a system, which is a pretty basic function for Spacewalk.  I believe it was reported before at https://www.redhat.com/archives/spacewalk-list/2011-November/msg00055.html.
> 
> [Thu Jan 12 11:12:34 2012] [error] Execution of /var/www/html/network/software/packages/install_confirm.pxt failed at Thu Jan 12 11:12:34 2012: RHN::Exception: DBD::Pg::st execute failed: ERROR:  function evr_t(unknown, integer, integer) does not exist\nLINE 23:                      AND SP.evr_id = PE.id), EVR_T(NULL, 0, ...\n                                                      ^\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.\n  RHN::DB /usr/share/perl5/vendor_perl/RHN/DB.pm 228 
> 
> Gurjeet Singh suggested a fix for the instantiation of the EVR_T at: http://www.redhat.com/archives/spacewalk-devel/2009-February/001536.html
> 
> --- Scheduler.pm.orig   2012-01-06 08:05:25.000000000 -0500
> +++ Scheduler.pm        2012-01-12 11:49:27.820683130 -0500
> @@ -660,7 +660,7 @@
>                      FROM rhnServerPackage SP, rhnPackageEvr PE
>                     WHERE SP.name_id = P.name_id
>                       AND SP.server_id = S.id
> -                     AND SP.evr_id = PE.id), ${rhn_class}EVR_T(NULL, 0, 0))
> +                     AND SP.evr_id = PE.id), ${rhn_class}(NULL, 0, 0)::EVR_T)
>               <
>               (SELECT EVR FROM rhnPackageEVR PE WHERE PE.id = P.evr_id)
>              )
> 
> Fixing DB/Scheduler.pm gets around that issue and EVR_T is instantiated fine.  However, a new issue pops up:
> 
> [Thu Jan 12 11:49:37 2012] [error] Execution of /var/www/html/network/software/packages/install_confirm.pxt failed at Thu Jan 12 11:49:37 2012: RHN::Exception: DBD::Pg::st execute failed: ERROR:  function nvl(evr_t, evr_t) does not exist\nLINE 19:    AND  (   (NVL((SELECT MAX(PE.evr)\n                      ^\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> 
> Any input?

The change above would break operations on the Oracle backend so we
cannot use it.

Please use the following patch (against 1.6):

diff --git a/web/modules/rhn/RHN/DB/Scheduler.pm b/web/modules/rhn/RHN/DB/Scheduler.pm
index ccb478b..e47187a 100644
--- a/web/modules/rhn/RHN/DB/Scheduler.pm
+++ b/web/modules/rhn/RHN/DB/Scheduler.pm
@@ -634,8 +634,6 @@ sub schedule_package_install {
   my $query;
   my $sth;
 
-  my $rhn_class = '';
-
   if ($package_id and $server_set) {
     $query = <<EOQ;
 INSERT INTO rhnServerAction (server_id, action_id, status)
@@ -656,14 +654,15 @@ SELECT    S.id
    AND  CP.package_id = P.id
    AND  PA.id = P.package_arch_id
    AND  AT.id = PA.arch_type_id
-   AND  (   (NVL((SELECT MAX(PE.evr)
-                    FROM rhnServerPackage SP, rhnPackageEvr PE
-                   WHERE SP.name_id = P.name_id
-                     AND SP.server_id = S.id
-                     AND SP.evr_id = PE.id), ${rhn_class}EVR_T(NULL, 0, 0))
-             <
-             (SELECT EVR FROM rhnPackageEVR PE WHERE PE.id = P.evr_id)
-            )
+   AND  (not exists(
+         select 1
+        from rhnServerPackage, rhnPackageEvr evr_installed, rhnPackageEvr evr_new
+        where P.name_id = rhnServerPackage.name_id
+              and S.id = rhnServerPackage.server_id
+              and rhnServerPackage.evr_id = evr_installed.id
+              and P.evr_id = evr_new.id
+              and evr_installed.evr >= evr_new.evr
+         )
          OR AT.label = 'solaris-patch'
          OR AT.label = 'solaris-patch-cluster'
         )
@@ -709,7 +708,7 @@ EOQ
   elsif ($package_id) {
     $query = <<EOQ;
 INSERT INTO rhnActionPackage (id, action_id, name_id, evr_id)
-(SELECT rhn_act_p_id_seq.nextval, ?, P.name_id, P.evr_id FROM rhnPackage P WHERE P.id = ?)
+(SELECT sequence_nextval('rhn_act_p_id_seq'), ?, P.name_id, P.evr_id FROM rhnPackage P WHERE P.id = ?)
 EOQ
     $sth = $dbh->prepare($query);
     #  warn "ins query:  $query\n$id, $user_id, ".$packages->label;

-- 
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat


[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]