Alter table changing engine: ERROR 1217 (23000)

[rongcuid]

On trying to alter a table from InnoDB to Mroonga, I get:

> ALTER TABLE posts ENGINE = Mroonga;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

To reproduce, I set up Flarum on MariaDB 10, using Ubuntu 22.04. I used both the Mroonga provided by official repo and the latest version from source, both give the same behavior.

Some possible insights from an experiment trying to create a copied table:

MariaDB [flarum]> CREATE TABLE posts_copy LIKE posts;
Query OK, 0 rows affected (0.181 sec)

MariaDB [flarum]> ALTER TABLE posts_copy ENGINE = Mroonga;
Query OK, 0 rows affected (0.575 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [flarum]> ALTER TABLE posts_copy ADD CONSTRAINT FOREIGN KEY (`discussion_id`) REFERENCES `discussions` (`id`) ON DELETE CASCADE; 
ERROR 1005 (HY000): reference table [flarum.discussions] is not mroonga table

While it looks like Mroonga requires other tables to be Mroonga too, I simply can't change every table's engine at once. I tried dumping the tables and recreating them, but I still get similar errors.

[komainu8]

I'll try reproduce this problem.

[kou]

Could you provide schema of posts and discussions?

it looks like Mroonga requires other tables to be Mroonga too

Right.