[rt-users] Migration from MySQL to PostreSQL - corrupt characters (german umlaut)
Hi RT Users, I'm testing the migration from MySQL to PostgreSQL and I'm experiencing problems with LATIN1 characters (particularly German umlauts) after the migration. They look like ü instead ü Hexcode from ü → c3 bc is encoded again to → c3 83 (Ã) and c2 bc (¼) (http://www.utf8-zeichentabelle.de/) First I've upgraded RT from 3.8 to 4.2 with mysql db (utf8) and everything went smoothly. New server with fresh installed RT, MySQL, Postgresql from package manager System (Debian Jessie) request-tracker: 4.2.8-3+deb8u1 mysql-server: 5.5.44-0+deb8u1 postresql: 9.4.4-0+deb8u1 apache: 2.4.10-10+deb8u3 php: 5.6.13+dfsg-0+deb8u1 Now I followed this tutorial to migrate the DB http://requesttracker.wikia.com/wiki/MigrateMysql2PostgresqlV4 The binary files, generated by rt-serializer --clone, include characters like öäü so I suppose that there is something going wrong while importing to Pg. 'rt-setup-database' creates the Pg DB as UTF8. postgres=# \l List of databases Name| Owner | Encoding | Collate | Ctype | Access privileges ---+--+---+-+---+--- postgres | postgres | SQL_ASCII | C | C | rt4 | rt_user | UTF8 | C | C | template0 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres postgres=# show server_encoding; server_encoding - SQL_ASCII (1 row) postgres=# show client_encoding; client_encoding - UTF8 (1 row) rt4=# select subject from tickets where id=82527; subject -- Hardware prüfen (1 row) Newly created tickets with umlauts are displayed correctly only the imported are corrupt! Switching the client_encoding via “set client_encoding='LATIN1';” or directly in /etc/postgresql/9.4/main/postgresql.conf client_encoding = latin1#(default would be the database encoding utf8) rt4=# set client_encoding='LATIN1'; SET rt4=# select subject from tickets where id=82527; subject -- Hardware prüfen (1 row) Can someone help me to migrate the db with a full utf8 setup? Best, Maik smime.p7s Description: S/MIME Cryptographic Signature
Re: [rt-users] Migration from MySQL to PostreSQL - corrupt characters (german umlaut)
RT 4.2.8 and MySQL: old and new tickets looks perfectly, also the serialized data seems to be ok. With locale LANG=en_US.UTF-8 ^^test ticket with umlaut ü^@^@^@^GSubject locale LANG=POSIX ^^test ticket with umlaut ^@^@^@^GSubject Maybe rt-importer or perl opens the binary files with wrong locales and while writing to the utf8 Pg DB all characters () are being re-encoded? I'm now testing to serialize mysql and import to mysql again. smime.p7s Description: S/MIME Cryptographic Signature
Re: [rt-users] Migration from MySQL to PostreSQL - corrupt characters (german umlaut)
I'm now testing to serialize mysql and import to mysql again. This is working, so it's a problem with Pg smime.p7s Description: S/MIME Cryptographic Signature
Re: [rt-users] Migration from MySQL to PostreSQL - corrupt characters (german umlaut)
Hi, you are right. History is not affected but Subject, User Real Name, Signature stored in User Preferences, Description of Scrips, Templates, Groups and Queues, Template Content, Article Name I noticed that the problem with the german umlauts is not in all fields. In my history for example the german umlauts displayed correctly but in the ticket subject they didn't. smime.p7s Description: S/MIME Cryptographic Signature
Re: [rt-users] Migration from MySQL to PostreSQL - corrupt characters (german umlaut)
I found an older post on the PostgreSQL mailinglist with same problems http://www.postgresql.org/message-id/d960cb61b694cf459dcfb4b0128514c207bb6...@exadv11.host.magwien.gv.at And I was able to fix the issue by dumping and re-importing the db! # Create empty PG-DB rt-setup-database --action create,schema,acl --dba rt_user --dba-password DBPASS # Run importer rt-importer PATH # Run Update-Pg-Sequences Script (http://requesttracker.wikia.com/wiki/MigrateMysql2PostgresqlV4#Import_your_RT_database) # Dump DB as latin1 pg_dump -E LATIN1 rt4 > rt4latin.sql # Replace client_encoding sed -i 's/LATIN1/UTF8/' rt4latin.sql # Recreate empty DB rt-setup-database --action drop --dba rt_user --dba-password DBPASS rt-setup-database --action create,schema,acl --dba rt_user --dba-password DBPASS # Import DB psql rt4 < rt4latin.sql smime.p7s Description: S/MIME Cryptographic Signature
Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.
Hey Greg, please check if your $Organization variable is set in RT_SiteConfig.pm It must fit to the values of field "base" and "target" in table "links" If your code snipped is correct then Set( $Organization, 'example.com' ); best Maik smime.p7s Description: S/MIME Cryptographic Signature
Re: [rt-users] How to clone the ticket
On 01/08/2016 12:11 PM, Ashley Etherington wrote: You can create a child of the ticket using the links section Open your existing ticket and look at the links section and click on create. This will "clone" your ticket and all custom fields Link should look like: Create.html?Queue=12&CloneTicket=84816&new-MemberOf=84816 BTW: Is there any possibility to clone the whole or selected content too? smime.p7s Description: S/MIME Cryptographic Signature
[rt-users] Delivery Status Notification
Hi @all, is there a simple way to proof, that a message and the attachment was delivered to the recipient? It would be much better if the recipient has to confirm, that the message arrived and he has read it. best Maik smime.p7s Description: S/MIME Cryptographic Signature - RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractical.com/training * Boston - October 24-26 * Los Angeles - Q1 2017