Skip to content
This repository was archived by the owner on Jun 2, 2022. It is now read-only.

Inconsistent database engine result in failed FK creation #59

Open
noogen opened this issue Apr 22, 2022 · 2 comments
Open

Inconsistent database engine result in failed FK creation #59

noogen opened this issue Apr 22, 2022 · 2 comments
Labels
bug Something isn't working

Comments

@noogen
Copy link

noogen commented Apr 22, 2022

Receiving the following error during initial migration usingphp artisan migrate:fresh --seed or visit your.site.com/setup

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table xxx.#clients (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table 'clients' add constraint 'clients_industry_id_foreign' foreign key ('industry_id') references 'industries' ('id') on delete no action on update no action)

To Reproduce
Steps to reproduce the behavior:

  1. clone project
  2. run through setup instruction .env file, database creation, etc....
  3. finally run php artisan migrate:fresh --seed or visit your.site.com/setup

Expected behavior
Should not error.

Desktop/Smartphone:
Should not matter.

Additional context
Issue is due to database engine difference between clients InnoDB and industries MyISAM table.

Instead of manually define database engine everywhere, I recommend to define it at the global/configuration area? https://github.com/cytech/BillingTrack/blob/master/config/database.php#L25

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => env('DB_STRICT', false),
            'engine' => 'innodb',
            //'charset'   => 'utf8',
            //'collation' => 'utf8_unicode_ci',

        ],
@cytech cytech added the bug Something isn't working label Apr 22, 2022
@cytech
Copy link
Owner

cytech commented Apr 22, 2022

interesting. wonder why this has never popped up before. Probably my Mysql server defaults to the default innodb.
i assume your default mysql server engine is set to MyISAM ? so the migrations that do not specify ->engine are going myISAM ?
anyhow I agree on the above change.
thank you

@noogen
Copy link
Author

noogen commented Apr 22, 2022

Yes, I created my database from the cpanel wizard and it defaulted to MyISAM.

... hah! Eureka, I didn't realize but I just did a search and found similar issue in the past that user didn't really know what happened: #43

# for free to subscribe to this conversation on GitHub. Already have an account? #.
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

2 participants