MariaDB will not change the collation for a database

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;

This has no effect, and the database is still reported as ucs2_bin and all of the procedures and functions are ucs2_bin as well. I tried all of the advice from every Q&A I could find on StackOverflow including these statements:

SET collation_connection = 'utf8mb4_unicode_ci';
SET NAMES 'utf8mb4';
SET CHARACTER SET 'utf8mb4';

When I drop and recreate the procedures they still come back as ucs2_bin.

The strangest thing of all is that if I drop and recreate the database with the name 'InternalFulfilment' the collation is wrong, but if I create a database with a different name then I get the collation I want, and running the script that creates the stored procedures creates procedures with the utf8mb4_unicode_ci collation.

It seems like MariaDB and/or HeidiSQL is remembering the original collation that I used when I first created the 'InternalFulfillment' database, and always uses ucs2_bin collation whenever I create a database with this name.

Does anyone have any idea where this might be stored so I can clear it. Thanks.

Additional comments after reading answers below

After leaving this overnight, the next morning I was able to drop and recreate the database with a different collation, but now it is stuck on the new collation.

Following on from the answer from @Anse:

DROP DATABASE IF EXISTS `InternalFulfillment`;

CREATE DATABASE `InternalFulfillment` /*!40100 COLLATE 'ucs2_bin' */;

USE `InternalFulfillment`;

CREATE TABLE `table1` (
    `column1` VARCHAR(50) NULL
)
COLLATE='ucs2_bin'
ENGINE=InnoDB;

DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `proc1`(IN `param1` VARCHAR(50))
    DETERMINISTIC
BEGIN
    SELECT
        column1
    FROM
        table1 t
    WHERE
        t.column1 = param1;
END//
DELIMITER ;

CALL proc1('test');

Produces: /* SQL Error (1267): Illegal mix of collations (ucs2_bin,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' */. If I re-run this script with utf8mb4_general_ci then it completes without error.

Yesterday my database was stuck in ucs2_bin and today it is stuck in utf8mb4_general_ci so there is something that is cached with a fairly long expiry time.

Solution

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.