[otrs] R: Exporting attachments from DB to FS

[CARNINO Daniele (FIAT SERVICES)]

Thanks for the script, Bohuslav.

I think it'll come in handy, since my db too is getting bigger&bigger....

 

Ciao, d.

---

Da: otrs-b...@otrs.org [mailto:otrs-b...@otrs.org] Per conto di Bohuslav Blín
Inviato: lunedì 29 settembre 2008 21.49
A: ot...@otrs.org
Oggetto: [otrs] Exporting attachments from DB to FS

Hi all,
i had big DB with otrs data, because of many attachments (like JPG, PDF,..). My daily backup was almost 2GB.
So I switch Ticket::StorageModule:from DB to FS, because I backup FS incrementaly.
But I also wanted to export existing data to FS.

So I created script for this. Now my daily backup mysql with otrs is 4MB :)

I hope this script will be helpful for somebody else too.

So here it is:
http://pastie.org/281623

(This script is using Ruby and )

Bohuslav Blin

[Alessio Tosi]

Hi Daniele,

please let me know if you succeed exporting the attachmentes from the db to FS

I've a 200k ticket db that has reached 750MB runnin on Ubuntu 8.04 LTS on a vmware server 1.0.7.

I've already moved all the var/article folder to an iscsi storage (I can confirm that there are no performance issues there) and would be great to shrink the db too.

 

Thx Bohuslav ;-)

AleX

Alessio Tosi
Sistemi Informativi
Tel. +39 0290026.812
Mob. +39 3486024492
OKI Systems (Italia) S.p.A.
via Milano 11
20084 Lacchiarella MI - Italy

View Alessio Tosi's profile 

Any opinions expressed in this email are those of the individual and not
necessarily of the Company. This email and any files transmitted with it,
including replies and forwarded copies (which may contain alterations)
subsequently transmitted from the Company are confidential and solely for
the use of the intended recipient. It may contain material protected by
legal privilege. If you are not the intended recipient or the person
responsible for delivering to the intended recipient, be advised that you
have received this email in error and that any use is strictly prohibited.
Please notify the sender immediately of the error and delete any copies of
this message

 

Warning: The Company cannot accept responsibility for any loss or damage arising from the use of this e-mail or attachments.

 

---

From: otrs-b...@otrs.org [mailto:otrs-b...@otrs.org] On Behalf Of CARNINO Daniele (FIAT SERVICES)
Sent: Tuesday, September 30, 2008 11:40 AM
To: User questions and discussions about OTRS.
Subject: [otrs] R: Exporting attachments from DB to FS

[Bohuslav Blín]

Here are some instructions for exporting attachment data from DB to FS
with my script:

1. Backup your attachment data
- mysqldump otrs article_attachment
- mysqldump otrs article_plain

2. Install ruby and rails on your system
my script uses a part of Ruby On Rails because of their simplicity
If you are not familiar with rails, look here for installation
instructions for your distribution: http://wiki.rubyonrails.org or ask
Google ;)
for Ubuntu 8.04 use this https://help.ubuntu.com/community/RubyOnRails

3. Run my migration script
This script is non-destructive, only export data
Maybe you will need setup correct permitions for new directories and
files, or run
this script as otrs user (you will need permit /bin/bash for this
user in /etc/passwd

4. Drop tables article_plain and article_attachment from otrs db

These steps worked for me. I hope for you too :)

Inscruc

Bohuslav
_______________________________________________
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

[Alex]

Have looked at the documentation for this but am unable to figure out where
these attachments are saved once the parameter is enabled ... ?

$Self->{TicketStorageModule} = 'Kernel::System::Ticket::ArticleStorageFS';

thanks,
alex

[Bohuslav Blín]

Yes.

You can do this also in Admin -> Sysconfig -> Core::Ticket ->
Ticket::StorageModule: FS

2008/9/30 Alex <al...@kukaki.net>:

[Mike Lykov]

В сообщении от Wednesday 01 October 2008 02:46:57 Bohuslav Blín написал(а):

> Yes.
>
> You can do this also in Admin -> Sysconfig -> Core::Ticket ->
> Ticket::StorageModule: FS

Thanks for the idea, Bohuslav.
Can you answer to question about storaging articles on FS?

I'm switched to FS акщь DB.
OTRS create folders by month, day and article number in /var/articles/* .
But what is strange:

If article has an attachment, then in this folder i see this files:

-rw-rw-r-- 1 otrs apache 306 Sep 20 12:42 file-1
-rw-rw-r-- 1 otrs apache 32 Sep 20 12:42 file-1.content_type
-rw-rw-r-- 1 otrs apache 4056505 Sep 20 12:42 pasport.jpg
-rw-rw-r-- 1 otrs apache 30 Sep 20 12:42 pasport.jpg.content_type
-rw-rw-r-- 1 otrs apache 5502648 Sep 20 12:42 plain.txt

.content-type contains description about it.

file-1 is a first text/plain part of letter.
pasport.jpg is an attachment in this article.

BUT why file plain.txt created in _every_ article folder? It contains all the
same in original (npt parsed/decoded) form and double consumed size.

How not to create plain.txt?

--
Mike Lykov
System Administrator, Domain Name Registrar REG.RU
http://www.reg.ru/ +7 495 580-11-11 int. 504

[Bohuslav Blín]

Hi Mike,

2008/10/1 Mike Lykov <co...@reg.ru>:

> If article has an attachment, then in this folder i see this files:
>
> -rw-rw-r-- 1 otrs apache 306 Sep 20 12:42 file-1
> -rw-rw-r-- 1 otrs apache 32 Sep 20 12:42 file-1.content_type
> -rw-rw-r-- 1 otrs apache 4056505 Sep 20 12:42 pasport.jpg
> -rw-rw-r-- 1 otrs apache 30 Sep 20 12:42 pasport.jpg.content_type
> -rw-rw-r-- 1 otrs apache 5502648 Sep 20 12:42 plain.txt
>
> .content-type contains description about it.
>
> file-1 is a first text/plain part of letter.
> pasport.jpg is an attachment in this article.
>
> BUT why file plain.txt created in _every_ article folder? It contains all the
> same in original (npt parsed/decoded) form and double consumed size.
>
> How not to create plain.txt?
>

This is how otrs works. If you look at tables article_attachment and
article_plain, you will see this data.

For example you need file plain.txt when you want to look at plain
(source) email in ticket view.

Bohuslav.

[Alessio Tosi]

/opt/otrs/var/article
Ciao
AleX

Alessio Tosi
Sistemi Informativi
Tel. +39 0290026.812
Mob. +39 3486024492
OKI Systems (Italia) S.p.A.
via Milano 11
20084 Lacchiarella MI - Italy

View Alessio Tosi's LinkedIn profileView Alessio Tosi's profile

Any opinions expressed in this email are those of the individual and not
necessarily of the Company. This email and any files transmitted with it,
including replies and forwarded copies (which may contain alterations)
subsequently transmitted from the Company are confidential and solely for
the use of the intended recipient. It may contain material protected by
legal privilege. If you are not the intended recipient or the person
responsible for delivering to the intended recipient, be advised that you
have received this email in error and that any use is strictly prohibited.
Please notify the sender immediately of the error and delete any copies of

this message

Warning: The Company cannot accept responsibility for any loss or damage
arising from the use of this e-mail or attachments.

-----Original Message-----
From: otrs-b...@otrs.org [mailto:otrs-b...@otrs.org] On Behalf Of