Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22211

case senitivity behavior not as shown by server, by db, by table, by connection

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Trivial
    • Resolution: Not a Bug
    • None
    • N/A
    • Character Sets

    Description

      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

      Attachments

        1. analyze.txt
          11 kB
          Juerg Oehler
        2. protocol.txt
          2 kB
          Juerg Oehler

        Activity

          People

            Unassigned Unassigned
            SwissGeorge Juerg Oehler
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.