B
B
BonBon Slick2020-09-15 19:42:39
Doctrine ORM
BonBon Slick, 2020-09-15 19:42:39

Alter table with IF conditions?

[notice] Migrating up to DoctrineMigrations\Version20200907062416
[error] Migration DoctrineMigrations\Version20200209084310 failed during Execution. Error: "An exception occurred while executing 'ALTER TABLE useraccounts_v3 ADD IF NOT EXISTS token VARCHAR(255) DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS token VARCHAR(255) DEFAULT NULL' at line 1"

In AbstractMySQLDriver.php line 79:
                                                                                                                 
  An exception occurred while executing 'ALTER TABLE useraccounts_v3 ADD IF NOT EXISTS token VARCHAR(255) DEFAU  
  LT NULL':                                                                                                      
                                                                                                                 
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manua  
  l that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS token VARCHAR  
  (255) DEFAULT NULL' at line 1                                                                                  
                                                                                                                 

In PDOConnection.php line 43:
                                                                                                                 
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manua  
  l that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS token VARCHAR  
  (255) DEFAULT NULL' at line 1                                                                                  
                                                                                                                 

In PDOConnection.php line 41:
                                                                                                                 
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manua  
  l that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS token VARCHAR  
  (255) DEFAULT NULL' at line 1


This is due to the fact that several projects from different teams are connected to the same database and the devil knows what columns and data are there. Therefore, it is impossible to drop as well as roll new migrations.
Tried to add these conditionsIF NOT EXISTS IF EXISTS
$this->addSql('ALTER TABLE member DROP IF EXISTS avatar_url');
        $this->addSql('ALTER TABLE member ADD IF NOT EXISTS first_login_at DATETIME DEFAULT NULL'');
        $this->addSql('ALTER TABLE cron_report ADD IF NOT EXISTS CONSTRAINT FK_B6C6A7F5BE04EA9 FOREIGN KEY (job_id) REFERENCES cron_job (id) ON DELETE CASCADE');

However, this resulted in the errors above.
It is also not clear what to do with indexes.
$this->addSql('CREATE UNIQUE INDEX UNIQ_302770B57597D3FE ON useraccounts_v3 (member_id)');


Check all tables and columns where there are more than a hundred tables manually and come up with SQL madness.
Therefore, I tried to add IFs.

Perhaps Doctrine has something for this?
And how to roll migrations without knowing what is in the remote database without its drop?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
C
ComodoHacker, 2020-09-15
@BonBonSlick

several projects from different teams are connected to one database and the devil knows what columns and data are there

That is the problem. It's time for you to allocate a person who will be responsible for the design of the database and all migrations.
If each team works with its own data that is not related to each other, then another option is possible. Spread data across different databases or different schemas.
Of course, you can find out in the script whether there is such and such a column or index in the table by making a request to the system views. But that doesn't change what was said above.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question