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

Are these run on the same server? What is the value of the lower_case_table_names variable? See Identifier Case-sensitivity.

For the character sets, you probably want utf8mb4 in MariaDB; utf8 only holds 3-bytes, and does not support the full UTF8 range. See unicode.

 
1 year, 10 months ago Vasant S
Re: Data case sensitive issue

The MariaDB database is on AWS RDS.

We have simulated the following scenarios and none of them allows us to insert the data in the table. What else we can do to make this work ?

INSERT INTO Test3_db.gum_adm_user VALUES('6380446','user-pvsERO6MFmyaDBAn','S7U9U5Y','XXXXXX','XXXXXX')

INSERT INTO Test3_db.gum_adm_user VALUES('6530748','user-16Ks6IuR4bsPy9Kf','s7u9u5y','XXXXXX','XXXXXX')

After the First Insert, we will get the below error in the second insert.

SQL Error [1062][23000]:(conn=13) Duplicate entry 's7u9u5y' for key 'UC_GUM_ADM_USER_2'

Note: Name Column has Unique Key Constraint.

Schema Structure

CREATE TABLE `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 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Scenario 1 : Change the Parameter Values for Collation and Character set at RDS Instance

show variables like '%collation%';

collation_connection utf8_bin collation_database utf8_bin collation_server utf8_bin

show variables like 'char%';

character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem utf8 character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /rdsdbbin/mariadb-10.5.15.R1/share/charsets/

Scenario 2 : Change the Parameter Values for Collation and Character set at Database Level Scenario 3 : Change the Parameter Values for Collation and Character set at Table Level Scenario 4 : Change the Parameter Values for Collation and Character set at Table and Column Level Scenario 5 : Change the Parameter Values for Collation and Character set at RDS, Database, Table and Column Level

All of the above changes together. But nothing works. MariaDB just does not allow duplicate values like Oracle.

 
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.