Small Oracle problems in rt-3.6.4

Hello All,

I’m currently playing around with a new VM installation of Oracle XE and
RT-3.6.4 and tried to run the regression test and found that drop.Oracle
has two spelling mistakes:
DROP TABLE OBJECTUSTOMFIELDS;
DROP TABLE OBJECTUSTOMFIELDVALUES;
should ofcourse be:
DROP TABLE OBJECTCUSTOMFIELDS;
DROP TABLE OBJECTCUSTOMFIELDVALUES;

Further I have a question about regression testing:
At the moment should there be ZERO tests failed?
I ask because 12 tests fail with 128 subtests failed of which quite a
bit can be explained because this wasn’t a totally clean rt-3.6.4
install, mail-gateway isn’t setup and probably some other small things.
Others are probably more serious such as the following:

at lib/t/regression/22search_tix_by_watcher.t line 54.

Wrong SQL query for ‘Requestor NOT LIKE “@example.com”’:SELECT main.*

FROM ( SELECT DISTINCT main.id FROM Tickets main JOIN Groups Groups_1
ON ( Groups_1.Domain = ‘RT::Ticket-Role’ ) AND ( Groups_1.Type =
‘Requestor’ ) AND ( Groups_1.Instance = main.id ) LEFT JOIN
CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = ‘0’ ) AND ( CachedGroupMembers_2.GroupId
= Groups_1.id ) WHERE (main.Status != ‘deleted’) AND ( ( main.id =
‘302’ OR main.id = ‘303’ OR main.id = ‘304’ OR main.id = ‘305’ OR
main.id = ‘306’ ) AND ( ( CachedGroupMembers_2.id IS NULL ) ) ) AND
(main.Type = ‘ticket’) AND (main.EffectiveId = main.id) )
distinctquery, Tickets main WHERE (main.id = distinctquery.id)
lib/t/regression/22search_tix_by_watcher…NOK 68

The query itself is valid and in production returns 5 rows, just plain
luck since the ticket ids exist :wink:
So what does the error in the test mean?

If there is interest I can setup a pristine environment and run
regression tests against it.

Thanks,

Joop

Hello All,

I’m currently playing around with a new VM installation of Oracle
XE and RT-3.6.4 and tried to run the regression test and found that
drop.Oracle has two spelling mistakes:
DROP TABLE OBJECTUSTOMFIELDS;
DROP TABLE OBJECTUSTOMFIELDVALUES;
should ofcourse be:
DROP TABLE OBJECTCUSTOMFIELDS;
DROP TABLE OBJECTCUSTOMFIELDVALUES;

I’ll get that fixed up.

Further I have a question about regression testing:
At the moment should there be ZERO tests failed?

I believe so

I ask because 12 tests fail with 128 subtests failed of which quite
a bit can be explained because this wasn’t a totally clean rt-3.6.4
install,

The test suite needs to run on an empty database. Did that happen?

mail-gateway isn’t setup and probably some other small things.
Others are probably more serious such as the following:

at lib/t/regression/22search_tix_by_watcher.t line 54.

Wrong SQL query for ‘Requestor NOT LIKE “@example.com”’:SELECT

main.* FROM ( SELECT DISTINCT main.id FROM Tickets main JOIN Groups
Groups_1 ON ( Groups_1.Domain = ‘RT::Ticket-Role’ ) AND
( Groups_1.Type = ‘Requestor’ ) AND ( Groups_1.Instance = main.id )
LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON
( CachedGroupMembers_2.MemberId = ‘0’ ) AND
( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (main.Status !
= ‘deleted’) AND ( ( main.id = ‘302’ OR main.id = ‘303’ OR main.id
= ‘304’ OR main.id = ‘305’ OR main.id = ‘306’ ) AND
( ( CachedGroupMembers_2.id IS NULL ) ) ) AND (main.Type =
‘ticket’) AND (main.EffectiveId = main.id) ) distinctquery,
Tickets main WHERE (main.id = distinctquery.id)
lib/t/regression/22search_tix_by_watcher…NOK 68

That’s a warning related to a change we backed out at the last minute
for 3.6.4. It’s relatively safe.

PGP.sig (186 Bytes)

DROP TABLE OBJECTCUSTOMFIELDS;
DROP TABLE OBJECTCUSTOMFIELDVALUES;

Thanks. Applied

PGP.sig (186 Bytes)

Jesse Vincent wrote:

Hello All,

I’m currently playing around with a new VM installation of Oracle XE
and RT-3.6.4 and tried to run the regression test and found that
drop.Oracle has two spelling mistakes:
DROP TABLE OBJECTUSTOMFIELDS;
DROP TABLE OBJECTUSTOMFIELDVALUES;
should ofcourse be:
DROP TABLE OBJECTCUSTOMFIELDS;
DROP TABLE OBJECTCUSTOMFIELDVALUES;

I’ll get that fixed up.
Thanks.

Further I have a question about regression testing:
At the moment should there be ZERO tests failed?

I believe so

I ask because 12 tests fail with 128 subtests failed of which quite a
bit can be explained because this wasn’t a totally clean rt-3.6.4
install,

The test suite needs to run on an empty database. Did that happen?
Yes, thats how I found the two missing ‘C’ in the drop schema.

I’ll setup a clean rt-3.6.4 without any local modifications and run the
regressions agains Mysql and Oracle and see if that makes any
difference and report back.

Joop

Hello all,
Owing to performance issues with RT on MySQL, we’re looking at moving to
RT on Oracle; unfortunately, the documentation on this migration process
on the wiki is somewhat lacking
(MySQLToOracle - Request Tracker Wiki). Have folks here
performed this migration with success? How did you go about it?

Cheers,
Jeff Albert

Hello all,
Owing to performance issues with RT on MySQL, we’re looking at
moving to RT on Oracle; unfortunately, the documentation on this
migration process on the wiki is somewhat lacking (http://
MySQLToOracle - Request Tracker Wiki). Have folks here
performed this migration with success? How did you go about it?

[I know this isn’t exactly an answer to your question]

Are these all performance issues you’ve raised on the mailing lists?
We’ve seen RT on MySQL scale to well over 10,000 tickets/day. But
you’re definitely going to have to tune and index. (Of course, you
might be able to push that to a DBA team if you’re switching to
Oracle :wink:

-jesse

PGP.sig (186 Bytes)

Hi Jesse,
No, we haven’t brought these issues to the mailing list; performance
isn’t actually that bad for most basic queries, which are of course
already index supported, but occasionally we’ll get a few people all
doing big “show me all tickets where content matches x” searches and
performance will suffer for everyone; we’ve had some issues with MySQL’s
InnoDB ibdata files getting really gigantic and sometimes preventing a
restart of MySQL; and, as you say, if we moved in an Oracle direction
we’d have the support of a team of DBAs to assist with these sorts of
issues.

One of the biggest problems we’ve had, actually, and the one that’s
pushing us in the direction of Oracle, has been a problem with running
out of memory during mysqldumps, which we use as part of our backup
process for MySQL databases. Research hasn’t turned up much: we’re
already using the --quick flag that’s often recommended to solve this
issue, with no success. If you or the fine readers of this list had any
suggestions on this one, I’d be very grateful.

At any rate, I am still interested in migrating to Oracle, as it’s a
well supported environment here, so I’ll restate my original questions:
have people made the MySQL to Oracle move? How was it accomplished?

Cheers,
Jeff Albert

Jesse Vincent wrote:

Hi Andrew,
We don’t have any particular reason to believe that Oracle would
intrinsically provide better performance; however, we do have a team of
DBAs to assist with Oracle. I am also looking at Postgres as an
alternative, since we have a fairly deep pool of experience there. I’m
curious about what changes you needed to make to achieve performance
gains; mostly an issue of refining indexes, or was there more to it than
that?

Cheers,
Jeff Albert

Andrew Sullivan wrote:

Jeff Albert wrote:

Hello all,
Owing to performance issues with RT on MySQL, we’re looking at moving to
RT on Oracle; unfortunately, the documentation on this migration process
on the wiki is somewhat lacking
(MySQLToOracle - Request Tracker Wiki). Have folks here
performed this migration with success? How did you go about it?
There was recently someone else who has migrated to Oracle. The biggest
problem is that Oracle isn’t considered ‘binary safe’ by RT and all
attachments are therefore saved as base64 CLOBs. So you’ll need to
encode the current binary mysql blobs to base64 clobs, other then that I
don’t think you’ll going to have much problems. As always in these kind
of situations its best to migrate a small test instance from mysql to
oracle.
The wiki article mentions Oracle Migration Workbench and if I remember
correctly then that is either included in Oracle SQL Developer 1.2 or
that program contains something like it. What it probably doesn’t do is
encode base64 but you could do that from a temporary table using a
database package/procedure, I seem to recall seeing one.

I’ll try to write up a wiki article about patches we use which allow
searching for email content using OracleText.

Joop