Foreign key constraint v1.8.0

JUMPING

1.7.2→1.8.0
PHP:8.0.2
MySQL:8.0.12
When I upgrade, prompt：

In Connection.php line 712:

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `post_mentions_tag` add co
  nstraint `post_mentions_tag_mentions_tag_id_foreign` foreign key (`mentions_tag_id`) references `tags` (`id`) on de
  lete cascade)


In Exception.php line 18:

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint


In PDOStatement.php line 117:

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

clarkwinkelmann

It's surprising that MySQL doesn't provide a more complete error message. There's often a reason mentioned along with such an error.

If it was an existing table I'd suspect that the table contains data that is not valid for the new foreign key, but since it's a new table introduced with Flarum 1.8 it should be empty and perfectly fine with a new foreign key.

Are you able to share your php flarum info output from before or after the update? Do you have any community extension? Did you ever make any manual change to the database prior to updating to 1.8?

Now that the error happened, Flarum has no way of re-trying. Either revert to a 1.7 backup, try finding the underlying issue, and then try again. Or you will have to manually inspect the tables and check which indexes, key or column might be missing and manually create them with the correct names.

JUMPING

clarkwinkelmann
I had reverted back to 1.7.2,
My Flarum info
Flarum core: 1.7.2 PHP version: 8.0.2 MySQL version: 8.0.12 Loaded extensions: Core, bcmath, calendar, ctype, date, filter, hash, iconv, json, SPL, pcre, readline, Reflection, session, standard, mysqlnd, tokenizer, zip, zlib, libxml, dom, PDO, bz2, SimpleXML, xml, xmlreader, xmlwriter, FFI, sqlite3, pgsql, pdo_sqlite, intl, mbstring, openssl, curl, fileinfo, gd, gettext, exif, mysqli, Phar, pdo_mysql, redis, Zend OPcache +--------------------------------+---------+--------+ | Flarum Extensions | | | +--------------------------------+---------+--------+ | ID | Version | Commit | +--------------------------------+---------+--------+ | askvortsov-auto-moderator | v0.1.2 | | | flarum-likes | v1.7.0 | | | flarum-flags | v1.7.0 | | | antoinefr-money | v1.2.0 | | | flarum-sticky | v1.7.0 | | | flarum-tags | v1.7.1 | | | flarum-suspend | v1.7.0 | | | flarum-approval | v1.7.0 | | | flarum-markdown | v1.7.0 | | | afrux-forum-widgets-core | v0.1.7 | | | ziiven-money-leaderboard | v0.1.4 | | | ziiven-daily-check-in | v0.3.7 | | | zerosonesfun-up | 1.0 | | | zerosonesfun-bbcode-space | 1.0 | | | zequeen-acgembed-remastered | v1.1.2 | | | v17development-user-badges | v0.2.1 | | | v17development-seo | v1.8.0 | | | the-turk-stickiest | 2.0.1 | | | pipecraft-id-slug | v1.1.0 | | | nosun-reply-to-see | v1.03 | | | justoverclock-header-slideshow | 0.1.1 | | | ianm-synopsis | 1.2.2 | | | fof-user-bio | 1.1.1 | | | fof-upload | 1.2.3 | | | fof-terms | 1.2.1 | | | fof-split | 1.1.0 | | | fof-sitemap | 1.0.3 | | | fof-profile-image-crop | 1.1.1 | | | fof-polls | 1.3.0 | | | fof-merge-discussions | 1.3.1 | | | fof-links | 1.1.3 | | | fof-frontpage | 1.1.1 | | | fof-drafts | 1.2.2 | | | fof-doorman | 1.1.2 | | | fof-default-user-preferences | 1.2.0 | | | flarum-subscriptions | v1.7.0 | | | flarum-statistics | v1.7.0 | | | flarum-nicknames | v1.7.0 | | | flarum-mentions | v1.7.0 | | | flarum-lock | v1.7.0 | | | flarum-lang-chinese-simplified | v1.3.0 | | | flarum-bbcode | v1.7.0 | | | clarkwinkelmann-money-rewards | 1.0.0 | | | clarkwinkelmann-emojionearea | 1.0.0 | | | askvortsov-rich-text | v2.1.7 | | | askvortsov-markdown-tables | v1.2.1 | | | askvortsov-categories | v3.0.8 | | | afrux-top-posters-widget | v0.1.4 | | | afrux-online-users-widget | v0.1.6 | | | afrux-news-widget | v0.1.1 | | +--------------------------------+---------+--------+ Base URL: Installation path: C:\phpstudy_pro\WWW\flarum Queue driver: sync Session driver: file Scheduler status: Never run Mail driver: smtp Debug mode: off

JUMPING

clarkwinkelmann
I had update again, sad....

Migrating Flarum...
Nothing to migrate.
Migrating extension: askvortsov-auto-moderator
Nothing to migrate.
Migrating extension: flarum-likes
Nothing to migrate.
Migrating extension: flarum-flags
Nothing to migrate.
Migrating extension: antoinefr-money
Nothing to migrate.
Migrating extension: flarum-sticky
Nothing to migrate.
Migrating extension: flarum-tags

In Connection.php line 712:

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'post_mentions_tag' already exists (SQL: create tabl
  e `post_mentions_tag` (`post_id` int unsigned not null, `mentions_tag_id` int unsigned not null, `created_at` datet
  ime default CURRENT_TIMESTAMP null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB)


In Exception.php line 18:

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'post_mentions_tag' already exists


In PDOStatement.php line 117:

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'post_mentions_tag' already exists

JUMPING

Okay, I've probably solved it，change all tables engine MylSAM => InnoDB

clarkwinkelmann

JUMPING did you change the tables to MyISAM manually in the past or were they always like that? Flarum should not have created MyISAM tables.

You might be missing other important foreign key constraints if the tables were not using InnoDB. I suggest you make a data-only database backup, then re-install Flarum (or make a second install) with the same extensions, then delete all data from the fresh install and restore the data backup on top of the fresh database. Then you can re-connect the database to your original forum or restore your avatars and other images/uploads on the disk of the new forum.