How does Redmine - Rails - Ruby use a MySQL database?

Added by Peter Werner almost 9 years ago

Hello everybody,

I know this subject sounds a little bit strange first, but let me tell you what is behind that.

Introduction: I try to use Redmine as a trouble ticket system for our company. Therefore I went through that painful process of how to write a plugin. I managed that and now I am at point that I could use Redmine.

The current situation: We had a ticket system before which I developed with Delphi using an MS SQL database. And here the trouble started: I tried o import my data using the existing import scripts for Trac and Mantis. But I was not able to connect to MS SQL with Ruby. I read all those hints how to use the sqlserver-adapter, odbc, DevKit to compile extension - nothing was working. So I gave up after two days and had the next idea: Store the data directly into the database.

Nothing special I thought - I know MS SQL, Firebird, MySQL ... So I took Visual Studio 2010 and developed an application. I could read my data and could store my data. But unfortunately my language (German) has some strange characters like ß, ö, ä, ü. When now Redmine loads the data I had written directly into the MySQL database these characters were displayed by Redmine like that: �. But when I took the mysql client to look into the database everything looked O.K.

Then I tried to store such characters with Redmine - and hell! - this was looking like that inside the database: ä. But great with Redmine. I found out that the Bitnami installer installs a MySQL database with character set latin1 as a default. O.K. I changed that database to utf-8 and also every table. So now I got it I thought - how naive I was. Redmine uses utf-8, the database uses utf-8, in .NET you can encode in every charset you like. And tried different ones, But still my data was scrambled!!!!

This is so frustrating for me that I am really close to the point to give up - or enter my old data manually. O.K. I can also drop the current database and make it new with utf-8 from the beginning. But I have already configured Redmine and I would not do that again. Anyway: Maybe someone can make any sense out of that and help me to understand:

  • Why stores Redmine characters like ß,ä,ö,ü in MySQL like that: ä? I changed the database to uft-8, every table uses utf-8,
    in database.yml encoding: utf8 is configured. But still it looks like that. But what can I do with such data in the future?
  • Why my data looks scrambled with Redmine when stored directly into the database although it looks great in the mysql client?

I know I not very fit with that encoding stuff. I am not a good developer too. But why is it so difficult to get data from one database to another? Thanks in advance for any idea how to solve my problem.

:-)Peter

Replies (9)

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by Felix Schäfer over 8 years ago

If I read you correctly, this is an encoding problem. If you have everything from the ground up UTF-8 and every program that accesses does so in UTF-8, it shouldn't be a problem.

If you had redmine in UTF-8 and MySQL with latin-1, you first need to change the collation of all tables from latin-1 to UTF-8 and convert the contents of the DBs from latin-1 to UTF-8, only changing the collation is not enough.

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by F. D. Castel over 8 years ago

Funny! I'm having EXACTLY the same problem now (Bitnami RedMine 1.0.3 + VS 2010 app = Encoding problems).

Eight months ago I did a little application in C# / VS 2010 to pump data from our old bugtracker to RedMine. Everything was working fine, including the accents / encoding. Unfortunately, I had to put that project on hold. And I don't remember now which RedMine version I was using at time.

Today, I built a new RedMine installation (from Bitnami, 1.0.3) and I ran the C# program again. This time, the encoding was broken! Exactly the way you described (Redmine showing �, etc). Now I'm trying to discover WHAT is wrong.

My steps were:

  • Installed Bitnami RedMine on a Bitnami RubyStack VM
  • Added line "encoding: utf8" in 'production' database (database.yml)
  • Rebuilt Redmine database as UTF-8:
mysql -uroot -pbitnami -e "drop database bitnami_redmine;" 
mysql -uroot -pbitnami -e "create database bitnami_redmine character set utf8;" 
cd /opt/bitnami/apps/redmine/
rake generate_session_store
rake db:migrate RAILS_ENV=production
rake redmine:load_default_data RAILS_ENV=production
  • Ran the C# application

As I said, these very same steps DID worked some time ago. The encoding was correct. But, today, something is broken.

I didn't touch the code so I'm wondering what may have changed in RedMine or MySql since then.

BTW: My C# app always used the following connection string:

<add name="Redmine" connectionString="server=REDMINESERVER;uid=root;pwd=bitnami;database=bitnami_redmine;charset=utf8;" providerName="MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>

And I'm using the same MySql .NET Provider (I didn't update it)

Any tips or ideas are (very) welcome.

Regards,

F.D.Castel

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by F. D. Castel over 8 years ago

Felix Schäfer wrote:

If you have everything from the ground up UTF-8 and every program that accesses does so in UTF-8, it shouldn't be a problem.

Agreed (in theory). But this is exactly what I'm doing (so I think :) )

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by F. D. Castel over 8 years ago

Well... DEFINITELY my program is doing something wrong.

I rebuilt the database again but this time I did run redmine:load_default_data using [pt-BR] data instead of the default [en]. This way, instead of "Home" Redmine shows "Página Inicial", etc

The data from my C# app appears with � characters, but Redmine localized strings are fine("Página Inicial", "Últimos projetos"). This with browser using default Encoding (UTF-8)

However, if I change the browser Encoding to "Western European (Windows)" the strings inserted by my program are displayed correctly! And the Redmine localized strings are wrong! ("Página inicial", "Últimos projetos").

So, the problem is on C# side. My program IS NOT sending strings in UTF-8, regardless the "charset=utf8" on connection string.

I upgraded MySql connector to v6.3.5.0 but the problem persists.

Peter, I hope this helps you in some way.

Regards,

F.D.Castel

P.S.: Of course, I'm wondering HOW this did work once...

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by Peter Werner over 8 years ago

Hello F.D.

thanks for you feedback on that matter F.D. No, I could not solve that correctly. In order to be able to pump the data into the database I replaced all characters which caused trouble: ä to ae and so on. A German reader could still read that but I really would like to understand what went wrong.

But also is interesting how new data now is stored into the database. O.K. everybody would expect UTF-8. But I have to admit that I am a fan of MS SQL Server. So in the future I would like to move my Redmine database from MySQL to MS SQL if I can manage it to get it work together with Ruby and Rails. Although my first attempts were not successful. Anyway: if I am going to try it I will do in in the same way - with the same trouble? But O.K. that is a new topic.

Regards

Peter

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by F. D. Castel over 8 years ago

Peter!

Please, if you can, try to run this SQL command on your VS app after connect to database:

set names utf8;

And run it again. Then tell me what happened (Sorry, but I cannot test this now).

About MSSQL, I once tried to install RedMine on a SQL Server but I had so many troubles that I gave up. YMMV, but I just found Bitnami on a VM much simpler ;)

Been there, done that.

Best regards,

F.D.Castel

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by Ivan Cenov over 8 years ago

Hi,

I wonder if this scenario will do it:

  1. Create SQL dump file using MySQL Administrator ot mysqldump.exe.
  2. Load a copy of the dump file into a text editor that is capable to convert texts to UTF-8 (Notepad++ is).
  3. Convert to UTF-8, then inspect what has happened with the German characters, and if they are visible go to next step.
  4. Change all occurrences of 'latin1' or 'latin2' with 'UTF-8'.
  5. Import this modified dump file. This operation will replace the tables and the data.
  6. Check what has happened ...

May this help you in some way...

Ivan

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by Julisa Moreno almost 8 years ago

Hello,
I'm new in Redmine. And I would like to know how the redmine store de data?, I mean redmine use MYSQL for store data and how can I access to them.

please help me... is very important to find out it, because is part of my job.

Regards,
Jul

RE: How does Redmine - Rails - Ruby use a MySQL database? - Added by Etienne Massip almost 8 years ago

With any MySQL client using credentials specified in your Redmine config/database.yml file.

(1-9/9)