missing column updated_at in mail_local_messages

[tytgatlieven]

Steps to reproduce

1. For some reason the updated_at column was missing in the local_mail_messages table of my installation. (I might have done something wrong with updates in the past)

2. The lacking of this column results in the inability to send mails, save drafts, etc.

3. The updated_at column is in the expected db schema (occ db:schema:expected). However it was not in the current schema (occ db:schema:current)

4. I tried automatic repairs using:- occ db:add-missing-columns

• occ maintenance:repair
• occ maintenance:repair --include-expensive
• occ mail:clean-up
• occ mail:repair:system-autoresponders
• occ mail:repair:tags
• occ mail:repair:tags:migration-jobs However, none fixed the issue

5. After manually adding the column according to the expected schema all is operational as expected

Expected behavior

I would assume either / all of:

• occ db:add-missing-columns
• occ maintenance:repair
• occ maintenance:repair --include-expensive

fix the table schema.

Actual behavior

I had to manually add the column to the database after which everything worked fine

Mail app version

4.3.1

Nextcloud version

31.0.0

Mailserver or service

imap server (docker-mailserver)

Operating system

docker compose on linux 6.6

PHP engine version

PHP 8.3

Nextcloud memory caching

No response

Web server

None

Database

MariaDB

Additional info

No response

[ChristophWurst]

Thanks for the report!

This is the first time I hear about this column missing in production. Do you have an entry (mail, 2020Date20221103140538) in your oc_migrations?

[nextcloud-command]

Hi there! It looks like this issue hasn’t seen much activity for a while and might need a bit more information to move forward. If we don’t hear back soon, we may close it to keep things organized. Thanks again for your contributions – we really appreciate it!

[tytgatlieven]

Sorry for the delay. I was only now able to check this.

I do have multiple rows in migrations where app equals mail: mail 0100Date20180825194217

There are 93 records for mail

Hope this helps?

[ChristophWurst]

I do have multiple rows in migrations where app equals mail:

Could you please share the list? It's not private information but similar to the names you find in https://github.com/nextcloud/mail/tree/main/lib/Migration.

[tytgatlieven]

Here you go:

mail | 0100Date20180825194217 mail | 0110Date20180825195812 mail | 0110Date20180825201241 mail | 0130Date20190408134101 mail | 0156Date20190828140357 mail | 0161Date20190902103559 mail | 0161Date20190902103701 mail | 0161Date20190902114635 mail | 0180Date20190927124207 mail | 0190Date20191118160843 mail | 0210Date20191212144925 mail | 1020Date20191002091034 mail | 1020Date20191002091035 mail | 1020Date20200206134751 mail | 1030Date20200228105714 mail | 1040Date20200422130220 mail | 1040Date20200422142920 mail | 1040Date20200506111214 mail | 1040Date20200515080614 mail | 1040Date20200529124657 mail | 1050Date20200624101359 mail | 1050Date20200831124954 mail | 1050Date20200921141700 mail | 1050Date20200923180030 mail | 1060Date20201015084952 mail | 1080Date20201119084820 mail | 1080Date20210108093802 mail | 1090Date20210127160127 mail | 1090Date20210216154409 mail | 1096Date20210407150016 mail | 1100Date20210304143008 mail | 1100Date20210317164707 mail | 1100Date20210326103929 mail | 1100Date20210409091311 mail | 1100Date20210419080523 mail | 1100Date20210419121734 mail | 1100Date20210421113423 mail | 1100Date20210512142306 mail | 1101Date20210616141806 mail | 1105Date20210922104324 mail | 1110Date20210908114229 mail | 1115Date20211216144446 mail | 1120Date20220223094709 mail | 1124Date20220531094751 mail | 1124Date20220601084530 mail | 1130Date20220412111833 mail | 1130Date20220520062301 mail | 1140Date20220628174152 mail | 1140Date20220701103556 mail | 1140Date20220808203258 mail | 1140Date20221027171138 mail | 1140Date20221113205737 mail | 1140Date20221206162029 mail | 2000Date20220908130842 mail | 2010Date20221002201527 mail | 2020Date20221103140538 mail | 2100Date20221013143851 mail | 2300Date20221205160349 mail | 2300Date20221215143450 mail | 2300Date20221216115727 mail | 2300Date20230120085320 mail | 2300Date20230127093733 mail | 2300Date20230214104736 mail | 2300Date20230221170502 mail | 3000Date20230301152454 mail | 3001Date20230307113544 mail | 3300Date20230706140531 mail | 3300Date20230801124717 mail | 3400Date20230807300513 mail | 3400Date20230814160451 mail | 3400Date20230818160236 mail | 3400Date20230819161945 mail | 3400Date20230823153943 mail | 3400Date20230907103114 mail | 3500Date20231005091430 mail | 3500Date20231009102414 mail | 3500Date20231114180656 mail | 3500Date20231115182612 mail | 3500Date20231115184458 mail | 3600Date20240205180726 mail | 3600Date20240220134813 mail | 3600Date20240220134814 mail | 3600Date20240227172825 mail | 3700Date20240430115406 mail | 3700Date20240506161400 mail | 3800Date20240628163133 mail | 4000Date20240716172702 mail | 4001Date20241009140707 mail | 4100Date20240916174827 mail | 4100Date20241021091352 mail | 4100Date20241209000000 mail | 4200Date20241210000000 mail | 4200Date20241210000001

[nextcloud-command]

Hi there! It looks like this issue hasn’t seen much activity for a while and might need a bit more information to move forward. If we don’t hear back soon, we may close it to keep things organized. Thanks again for your contributions – we really appreciate it!

[ChristophWurst]

mail | 2010Date20221002201527 mail | 2020Date20221103140538 mail | 2100Date20221013143851

So 2020Date20221103140538 was executed.

Did you ever restore backups on your database that could have caused the inconsistency?

[nextcloud-command]

Hi there! It looks like this issue hasn’t seen much activity for a while and might need a bit more information to move forward. If we don’t hear back soon, we may close it to keep things organized. Thanks again for your contributions – we really appreciate it!