AyMINE Framework management FAQ

Frequent problems and recommendations

• Selection filter for string doesn't work

Selection filter for string doesn't work

Problem

User setup filter for string but nothing returns

Error message for MariaDB / MySQL from the system log:

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_czech_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'

Cause

Most often case is wrong collation used by application and database. Request is send with collation used by application but database uses collation defined during the creation. Often it occurs when searched field is generated by stored method.

Stored method returns collation according to the settings during their storing / compiling. Developer should use the collation settings before the function compilation

Resolution

Recompile stored procedures
Repair stored methods or table column collation to be the same with application. Set the collation before compiling stored method:

SET @@local.character_set_connection=utf8mb4;
SET @@local.collation_connection=utf8mb4_general_ci;

Set right collation in the AyMINE settings (collation cannot be defined by the connection string):

$ret->exec('SET @@local.collation_connection=utf8mb4_general_ci');

Repair tables
Used the framework development select (see framework-dev.sql file) that selects all table columns with wrong collation and generates script for repair.

Implementation notes

For different nationalities application server should use different databases because collation settings is per column and per function not per records. Single database cannot be defined with several different collations.