FixMe Several lists seem to have armscii8_bin collation??

[EricWebsite]

In a Fabrik installation which was upgraded from F3 to F4, I noticed that several lists seem to have armscii8_bin collation (List > Details > Advanced). Please see screenshot1.jpg.
armscii8_bin stands for "Armenian Standard Code for Information Interchange" and it supports the Armenian alphabet and a limited set of additional Latin characters.

Looking into the list params with phpmyadmin, I found: "collation":"utf8_general_ci".
Apparently the dropdown does not recognise this and displays the first item from it's list options.

The table and it's fields have utf8mb3_general_ci collation as can be seen with phpmyadmin.
utf8_general_ci and utf8mb3_general_ci are are effectively the same collation, as utf8 is an alias for utf8mb3 in MariaDB and MySQL.

So far this did not cause problems. Because I have set my Fabrik global options to "Add new fileds only". But when it's set it to "Yes", indeed upon saving the list, the table was modified and the collation set to armscii8_bin! See screenshot2.jpg.
The column collations remained utf8mb3_general_ci, fortunately.

I have no idea if this situation occurrs in many Fabrik installations.
Still, this is a potential danger and I suggest (as a first step) to process utf8_general_ci as utf8mb3_general_ci to avoid changing the database collation.
And maybe later convert all text fieds to utf8mb4_general_ci?

System Details:
Fabrik: Version: 4.4.1 (GitDate: 2024-10-18)
Joomla Version: 5.2.2
PHP Version: 8.3.14
Database Version: 10.11.6-MariaDB-hetzner1

 

[troester]

Hmm, I can't replicate, a new list is showing this on my server.

Fabrik is setting nothing by itself.

Check your database's default collation.

It may be this one

Fabrik is doing

SHOW COLLATION WHERE Compiled = 'Yes'
to get the list of collations
and
SHOW VARIABLES LIKE "collation_database"
to get the default for new lists.

What do you get with these in phpMyAdmin?

 

[EricWebsite]

Yes it is no problem for new tables, the default collation of the database is used. It happens only with old lists, where most likely at the time of their creation, the db default was utf8_general_ci. That value got included in the list's params.

When I do "SHOW COLLATION WHERE Compiled = 'Yes'" I get the attached list of collations.txt. Indeed 'utf8_general_ci' is missing on that list.

The output of SHOW VARIABLES LIKE "collation_database" is:

Variable_name	Value
collation_database	utf8mb4_unicode_ci

So new lists are OK, old lists with utf8_general_ci in the params run the risk of being changed to armscii8_bin. And when a new element is added to that table, it most likely will be armscii8_bin!

 

[troester]

Can you try
https://bitbucket.org/fabrikar/fabrik/branch/outdated-list-collation

 

[EricWebsite]

I applied the patches and tested. The results are:

• When a value in the params is present that is not recognised, the param is cleared during the first save
• a warning is only shown when the Fabrik Options are set to "Yes"
• On consequent saves, params are updated with the value selected in the dropdown. Default this is the collation setting for the entire database
• The actual table collation in the database is not changed anymore, even when Fabrik Options are set to "Yes"
• The actual table collation is changed when Fabrik Options are set to "Yes"

This certainly removes reduces the risk that the table collation is altered unintended.
But would it not be better to show the warning always, and fill the collation param field with the actual table collation?

Test results

in list params	table collation in db	displayed in list	Fabrik Options setting
on initial opening			add new fields only
utf8_general_ci	utf8mb3_general_ci	"none"
saved list, reopened
"" (no value)	utf8mb3_general_ci	utf8mb4_unicide_ci remark: this is the default db setting
saved list again, reopened
utf8mb4_unicode_ci	utf8mb3_general_ci	utf8mb4_unicide_ci
Second test
opened list initially			yes
urf8_general_ci	utf8mb3_general _ci	"none"
saved list, warning: No 'Collation' selected in 'Advanced' (original list collation parameter was 'utf8_general_ci' List parameter cleared, table collation in database untouched in params: ""	utf8mb3_general _ci	utf8mb4_unicode_ci
saved list again, reopened
utf8mb4_unicode_ci	uft8mb4_general_ci	utf8mb4_unicode_ci

 

[troester]

a warning is only shown when the Fabrik Options are set to "Yes"

Yup, this is to expect. If "add new fields only" it's returning at once (line 1016).

Default this is the collation setting for the entire database

But I thought it would be the table collation not the DB collation.
Thanks for testing, I have to look at it again.

 

[EricWebsite]

Tnx!

 

[EricWebsite]

I would like to suggest the following approach:

• when loading the list, always get the table collation
• if that differs from the value in the params, just display a warning, change nothing
• only save the collation param if the user has changed it

That way, unintended changes will not occurr.

 

[troester]

The collation behaviour as it is is weird anyway:

If you have a valid collation parameter stored and change your table collation e.g. in phpMyAdmin it will reset it to the stored param collation if you edit and save your list without any notice (usually you won't look at the Advanced tab if you add a join or change access or whatever).
I wasn't aware of this until these tests now.

Similar with "Force query collation", no notice if the collations are changed etc.

So your approach sounds very reasonable, needs some thoughts to do it.

 

[EricWebsite]

If you have a valid collation parameter stored and change your table collation e.g. in phpMyAdmin it will reset it to the stored param collation if you edit and save your list without any notice (usually you won't look at the Advanced tab if you add a join or change access or whatever).

Yes it does. And that's risky. I have done some tests and a simple

 

in a field that has latin1_general_ci collation already generates an error if you try to convert that to utf8mb4_general_ci.
I don't know if Fabrik will show such an error.

As far as I understand "Force query collation", it is inserting extra parameters in a query to make fields compatible, like:

SELECT t1.*, t2.*
FROM table1 t1
JOIN table2 t2
ON t1.name = t2.name COLLATE utf8mb4_general_ci;

where t1 is in latin1_general_ci collation and t2 in utf8mb4_general_ci.