• Status Unconfirmed
  • Percent Complete
  • Task Type Bug Report
  • Category Backend/Core
  • Assigned To No-one
  • Operating System All
  • Severity Low
  • Priority Very Low
  • Reported Version 1.0-rc7
  • Due in Version Undecided
  • Due Date Undecided
  • Votes
  • Private
Attached to Project: Flyspray
Opened by Stefan2 - 05.01.2019

FS#2527 - Database Check »Your mysql supports full utf-8 since 5.5.3.« always shown

Steps done to create the problem:
Access /index.php?do=admin&area=checks with a MySQL Version >= 5.5.3

Expected behavior:
Flyspray tests for character set and displays »Your mysql supports full utf-8 since 5.5.3. You are using x.x.x and flyspray tables could be upgraded.« when database schema or one table isn’t set to utf8mb4 character set.

Experienced behavior:
Flyspray always shows this note, even though character set is correct.

As far as I can tell from the source, a query gets executed to the database (and if I do that manually the result is “utf8mb4, utf8mb4_unicode_ci” for my database), but the result doesn’t get checket, the note is always shown (line 123)

Project Manager

Umh, yeah. That check is still incomplete.

It only checks default character set of the database, but should also include default character set of tables.

But what really is important is the character set of table fields (and their collation). Not all TEXT, CHAR, or VARCHAR fields are supposed to be UTF8(PostgreSQL) (or utf8mb4 for MySQL).
For instance the field flyspray_users.user_name could stay ASCII/LATIN1 in my opinion.
Or flyspray_users.user_pass, which only contains hashes that are written to the field with ASCII chars.

So the individual character set of table fields should be defined in the xmlschema too. At least for the TEXT/CHAR/VARCHAR fields that are not supposed to be UTF8. For this the opt of xmlschema03 can be used, but there are some issues that should be resolved within ADODB first, see my pull request for example.

So currently the notice is always shown because there is not yet explicite defined which table TEXT/CHAR/VARCHAR fields should be what character set and what collation within Flyspray.

So the next simple step is checking tables, but that would also not solve the problem I tried to address with the check.

The full chain I have in mind is:

  1. Fix and enhance ADODB to full support OPT and DESCR XMLSCHEMA03 tags including platform specific settings like defining for MySQL if a table should be MyISAM or InnoDB engine.
  2. Add the OPT and DESCR infos to tables and fields of setup/upgrade/1.0/flyspray-install.xml and setup/upgrade/1.0/upgrade.xml
  3. Handle the OPT and DESCR infos during Flyspray install and Flyspray upgrades. Ideally ADODBs xmlschema stuff does it for us automatically, but maybe some php tweaking is required in the upgrade code too.
  4. Verify the correct definitions by comparing the live existing Flyspray database with the setup/upgrade/xxx/flyspray-install.xml of the current version. (for this the current flyspray-install.xml must be kept somewhere after install, not just delete the setup/ folder) and show any potential problems to the admin.
  5. Close this task as resolved. :)


Available keyboard shortcuts


Task Details

Task Editing