Migration 2019_08_12_222938_create_avatars_for_existing_contacts fails because of missing contacts.deleted_at

[allo-]

Describe the bug I've migrated from an old monica version (from 2019) to v3.7.0

Now I get a database error that looks like some migration is missing:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'address_book_id' in 'where clause' (SQL: select `accounts`.*, (select count(*) from `contacts` where `accounts`.`id` = `contacts`.`account_id` and `address_book_id` is null and `contacts`.`deleted_at` is null) as `contacts_count`, (select count(*) from `reminders` where `accounts`.`id` = `reminders`.`account_id`) as `reminders_count`, (select count(*) from `notes` where `accounts`.`id` = `notes`.`account_id`) as `notes_count`, (select count(*) from `activities` where `accounts`.`id` = `activities`.`account_id`) as `activities_count`, (select count(*) from `gifts` where `accounts`.`id` = `gifts`.`account_id`) as `gifts_count`, (select count(*) from `tasks` where `accounts`.`id` = `tasks`.`account_id`) as `tasks_count` from `accounts` where `accounts`.`id` = 1 limit 1)

Migrations containing address_book_id are:

• 2020_03_25_065551_add_addressbook_subscription.php
• 2020_03_25_082324_add_contact_address_book_id.php

But it looks like the migrate script finished without any problems:

✓ Maintenance mode: on
✓ Resetting application cache
✓ Clear config cache
✓ Resetting route cache
✓ Resetting view cache
✓ Performing migrations
✓ Check for encryption keys
✓ Ping for new version
✓ Cache configuraton
✓ Maintenance mode: off
Monica v3.7.0 is set up, enjoy.

What can I do to fix it? And is there a missing migration? The two migrations don't seem to modify the accounts table, but contact and addressbook

[allo-]

The migration seems to fail earlier and probably the force skipped it:

Migrating: 2019_08_12_222938_create_avatars_for_existing_contacts

In Connection.php line 712:
                                                                                                                                                            
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'contacts.deleted_at' in 'where clause' (SQL: select count(*) as aggregate from `contacts` where   
  (`avatar_adorable_url` is null or `avatar_default_url` not like avatars/%) and `contacts`.`deleted_at` is null)                                           
                                                                                                                                                            

In Connection.php line 368:
                                                                                                  
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'contacts.deleted_at' in 'where clause' 

[allo-]

Possibly related to #6316

[allo-]

Looks like 6f887dfb7590ee62833a8d99617e0993eb1a83db accidentally deleted database/migrations/2017_02_10_215705_remove_deleted_at_from_contact.php.

Git lists it as renamed to to database/migrations/2022_01_02_222042_contact_soft_delete.php but with changes that revert the effect:

index d244989b8..1abeb73a0 100644
--- a/database/migrations/2017_02_10_215705_remove_deleted_at_from_contact.php
+++ b/database/migrations/2022_01_02_222042_contact_soft_delete.php
@@ -4,7 +4,7 @@ use Illuminate\Support\Facades\Schema;
 use Illuminate\Database\Schema\Blueprint;
 use Illuminate\Database\Migrations\Migration;
 
-class RemoveDeletedAtFromContact extends Migration
+class ContactSoftDelete extends Migration
 {
     /**
      * Run the migrations.
@@ -13,11 +13,11 @@ class RemoveDeletedAtFromContact extends Migration
      */
     public function up()
     {
-        Schema::table('contacts', function (Blueprint $table) {
-            $table->dropColumn(
-                'deleted_at'
-            );
-        });
+        if (! Schema::hasColumn('contacts', 'deleted_at')) {
+            Schema::table('contacts', function (Blueprint $table) {
+                $table->softDeletes();
+            });
+        }
     }
 
     /**
@@ -28,7 +28,7 @@ class RemoveDeletedAtFromContact extends Migration
     public function down()
     {
         Schema::table('contacts', function (Blueprint $table) {
-            $table->softDeletes();
+            $table->dropSoftDeletes();
         });
     }
 }

So it looks like the new migration assume there was no deletion of the deleted_at column and fail to run when they need to access it.

[allo-]

Workaround (I hope I didn't break anything): Add the column before the migrations from 2019 to 2022:

alter table contacts add column deleted_at timestamp null;

I hope as the 2022 migration drops soft deletes anyway it is (hopefully) irrelevant if I missed any constraints/indices or other things I should have added in addition to the column.

[0xAl3xH]

Thank you! This worked for me! ^ Should really be fixed... For posterity, this is the full steps in case someone else runs into this issue and is using docker to run their monica instances

1. docker ps to find your sql container id
2. docker exec -it [your sql container id] bash
3. mysql --user=[your username] --password=[your password] monica
4. alter table contacts add column deleted_at timestamp null; in the mysql shell. I got a response like Query OK, 0 rows affected (0.07 sec)
5. docker ps to find your monica container id
6. docker exec -it [your monica container id] bash
7. run php artisan migrate and hope everything works.