tohsakarat this is a consequence of the database table prefix causing the auto-generated index names to become too long. That issue can affect any extension that uses long table and/or column names, and this extension is one of them.
I suggest running Flarum in a dedicated database without any table prefix, this will be the easiest solution, and also cover you for other extensions that might have the same issue.
If you need to proceed with the current prefix, you'll need to manually create the tables and indexes with custom shorter names, and then manually mark the migrations as done. This is not easy to do.
I think there's also a way to raise the maximum index name length somewhere in the MySQL server settings, but I've never tried doing that.
I'm not currently planning to address this issue in the codebase. I might add a fatal error when the prefix is too long to at least prevent breaking the database with partial migrations.
Maybe in a future major version I'll rename everything to be a bit shorter. But the issue doesn't have a definite solution, there will always be a prefix length that makes the names become too long.