Change text in tickts using database

Hi,

I have to change some test inside the tickets directly on database.

I don´t find where are the comments, or the emails body that we send to rt
or create trought web.

Could you tell me what table and what comun have this data??

Thanks.

Un saludo.
Juanjo Corral

Hi,

I have to change some test inside the tickets directly on database.

I don�t find where are the comments, or the emails body that we send to
rt or create trought web.

Could you tell me what table and what comun have this data??

It’s in the Content field in the Attachments table. This is keyed to the
Transactions table via TransactionId, and the Transactions table in turn
is keyed to the Tickets table via ObjectId.

Note that the Content field in Attachments has a LONGBLOB data type, and
will also contain binary data where relevant (eg, an image attachment).
So be careful when editing it, as you can easily create problems for
yourself! The table has a ContentType field which will tell you what
kind of data is inside Content.

(Disclaimer: the above relates to RT v 3. It may be different for other
versions).

Mark
http://mark.goodge.co.uk

Hi,

I have to change some test inside the tickets directly on database.

I don´t find where are the comments, or the emails body that we send to rt or create trought web.

Could you tell me what table and what comun have this data??

The Attachments table contains most of it.

Each ticket in the Tickets table has one or more transactions in the Transactions table, and each transaction can have zero or more attachments in the Attachments table, depending on the kind of transaction.

Tim

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

Thanks.

But i don´t see the email content, only see the headers.

I´m looking to change the content of the messages.

I see in the attachments tab, a column called content, but says (trough
phpMyAdmin) Binary Not Edit!!

Regards.2012/10/18 Tim Cutts tjrc@sanger.ac.uk

On 18 Oct 2012, at 15:58, Juanjo juanjillo@gmail.com wrote:

Hi,

I have to change some test inside the tickets directly on database.

I don´t find where are the comments, or the emails body that we send to
rt or create trought web.

Could you tell me what table and what comun have this data??

The Attachments table contains most of it.

Each ticket in the Tickets table has one or more transactions in the
Transactions table, and each transaction can have zero or more attachments
in the Attachments table, depending on the kind of transaction.

Tim


The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

Un saludo.
Juanjo Corral

Thanks.

But i don�t see the email content, only see the headers.

I�m looking to change the content of the messages.

I see in the attachments tab, a column called content, but says (trough
phpMyAdmin) Binary Not Edit!!

That’s because (as I said in my previous reply), it’s a LONGBLOB data
type as it can also contain images and other forms of attachments.
However, when the content is text (either text/plain or text/html) it’s
perfectly possible to edit it. If you browse the Attachments table using
phpMyAdmin and select “Full texts” and “Show BLOB contents” in the
options, you will be able to view the contents of the Content column
without any problems. You can also see the contents of that field if you
view the table using MySQL on the command line.

You will, however, need to use a tool other than phpMyAdmin to edit it,
as phpMyAdmin won’t allow direct editing of a binary column. There may
be other MySQL administration packages that will do it, although I’m not
able to recommend one as I’m only familiar with phpMyAdmin. Personally,
I would write my own interface to do it using the scripting language of
my choice.

However, if you’re not familiar enough with MySQL in general to be able
to use it without phpMyAdmin (eg, via the command line or from a
scripted interface), then you are not yet experienced or knowledgeable
to be editing tickets in RT directly on the database. That sort of thing
requires a reasonably advanced level of MySQL ability, and if your only
exposure to MySQL is through an intermediary administration tool like
phpMyAdmin then you are not at that level.

Mark

Thanks.

But i don´t see the email content, only see the headers.

I´m looking to change the content of the messages.

I see in the attachments tab, a column called content, but says (trough phpMyAdmin) Binary Not Edit!!

You certainly can edit them with the mysql command line client, but I wouldn’t advise it. Why do you need to edit them anyway?

Tim

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

I need edit to change some characterset. I migrate from mysql to
postgresql. And some local character (spaniard character) text are now
showhing proper.

I wan´t to mantain the Actually encondig and Collate. To make that, i think
that is enought to only change a string like ú to my spaniard character.

Run this sctipt on the rtdb database using pgAdmin 3.

update attachments set content = replace(content, ‘ú’, ‘ú’)

Regards.2012/10/19 Tim Cutts tjrc@sanger.ac.uk

On 19 Oct 2012, at 08:00, Juanjo juanjillo@gmail.com wrote:

Thanks.

But i don´t see the email content, only see the headers.

I´m looking to change the content of the messages.

I see in the attachments tab, a column called content, but says (trough
phpMyAdmin) Binary Not Edit!!

You certainly can edit them with the mysql command line client, but I
wouldn’t advise it. Why do you need to edit them anyway?

Tim


The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

Un saludo.
Juanjo Corral

I need edit to change some characterset. I migrate from mysql to postgresql. And some local
character (spaniard character) text are now showhing proper.

I wan*t to mantain the Actually encondig and Collate. To make that, i think that is enought to
only change a string like u to my spaniard character.

Run this sctipt on the rtdb database using pgAdmin 3.

update attachments set content = replace(content, ‘u’, ‘*’)

If you do run this, please make sure if you report any bugs in the
future to mention that you did a conversion from mysql to pg and then
massaged the data by hand. It’ll help us know that much of the data
shouldn’t be trusted.

If I were you, I’d be trying to make the mysql -> pg conversion part
work without needing the massage afterwards. That just seems fraught
with peril.

-kevin> 2012/10/19 Tim Cutts <[1]tjrc@sanger.ac.uk>

 On 19 Oct 2012, at 08:00, Juanjo <[2]juanjillo@gmail.com> wrote:

 > Thanks.
 >
 > But i don*t see the email content, only see the headers.
 >
 > I*m looking to change the content of the messages.
 >
 > I see in the attachments tab, a column called content, but says (trough phpMyAdmin) Binary
 Not Edit!!

 You certainly can edit them with the mysql command line client, but I wouldn't advise it.
 Why do you need to edit them anyway?
 Tim

Yes you are right. Is very dangerous. But i use a adapted script to make
the migration.
I don have the knowledge to do by myself.

I lost some locale data in the migration.

If run this in rtdb database:

update attachments set content = replace(content, ‘u’, ‘*’)

I believe that these changes are so located, are not capable by
themselves alter
the overall operation off RT.

For example, all the characters in words on content column that i change,
and reindexed now appears in the search.

Im only do that on the test rt installation, not in the production
enviroment. Yet :D2012/10/19 Kevin Falcone falcone@bestpractical.com

If you do run this, please make sure if you report any bugs in the
future to mention that you did a conversion from mysql to pg and then
massaged the data by hand. It’ll help us know that much of the data
shouldn’t be trusted.

If I were you, I’d be trying to make the mysql → pg conversion part
work without needing the massage afterwards. That just seems fraught
with peril.

Un saludo.
Juanjo Corral