Something went wrong: SQLSTATE[HY000] - MySQL: 5.7.23

[terabytesoftw]

hi, installed flarum branch dev-master,

Something went wrong: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table tag_useradd constrainttag_user_tag_id_foreign foreign key (tag_id) references tags (id) on delete cascade)

The problem tables:

• migrations. --> MyIsam.
• tags --> MyIsam.

The rest od tables if you create in Innodb.

Environment

• Flarum version: dev-master
• Webserver: LiteSpeedTech
• Hosting environment: Shared.
• PHP version: 7.1

[tobyzerner]

@franzliedke can you please look at this in relation to fb6b51b?

[terabytesoftw]

@tobscure its correct but tags is a separate extension and creates the tables as MyISAM, I proble flarum / core as dev-master and it did not work just those two tables (tags, migrations) all the others are like innodb.

[tobyzerner]

The changes in fb6b51b should theoretically force InnoDB to be used for all tables, including those created by extensions, and the migrations table.

Are you experiencing this on a totally fresh install of dev-master?

[terabytesoftw]

If a completely new installation of zero, is correct using flarum/core dev-master

[bobbwal]

I'm having the same issues on a fresh install. Even with the changes from fb6b51b my migrations and tags are created as MyISAM.

[franzliedke]

Ah, I'd figured it would be something like this. However, fb6b51b also sets InnoDB as engine for every table created through Migration::createTable. It turns out that the tags table is the only one not using that helper, which is why the default was necessary. That said, I just added an explicit InnoDB engine to that table's migration.

[franzliedke]

I guess we could also add the explicit schema to the migrations repository: https://github.com/flarum/core/blob/796b57753d34d4ea741dbebcbc550b17808f6c94/src/Database/DatabaseMigrationRepository.php#L102-L105

@tobscure @luceos What do you think?

[tobyzerner]

@franzliedke If we have properly set the default engine in the config then we shouldn't need to define it explicitly in the schema at all. Remember by adding the "InnoDB" line to our migrations we are automatically making them MySQL-only.

[luceos]

I agree with @tobscure ; adding enforcement in different locations might only complicate trouble shooting in the future.

[franzliedke]

I agree with the reasoning about being MySQL-specific, and I have reverted all changes that set InnoDB explicitly for table creation statements.

That said, there is at least one existing migration where we explicitly set the engine, and some code (e.g. fulltext search) is very MySQL-specific, so I am not entirely sure that we will ever be able to seamlessly enable other DB engines.

[marcteys]

I guess it's related, but when I try to run php flarum migrate I also end up with this error:

In Connection.php line 664:
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `flar_posts` add constraint `flar_posts_user_id_foreign` foreign key (`user_id`) references `flar_users` (`id`) on delete set null)

I guess the issue is not solved ?
I am also running on mysql. From your comments, you suggest that I should switch to innoDB for the migration?

[luceos]

@marcteys Marc please create a new issue and add the information we ask you there, amongst others the php flarum info output. Thank you.