Internal server error when loading mail app after using groupware provisioning

Nextcloud version (eg, 20.0.5): 27RC2
Operating system and version (eg, Ubuntu 20.04): Centos 8 Stream
Apache or nginx version (eg, Apache 2.4.25): nextcloud-aio-apache:latest
PHP version (eg, 7.4): 8.0.28

The issue you are facing:
I was trying to use the pre-provisioning for Mail in groupware settings and then loading the mail app resulted in an internal server error. Loggs indicated the issue was due to oc_mail_aliases. I read some posts here which indicated that dropping the oc_mail* tables and reinstalling mail seemed to resetup from scratch.
However this made the problem worse because now mail causes numerous errors failing to JOIN tables.

I have a database backup, but would rather recreate the empty tables and reconfigure email accounts than rollback the database and lose file states.

Database is a manually setup nextcloud-aio-postgresql container via docker compose

Is this the first time you’ve seen this error? (Y/N):
y

Steps to replicate it:

  1. use nextcloud aio
  2. have mail pre-provisioning configured but not working correctly
  3. manually upgrade to NC to 27RC2 for other reasons
  4. revisit pre-preprovisioning mail settings, remove current config, reconfigure
  5. settings responds with 0 accounts sucessfully configured
  6. loading mail results in internal server error and logs reports issue with oc_mail_aliases
  7. uninstall mail app and drop all oc_mail** tables

The output of your Nextcloud log in Admin > Logging:

[index] Error: OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "oc_mail_provisionings" does not exist
LINE 1: SELECT * FROM "oc_mail_provisionings" ORDER BY "provisioning...
                      ^ at <<closure>>

 0. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 295
    OC\DB\Exceptions\DbalException::wrap(["Doctrine\\DBAL ... "])
 1. /var/www/html/lib/public/AppFramework/Db/QBMapper.php line 335
    OC\DB\QueryBuilder\QueryBuilder->executeQuery()
 2. /var/www/html/custom_apps/mail/lib/Db/ProvisioningMapper.php line 58
    OCP\AppFramework\Db\QBMapper->findEntities(["OC\\DB\\QueryBuilder\\QueryBuilder"])
 3. /var/www/html/custom_apps/mail/lib/Service/Provisioning/Manager.php line 108
    OCA\Mail\Db\ProvisioningMapper->getAll()
 4. /var/www/html/custom_apps/mail/lib/Http/Middleware/ProvisioningMiddleware.php line 66
    OCA\Mail\Service\Provisioning\Manager->getConfigs()
 5. /var/www/html/lib/private/AppFramework/Middleware/MiddlewareDispatcher.php line 96
    OCA\Mail\Http\Middleware\ProvisioningMiddleware->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
 6. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 129
    OC\AppFramework\Middleware\MiddlewareDispatcher->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
 7. /var/www/html/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Mail\\Controller\\PageController"], "index")
 8. /var/www/html/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main("OCA\\Mail\\Controller\\PageController", "index", ["OC\\AppFramewo ... "], ["mail.page.index"])
 9. /var/www/html/lib/base.php line 1064
    OC\Route\Router->match("/apps/mail/")
10. /var/www/html/index.php line 36
    OC::handleRequest()

Caused by:

Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "oc_mail_provisionings" does not exist
LINE 1: SELECT * FROM "oc_mail_provisionings" ORDER BY "provisioning...
                      ^ at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/src/Connection.php line 1814
    Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(["Doctrine\\DBAL ... "], ["Doctrine\\DBAL\\Query"])
 1. /var/www/html/3rdparty/doctrine/dbal/src/Connection.php line 1749
    Doctrine\DBAL\Connection->handleDriverException(["Doctrine\\DBAL ... "], ["Doctrine\\DBAL\\Query"])
 2. /var/www/html/3rdparty/doctrine/dbal/src/Connection.php line 1055
    Doctrine\DBAL\Connection->convertExceptionDuringQuery(["Doctrine\\DBAL ... "], "SELECT * FROM \ ... c", [], [])
 3. /var/www/html/lib/private/DB/Connection.php line 262
    Doctrine\DBAL\Connection->executeQuery("SELECT * FROM \ ... c", [], [], null)
 4. /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php line 345
    OC\DB\Connection->executeQuery("SELECT * FROM \ ... c", [], [])
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 280
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 293
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/html/lib/public/AppFramework/Db/QBMapper.php line 335
    OC\DB\QueryBuilder\QueryBuilder->executeQuery()
 8. /var/www/html/custom_apps/mail/lib/Db/ProvisioningMapper.php line 58
    OCP\AppFramework\Db\QBMapper->findEntities(["OC\\DB\\QueryBuilder\\QueryBuilder"])
 9. /var/www/html/custom_apps/mail/lib/Service/Provisioning/Manager.php line 108
    OCA\Mail\Db\ProvisioningMapper->getAll()
10. /var/www/html/custom_apps/mail/lib/Http/Middleware/ProvisioningMiddleware.php line 66
    OCA\Mail\Service\Provisioning\Manager->getConfigs()
11. /var/www/html/lib/private/AppFramework/Middleware/MiddlewareDispatcher.php line 96
    OCA\Mail\Http\Middleware\ProvisioningMiddleware->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
12. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 129
    OC\AppFramework\Middleware\MiddlewareDispatcher->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
13. /var/www/html/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Mail\\Controller\\PageController"], "index")
14. /var/www/html/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main("OCA\\Mail\\Controller\\PageController", "index", ["OC\\AppFramewo ... "], ["mail.page.index"])
15. /var/www/html/lib/base.php line 1064
    OC\Route\Router->match("/apps/mail/")
16. /var/www/html/index.php line 36
    OC::handleRequest()

Caused by:

Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "oc_mail_provisionings" does not exist
LINE 1: SELECT * FROM "oc_mail_provisionings" ORDER BY "provisioning...
                      ^ at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php line 77
    Doctrine\DBAL\Driver\PDO\Exception::new(["PDOException", ... ]])
 1. /var/www/html/3rdparty/doctrine/dbal/src/Connection.php line 1050
    Doctrine\DBAL\Driver\PDO\Connection->query("SELECT * FROM \ ... c")
 2. /var/www/html/lib/private/DB/Connection.php line 262
    Doctrine\DBAL\Connection->executeQuery("SELECT * FROM \ ... c", [], [], null)
 3. /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php line 345
    OC\DB\Connection->executeQuery("SELECT * FROM \ ... c", [], [])
 4. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 280
    Doctrine\DBAL\Query\QueryBuilder->execute()
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 293
    OC\DB\QueryBuilder\QueryBuilder->execute()
 6. /var/www/html/lib/public/AppFramework/Db/QBMapper.php line 335
    OC\DB\QueryBuilder\QueryBuilder->executeQuery()
 7. /var/www/html/custom_apps/mail/lib/Db/ProvisioningMapper.php line 58
    OCP\AppFramework\Db\QBMapper->findEntities(["OC\\DB\\QueryBuilder\\QueryBuilder"])
 8. /var/www/html/custom_apps/mail/lib/Service/Provisioning/Manager.php line 108
    OCA\Mail\Db\ProvisioningMapper->getAll()
 9. /var/www/html/custom_apps/mail/lib/Http/Middleware/ProvisioningMiddleware.php line 66
    OCA\Mail\Service\Provisioning\Manager->getConfigs()
10. /var/www/html/lib/private/AppFramework/Middleware/MiddlewareDispatcher.php line 96
    OCA\Mail\Http\Middleware\ProvisioningMiddleware->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
11. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 129
    OC\AppFramework\Middleware\MiddlewareDispatcher->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
12. /var/www/html/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Mail\\Controller\\PageController"], "index")
13. /var/www/html/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main("OCA\\Mail\\Controller\\PageController", "index", ["OC\\AppFramewo ... "], ["mail.page.index"])
14. /var/www/html/lib/base.php line 1064
    OC\Route\Router->match("/apps/mail/")
15. /var/www/html/index.php line 36
    OC::handleRequest()

Caused by:

PDOException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "oc_mail_provisionings" does not exist
LINE 1: SELECT * FROM "oc_mail_provisionings" ORDER BY "provisioning...
                      ^ at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php line 72
    PDO->query("SELECT * FROM \ ... c")
 1. /var/www/html/3rdparty/doctrine/dbal/src/Connection.php line 1050
    Doctrine\DBAL\Driver\PDO\Connection->query("SELECT * FROM \ ... c")
 2. /var/www/html/lib/private/DB/Connection.php line 262
    Doctrine\DBAL\Connection->executeQuery("SELECT * FROM \ ... c", [], [], null)
 3. /var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php line 345
    OC\DB\Connection->executeQuery("SELECT * FROM \ ... c", [], [])
 4. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 280
    Doctrine\DBAL\Query\QueryBuilder->execute()
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 293
    OC\DB\QueryBuilder\QueryBuilder->execute()
 6. /var/www/html/lib/public/AppFramework/Db/QBMapper.php line 335
    OC\DB\QueryBuilder\QueryBuilder->executeQuery()
 7. /var/www/html/custom_apps/mail/lib/Db/ProvisioningMapper.php line 58
    OCP\AppFramework\Db\QBMapper->findEntities(["OC\\DB\\QueryBuilder\\QueryBuilder"])
 8. /var/www/html/custom_apps/mail/lib/Service/Provisioning/Manager.php line 108
    OCA\Mail\Db\ProvisioningMapper->getAll()
 9. /var/www/html/custom_apps/mail/lib/Http/Middleware/ProvisioningMiddleware.php line 66
    OCA\Mail\Service\Provisioning\Manager->getConfigs()
10. /var/www/html/lib/private/AppFramework/Middleware/MiddlewareDispatcher.php line 96
    OCA\Mail\Http\Middleware\ProvisioningMiddleware->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
11. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 129
    OC\AppFramework\Middleware\MiddlewareDispatcher->beforeController(["OCA\\Mail\\Controller\\PageController"], "index")
12. /var/www/html/lib/private/AppFramework/App.php line 183
    OC\AppFramework\Http\Dispatcher->dispatch(["OCA\\Mail\\Controller\\PageController"], "index")
13. /var/www/html/lib/private/Route/Router.php line 315
    OC\AppFramework\App::main("OCA\\Mail\\Controller\\PageController", "index", ["OC\\AppFramewo ... "], ["mail.page.index"])
14. /var/www/html/lib/base.php line 1064
    OC\Route\Router->match("/apps/mail/")
15. /var/www/html/index.php line 36
    OC::handleRequest()

no help? :frowning:

Can someone please direct me to where I can get the portions of the schema for mail database so I can recreate the missing tables?