Rivit Technology Partners Inc.

Redmine Error 500 – Double Byte Characters

A simpler way to deal with two byte characters.

I can’t say enough about Redmine.  Rivit uses it to Manage Projects, Help Desk Issues, and Time and Attendance.  I’ll speak more to how we use it in a future Blog.  But I wanted to focus on an ongoing issue we’ve had and how we dealt with it.

Rivit’s Implementation

Our implementation of Redmine uses MYSQL.  It’s cloud-based.  When we initially set this up years ago, we didn’t notice that new databases were created with UTF8_swedish_ci collation and character set.  Redmine worked great.  But…. as we started allowing international clients into Redmine they started to complain about an Error 500 showing up.  They couldn’t save any issues that they reported to us.  We triaged the problem and found out it was double-byte characters.  Japanese, accented characters, etc… could not be entered into a subject, description or comment.

We finally found out that it was the MySQL table definition that was causing the problem.  Most people who reported this issue attempted to use the SQL  “ALTER table” command to change the collation and the field level characters set.  This has the potential issue of losing data.

A Simpler Way of Dealing with It:

1. Using PHPMYADMIN we copied the structure of the database in question to a new database.
2. We than used the following SQL statement on the new empty database – ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3. We than converted each table in the new database using the following SQL statement – ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 
4. We than went back to the original database in PHPMYADMIN and copied the data from the database.  ONLY the data.  This creates a series of insert statements in a sql file.
5. Lastly we opened the new empty database in PHPMYADMIN and imported the SQL dump that was created above.
6. Rename the old database.  We added a date to the end of the database name
7. Rename the new database to match the original database name

Success.  No Data loss.  Redmine now has the ability to use double-byte characters.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn