The Ultimate Guide to High Availability with MariaDB

Comments - Data case sensitive issue

1 year, 10 months ago Ian Gilfillan
Re: Data case sensitive issue [Answer]

I think what is happening is that by specifying the CHARACTER SET as utf8 on the field level (which has a case insensitive default collation), but not the collation, you are overriding the default table collation. Try:

CREATE OR REPLACE TABLE a4vdds.`gum_adm_user` (
  `ID` decimal(19,0) NOT NULL, 
  `UUID` varchar(255) CHARACTER SET utf8 NOT NULL, 
  `NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  
  `ENCODED_PASSWORD` varchar(255) CHARACTER SET utf8 NOT NULL, 
  `DATA` longtext CHARACTER SET utf8 DEFAULT NULL, 
  PRIMARY KEY (`ID`), 
  UNIQUE KEY `UC_GUM_ADM_USER_1` (`UUID`), 
  UNIQUE KEY `UC_GUM_ADM_USER_2` (`NAME`), 
  CONSTRAINT `SYS_C008765` CHECK ('ENCODED_PASSWORD' is not null), 
  CONSTRAINT `SYS_C008763` CHECK ('UUID' is not null), 
  CONSTRAINT `SYS_C008762` CHECK ('ID' is not null), 
  CONSTRAINT `SYS_C008764` CHECK ('NAME' is not null) ) 
ENGINE=InnoDB DEFAULT CHARSET='utf8' DEFAULT COLLATE='utf8_bin';

INSERT INTO a4vdds.gum_adm_user 
  VALUES('6530748','user-16Ks6IuR4bsPy9Kf','s7u9u5y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.006 sec)

INSERT INTO a4vdds.gum_adm_user 
  VALUES('6380446','user-pvsERO6MFmyaDBAn','S7U9U5Y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.003 sec)

Or just remove the field level specification entirely:

CREATE OR REPLACE TABLE a4vdds.`gum_adm_user` (
  `ID` decimal(19,0) NOT NULL, 
  `UUID` varchar(255) CHARACTER SET utf8 NOT NULL, 
  `NAME` varchar(255) NOT NULL, 
  `ENCODED_PASSWORD` varchar(255) CHARACTER SET utf8 NOT NULL, 
  `DATA` longtext CHARACTER SET utf8 DEFAULT NULL, 
  PRIMARY KEY (`ID`), 
  UNIQUE KEY `UC_GUM_ADM_USER_1` (`UUID`), 
  UNIQUE KEY `UC_GUM_ADM_USER_2` (`NAME`), 
  CONSTRAINT `SYS_C008765` CHECK ('ENCODED_PASSWORD' is not null),
  CONSTRAINT `SYS_C008763` CHECK ('UUID' is not null), 
  CONSTRAINT `SYS_C008762` CHECK ('ID' is not null), 
  CONSTRAINT `SYS_C008764` CHECK ('NAME' is not null) ) 
ENGINE=InnoDB DEFAULT CHARSET='utf8' DEFAULT COLLATE='utf8_bin';
Query OK, 0 rows affected (0.055 sec)

INSERT INTO a4vdds.gum_adm_user 
  VALUES('6380446','user-pvsERO6MFmyaDBAn','S7U9U5Y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.027 sec)

INSERT INTO a4vdds.gum_adm_user 
  VALUES('6530748','user-16Ks6IuR4bsPy9Kf','s7u9u5y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.003 sec)
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.