Hi,
I will answer to this one because we also did a migration from mysql to Oracle.
We did had some issues for attachements, and the only way we found was to
extract old attachments from mysql and put them on the filesystem.
And now we only use ArticleStorageDB for new attachments (in Oracle), this make
our life easier to have only one backup to care, which is Oracle.
For the process we used:
1) mysqldump --> dumps data to text file
2) create schema in oracle using the otrs-schema.oracle.sql
3) sqlldr (sql loader) to import data from the text file
4) Recreate sequences
I used a php file to export attachments from mysql to filesystem (found on the
mailing list I think)
$cat export_otrs.php
<?php
error_reporting(E_ALL);
set_time_limit(0);
$db = mysql_connect("localhost","USER","PASSWORD") or die(mysql_error())
;
mysql_select_db("otrs",$db) or die(mysql_error());
$basedir="..../otrsdev/var/article/";
$sql="select article.id as ID,content_path,filename,content_type,content
from article , article_attachment WHERE
article.id=article_attachment.article_id";
$result = mysql_query ($sql);
if (mysql_num_rows ($result)>0) {
while ($row = mysql_fetch_array ($result)){
echo $row["content_path"]."/".$row["ID"]."....";
$image_type = $row["content_type"];
$image = $row["content"];
$image_file = $row["filename"];
$destdir=$basedir.$row["content_path"]."/".$row["ID"];
#Header ($image_type);
echo "..mkdir";
system("mkdir -p $destdir");
echo "..chmod";
system("chmod -R 777 $destdir");
$file = fopen($destdir."/".$image_file,"w");
echo "..fwrite";
fwrite($file, $image);
$file = fopen($destdir."/".$image_file.".content_type","w");
echo "..fwrite_content_type";
fwrite($file, $image_type);
echo "..-> ";
echo $destdir."/".$image_file."\n";
}
//$file = fopen("
}
?>
Bastien Leblanc
-----Message d'origine-----
De : [email protected] [mailto:[email protected]] De la part de Michiel
Beijen
Envoyé : mardi 26 octobre 2010 17:08
À : User questions and discussions about OTRS.
Objet : Re: [otrs] Article Attachment Base64 Encoding
Hi Jeff,
that's a nice piece of work you're pulling off there!
Am I correct if I think you're asking how to base64-encode data that's already
there?
If you're on Oracle, you can use Java for it:
http://wiki.oracle.com/thread/1668760/Base64+Encoding%2FDecoding+from+PL%2FSQL
But if you'd ask me I'd prefer to move the articles out of the database by
switching to ArticleStorageFS. It's much faster and usually makes your DBA's
happier because it's less LOB storage in the database.
Can you share the process you used to do this conversion?
Regards,
Mike.
On Tue, Oct 26, 2010 at 3:59 AM, Jeff Travers <[email protected]> wrote:
> We converted our OTRS installation from MySQL to Oracle. For the most
> part, the migration was successful. The only issue we are having is
> that the "Content" column in the Article_Attachment table needs to be
> encoded in BASE64. Is there an easy method in which to accomplish
> this conversion? Is there a DB-Update script we could modify in order
> to encode the data in this column to BASE64?
>
> ---------------------------------------------------------------------
> 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
>
---------------------------------------------------------------------
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
-------
Ce message et toutes les pièces jointes sont établis à l'intention exclusive de
ses destinataires et sont confidentiels. L'intégrité de ce message n'étant pas
assurée sur Internet, la SNCF ne peut être tenue responsable des altérations
qui pourraient se produire sur son contenu. Toute publication, utilisation,
reproduction, ou diffusion, même partielle, non autorisée préalablement par la
SNCF, est strictement interdite. Si vous n'êtes pas le destinataire de ce
message, merci d'en avertir immédiatement l'expéditeur et de le détruire.
-------
This message and any attachments are intended solely for the addressees and are
confidential. SNCF may not be held responsible for their contents whose
accuracy and completeness cannot be guaranteed over the Internet. Unauthorized
use, disclosure, distribution, copying, or any part thereof is strictly
prohibited. If you are not the intended recipient of this message, please
notify the sender immediately and delete it.
---------------------------------------------------------------------
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
