[rt-users] Migration from MySQL to PostgreSQL - Pg datatypes for blobs?

[Václav Ovsík]

Hi Emmanuel,
thanks for your reply.

On Sat, May 15, 2010 at 07:23:01AM +0200, Emmanuel Lacour wrote:
> With Pg, this content is handled specifically, see _EncodeLOB in
> lib/RT/Record.pm.
> 
> (see also mysql2Pg page on RTwiki).

I saw Base64 encoding in script on wiki page and also elsewhere, but
considered that as workaround to store binary data into Pg text
type-field.

Are you sure, that _EncodeLOB is used only for Pg?
Running grep in lib/RT (git 3.8-trunk):

zito at bobek:/data/soft/rt/rt/lib/RT$ find . -name I18N -prune -o -type f -print |xargs fgrep Pg
./Handle.pm:    elsif ( $db_type eq 'Pg' ) {
./Handle.pm:    elsif ( $db_type eq 'Pg' ) {
./Interface/Web/Session.pm:        Pg    => 'Apache::Session::Postgres',
./Interface/Web/Handler.pm:    unless ( RT->Config->Get('DatabaseType') =~ /(?:mysql|Pg)/ ) {
./Report/Tickets.pm:            elsif ( $db_type eq 'Pg' ) {
./Report/Tickets.pm:        # Pg 8.3 requires explicit casting
./Report/Tickets.pm:        $func .= '::text' if $db_type eq 'Pg';
./Installer.pm:                  } qw/mysql Pg SQLite Oracle/
./Installer.pm:                Pg     => 'PostgreSQL',        #loc
./Test.pm:        # Pg doesn't like if you issue a DROP DATABASE while still connected

I'm not sure.

I think, that Pg cluster initialized to ASCII can handle binary data in
the text data-type, but initialized to UTF-8 not. The correct solution
should be to change types for fields holding binary data to data-type
bytea. Maybe I'm simply not yet understanding the whole thing.


> also, are-you sure that you're mysql DB is using utf-8. If not, you
> should first convert it or convert on the fly in your script.

I hope yes. National characters are displayed fine in the web interface
and mails. The Content field causing problem is MySQL LONGBLOB type. It
can contain binary data.

BTW: I did with old data following in bash script:

make_database()
{
    local pref="$1"; shift
    local db="$1"; shift
    local user="$1"; shift
    local pass="$1"; shift

    mysql -e "CREATE DATABASE $db CHARACTER SET binary;"
    ssh zito at aslan.i.cz cat $aslan_db_dir/$db.sql.gz | zcat | mysql --default-character-set=binary $db
    mysql -e "GRANT ALL ON $db.* TO '$user'@'$RT_DB_HOST' IDENTIFIED BY '$pass';" $db
    export RT_DB_NAME="$db"
    export RT_DB_USER="$user"
    export RT_DB_PASSWORD="$pass"

    mysql $db < unaccent.sql
    mysql $db < update-duplic-emailaddress.sql
    mysql $db < update-duplic-name.sql

    { echo 3.2.2; echo 3.7.87; echo y; } \
        | $RT_SETUP_DATABASE --datadir $SCHEMA_PATH --action=upgrade

    $UPGRADE_MYSQL_SCHEMA $db >/tmp/queries
    mysql $db </tmp/queries

    { echo 3.7.87; echo; echo y; } \
        | $RT_SETUP_DATABASE --datadir $SCHEMA_PATH --action=upgrade

mysql $db < emailaddress-testing.sql

    mig_$db $pref
}


Regards
-- 
Zito