trouble on migration from 2.4.5 to 2.4.7

[leocertuche]

Hi there,

Last night we migrated our production server from 2.4.5 to 2.4.7. Since both are 2.4.* we didn't run any DBUpdate-to-2.4-post* script, just installed 2.4.7 on a fresh server (opensuse 11.2), moved the database from the old one (opensuse 10.3), moved all customizations and started the new server. Everything seemed fine at first but today we're facing two issues:

1. Special characters from spanish like ñ, á, é, í, ó, ú are not correctly shown on the front end. For instance there is a queue called Campañas which looks perfect on phpmyadmin but it's shown on the front end as Campañas. Charset on the database remains the same of the old server (utf8_general_ci) while DefaultCharset on sysconfig was iso-8859-1. I've changed it to variations such as iso-8859-15 (which appears as the a_content_type of most articles) but still no luck

2. We're getting the following error randomly when trying to create new tickets:
Error: Can't get ArticleID from INSERT!
I was googling about this error but there are only old entries like this: http://bugs.otrs.org/show_bug.cgi?id=319

Thanks in advance for any help.

[leocertuche]

Hello,

We're still facing some problems but have solved some others. Here's the current status:

Text with spanish characters were truncated from tickets, event attachments were gone. After running REPARE TABLE to the database tables, part of the problem was solved but our queues named with spanish characters still show wrong

Any hint towards resolving this issue is much appreciated, here's the environment:

openSUSE 11.2 "Emerald" - Kernel "
MySQL 5.1.36-log
Perl 5.10.0 (linux)
Apache/2.2.13
mod_perl/2.0.4
collation on database: utf8_general_ci
DefaultCharSet on SysConfig is iso-8859-1

Thanks,

[leocertuche]

We're closer!

Comparing the server characterset of the old mysql and the new one, we found that the old one was latin1 while the new one is utf8, we also found a nice FAQ entry that explains how to do the opposite procedure at http://faq.otrs.org/otrs/public.pl?Acti ... ItemID=315 so we'll follow it "backwards" tonight and let you know.

Thanks for listening

[leocertuche]

Hello,

I'd love to have better news, but I don't, here is the outcome from following the http://faq.otrs.org/otrs/public.pl?Acti ... ItemID=315 article:

-mysqldump seems to have a problem dealing with large backups (see http://bugs.mysql.com/bug.php?id=9756 and http://bugs.mysql.com/bug.php?id=40789). Our database is 3GB, backup happens, but when trying to restore it we got errors such as Unknown command '\0'. or Unknown command '\"'.

-after mysqldump, the FAQ article recommends a drop of the database. WRONG ADVICE! it'd be safer to recommend a mv /var/lib/mysql/otrs /var/lib/mysql/otrs2. At least that way you won't loose the data that your just-created-backup won't let you restore

-following step is running /opt/otrs/scripts/tools/charset-convert.pl but this script returns an Out of memory! message. I changed ulimit memory values to the maximum but the out of memory message keeps showing

So it was impossible for us to follow the FAQ article steps. mysqldump seems to be working with smaller files so we're currently trying to open the 3GB sql file with a text editor (emeditor seems to be the weapon of choice) to remove the heaviest insert lines (the ones during the first month when we used to store attachments on database) to see if it can be restored.

Any advice is much appreciated.

Thanks,

[jojo]

Please have a look at the MySQL manual.

You can make mysql dumps being much larger than 3GB an reimporting them.

Have you thought on buying some help for an OTRS update?

[leocertuche]

Hello jojo,

Thanks for your reply and suggestion. More than buying some help, I'll invest on becoming OTRS partner but that won't happen until august, when the training is scheduled in Mexico. In the meantime buying a support contract for the current update is simply out of the budget so we're relying only on the voluntary help from experienced users like you.

So if is there any suggestion toward our current situation, my team and our customer will highly appreciated.

Thanks for your understanding,

[leocertuche]

So after reducing our sql file to 2GB removing "insert into article_attachment" lines, we were able to restore it. Special characters still won't show so we are giving it a try again to the mentioned FAQ article but the charset-convert.pl script keeps saying Out of Memory!

Here is the outout of ulimit and free:

seus:/ # ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) 200000
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 31977
max locked memory (kbytes, -l) 2048000
max memory size (kbytes, -m) 4100200
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 31977
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
seus:/ # free
total used free shared buffers cached
Mem: 4100200 1433376 2666824 0 5364 499072
-/+ buffers/cache: 928940 3171260
Swap: 4192924 411440 3781484

Thanks in advance for your help,

[leocertuche]

We were able to restore our backup preserving special characters by forcing our database to use latin1 as charset, by including the following commands in my.cnf:

[client]

default-character-set = latin1




[mysqld]

init_connect='SET collation_connection = latin1_swedish_ci'

init_connect='SET NAMES latin1'



So instead of forcing our database to work with utf-8, we forced MySQL to use latin1 charset.