I have been researching this problem on StackOverflow for more than 24 hours and decided that this isn't already covered elsewhere even though there are many Q&A about the same topic.
I am using HeidiSQL 9.3 against MariaDB 10.1 and have a strange problem as follows: I originally accepted the default collation when I created my database then realized that this wasn't what I wanted and tried to change it with
ALTER DATABASE InternalFulfillment CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET collation_connection = 'utf8mb4_unicode_ci';
SET NAMES 'utf8mb4';
SET CHARACTER SET 'utf8mb4';
DROP DATABASE IF EXISTS `InternalFulfillment`;
CREATE DATABASE `InternalFulfillment` /*!40100 COLLATE 'ucs2_bin' */;
CREATE TABLE `table1` (
`column1` VARCHAR(50) NULL
CREATE DEFINER=`root`@`%` PROCEDURE `proc1`(IN `param1` VARCHAR(50))
t.column1 = param1;
/* SQL Error (1267): Illegal mix of collations (ucs2_bin,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' */
Looks like there is some collation cache in MariaDB. I'm the author of HeidiSQL, and I'm pretty sure there is no such collation cache thing in HeidiSQL itself, so it must be a MySQL and/or MariaDB issue.
However, I just tried to reproduce the issue on a MySQL v5.7.9 server on my local Windows, without luck:
CREATE DATABASE `InternalFulfillment` /*!40100 COLLATE 'ucs2_bin' */; CREATE TABLE `table1` ( `Column 1` VARCHAR(50) NULL ) COLLATE='ucs2_bin' ENGINE=InnoDB;
Both the database and table1 have
ucs2_bin collation, as expected.
ALTER DATABASE `internalfulfillment` COLLATE 'utf8mb4_general_ci'; CREATE TABLE `table2` ( `Column 1` VARCHAR(50) NULL ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
Now, the database and the newly created table2 report the changed collation, as expected:
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='internalfulfillment'; >> utf8mb4_general_ci SELECT TABLE_NAME, TABLE_COLLATION FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA='internalfulfillment'; TABLE_NAME | TABLE_COLLATION table1 | ucs2_bin table2 | utf8mb4_general_ci
So, my guess is that you have hit a bug in MariaDB.