Project

General

Profile

Problem with character after backup and restore databesa data

Added by Bruno Moura about 13 years ago

I have done an upgrade in redmine to version 1.1.1 (provided by Bitnami stack, my previous version was 1.0.2)

And every day I scheduled a cron job to make a backup of redmine data in mysql using mysqldump with the command bellow:

mysqlsump --skip-opt -u$MYSQLUSER -p$MYSQLPASSWORD --quote-names --complete-insert --create-options --quick --lock-tables=false --skip-add-locks $name -r $BACKUPDIR/$name.sql

But I realized that all tables and fields were saved with the latin1 charset:
/*!40101 SET character_set_client = utf8 */;
ENGINE=InnoDB DEFAULT CHARSET=latin1;

After restore database I'm having problems with accents because in my job people use Portuguese and now I have words displayed like this Integração instead Integração and Correção instead Correção.

In my database.yml in not put yet a line to specific the encoding like

encoding: utf-8

But I believed that this charset is the defult in redmine 1.1.1.

There is a good and simple solution for this issue and someone knows show correctly again text contends with accents?

Thanks so much for your help and if more information is necessary I'll provide them.

Bellow is described my environment:

bash-3.2$ RAILS_ENV=production script/about
.../redmine-1.1.1-0/apps/redmine/config/../vendor/rails/railties/lib/rails/gem_dependency.rb:119:Warning: Gem::Dependency#version_requirements is deprecated and will be removed on or after August 2010. Use #requirement

About your application's environment
Ruby version 1.8.7 (i686-linux)
RubyGems version 1.3.7
Rack version 1.0
Rails version 2.3.5
Active Record version 2.3.5
Active Resource version 2.3.5
Action Mailer version 2.3.5
Active Support version 2.3.5
Edge Rails revision unknown
Application root /appl/home/pcsdev/redmine-1.1.1-0/apps/redmine
Environment production
Database adapter mysql
Database schema version 20101114115359

bash-3.2$ rails --version
Rails 2.3.9

bash-3.2$ mysql --version
.../redmine-1.1.1-0/mysql/bin/mysql.bin Ver 14.14 Distrib 5.1.50, for pc-linux-gnu (i686) using readline 5.1


Replies (3)

RE: Problem with character after backup and restore databesa data - Added by Robert Murdock about 12 years ago

I have the same problem.
I installed a new redmine version on a new server and transfered the database from the old server to the new one.
I followed the "how-to" regarding database migration with all the commands like "rake db:" etc and all run smoothly.

However, when i go to the redmine webpage, all the french characters accents are replaced with garbage.
Here is an example : "Redéfinir l’échelle de couleur pour la représentation des écarts"

I tryed to backup the database with --default-character-set=latin1 or utf8, and restore the same way but it doesn't work.

I also connected directly to the old server database to see if the problem was occuring during backup or if it was already corrupted in the database, and i can tell that inside mysql the character are already not saved correctly. But the strange thing is that when viewing a page, everything is correct ? So why i can't do the same thing with the new server ? It should be ok.

help ! Thanks

RE: Problem with character after backup and restore databesa data - Added by Cameron Guill about 12 years ago

The problem is the character set is converted by the OS with the redirection on output. mysqldump --default-character-set=utf8 > file.sql will still be garbled if the OS does not support utf8. If you use --result-file=file.sql the export should be ok. I have had to do this in the past and it works well.

Ex.

mysqlsump --skip-opt -u$MYSQLUSER -p$MYSQLPASSWORD --quote-names --complete-insert --create-options --quick --lock-tables=false --skip-add-locks $name --default-character-set=utf8 --result-file=$BACKUPDIR/$name.sql

Short example:
mysqldump -u user -p pass SCHEMA --single-transaction --default-character-set=utf8 --result-file=FILE.sql

RE: Problem with character after backup and restore databesa data - Added by Robert Murdock about 12 years ago

I found a workaround.

I tried what you said but it didn't work.

I had to comment every "encoding=UTF8" lines in the database.yml file in /opt/bitnami/apps/redmine/config/database.yml

Now i think ruby is using the default system charset or whatsoever, as i'm using latin1 by default. This prevent double encoding of characters.

Thanks !

    (1-3/3)