Database upgrade issue from RT 3.6.3 to RT 3.8.4

Hello All

I have recently upgraded my application from RT 3.6.3 to 3.8.4. Application
has seems to be upgraded correctly but when I have upgraded database. It has
shown me that database upgrade is successful. And I have also been able to
log in system but whenever I tried to see the basics of any tickets or
custom fields of any ticket RT has gone into infinite loop of query and
seems to have been never returned from it when I checked in logs it has
shown me this error

[error] [client 192.168.7.79] FastCGI: server
"/opt/rt3/bin/mason_handler.fcgi" stderr: RT::Handle=HASH(0xb8dce30)
couldn’t prepare the query 'SELECT main.* FROM Attributes main WHERE
(main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND (main.ObjectType =
‘RT::CustomField’) 'ORA-00932: inconsistent datatypes: expected - got CLOB
(DBD ERROR: error possibly near <> indicator at char 43 in 'SELECT main.
FROM Attributes main WHERE (<*>main.Content = ‘3’) AND (main.Name =
‘BasedOn’) AND (main.ObjectType = ‘RT::CustomField’) '), referer:
http://rtbeta.elitecore.co.in/Ticket/Display.html?id=132467

[Tue Nov 03 16:14:02 2009] [error] [client 192.168.7.79] FastCGI: server
"/opt/rt3/bin/mason_handler.fcgi" stderr: DBD::Oracle::db prepare failed:
ORA-00932: inconsistent datatypes: expected - got CLOB (DBD ERROR: error
possibly near <> indicator at char 43 in 'SELECT main. FROM Attributes
main WHERE (<>main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND
(main.ObjectType = ‘RT::CustomField’) ') [for Statement "SELECT main.
FROM
Attributes main WHERE (main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND
(main.ObjectType = ‘RT::CustomField’) "] at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 470,
line 2501., referer:
http://rtbeta.elitecore.co.in/Ticket/Display.html?id=132467

[Tue Nov 03 16:14:02 2009] [error] [client 192.168.7.79] FastCGI: server
"/opt/rt3/bin/mason_handler.fcgi" stderr: RT::Handle=HASH(0xb8dce30)
couldn’t prepare the query 'SELECT main.* FROM Attributes main WHERE
(main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND (main.ObjectType =
‘RT::CustomField’) 'ORA-00932: inconsistent datatypes: expected - got CLOB
(DBD ERROR: error possibly near <> indicator at char 43 in 'SELECT main.
FROM Attributes main WHERE (<*>main.Content = ‘3’) AND (main.Name =
‘BasedOn’) AND (main.ObjectType = ‘RT::CustomField’) '), referer:
http://rtbeta.elitecore.co.in/Ticket/Display.html?id=132467

[Tue Nov 03 16:14:02 2009] [error] [client 192.168.7.79] FastCGI: server
"/opt/rt3/bin/mason_handler.fcgi" stderr: DBD::Oracle::db prepare failed:
ORA-00932: inconsistent datatypes: expected - got CLOB (DBD ERROR: error
possibly near <> indicator at char 43 in 'SELECT main. FROM Attributes
main WHERE (<>main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND
(main.ObjectType = ‘RT::CustomField’) ') [for Statement "SELECT main.
FROM
Attributes main WHERE (main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND
(main.ObjectType = ‘RT::CustomField’) "] at
/usr/local/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 470,
line 2501., referer:
http://rtbeta.elitecore.co.in/Ticket/Display.html?id=132467

I don’t know what kind of error is this and also my upgrade database command
was completed successfully. Can any one suggest me what is the problem. And
how to correct and what I am missing

Thanks & Regards

Varun Vyas

Varun wrote:

Hello All

I have recently upgraded my application from RT 3.6.3 to 3.8.4.
Application has seems to be upgraded correctly but when I have
upgraded database. It has shown me that database upgrade is
successful. And I have also been able to log in system but whenever I
tried to see the basics of any tickets or custom fields of any ticket
RT has gone into infinite loop of query and seems to have been never
returned from it when I checked in logs it has shown me this error

[error] [client 192.168.7.79] FastCGI: server
"/opt/rt3/bin/mason_handler.fcgi" stderr: RT::Handle=HASH(0xb8dce30)
couldn’t prepare the query 'SELECT main.* FROM Attributes main WHERE
(main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND (main.ObjectType
= ‘RT::CustomField’) 'ORA-00932: inconsistent datatypes: expected -
got CLOB (DBD ERROR: error possibly near <> indicator at char 43 in
’SELECT main.
FROM Attributes main WHERE (<*>main.Content = ‘3’) AND
(main.Name = ‘BasedOn’) AND (main.ObjectType = ‘RT::CustomField’) '),
referer: http://rtbeta.elitecore.co.in/Ticket/Display.html?id=132467

A good idea is when you get errors like these in your rt.log is to cut
and paste the sql statement into sqlplus and see if it will give you an
error message that is more clear then rt.log

SELECT main.* FROM Attributes main WHERE (main.Content = ‘3’) AND
(main.Name = ‘BasedOn’) AND (main.ObjectType = ‘RT::CustomField’)
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

As you, hopefully, can see, the * is under main.Content = ‘3’ and if you
know Oracle SQL a bit then it is obvious that you can’t do ‘=’ on a
CLOB. So this qualifies as a bug.

Are you sure you’re on 3.8.4 because I posted to rt-devel about
something like it but that was for 3.8.6 where they overhauled
hierarchical customfields and I found that converting from old to new
resulted in something like this.

Regards,

Joop

Hello Joop

As per your guessing you are right I am facing problem of corruption in sql
query when I upgraded from 3.6.3 to 3.8.6 application is working fine but
when I want to look at page where custom fields are or want to see the page
where tickets basics are there. I am not able to go that page and RT doesn’t
seem too returned with that page and goes in unending loop of query firing
and also I get the query which I posted to you which is not firing as per
your suggestion. So can you can help me in how to fix this problem I am not
been able to find any solution for it.

Any help is highly appreciated.

Thanks & Regards

Varun VyasFrom: Joop [mailto:JoopvandeWege@mococo.nl]
Sent: Tuesday, November 03, 2009 1:23 PM
To: Varun
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Database upgrade issue from RT 3.6.3 to RT 3.8.4

Varun wrote:

Hello All

I have recently upgraded my application from RT 3.6.3 to 3.8.4. Application
has seems to be upgraded correctly but when I have upgraded database. It has
shown me that database upgrade is successful. And I have also been able to
log in system but whenever I tried to see the basics of any tickets or
custom fields of any ticket RT has gone into infinite loop of query and
seems to have been never returned from it when I checked in logs it has
shown me this error

[error] [client 192.168.7.79] FastCGI: server
"/opt/rt3/bin/mason_handler.fcgi" stderr: RT::Handle=HASH(0xb8dce30)
couldn’t prepare the query 'SELECT main.* FROM Attributes main WHERE
(main.Content = ‘3’) AND (main.Name = ‘BasedOn’) AND (main.ObjectType =
‘RT::CustomField’) 'ORA-00932: inconsistent datatypes: expected - got CLOB
(DBD ERROR: error possibly near <> indicator at char 43 in 'SELECT main.
FROM Attributes main WHERE (<*>main.Content = ‘3’) AND (main.Name =
‘BasedOn’) AND (main.ObjectType = ‘RT::CustomField’) '), referer:
http://rtbeta.elitecore.co.in/Ticket/Display.html?id=132467

A good idea is when you get errors like these in your rt.log is to cut and
paste the sql statement into sqlplus and see if it will give you an error
message that is more clear then rt.log

SELECT main.* FROM Attributes main WHERE (main.Content = ‘3’) AND
(main.Name = ‘BasedOn’) AND (main.ObjectType = ‘RT::CustomField’)
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

As you, hopefully, can see, the * is under main.Content = ‘3’ and if you
know Oracle SQL a bit then it is obvious that you can’t do ‘=’ on a CLOB. So
this qualifies as a bug.

Are you sure you’re on 3.8.4 because I posted to rt-devel about something
like it but that was for 3.8.6 where they overhauled hierarchical
customfields and I found that converting from old to new resulted in
something like this.

Regards,

Joop

Varun wrote:

Hello Joop

As per your guessing you are right I am facing problem of corruption
in sql query when I upgraded from 3.6.3 to 3.8.6 application is
working fine but when I want to look at page where custom fields are
or want to see the page where tickets basics are there. I am not able
to go that page and RT doesn’t seem too returned with that page and
goes in unending loop of query firing and also I get the query which I
posted to you which is not firing as per your suggestion. So can you
can help me in how to fix this problem I am not been able to find any
solution for it.

Any help is highly appreciated.

I’m sorry but that is something that changed in RT and I’m not able to
help you there. Basically what I wrote to rt-devel is that probably
there is a mix up in column names and the query should read:

SELECT main.*
FROM Attributes main
WHERE (main.OBJECTID = ‘221’)
AND (main.Name = ‘BasedOn’)
AND (main.ObjectType = ‘RT::CustomField’)

instead of:

SELECT main.*
FROM Attributes main
WHERE (main.Content = ‘221’)
AND (main.Name = ‘BasedOn’)

Regards,

Joop

     AND (main.ObjectType = 'RT::CustomField')

Hello All

Yesterday I have upgraded from 3.6.3 to 3.8.5 all has works well. But I m
facing a strange problem for e.g. I have logged with root user and all
operations works fine but when I logged in with other user after logging out
I has got window and all queues which “root user has access”. I have checked
credentials of the other user but It was a normal user with no admin
privileges. But still I was able to look in the queues that only admin can
see. Once I have cleared the cache cookies form browser then my normal
user’s home page was fine and I can able to see the queue which that user is
supposed to see. But again when I logged in with admin user I was seeing the
queue of my previous user and not been able to see the admin queues. I am
facing this problem with my new installation (i.e. upgradation from 3.6.3 to
3.8.5 ) I have checked all stuffs with my browser but it is fine and also I
have no problem in switching users and I was able to see the home pages of
relevant users when I have used my beta machine having RT 3.6.3 installed

Please can any one suggest me what is problem with my installation .

Thanks & Regards

Varun VyasFrom: Joop [mailto:JoopvandeWege@mococo.nl]
Sent: Wednesday, November 04, 2009 1:01 PM
To: Varun
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Database upgrade issue from RT 3.6.3 to RT 3.8.4

Varun wrote:

Hello Joop

As per your guessing you are right I am facing problem of corruption in sql
query when I upgraded from 3.6.3 to 3.8.6 application is working fine but
when I want to look at page where custom fields are or want to see the page
where tickets basics are there. I am not able to go that page and RT doesn’t
seem too returned with that page and goes in unending loop of query firing
and also I get the query which I posted to you which is not firing as per
your suggestion. So can you can help me in how to fix this problem I am not
been able to find any solution for it.

Any help is highly appreciated.

I’m sorry but that is something that changed in RT and I’m not able to help
you there. Basically what I wrote to rt-devel is that probably there is a
mix up in column names and the query should read:

SELECT main.*
FROM Attributes main
WHERE (main.OBJECTID = ‘221’)
AND (main.Name = ‘BasedOn’)
AND (main.ObjectType = ‘RT::CustomField’)

instead of:

SELECT main.*
FROM Attributes main
WHERE (main.Content = ‘221’)
AND (main.Name = ‘BasedOn’)

Regards,

Joop

     AND (main.ObjectType = 'RT::CustomField')

Hello Joop

Well its okay . But still thanks a ton for guiding me for this. I was just
thinking that I might have not been upgraded database correctly. But thanks
for letting me know about real problem.

Regards

Varun VyasFrom: Joop [mailto:JoopvandeWege@mococo.nl]
Sent: Wednesday, November 04, 2009 1:01 PM
To: Varun
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Database upgrade issue from RT 3.6.3 to RT 3.8.4

Varun wrote:

Hello Joop

As per your guessing you are right I am facing problem of corruption in sql
query when I upgraded from 3.6.3 to 3.8.6 application is working fine but
when I want to look at page where custom fields are or want to see the page
where tickets basics are there. I am not able to go that page and RT doesn’t
seem too returned with that page and goes in unending loop of query firing
and also I get the query which I posted to you which is not firing as per
your suggestion. So can you can help me in how to fix this problem I am not
been able to find any solution for it.

Any help is highly appreciated.

I’m sorry but that is something that changed in RT and I’m not able to help
you there. Basically what I wrote to rt-devel is that probably there is a
mix up in column names and the query should read:

SELECT main.*
FROM Attributes main
WHERE (main.OBJECTID = ‘221’)
AND (main.Name = ‘BasedOn’)
AND (main.ObjectType = ‘RT::CustomField’)

instead of:

SELECT main.*
FROM Attributes main
WHERE (main.Content = ‘221’)
AND (main.Name = ‘BasedOn’)

Regards,

Joop

     AND (main.ObjectType = 'RT::CustomField')

Hello All

Yesterday I have upgraded from 3.6.3 to 3.8.5 all has works well. But I m
facing a strange problem for e.g. I have logged with root user and all
operations works fine but when I logged in with other user after logging out
I has got window and all queues which “root user has access”. I have checked
credentials of the other user but It was a normal user with no admin
privileges. But still I was able to look in the queues that only admin can
see. Once I have cleared the cache cookies form browser then my normal
user’s home page was fine and I can able to see the queue which that user is
supposed to see. But again when I logged in with admin user I was seeing the
queue of my previous user and not been able to see the admin queues. I am
facing this problem with my new installation (i.e. upgradation from 3.6.3 to
3.8.5 ) I have checked all stuffs with my browser but it is fine and also I
have no problem in switching users and I was able to see the home pages of
relevant users when I have used my beta machine having RT 3.6.3 installed

Please can any one suggest me what is problem with my installation .

Thanks & Regards

Varun Vyas

Hi Varun,

This sounds very similar to a recent thread. In that case the
problem was caused by an Apache mod_cache module caching cookies
incorrectly. I know they were working on an application change
to address the problem, but disabling the mod_cache module
fixed it immediately. It might be worth a look.

Regards,
Ken

Hello Ken

Thanks for your suggestion. But in my apache I don’t have mod_cache module
enabled. It is not there in my DSO libraries also. Any other ideas ??

Thanks
VarunFrom: Kenneth Marshall [mailto:ktm@rice.edu]
Sent: Wednesday, November 04, 2009 7:17 PM
To: Varun
Cc: ‘Joop’; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Cookies Problem with RT 3.8.5

Hello All

Yesterday I have upgraded from 3.6.3 to 3.8.5 all has works well. But I m
facing a strange problem for e.g. I have logged with root user and all
operations works fine but when I logged in with other user after logging
out
I has got window and all queues which “root user has access”. I have
checked
credentials of the other user but It was a normal user with no admin
privileges. But still I was able to look in the queues that only admin can
see. Once I have cleared the cache cookies form browser then my normal
user’s home page was fine and I can able to see the queue which that user
is
supposed to see. But again when I logged in with admin user I was seeing
the
queue of my previous user and not been able to see the admin queues. I am
facing this problem with my new installation (i.e. upgradation from 3.6.3
to
3.8.5 ) I have checked all stuffs with my browser but it is fine and also
I
have no problem in switching users and I was able to see the home pages of
relevant users when I have used my beta machine having RT 3.6.3 installed

Please can any one suggest me what is problem with my installation .

Thanks & Regards

Varun Vyas

Hi Varun,

This sounds very similar to a recent thread. In that case the
problem was caused by an Apache mod_cache module caching cookies
incorrectly. I know they were working on an application change
to address the problem, but disabling the mod_cache module
fixed it immediately. It might be worth a look.

Regards,
Ken

Hi Joop

I have one problem regarding RT database. We have very minor amount of data
in RT u can say 5gb overall but we still are facing problem with particular
updation of records when we have try to find what is cause then we came to
know that we have attachments table in RT which alone is taking 4gb of chunk
of data apart from overall data. And because of this any updation query when
hits attachments table takes a hell lot of time and our customer feels RT is
slow like tortoise and not helping them much in doing there day to day
activities and they are irritated while using RT. Can you can suggest me how
I can manage these attachment table particularly so that even when data
increases significantly we do not suffer from latecy while updation.

Waiting for a valuable answer from your side

Thanks & Regards

VarunFrom: Joop [mailto:JoopvandeWege@mococo.nl]
Sent: Wednesday, November 04, 2009 1:01 PM
To: Varun
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Database upgrade issue from RT 3.6.3 to RT 3.8.4

Varun wrote:

Hello Joop

As per your guessing you are right I am facing problem of corruption in sql
query when I upgraded from 3.6.3 to 3.8.6 application is working fine but
when I want to look at page where custom fields are or want to see the page
where tickets basics are there. I am not able to go that page and RT doesn’t
seem too returned with that page and goes in unending loop of query firing
and also I get the query which I posted to you which is not firing as per
your suggestion. So can you can help me in how to fix this problem I am not
been able to find any solution for it.

Any help is highly appreciated.

I’m sorry but that is something that changed in RT and I’m not able to help
you there. Basically what I wrote to rt-devel is that probably there is a
mix up in column names and the query should read:

SELECT main.*
FROM Attributes main
WHERE (main.OBJECTID = ‘221’)
AND (main.Name = ‘BasedOn’)
AND (main.ObjectType = ‘RT::CustomField’)

instead of:

SELECT main.*
FROM Attributes main
WHERE (main.Content = ‘221’)
AND (main.Name = ‘BasedOn’)

Regards,

Joop

     AND (main.ObjectType = 'RT::CustomField')

Good day all…

Can someone please assist me ...

We are running RT-3.8.5 on RHEL4 and using MySQL database.

I would like to force users to enter certain fields such as

  •      Time worked
    
  •      Comments
    
  •      Subject
    

    I tried to do this on the database level by setting these fields to “not
    null”, however this does not work as it seems the software does not enter a
    null for a blank field but rather an empty space.

Does anybody know of a way to enforce these settings?

Your help is much appreciated.

Regards

Machiel