Rt at Glance and upgrade from 3.2.1 to 3.8.0

Hello,

I have been using RT 3.2.1 for several years now and it proved very
robust and useful. I am now considering upgrading to a newer release
and I selected 3.8.0 though it is quite new.

I installed a new server :
The platform is Scientific Linux 5.2 which is a clone of RedHat EL 5.2.

I managed to resolve all perl dependencies : the maximum with a
meta-package I wrote and the remaining 4 or 5 via CPAN.

I loaded my current 3.2.1 database into the new 3.8.0 and performed
the upgrade procedure with “make upgrade”.

It seems OK but the first noticeable thing is the RT at Glance page
which is empty for all users. We used to have the list of unowned
tickets and the listed of tickets owned by the logged user for
the only queue we have. These elements are not available in the
list.

=> The questions are :

a) Is it normal ?

b) I created a system-wide saved search and it was avalable in the
list, so, I suppose these Elements should correspond to predefined
searches… Do I have to create these searches myself ?
If yes, how do I create a generic search for “Tickets I own” ?

c) In general : what is the logic to make Elements available in the
list for RT at a Glance ?

Thanks.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

hi:
your upgrade procedure is like what i did, which is wrong.
if you use mysql, you should run the mysql upgrade script first,
then run the rt upgrade script. or you will get empty “RT at glance” page.2008/8/6 Jean-Michel Barbet Jean-Michel.Barbet@subatech.in2p3.fr:

Hello,

I have been using RT 3.2.1 for several years now and it proved very
robust and useful. I am now considering upgrading to a newer release
and I selected 3.8.0 though it is quite new.

I installed a new server :
The platform is Scientific Linux 5.2 which is a clone of RedHat EL 5.2.

I managed to resolve all perl dependencies : the maximum with a
meta-package I wrote and the remaining 4 or 5 via CPAN.

I loaded my current 3.2.1 database into the new 3.8.0 and performed
the upgrade procedure with “make upgrade”.

It seems OK but the first noticeable thing is the RT at Glance page
which is empty for all users. We used to have the list of unowned
tickets and the listed of tickets owned by the logged user for
the only queue we have. These elements are not available in the
list.

=> The questions are :

a) Is it normal ?

b) I created a system-wide saved search and it was avalable in the
list, so, I suppose these Elements should correspond to predefined
searches… Do I have to create these searches myself ?
If yes, how do I create a generic search for “Tickets I own” ?

c) In general : what is the logic to make Elements available in the
list for RT at a Glance ?

Thanks.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

d tbsky wrote:

hi:
your upgrade procedure is like what i did, which is wrong.
if you use mysql, you should run the mysql upgrade script first,
then run the rt upgrade script. or you will get empty “RT at glance” page.

Thanks very much,

So, trying to follow directions posted in thread :
“RT at a Glance HASH ref error during upgrade from 3.4 to 3.8”,
I am reloading my RT 3.2.1/mysql 4.0.20 database in the new
RT 3.8.0/mysql 5.0.45. to do again the upgrade procedure.

If I understood well, steps are :

a) save DB on old server :

mysqldump -u rt_user -p xxx --single-transaction --databases rt3 > dump

b) reload DB on new server :

mysql -u root -p < dump

c) launch mysql upgrade script : etc/upgrade/schema.mysql-4.0-4.1.pl

d) update the schema (I suppose the best is with the following) :

/opt/rt3/sbin/rt-setup-database --dba root
–prompt-for-dba-password --action upgrade

Unfortunately the mysql upgrade script fails :

[root]# perl rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl rt3 rt_user xxxxx
DBD::mysql::db column_info failed: column_info doesn’t support column
selection at rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl line 227.
DBD::mysql::db column_info failed: column_info doesn’t support column
selection at rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl line 227.

Also, I am not sure if I have to run this mysql update script …

I do not know what is the default Mysql character set in 4.0 and
I do not know how to set it in the new Mysql 5.0… The systems both
have locale set to en_US.UTF-8…

Thanks for advices.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

hi:
maybe your version of DBD::mysql is too old?
you can try rt3.8.1rc2, the “make testdeps” will check DBD::mysql version.
i got errors before upgrade my DBD::mysql, but i forgot the error message.2008/8/7 Jean-Michel Barbet Jean-Michel.Barbet@subatech.in2p3.fr:

d tbsky wrote:

hi:
your upgrade procedure is like what i did, which is wrong.
if you use mysql, you should run the mysql upgrade script first,
then run the rt upgrade script. or you will get empty “RT at glance” page.

Thanks very much,

So, trying to follow directions posted in thread :
“RT at a Glance HASH ref error during upgrade from 3.4 to 3.8”,
I am reloading my RT 3.2.1/mysql 4.0.20 database in the new
RT 3.8.0/mysql 5.0.45. to do again the upgrade procedure.

If I understood well, steps are :

a) save DB on old server :

mysqldump -u rt_user -p xxx --single-transaction --databases rt3 > dump

b) reload DB on new server :

mysql -u root -p < dump

c) launch mysql upgrade script : etc/upgrade/schema.mysql-4.0-4.1.pl

d) update the schema (I suppose the best is with the following) :

/opt/rt3/sbin/rt-setup-database --dba root
–prompt-for-dba-password --action upgrade

Unfortunately the mysql upgrade script fails :

[root]# perl rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl rt3 rt_user xxxxx
DBD::mysql::db column_info failed: column_info doesn’t support column
selection at rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl line 227.
DBD::mysql::db column_info failed: column_info doesn’t support column
selection at rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl line 227.

Also, I am not sure if I have to run this mysql update script …

I do not know what is the default Mysql character set in 4.0 and
I do not know how to set it in the new Mysql 5.0… The systems both
have locale set to en_US.UTF-8…

Thanks for advices.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

d tbsky wrote:

hi:
maybe your version of DBD::mysql is too old?
you can try rt3.8.1rc2, the “make testdeps” will check DBD::mysql version.
i got errors before upgrade my DBD::mysql, but i forgot the error message.

Thanks,

The problem of having an empty “RT at Glance” page is now fixed.
But I have problems with accents not displayed correctly (see
at the end).

Here is the log of my actions :

a) Starting MySQL for the first time and putting a root password :

service mysqld start
Initializing MySQL database: Installing MySQL system tables…
OK
/usr/bin/mysqladmin -u root password ‘xxxxxxxx’

b) Install the RT 3.8.0 initial database

(I believe this has to be done to create users and rights for the rt3
database in mysql )

make initialize-database
/usr/bin/perl sbin/rt-setup-database --action init --dba root
–prompt-for-dba-password

c) Install a newer DBD::mysql module than the one from distro
(the installed version was perl-DBD-MySQL-3.0007-1.fc6).
This is to make the script etc/upgrade/schema.mysql-4.0-4.1.pl
work.

perl -MCPAN -e shell
install DBD:mysql

d) dump the data from RT 3.2.1 and load them in the new RT 3.8.0

oldserver>
mysqldump -u rt_user --password=xxxxxxx --single-transaction
–databases rt3 > rt3-migrate.sql

newserver>
mysql -u root -p --default-character-set=binary < ~/rt3-migrate.sql
Enter password:

e) run mysql upgrade script. I had to modify the script because
some tables do not exist in the DB from 3.2.1 :

diff etc/upgrade/schema.mysql-4.0-4.1-new.pl
etc/upgrade/schema.mysql-4.0-4.1.pl
47a48,49

ObjectCustomFields
ObjectCustomFieldValues

cd rt-3.8.0
perl etc/upgrade/schema.mysql-4.0-4.1-new.pl rt3
rt_user xxxxxx > etc/upgrade/upgrade-mysql-4-5.sql
mysql -u root -p rt3 < etc/upgrade/upgrade-mysql-4-5.sql

f) upgrade schemas etc from 3.2.1 to 3.8.0 :

/opt/rt3/sbin/rt-setup-database --dba root
–prompt-for-dba-password --action upgrade

=> Now I have another problem :

In the ticket lists diplayed on the “RT at Glance” page, the
french accentued characters are replaced by a comma. I could send
a new request on the list for that but I suppose it is linked
to this issue of character sets !

Moreover, in some elements, french accents are correctly
displayed and in some not :

Not Correct :

  • Titles of migrated tickets
  • Names of people acting on the tickets
  • Name of attachments if they have accents for newly created tickets
  • Body (template) of email message to the sender in reponse for a new
    ticket
    Correct :
  • Body of migrated tickets
  • Titles of newly created tickets

If someone can help …

JM

Jean-Michel.

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

hi:
why are you using rt 3.8.0? you can try rt 3.8.1rc2. it is utf8 ok.
if you use 3.8.0, you may need to tune mysql utf8 parameters.2008/8/8 Jean-Michel Barbet Jean-Michel.Barbet@subatech.in2p3.fr:

d tbsky wrote:

hi:
maybe your version of DBD::mysql is too old?
you can try rt3.8.1rc2, the “make testdeps” will check DBD::mysql version.
i got errors before upgrade my DBD::mysql, but i forgot the error message.

Thanks,

The problem of having an empty “RT at Glance” page is now fixed.
But I have problems with accents not displayed correctly (see
at the end).

Here is the log of my actions :

a) Starting MySQL for the first time and putting a root password :

service mysqld start
Initializing MySQL database: Installing MySQL system tables…
OK
/usr/bin/mysqladmin -u root password ‘xxxxxxxx’

b) Install the RT 3.8.0 initial database

(I believe this has to be done to create users and rights for the rt3
database in mysql )

make initialize-database
/usr/bin/perl sbin/rt-setup-database --action init --dba root
–prompt-for-dba-password

c) Install a newer DBD::mysql module than the one from distro
(the installed version was perl-DBD-MySQL-3.0007-1.fc6).
This is to make the script etc/upgrade/schema.mysql-4.0-4.1.pl
work.

perl -MCPAN -e shell
install DBD:mysql

d) dump the data from RT 3.2.1 and load them in the new RT 3.8.0

oldserver>
mysqldump -u rt_user --password=xxxxxxx --single-transaction
–databases rt3 > rt3-migrate.sql

newserver>
mysql -u root -p --default-character-set=binary < ~/rt3-migrate.sql
Enter password:

e) run mysql upgrade script. I had to modify the script because
some tables do not exist in the DB from 3.2.1 :

diff etc/upgrade/schema.mysql-4.0-4.1-new.pl
etc/upgrade/schema.mysql-4.0-4.1.pl
47a48,49

ObjectCustomFields
ObjectCustomFieldValues

cd rt-3.8.0
perl etc/upgrade/schema.mysql-4.0-4.1-new.pl rt3
rt_user xxxxxx > etc/upgrade/upgrade-mysql-4-5.sql
mysql -u root -p rt3 < etc/upgrade/upgrade-mysql-4-5.sql

f) upgrade schemas etc from 3.2.1 to 3.8.0 :

/opt/rt3/sbin/rt-setup-database --dba root
–prompt-for-dba-password --action upgrade

=> Now I have another problem :
###############################

In the ticket lists diplayed on the “RT at Glance” page, the
french accentued characters are replaced by a comma. I could send
a new request on the list for that but I suppose it is linked
to this issue of character sets !

Moreover, in some elements, french accents are correctly
displayed and in some not :

Not Correct :

  • Titles of migrated tickets
  • Names of people acting on the tickets
  • Name of attachments if they have accents for newly created tickets
  • Body (template) of email message to the sender in reponse for a new
    ticket
    Correct :
  • Body of migrated tickets
  • Titles of newly created tickets

If someone can help …

JM

Jean-Michel.

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

d tbsky wrote:

hi:
why are you using rt 3.8.0? you can try rt 3.8.1rc2. it is utf8 ok.
if you use 3.8.0, you may need to tune mysql utf8 parameters.

This was because “rc” usually means “Release Candidate” and I wanted
to install a stable release but this may be a wrong assumption…

I could give a try, I suppose an upgrade from 3.8.0 is not too
difficult but I hope the perl module tree has not changed too much
between these 2 versions.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

hi:
it is a little fix.almost the same as 3.8.0. and utf8 fix is what
we need. so i don’t need to tune mysql utf8 parameter.
by the way, can you use the new “WYSIWYG” editor? i still don’t know
where i can see the result.

Regards,
tbskyd2008/8/8 Jean-Michel Barbet Jean-Michel.Barbet@subatech.in2p3.fr:

d tbsky wrote:

hi:
why are you using rt 3.8.0? you can try rt 3.8.1rc2. it is utf8 ok.
if you use 3.8.0, you may need to tune mysql utf8 parameters.

This was because “rc” usually means “Release Candidate” and I wanted
to install a stable release but this may be a wrong assumption…

I could give a try, I suppose an upgrade from 3.8.0 is not too
difficult but I hope the perl module tree has not changed too much
between these 2 versions.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

d tbsky wrote:

hi:
it is a little fix.almost the same as 3.8.0. and utf8 fix is what
we need. so i don’t need to tune mysql utf8 parameter.

After looking at the way the mysql update procedure works, I believe
it only changes the column charset and collatiin definition, not the
data
, so if some data where stored in latin1 in my previous Mysql
4.0 database, the column type will change to binary, then to utf8
and data will not be displayed correctly.

Can other people comment on this ?

by the way, can you use the new “WYSIWYG” editor? i still don’t know
where i can see the result.

If I try to reply to a ticket in RT web interface, there is a tiny
black triangle on top of the message box and it expands in a set of
tools to get rich text, then I click save and I can send the message.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

d tbsky wrote:

hi:
it is a little fix.almost the same as 3.8.0. and utf8 fix is what
we need. so i don’t need to tune mysql utf8 parameter.

After looking at the way the mysql update procedure works, I believe
it only changes the column charset and collatiin definition, not the
data
, so if some data where stored in latin1 in my previous Mysql
4.0 database, the column type will change to binary, then to utf8
and data will not be displayed correctly.

yes the data are converted to utf8, so rt must treat them as utf8.
but mysql default use latin1 for connection, so the data are corrupt
when present in the browser. either patch rt (rt 3.8.1rc2) or force mysql
client parameters to use utf8 connection and we can have utf8 from
mysql to browser.

Can other people comment on this ?

by the way, can you use the new “WYSIWYG” editor? i still don’t know
where i can see the result.

If I try to reply to a ticket in RT web interface, there is a tiny
black triangle on top of the message box and it expands in a set of
tools to get rich text, then I click save and I can send the message.

that’s what i want to ask. after using the editor and save,where can
you see the rich text affect? i only see plain text after save. and i
get plain text email.
thanks for your help!!

by the way, can you use the new “WYSIWYG” editor? i still don’t know
where i can see the result.

If I try to reply to a ticket in RT web interface, there is a tiny
black triangle on top of the message box and it expands in a set of
tools to get rich text, then I click save and I can send the message.

that’s what i want to ask. after using the editor and save,where can
you see the rich text affect?

Simple HTML things like bold and italic should pass right through and
show up in the transaction history. The PreferRichText config option
may help show other pieces of the puzzle.

RT scrubs HTML rather aggressively and we may need to tweak
share/html/Elements/ScrubHTML.

It would be great if you poked at the options in that file until it
showed you what you want to see in the history and submitted a
patch to rt-bugs@bestpractical.com

i only see plain text after save. and i
get plain text email.

You need to tweak your templates slightly in order to send html mail,
but it seems to tickle a bug.

You should get mail about a bug report in a few minutes.

-kevin

d tbsky wrote:

d tbsky wrote:

hi:
it is a little fix.almost the same as 3.8.0. and utf8 fix is what
we need. so i don’t need to tune mysql utf8 parameter.

After looking at the way the mysql update procedure works, I believe
it only changes the column charset and collatiin definition, not the
data
, so if some data where stored in latin1 in my previous Mysql
4.0 database, the column type will change to binary, then to utf8
and data will not be displayed correctly.

yes the data are converted to utf8, so rt must treat them as utf8.
but mysql default use latin1 for connection, so the data are corrupt
when present in the browser. either patch rt (rt 3.8.1rc2) or force mysql
client parameters to use utf8 connection and we can have utf8 from
mysql to browser.

I have an issue during conversion where it sees all the accents as the
same (in the database with the unique key on name in Users). It should
see them as distinct after conversion. Do the temp tables use it’s own
charset? I remember mssql use to have a similar issue where the system
default could be different from the database and joins and such go all
wrong… Are the temp tables possibly not doing UTF*? I’m not 100%, to
get around it before i just did a straight alter with no binary, but i
think it was storing latin1 inside utf8 fields… so now I have the
original issue.

ERROR 1062 (23000): Duplicate entry ‘securite@test.addy’ for key 2

If I… SELECT name FROM Users WHERE name LIKE ‘securite@test.addy’;
securit�@test.addy
securite@test.addy

I tried converting the table by removing the index before hand and it
errors the same when i go to put it back.

Does anyone have any insight as to why mysql sees this as the same? the
client charset doesn’t affect it just the display.

Can other people comment on this ?

by the way, can you use the new “WYSIWYG” editor? i still don’t know
where i can see the result.

If I try to reply to a ticket in RT web interface, there is a tiny
black triangle on top of the message box and it expands in a set of
tools to get rich text, then I click save and I can send the message.

that’s what i want to ask. after using the editor and save,where can
you see the rich text affect? i only see plain text after save. and i
get plain text email.
thanks for your help!!


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Thanks!
Curtis.

Curtis Bruneau wrote:

d tbsky wrote:

d tbsky wrote:

hi:
it is a little fix.almost the same as 3.8.0. and utf8 fix is what
we need. so i don’t need to tune mysql utf8 parameter.

After looking at the way the mysql update procedure works, I believe
it only changes the column charset and collatiin definition, not the
data
, so if some data where stored in latin1 in my previous Mysql
4.0 database, the column type will change to binary, then to utf8
and data will not be displayed correctly.

yes the data are converted to utf8, so rt must treat them as utf8.
but mysql default use latin1 for connection, so the data are corrupt
when present in the browser. either patch rt (rt 3.8.1rc2) or force mysql
client parameters to use utf8 connection and we can have utf8 from
mysql to browser.

I have an issue during conversion where it sees all the accents as the
same (in the database with the unique key on name in Users). It should
see them as distinct after conversion. Do the temp tables use it’s own
charset? I remember mssql use to have a similar issue where the system
default could be different from the database and joins and such go all
wrong… Are the temp tables possibly not doing UTF*? I’m not 100%, to
get around it before i just did a straight alter with no binary, but i
think it was storing latin1 inside utf8 fields… so now I have the
original issue.

ERROR 1062 (23000): Duplicate entry ‘securite@test.addy’ for key 2

If I… SELECT name FROM Users WHERE name LIKE ‘securite@test.addy’;
securit�@test.addy
securite@test.addy

I tried converting the table by removing the index before hand and it
errors the same when i go to put it back.

Does anyone have any insight as to why mysql sees this as the same? the
client charset doesn’t affect it just the display.

Can other people comment on this ?

by the way, can you use the new “WYSIWYG” editor? i still don’t know
where i can see the result.

If I try to reply to a ticket in RT web interface, there is a tiny
black triangle on top of the message box and it expands in a set of
tools to get rich text, then I click save and I can send the message.

that’s what i want to ask. after using the editor and save,where can
you see the rich text affect? i only see plain text after save. and i
get plain text email.
thanks for your help!!


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

I looked into this a bit more, It has to do with the default collation
"utf8_general_ci" which doesn’t see accents as distinct ( i think ).
http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html There’s
obviously issues to each one as the rules change… I also read stuff
about the server collation affecting sorting also… I’m going to try
utf8_unicode_ci but it may have the same issue, I saw someone else use
utf8_bin to solve the issue. I’ll look into it more and post my results.

Curtis

You need to tweak your templates slightly in order to send html mail,
but it seems to tickle a bug.

Are these tweaks documented in the wiki or elsewhere? HTML-mail
templates are pretty high on my users’ most-wanted list.

(Oh, yeah, and I guess info on the associated bug would be useful
too…)

Mark Roedel
Senior Programmer / Analyst
LeTourneau University

You need to tweak your templates slightly in order to send html mail,
but it seems to tickle a bug.

Are these tweaks documented in the wiki or elsewhere? HTML-mail
templates are pretty high on my users’ most-wanted list.

(Oh, yeah, and I guess info on the associated bug would be useful
too…)

There is an open bug, but it is a 3.8.2 blocker for now

I Really want to get 3.8.1 shipped soon, we only have one pending
bug. Fixing the HTML mail problem is going to be a little too
invasive this late in the RC process, especially since it isn’t a
regression. Documentation will be part of the fix.

-kevin

Jean-Michel Barbet wrote:

The problem of having an empty “RT at Glance” page is now fixed.
But I have problems with accents not displayed correctly (see
at the end).

The problem with accents is fixed. It seems I was already using
the utf8 character set under RT 3.2.1 and Mysql 4.0
(Fedora Core release 2 (Tettnang)).

With 2 modifications to the operating procedure I gave, the
accents are correct :

a) I no longer use the --default-character-set=binary when loading
the data dumped from RT 3.2.1 :

mysql -u root -p  < ~/rt3-migrate.sql

b) I added the following line at the top of the dump rt3-migrate.sql
right after the create database instruction :

ALTER DATABASE rt3 DEFAULT CHARACTER SET utf8;

Thanks especially to d.tbsky for support and clues.

JM Barbet

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

Jean-Michel Barbet wrote:

The problem with accents is fixed.

Sorry, I spoke too soon. I still have a problem with the column
"Content" of the Attachements table.

In RT 3.2.1/MySQL4.0 this column is longtext

The mysql upgrade scripts does this :

ALTER TABLE Attachments MODIFY Content LONGBLOB NULL DEFAULT NULL;

which results in corrupted accents

If I change back to LONGTEXT, it is OK.

=> What should I do ? Is is important that the type of this column
is LONGBLOB in RT 3.8.0 and if yes, how do I convert from RT 3.2.1
without corrupting the accents ?

Thanks and sorry to bring new issues.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

  1. try 3.8.1rc5
  2. do you upgrade mysql as well?On Tue, Aug 12, 2008 at 10:55 AM, Jean-Michel Barbet Jean-Michel.Barbet@subatech.in2p3.fr wrote:

Jean-Michel Barbet wrote:

The problem with accents is fixed.

Sorry, I spoke too soon. I still have a problem with the column
"Content" of the Attachements table.

In RT 3.2.1/MySQL4.0 this column is longtext

The mysql upgrade scripts does this :

ALTER TABLE Attachments MODIFY Content LONGBLOB NULL DEFAULT NULL;

which results in corrupted accents

If I change back to LONGTEXT, it is OK.

=> What should I do ? Is is important that the type of this column
is LONGBLOB in RT 3.8.0 and if yes, how do I convert from RT 3.2.1
without corrupting the accents ?

Thanks and sorry to bring new issues.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

Ruslan Zakirov wrote:

  1. try 3.8.1rc5
  2. do you upgrade mysql as well?

Hi Ruslan,

No, I did not try 3.8.1rc5, I am still with 3.8.0.
What I am doing is transfering the RT databse from a server under
Fedora Core release 2 (Tettnang), MySQL-server-4.0.20-0, RT 3.2.1
to a distinct newly installed server under Scientific Linux SL release
5.2 (Boron) (equivalent to a RHEL 5.2) with mysql-server-5.0.45-7.el5
and RT 3.8.0.

Finally I made a satisfying migration by doing the following :

  1. dump separately the data and the structure of existing RT3.2.1
    database

  2. load the structure only on the new RT (not the data)

  3. run a scrip that declares the column type to UTF8 for each field
    that I want to be preserved because it is already in UTF8;
    do not modify the table default character yet (example for the
    column “Subject” of table “Attachments” :
    USE rt3;
    ALTER TABLE Attachments MODIFY Subject VARBINARY(255) NULL DEFAULT NULL;
    ALTER TABLE Attachments MODIFY Subject VARCHAR(255) CHARACTER SET utf8
    NULL DEFAULT NULL;

  4. load the data on the new RT

  5. create and run the mySQL upgrade script :

perl etc/upgrade/schema.mysql-4.0-4.1-new.pl rt3 rt_user xxxxx >
mysqlupgrade.sql
mysql -u root -p rt3 < mysqlupgrade.sql

  1. upgrade the schema :

/opt/rt3/sbin/rt-setup-database --dba root
–prompt-for-dba-password --action upgrade
[…]
Enter RT version you’re upgrading from: 3.2.1
[…]

  1. restart server

rm -fr /opt/rt3/var/mason_data/obj
service httpd restart

With this procedure, I am happy :slight_smile:

Your comments are welcome.

Especially, should I use 3.8.1rc5 for production rather than 3.8.0 ?
and why …

Thank you very much.

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr