Flyspray

try version flyspray-1.0.alpha2

Ok.

now i understand where problem:

Process of installing bug tracker not asking collation and charset for creating tables and make tables by default charser

need add to install request about charset of table creating

in version 0.9.9.7 have same problem

collation imho only important for sorting, not storing inside mysql I think.

utf8 (utf8mb4 for mysql) should be used always.

Which mysql version? Could you provide samples as attachments?

Maybe problem of server communication?

In my .htaccess first entry is

AddDefaultCharset utf-8

I have no experience with nginx. On apache server you can use the htaccess.dist in the root directory of flyspray and copy it to .htaccess

Could you check with a tool like wireshark what is traveling between your browser and webserver?

.htaccess have only Apache

i not use Apache
i use Nginx + FPM-PHP

Oh, I can confirm this also on Apache.

It even fails with the first kyrillic t on my test server!

from

тестовая задача

Severity raised and I will dig into the problem..

But it is working on bugs.flyspray.org ..

So lets find the diff...

mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = "XXX";
+--------------+--------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME  | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------+----------------------------+------------------------+----------+
| NULL         | XXX          | latin1                     | latin1_german1_ci      | NULL     |
+--------------+--------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="XXX" AND TABLE_NAME LIKE "fsgit_%";
+-------------------------------+------------+--------+---------+------------+-------------------+
| TABLE_NAME                    | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_COLLATION   |
+-------------------------------+------------+--------+---------+------------+-------------------+
| fsgit_admin_requests          | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_assigned                | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_attachments             | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_cache                   | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_comments                | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_dependencies            | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_effort                  | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_groups                  | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_history                 | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_links                   | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list                    | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_list_category           | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_os                 | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_resolution         | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_status             | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_tag                | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_tags               | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_tasktype           | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_list_version            | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_listx                   | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_notification_messages   | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_notification_recipients | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_notifications           | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_prefs                   | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_projects                | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_registrations           | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_related                 | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_reminders               | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_searches                | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_tags                    | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_task_tag                | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_task_tags               | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_tasks                   | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_user_emails             | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_users                   | BASE TABLE | MyISAM |      10 | Dynamic    | latin1_german1_ci |
| fsgit_users_in_groups         | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
| fsgit_votes                   | BASE TABLE | MyISAM |      10 | Fixed      | latin1_german1_ci |
+-------------------------------+------------+--------+---------+------------+-------------------+
37 rows in set (0.00 sec)

mysql> status;
--------------
mysql  Ver xxx for redhat-linux-gnu (x86_64) using readline xxx

Connection id:          xxx
Current database:       XXX
Current user:           xxx@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         xxx
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock

mysql>

This is from one of my german hosting environments.. Everything latin1 by default, but should be able to change.
So this is probably a thing that must be done correct on install and upgrades.

Ok, after

ALTER TABLE fsgit_tasks CONVERT TO CHARACTER SET utf8;

my testinstall accepted the kyrillic.

I would like to fix this by setting the needed params forcing utf8 database tables and fields even if default database settings in the webhosting are not utf8.

Best would be inside the flyspray-install.xml and upgrade.xml, but neither

<opt platform="mysql">DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci</opt>

for tables nor

<query platform="mysql">ALTER TABLE .. CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci</query>

is currently working with xmlschema03.

I sent patch to ADOdb/ADOdb on github.com, so maybe this can be done after a ADOdb 5.20 release?

Another workaround would be handling it in setup/index.php and setup/upgrade.php
after db creation by converting every table to utf8 once. (by reading from INFORMATION_SCHEMA and if tables or fields not utf8 then convert)

updated 20171008 to avoid misleading 'utf8' which is not utf8 in mysql. use utf8mb4.

Ok, after
> ALTER TABLE fsgit_tasks CONVERT TO CHARACTER SET utf8;
> my testinstall accepted the kyrillic.

Yes. I slove this problem same.
but i use more improvment utf such as utf8mb4 - because in my projects iam using not only Cyrillic and Asians languages for many difficult Asian languages need using only utf8mb4 but not utf8

i think it need add to Install process - because not all DB having correct default charset

utf8mb4 for Flyspray would be a new feature maybe has some at the first look overseen complex consequences.

We are in the phase of getting Flyspray 1.0 ready, so only the setting for utf8 should be fixed at the moment.

Edit 2017-10-07: 'utf8' is just the 3byte castrated variant of mysql, utf8mb4 is the real utf8 for mysql.

At least we try now to set the database default to utf8 at install. FS1.0-beta.

UTF8 in MySQL

Research

Prefered collations

Adapted setup/index.php and http://www.flyspray.org/manual/install/ to use utf8mb4 and utf8mb4_unicode_ci by default as the string 'utf8' in the mysqlworld means 'incomplete 3byte only utf8 and simply cut the 4th byte and dont care if that creates problems'

IMHO installs with mysqldb < 5.5.3 or only that 3-byte utf8 should show a warning at login or in flyspray admin area. (admin accounts)

Also any possible cuts of 4byte utf8 data should be investigated and potential bad impact to flyspray.

Using "_ci" collations is a bad security practice as it makes bruteforce attempts faster by the power of ²⁶, as A-Z letters become equal to a-z thus login "AdMinIstRatOR" becomes "administrator".
You should always use "_bin" collations.

@vrobolab passwords ar stored encrypted. It depends IMHO on the encrypt/decrypt routines. The compare of hashes is not done by SQL.

If your ancient flyspray_comments table in an old Flyspray installation with Mysql is also not yet utf8/utf8mb4:

For old MySQL older verion 5.5: ( if your dbtable prefix is flyspray_ )

ALTER TABLE flyspray_comments CONVERT TO CHARACTER SET utf8;

For MySQL since 5.5 and newer or MariaDB:

ALTER TABLE flyspray_comments CONVERT TO CHARACTER SET utf8mb4;

Newer Flyspray installations should not have this problem as database configuration with utf8/utf8mb4 is explaining in the installation guide and newer MySQL have utf8mb4 set as default character set.

I try to catch that issue during upgrading automatically in future too to correct wrong character sets. Sadly xmlschema03 of ADOdb does not handle that for us by definition in the XML-files and we must write that in PHP-upgradescript by ourself.