Sql Server migrations fail with multiSchema when schemas include "dbo" schema #23688
Description
2024-04-02T07:53:58.682Z prisma:schemaEngine:stderr {"timestamp":"2024-04-02T07:53:58.682766692Z","level":"ERROR","fields":{"message":"Cannot drop the schema 'dbo'.","code":15150},"target":"tiberius::tds::stream::token","span":{"db.statement":"DROP SCHEMA IF EXISTS [dbo]","name":"quaint:query"},"spans":[{"name":"DevDiagnostic"},{"db.statement":"DROP SCHEMA IF EXISTS [dbo]","name":"quaint:query"}]}
2024-04-02T07:53:58.683Z prisma:schemaEngine:stderr {"timestamp":"2024-04-02T07:53:58.682822306Z","level":"INFO","fields":{"message":"Attempting best_effort_reset"},"target":"sql_schema_connector","span":{"namespaces":"Some(Namespaces(\"support\", [\"admin\", \"dbo\", \"test\"]))","name":"best_effort_reset"},"spans":[{"name":"DevDiagnostic"},{"namespaces":"Some(Namespaces(\"support\", [\"admin\", \"dbo\", \"test\"]))","name":"best_effort_reset"}]}
When running prisma migrate dev, schemaEngine tries to drop the dbo schema, fails and does not drop other table schemas from the schemas list. This causes an error There is already an object named 'test' in the database.
Proposed solutions:
- Do not try dropping
dboschema (and other built-in schemas). It is impossible to do. - Sort schemas (namespaces) so
dbois the last, and when dropping fails, other schemas are already dropped.