Database encoding issues
We’ve been having a rush of encoding issues recently, with people experiencing all kinds of issues – from popups not loading, weird errors occurring to text being displayed incorrectly. After much investigating it seems almost all these issues are related to database encoding (in addition to some display and encoding/decoding/conversion issues inside thebuggenie – these are also being corrected).
Database charsets / encoding
For The Bug Genie to function properly, it is expected that the database and table charset / encoding is utf-8. Some users are using different charsets, such as latin-8 as well as cyrillic variants, which are causing a lot of issues when The Bug Genie expects this to be utf-8. In addition to this, as The Bug Genie starts to use JSON more extensively, having data stored in non-utf8 format increasingly starts to become a proplem as JSON is a format which expects data to be encoded in unicode (with utf-8 as the default encoding). The Bug Genie defaults to creating tables with utf-8 encoding, but sometimes the database collation setting, default database encoding and other settings are causing incompatibilities with the data stored and the expected utf-8 encoding. We have also not communicated clearly that the database collation / encoding must be utf-8 for The Bug Genie to function properly. This will be improved in upcoming versions.
Migrating data to utf-8
If you’re already experiencing issues with utf-8 encoding or weird issues in The Bug Genie, it is recommended that you investigate whether your database encoding and collation settings are all set to utf-8. If they are not, you should convert your database content to utf-8 – both to fix most of the weird issues in The Bug Genie, and to make sure your data won’t get corrupted in the future.
There are several good guides out there to convert database contents (as well as the database itself + tables) to utf-8. Most of them involves the following steps:
- Dump your existing data to a .sql file
- Dump your existing database, and recreate it with the same name but with specified utf-8 encoding + collation
- Convert your .sql file from the old encoding to utf-8
- Import into your new database
Converting MySQL data to utf-8
Converting Postgres data to utf-8
Postgres mailing list: http://archives.postgresql.org/pgsql-general/2006-03/msg01515.php
There are also several bugreports for these issues in The Bug Genie. We will continue to investigate and improve these and related encoding issues in the bug genie source code, as well.