environment:

• i'm connected by mysql on the local linux machine
• database was created by this version mariadb - no migration
• table was created by this server default configuration .. <utf8mb4_general_ci>
• sometimes later added to server configuration:
  <collation_server=utf8mb4_bin>
  <init-connect ='SET NAMES utf8mb4, collation_connection = utf8mb4_bin'

test: select cdgrp,tshort,tlong from t_config where tshort = 'xau';
result: | Currency | XAU | Gold |
expected: Empty set
fix with: alter table t_config convert to character set utf8mb4 collate utf8mb4_bin;

redo test after drop table, create it newly (with changed server configuration)
test: select cdgrp,tshort,tlong from t_config where tshort = 'xau';
result: Empty set

conclusion: just changing collation in server configuration does show up everything ok. but the engine behave not as expected. it need's an alter table to set everything correct - changing server configuration does not.

after fix: select cdgrp, tshort from t_config where cdgrp = 'CDGRP' return empty dataset
analyzing proceedure (see details in analyze.txt)

• analze table ok;
• find all tuples not using index
  fix: drop / create index

conclusion: alter table convert character set and collation does fix the issue but can leave inconsistent indexes behind. any checks on table structure do not recognise inkonsistency.

have fun