The Ultimate Guide to High Availability with MariaDB

Data case sensitive issue

You are viewing an old version of this question. View the current version here.

Question 1 : In Oracle we have CHARACTERSET as AL32UTF8 and NLS_NCHAR_CHARACTERSET as AL16UTF16, is it same as CHARSET=utf8 and COLLATE=utf8_bin in Mariadb ? Will the underlying data storing, sorting is similar ? Can we expect any surprises when we use DMS during data transfer between oracle and mariadb ?

Question 2: Why the below two scripts are behaving differently ?

Script 1 : Does not allow inserts for CAPTIAL LETTER and SMALL LETTER and consider them as Unique .

CREATE 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 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

Script 2 : Does allow inserts for CAPTIAL LETTER and SMALL LETTER and do not consider them as Unique .

CREATE TABLE a4vdds.`gum_adm_user` ( `ID` decimal(19,0) NOT NULL, `UUID` varchar(255) NOT NULL, `NAME` varchar(255) NOT NULL, `ENCODED_PASSWORD` varchar(255) NOT NULL, `DATA` longtext 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

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)

Comments

Comments loading...
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.