Changing collation for Fabrik 4

[ontarget]

Hi
What collation do you recommend for Fabrik 4 - it seems to be defaulted on latin_swedish
Would utf8mb4_general_ci be ok or better to use as this seems to be the norm in other joomla tables.
I had an issue with validating a utf8mb4_general_ci generated username in a latin_swedish field element in Fabrik:

Incorrect string value: '\xE2\x80\x8B122...' for column 'TCN' at row 1.

TCN field is mapped to the J! username field from the jos_users table

• Is there a general config setting to change collation in Fabrik or does it need to be done on each list (Details > Advanced setting)?
• Should I use Force Query Collation and set it to utf8mb4_general_ci in every list?
• Can i simply change one column in Fabrik table to use utf8mb4_general_ci or do i need to do all of them with alter in phpMyAdmin?
  
• Can this change "break" any settings in Fabrik, I tested on my sandbox and everything "seems" ok!

Thanks for any advice / suggestions

 

[troester]

it seems to be defaulted on latin_swedish

No, it defaults to whatever your database is set to.
So if you have a new installation you should set your database collation to utf8/utf16... directly after creation (before installing Joomla etc) if this is not automatically done by your MySQL server.

I assume you have an old upgraded site? (Pretty old if the J! prefix is jos_)

As far as I know Fabrik does nothing with any collation settings - beside you are using this Advanced setting in list settings.

I assume if you get "Incorrect string value: '\xE2\x80\x8B122...' for column 'TCN' " then it's a username containing characters which are not supported in latin_swedish.

Can this change "break" any settings in Fabrik, I tested on my sandbox and everything "seems" ok!

I don't know. I assume it should be ok, but...

 

[boyjah4]

"No, it defaults to whatever your database is set to."

No, in my experience (over and over again) it does not. Fabrik 4 does weird things with collation for its various data tables, assigning collation to many that has NO ASSOCIATION with our verified DB collation settings or previous collation settings that all other plugins cooperate with. So many of us have experienced this. And we still cannot update collation for some Fabrik data columns. For example, in table fabrik_thumbs we are able to update collation for"special" and for "thumb" to utf8mb4_unicode_ci. But we get an error when trying to update collation for user_id

 

[troester]

Fabrik doesn't set collations, it only sets CHARSET=UTF8 for its own meta tables (#__fabrik__).
It's taking your DB collation as default (i.e. while creating new tables).

If you have existing tables/lists and change your DB collation it's up to you to change also the table and column collations in the DB and set the list collation (Details/Advanced) to the new one.

Thumbs element:
Since about 7 years user_id is varchar(40) so it seems your site is a very, very old updated one.
The error message is not related to Fabrik but to the DB index settings/restrictions.
You'll find several possibilities to fix it in general.

In case of the thumbs element it may be enough to set user_id to varchar(40).