Flyspray - The bug killer!

  • Status Confirmed
  • Percent Complete
    10%
  • Task Type Bug Report
  • Category Database Queries
  • Assigned To
    peterdd
  • Operating System All
  • Severity High
  • Priority Medium
  • Reported Version 0.9.9.7
  • Due in Version 1.0
  • Due Date Undecided
  • Votes 2
  • Private
Attached to Project: Flyspray - The bug killer!
Opened by Ivan Zhirkov - 04.09.2015
Last edited by peterdd - 09.08.2016

FS#2044 - Need set_charset for DB connect in config file

make a option in configuration file

me need set same $db1→set_charset(’utf8mb4’); for my MySQLi

have a problem with national letters

I see this error after i try enter national letters to the summary input text field for create new task

Query {UPDATE `flyspray_tasks` SET project_id = ?, task_type = ?, item_summary = ?, detailed_desc = ?, item_status = ?,
 mark_private = ?, product_category = ?, closedby_version = ?, operating_system = ?, task_severity = ?, task_priority = ?, 
last_edited_by = ?, last_edited_time = ?, due_date = ?, percent_complete = ?, product_version = ?, estimated_effort = ?
 WHERE task_id = ?} 
with params {1,1,тестовая задача,<p>sdfsdfdsfsd</p> ,2,0,4,0,1,2,4,1,1441344777,0,0,1,0,2} Failed!
(Incorrect string value: '\xD1\x82\xD0\xB5\xD1\x81...' for column 'item_summary' at row 1)
Ivan Zhirkov commented on 04.09.2015 05:40

try version flyspray-1.0.alpha2

Ivan Zhirkov commented on 04.09.2015 10:49

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

Ivan Zhirkov commented on 04.09.2015 11:10

in version 0.9.9.7 have same problem

Project Manager
peterdd commented on 04.09.2015 17:58

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?

Project Manager
peterdd commented on 04.09.2015 21:45

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?

Ivan Zhirkov commented on 05.09.2015 07:03

.htaccess have only Apache

i not use Apache
i use Nginx + FPM-PHP

Project Manager
peterdd commented on 10.09.2015 17:19

Oh, I can confirm this also on Apache.

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

Unicode code pointcharacterUTF-8(hex.) name
U+0442тd1 82CYRILLIC SMALL LETTER TE

from

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

Severity raised and I will dig into the problem..

Project Manager
peterdd commented on 10.09.2015 17:21

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

So lets find the diff...

Project Manager
peterdd commented on 10.09.2015 17:51
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.

Project Manager
peterdd commented on 10.09.2015 18:05

Ok, after

ALTER TABLE fsgit_tasks CONVERT TO CHARACTER SET utf8;

my testinstall accepted the kyrillic.

Project Manager
peterdd commented on 11.09.2015 04:40

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.

Ivan Zhirkov commented on 15.09.2015 15:57
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

Ivan Zhirkov commented on 15.09.2015 15:57

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

Project Manager
peterdd commented on 18.09.2015 01:18

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.

Project Manager
peterdd commented on 10.10.2015 06:12

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

Project Manager
peterdd commented on 09.08.2016 19:28

UTF8 in MySQL

utf8 in MySQL: only 1,2, and 3 byte characters, but no 4 byte characters
utf8mb4 in MySQL:

  • available since MySQL 5.5.3, with some limitations for key sizes
  • limitations removed since MySQL 5.7.7

Research

Research needed: status of this topic for MariaDB (Mysql fork used often by linux distributions)
Research needed: similiar status of other databases (PostgreSQL and maybe check for sqlite)

Prefered collations

MySQL utf8: utf8_unicode_ci (more accurate sorting than utf8_general_ci)
MysQL utf8mb4: utfmb4_unicode_ci (more accurate sorting than utf8mb4_general_ci)

Project Manager
peterdd commented on 09.10.2017 01:32

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.

V commented on 09.10.2017 04:06

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.

Project Manager
peterdd commented on 06.06.2018 15:34

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

Loading...

Available keyboard shortcuts

Tasklist

Task Details

Task Editing