[Bug]: DB conversion from MySQL to PgSQL fails when migrating oc_calresources_rooms, as oc_calresources_stories isn't copied first

[ronnybremer]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

Running the command:

occ db:convert-type --all-apps pgsql user_nextcloud pgsql-prod --port=5433 db_nextcloud -vv

on an NC 31.05 instance aborts while transferring calendar data. After the schema is fully built and the first tables are transferred, the conversion stops with an error.

 - oc_authtoken
 72/72 [============================] 100% < 1 sec/< 1 sec
 - oc_bruteforce_attempts
    0 [>---------------------------] < 1 sec
 - oc_calendar_appt_bookings
    0 [>---------------------------] < 1 sec
 - oc_calendar_appt_configs
    0 [>---------------------------] < 1 sec
 - oc_calendar_invitations
 19/19 [============================] 100% < 1 sec/< 1 sec
 - oc_calendar_reminders
 693/693 [============================] 100% 7 secs/7 secs
 - oc_calendar_resources
    0 [>---------------------------] < 1 sec
 - oc_calendar_resources_md
    0 [>---------------------------] < 1 sec
 - oc_calendar_rooms
 5/5 [============================] 100% < 1 sec/< 1 sec
 - oc_calendar_rooms_md
 17/17 [============================] 100% < 1 sec/< 1 sec
 - oc_calendarchanges
chunked query, 4 chunks
 3512/3512 [============================] 100% 31 secs/31 secs
 - oc_calendarobjects
chunked query, 2 chunks
 1500/1500 [============================] 100% 16 secs/16 secs
 - oc_calendarobjects_props
chunked query, 5 chunks
 4545/4545 [============================] 100% 44 secs/44 secs
 - oc_calendars
 35/35 [============================] 100%  1 sec/1 sec
 - oc_calendarsubscriptions
 8/8 [============================] 100% < 1 sec/< 1 sec
 - oc_calresources_buildings
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_calresources_resources
    0 [>---------------------------] < 1 sec
 - oc_calresources_restricts
    0 [>---------------------------] < 1 sec
 - oc_calresources_rooms
 0/5 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 50:

  [Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException (7)]
  An exception occurred while executing a query: SQLSTATE[23503]: Foreign key violation: 7 ERROR:  insert or update on table "oc_calresources_rooms" violates foreign key constraint "fk_75864578aa5d4036"
  DETAIL:  Key (story_id)=(2) is not present in table "oc_calresources_stories".


Exception trace:
  at /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:50
 Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert() at /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php:1976
 Doctrine\DBAL\Connection->handleDriverException() at /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php:1918
 Doctrine\DBAL\Connection->convertExceptionDuringQuery() at /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php:1218
 Doctrine\DBAL\Connection->executeStatement() at /var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php:292
 Doctrine\DBAL\Connections\PrimaryReadReplicaConnection->executeStatement() at /var/www/html/nextcloud/lib/private/DB/Connection.php:466
 OC\DB\Connection->executeStatement() at /var/www/html/nextcloud/lib/private/DB/ConnectionAdapter.php:67
 OC\DB\ConnectionAdapter->executeStatement() at /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:306
 OC\DB\QueryBuilder\QueryBuilder->executeStatement() at /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:268
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/html/nextcloud/core/Command/Db/ConvertType.php:355
 OC\Core\Command\Db\ConvertType->copyTable() at /var/www/html/nextcloud/core/Command/Db/ConvertType.php:401
 OC\Core\Command\Db\ConvertType->convertDB() at /var/www/html/nextcloud/core/Command/Db/ConvertType.php:195
 OC\Core\Command\Db\ConvertType->execute() at /var/www/html/nextcloud/3rdparty/symfony/console/Command/Command.php:326
 Symfony\Component\Console\Command\Command->run() at /var/www/html/nextcloud/3rdparty/symfony/console/Application.php:1078
 Symfony\Component\Console\Application->doRunCommand() at /var/www/html/nextcloud/3rdparty/symfony/console/Application.php:324
 Symfony\Component\Console\Application->doRun() at /var/www/html/nextcloud/3rdparty/symfony/console/Application.php:175
 Symfony\Component\Console\Application->run() at /var/www/html/nextcloud/lib/private/Console/Application.php:187
 OC\Console\Application->run() at /var/www/html/nextcloud/console.php:87
 require_once() at /var/www/html/nextcloud/occ:33

I do see the oc_calresources_buildings table being copied, but not the oc_calresources_stories table, leading to the foreign key error.

Steps to reproduce

1. NC 31.05 running on MySQL 8
2. add a calendar resource building, floor and room
3. migrate

Expected behavior

the oc_calresources_stories table should be copied before the oc_calresources_rooms table.

Nextcloud Server version

31

Operating system

RHEL/CentOS

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database engine version

MySQL

Is this bug present after an update or on a fresh install?

Upgraded to a MAJOR version (ex. 31 to 32)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

No response

[kesselb]

cc @ChristophWurst @st3iny

[st3iny]

Thanks for the report.

I'm wondering if there is a way to specify the order in which to copy the tables? I'm looking at the code and can't find one.

To be able to fully fix this, we would need apps to register meta data about dependencies between tables so that they are copied in the right order.

[kesselb]

I'm wondering if there is a way to specify the order in which to copy the tables? I'm looking at the code and can't find one.

This is likely another reason why foreign keys are not so common in our codebase :)

[ChristophWurst]

Yes, but this should not be a reason against foreign keys. E.g. the cascade delete is awesome. The manual orphan checks are all terribly inefficient.

Back to the topic.

As an alternative to the ordered import we could use SET FOREIGN_KEY_CHECKS=0; for the duration of the conversion.