Applying 2020042101.sql to existing MariaDB causes error 150 "Foreign key constraint is incorrectly formed"

[egoexpress]

While trying to do this:

During an update I tried to apply https://github.com/idno/known/blob/master/warmup/schemas/mysql/2020042101.sql to an existing MariaDB Known database. It failed to create all 3 tables contained in the file with the error above.

I encountered this error on the MariaDB side:

Can't create table 'entities_metadata' (errno: 150 "Foreign key constraint is incorrectly formed")

I found the following solution:

It seems this error is caused if the column a foreign key references to doesn't exactly match the foreign key column itself.

I digged around a bit and it seems the collate setting for entities._id and entities_metadata._id don't match (at least in my case).
I was able to resolve the issue by adding collate 'utf8_unicode_ci' to the _id column in the CREATE statement.s Adding this option lets me create the 3 tables.

Example for entities_metadata:

CREATE TABLE IF NOT EXISTS `entities_metadata` (
  `_id` varchar(32) NOT NULL collate 'utf8_unicode_ci',
  `name` varchar(64) NOT NULL,
  `value` text NOT NULL,
  KEY `value` (`value`(255)),
  KEY `name` (`name`),
  KEY (`_id`),
  CONSTRAINT `em_id_id` FOREIGN KEY (`_id`) REFERENCES `entities` (`_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Some other notes:

I'm using MariaDB as noted above.
I ran an older GitHub release of Known and tried to update to the latest GitHub master (1.2.3+2020061901).

[mapkyca]

Interesting... I've not seen this when I updated things, but maybe that was related to the default configured... Perhaps this is the sort of thing we need to define...

[egoexpress]

The first post on my site is from late 2015, so the initial DB was set up a while ago. :)
Maybe it's an option to define collate at least for the key columns in the default install so these errors won't occur in the future.

I just wanted to raise this issue here so that anyone with similar problems has a starting point to get his Known instance up and running again.

[mapkyca]

Yeah, thanks for that.

I really want to move the *sql over to a schema manager based around doctrine dbal, but quite honestly I've not had time. Managing SQL in .sql files is not a good way of doing things in $currentYear

[benwerd]

Closing out in favor of the larger issue, which is #2859.