Symfony 4.4+PostgreSQL 9.6执行Doctrine回滚迁移时出现SQLSTATE[42P06]错误

执行回滚迁移命令：

php bin/console doctrine:migrations:migrate DoctrineMigrations\Version20220713135119

命令行执行输出：

PS C:\_EnvTest\my_project_composer2> php bin/console doctrine:migrations:migrate  DoctrineMigrations\Version20220713135119     

 WARNING! You are about to execute a migration in database "agencegrafikart" that could result in schema changes and data loss. Are you sure you wish to continue? (yes/no) [yes]:
 > 

[notice] Migrating down to DoctrineMigrations\Version20220713135119
[error] Migration DoctrineMigrations\Version20220718131542 failed during Execution. Error: "An exception occurred while executing a query: SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  le schema « public » already exist"

In ExceptionConverter.php line 91:

  An exception occurred while executing a query: SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema « public » already exist


In Exception.php line 30:

  SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema « public » already exist  


In Connection.php line 72:

  SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema « public » already exist

对应的迁移代码（down方法）：

$this->addSql('CREATE SCHEMA public');
$this->addSql('ALTER TABLE option_property DROP CONSTRAINT FK_AB856D7AA7C41D6F');
$this->addSql('DROP SEQUENCE option_id_seq CASCADE');
$this->addSql('DROP TABLE option');
$this->addSql('DROP TABLE option_property');

问题原因 #

PostgreSQL数据库默认自带public模式，迁移的down方法中尝试重复创建该模式，触发42P06重复模式错误，导致回滚失败。

解决方法 #

1. 修改迁移文件：直接移除$this->addSql('CREATE SCHEMA public');这一行，重新执行回滚命令即可。
2. 兼容处理（可选）：如果需要保留创建逻辑，可将SQL改为条件创建：CREATE SCHEMA IF NOT EXISTS public;，但由于public是默认存在的，推荐直接移除该行。
3. 手动执行SQL：若无法修改迁移文件，可手动执行剩余的回滚SQL语句，跳过创建public模式的步骤。

内容的提问来源于stack exchange，提问作者Manon