Error Add Primary Key Query about akaunting HOT 3 CLOSED

I have got this same error.
I checked and found that initially the user_roles table created with 3 columns user_id, user_role & user_type with primary index in database/migrations/2017_09_14_000000_core_v1.php

And in database/migrations/2022_07_21_000000_core_v305.php its dropping primary index for user_id, user_role & user_type then adding primary index for user_id, user_role and last dropping the column user_type

So I assume that the purpose of this 2022_07_21_000000_core_v305.php migration is dropping the user_type column.
Instead of dropping primary index for all three column primary index I just drop only user_type index then drop the user_type column.

And it worked. I don't know any underlying consequences 😀

This is the code:

    public function up()
    {
        Schema::table('user_roles', function (Blueprint $table) {
            $table->dropPrimary(['user_type']);
//            $table->dropPrimary(['user_id', 'role_id', 'user_type']);
//            $table->primary(['user_id', 'role_id']);
        });

        Schema::table('user_roles', function (Blueprint $table) {
            $table->dropColumn('user_type');
        });
    }

from akaunting.

I got this error too.

In MySQL, you can't drop a primary key with "DROP INDEX IF EXISTS" as MySQL does not support the "IF EXISTS" clause with "DROP INDEX" or "DROP PRIMARY KEY". If a primary key does not exist, attempting to drop it results in an error, as you experienced.

I check on the database and I didn't see the primary key that we want to delete using this migration code

$table->dropPrimary(['user_id', 'role_id', 'user_type']);

So I've just tried to change the migration file database/migrations/2022_07_21_000000_core_v305.php like this

public function up()
{
    DB::statement('ALTER TABLE user_roles ADD PRIMARY KEY(`user_id`, `role_id`)');
}

Note: for column user_roles, make sure to include the DB_PREFIX.
e.g: if your DB_PREFIX=ak, So you need to change the table name to ak_user_roles

from akaunting.

This issue solved this commit

from akaunting.