Why I see that "COLLATION" xxx 'is not valid for CHARACTER SET' yyy '"

Question

I am in MySQL 5.6.22 (InnoDB) on Amazon RDS. I tried to set all my tables, columns, joins and database encoding and collation in utf8mb4 / utf8mb4_unicode_ci. I can’t find any evidence anywhere that anything has charset latin1 , but when I execute the following code (either via node-mysql or directly in the “Sequel Pro” application 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 can not find anything installed in latin1 in my configuration.

The 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/ 

The output of show variables like "collation%" :

 collation_connection utf8mb4_unicode_ci collation_database utf8mb4_unicode_ci collation_server utf8mb4_unicode_ci 

MyTable CREATE TABLE:

 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; 

Answer 1

• Why is the COLLATE clause worth comparing BIGINT versus literal numbers? Delete the COLLATE clause in the UPDATE statement. . This is the main solution, as in the comments on the OP.

• Is the code inside a stored procedure that was created using latin1? Do a SHOW CREATE PROCEDURE (or FUNCTION) to make sure it is. If so, then DROP and reCREATE it with utf8mb4 in force.

• You could risk changing character_set_filesystem and character_set_server. Change them.