Install using MySQL causes exception

[luspi]

Describe the bug
When telling 2FAuth to use MySQL as database, an exception occurs while running php artisan migrate:refresh:

Migration table not found.
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.03 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.02 seconds)
Migrating: 2016_06_01_000001_create_oauth_auth_codes_table
Migrated:  2016_06_01_000001_create_oauth_auth_codes_table (0.04 seconds)
Migrating: 2016_06_01_000002_create_oauth_access_tokens_table
Migrated:  2016_06_01_000002_create_oauth_access_tokens_table (0.04 seconds)
Migrating: 2016_06_01_000003_create_oauth_refresh_tokens_table
Migrated:  2016_06_01_000003_create_oauth_refresh_tokens_table (0.04 seconds)
Migrating: 2016_06_01_000004_create_oauth_clients_table
Migrated:  2016_06_01_000004_create_oauth_clients_table (0.03 seconds)
Migrating: 2016_06_01_000005_create_oauth_personal_access_clients_table
Migrated:  2016_06_01_000005_create_oauth_personal_access_clients_table (0.01 seconds)
Migrating: 2017_03_03_100000_create_options_table
Migrated:  2017_03_03_100000_create_options_table (0.02 seconds)
Migrating: 2019_05_16_162730_create_twofaccounts_table
Migrated:  2019_05_16_162730_create_twofaccounts_table (0.01 seconds)
Migrating: 2020_03_25_095517_add_order_column_to_twofaccounts_table
Migrated:  2020_03_25_095517_add_order_column_to_twofaccounts_table (0.02 seconds)
Migrating: 2020_10_05_210557_add_last_seen_to_users_table
Migrated:  2020_10_05_210557_add_last_seen_to_users_table (0.01 seconds)
Migrating: 2020_10_11_202302_add_provider_column_to_oauth_clients_table
Migrated:  2020_10_11_202302_add_provider_column_to_oauth_clients_table (0 seconds)
Migrating: 2020_10_20_210129_create_groups_table
Migrated:  2020_10_20_210129_create_groups_table (0.02 seconds)
Migrating: 2020_10_20_211115_add_group_id_column_to_twofaccounts_table

   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 3780 Referencing column 'group_id' and referenced column 'id' in foreign key constraint 'twofaccounts_group_id_foreign' are incompatible. (SQL: alter table `twofaccounts` add constraint `twofaccounts_group_id_foreign` foreign key (`group_id`) references `groups` (`id`) on delete set null)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
    667|         // If an exception occurs when attempting to run a query, we'll format the error
    668|         // message to include the bindings with SQL, which will make this exception a
    669|         // lot more helpful to the developer instead of just the database's errors.
    670|         catch (Exception $e) {
  > 671|             throw new QueryException(
    672|                 $query, $this->prepareBindings($bindings), $e
    673|             );
    674|         }
    675| 

      +12 vendor frames 
  13  database/migrations/2020_10_20_211115_add_group_id_column_to_twofaccounts_table.php:24
      Illuminate\Support\Facades\Facade::__callStatic()

      +34 vendor frames 
  48  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

To Reproduce
Steps to reproduce the behavior:

1. Clone git repo
2. Install php dependencies using php composer
3. Create MySQL user/database
4. Edit .env file to use MySQL backend
5. Run php artisan migrate:refresh

Expected behavior
Command should complete successfully (it does when using SQLite backend).

Server:

• OS: Ubuntu 20.04
• 2FAuth version: 2.0.0

[Bubka]

I pushed a fix yesterday on the dev branch (commit e7156d0) for a key length issue with older versions of MySQL.

If you are confortable with git you can checkout the dev branch and try to migrate again. It's a key issue in both cases, maybe the change will fix your issue too.

What version of MySQL do you use? I will try to reproduce your issue on my side.

[luspi]

I'm using the latest stable MySQL, version 8.0.22.

I just tried it with the dev branch and still get the same exception.

[Bubka]

Thanks for the feedback, I was able to reproduce the issue, even with MySQL v5.7.*

I fixed all the issues I found with migrations, you can pull latest commits on your dev branch if you want to try again otherwise I will publish a new release tomorrow.

[muratulashozturk]

I have installed 5.7.32 - MySQL Community Server (GPL) but still facing the same error.

  SQLSTATE[HY000]: General error: 1005 Can't create table `twofapp`.`twofaccounts` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `twofaccounts` add constraint `twofaccounts_group_id_foreign` foreign key (`group_id`) references `groups` (`id`) on delete set null

[Bubka]

Did you run the migration from the dev branch of 2FAuth?
Because the master branch is not up-to-date yet (i'm preparing a new release right now)

[muratulashozturk]

oh, didn't see that. trying it right now.

[Bubka]

Wait, I just released v2.0.1 on the master branch. Just git pull to update your repo.

[luspi]

Perfect, it works now, thanks!