What happened?

Description

I got an error when running all the pending migrations for the Craft 5 upgrade using php craft up. The actual error was related to a plugin and not important here. The CLI command then asked whether I wanted to restore the database backup created by the command before running the migrations. I did that, then fixed the problem in my code that caused the migration to fail.

Then I ran php craft up again, but this time I got this error:

*** applying m230617_070415_entrify_matrix_blocks
    > alter column sectionId in table {{%entries}} to integer ... done (time: 0.036s)
    > add column primaryOwnerId integer AFTER `parentId` to table {{%entries}} ... done (time: 0.012s)
    > add column fieldId integer AFTER `primaryOwnerId` to table {{%entries}} ... done (time: 0.012s)
    > add column deletedWithOwner boolean NULL DEFAULT NULL AFTER `dateDeleted` to table {{%elements}} ... done (time: 0.092s)
    > create table {{%elements_owners}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'cb_elements_owners' already exists

Checked the migration in question – this one creates the elements_owners table, so it was created during the first attempt at running migrations, but not deleted when restoring the database backup.

Steps to reproduce

1. Create a migration that will create a table and then return an error.
2. Run the migration using php craft up, answering yes when asked whether you want to create a database backup and whether you want to restore that backup after the migration fails.

Expected behavior

If I restore a database backup after a failed migration, I expect the database to be restored to the exact state as before that migration. I don't expect tables that were created before the error occurred to stick around, causing additional errors. I would expect to be able to run migrations again, without additional errors due to the first attempt.

Actual behavior

Tables created during the failing migration (or any previous migration) stick around, even when the database backup is restored.

The db/restore command explicitly asks whether to drop all tables before restoring the backup. The migrate/up command should do this as a default. It could also add the same prompt asking whether I want to drop all tables before restoring the backup, though I can't see any case where I wouldn't want this behaviour.

Craft CMS version

5.1.2

PHP version

8.2

Operating system and version

No response

Database type and version

MySQL

Image driver and version

No response

Installed plugins and versions