Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why am I seeing "COLLATION 'xxx' is not valid for CHARACTER SET 'yyy'"

I am on MySQL 5.6.22 (InnoDB) on Amazon RDS. I have attempted to set all of my tables, columns, connection and database charset and collation settings to utf8mb4 / utf8mb4_unicode_ci. I can find no evidence anywhere that anything has charset latin1, yet when I execute the following code (either via node-mysql, or directly in "Sequel Pro" app on my Mac):

update MyTable m 
set m.Column8 = 1
where m.Column3 = 26 and m.Column4 = 76
collate utf8mb4_unicode_ci

I get this error message:

COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'

I cannot find anything set to latin1 in my configuration.

Output of show variables like "char%":

character_set_client        utf8mb4
character_set_connection    utf8mb4
character_set_database      utf8mb4
character_set_filesystem    utf8mb4
character_set_results       utf8mb4
character_set_server        utf8mb4
character_set_system        utf8
character_sets_dir          /rdsdbbin/mysql-5.6.22.R1/share/charsets/

Output of show variables like "collation%":

collation_connection        utf8mb4_unicode_ci
collation_database          utf8mb4_unicode_ci
collation_server            utf8mb4_unicode_ci

MyTable's CREATE TABLE info is:

CREATE TABLE `MyTable` (
  `Column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Column2` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Column3` bigint(20) unsigned NOT NULL,
  `Column4` bigint(20) unsigned NOT NULL,
  `Column5` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Column6` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Column7` varchar(112) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `Column8` tinyint(1) unsigned NOT NULL,
  `Column9` decimal(16,14) DEFAULT NULL,
  `Column10` decimal(17,14) DEFAULT NULL,
  `Column11` bigint(20) unsigned DEFAULT NULL,
  `Column12` bigint(20) unsigned DEFAULT NULL,
  `Column13` timestamp(6) NULL DEFAULT NULL,
  `Column14` timestamp(6) NULL DEFAULT NULL,
  `Column15` tinyint(4) NOT NULL DEFAULT '1',
  `Column16` tinyint(4) NOT NULL DEFAULT '1',
  `Column17` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Column18` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Column19` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`Column1`),
  KEY `IX_Reevues_Column3` (`Column3`),
  KEY `IX_Reevues_Column4` (`Column4`),
  KEY `IX_Reevues_Column6` (`Column6`),
  KEY `IX_Reevues_Column8` (`Column8`),
  KEY `IX_Reevues_Column2` (`Column2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
like image 484
Mason G. Zhwiti Avatar asked Feb 23 '15 15:02

Mason G. Zhwiti


1 Answers

  1. Why have a COLLATE clause when comparing a BIGINTs compared to literal numbers? Remove the COLLATE clause in the UPDATE statement. -- This is the main solution, as per OP's comments.

  2. Is the code inside a Stored Routine that was build with latin1? Do SHOW CREATE PROCEDURE (or FUNCTION) to see if that were the case. If so, then DROP and reCREATE it with utf8mb4 in force.

  3. It is risky to change character_set_filesystem and character_set_server. Change them back.

like image 135
Rick James Avatar answered Oct 24 '22 00:10

Rick James