migrations

migrations copied to clipboard

Hi all.

I am using MySQL and I am connecting doctrine to my DB with an URL, like mysql://root:root@mysql/default?serverVersion=5.7

But the thing is, I am working with an existing DB that I absolutely cannot modify, and there are several schemas in it. Let's say for instance there's a "secondary" schema.

The problem is that when I create an entity in default schema, let's say default.tableA, then create an entity in secondary schema, let's say secondary.tableZ, when I use make:migration command, it generates migrations file only for default schema, not the secondary one.

This is due to the fact that I am using "default" as default DB in connection URL, and that mysql has mixed the concepts of DB and schema.

Is there a solution for generating migration of all schemas/DBs?

I guess that is should be moved to doctrine/orm

Hard to tell what the issue is here… I'm a bit confused.

an existing DB that I absolutely cannot modify

I use make:migration

Isn't this contradictory?

You are right, my message was not very accurate.

So to clarify this, I can add new tables, or new columns, but not change was is already existing. The reason for this is easy to understand: the database is common to two differents websites/app. One website is used as a commercial website, while the other is used as backoffice. And for this new backoffice, I am using symfony/doctrine (while the other website is plain vanilla php)

Therefore, now that I am using symfony/doctrine, I am trying to do things the proper 2021 way with migrations and everything.

The database itself contains something like 8 different schema (or "databases", as mySQL name them). One for everything related to products (tables about products themselves, categories, stock management, etc....), one for all things related to billing, one forrr statistics and logs, etc... I think you get the idea. In total, there are about 800 tables spread across all these schemas(DB)

Thanks, it's a bit more clear now.

https://stackoverflow.com/questions/6844153/migrating-multiple-databases-using-doctrine2-with-symfony2 seems to show it should be possible to manage several databases with doctrine/migrations . I don't know however where that make:migration command you are mentioning is defined, and how much it delegates to Doctrine. If it does the same as commands defined in the bundle, do you reproduce the issue with those commands?

Thank you, but my problem is a bit different. In the stack question you mentionned, the guy had 2 REALLY different database, and thus 2 different connections.

And that's really the curcial point here, that may be a bit hard to understand, but in MySQL, they named schemas "database"...so in reality, I have one one "real" database, with 8 different schemas. But MySQL keep refering to these schemas as "databases".

And at this point you might say "ok, but it's just a name, what's the fuss about it?"

Well, it's not just a name. Because in the connection URL (located in .env file), I MUST specify a database. I cannot leave it blank or the connection won't work. So instead of connecting only to mysql://root:root@mysql/ (yes it's in a docker container) where I would/could have access to my 8 schema, doctrine forces me to pick one of my schemas to fill this "database" parameter in the connection URL. In my example in first post, I've picked "default".

And that's why when I try to create migration, it only generates migration for this "default" schema, or not the other ones, because of this whole naming/concept confusion between "schema" and "database" in MySQL. While all my 8 schemas are on the same "real" database, and thus, on the same connection (and of course, there arre foreign keys constraints defined between the differents schema, and so is doctrine mapping )

Well, you could have 2 connections to the same host, what forces you to have only one? I don't think you will need to perform cross-schema/database queries during the migrations you are going to write, right?

having multiple connections, even to the same DB, would not allow me to have mapping between the different schemas. Therefore, migrations would probably fail when encountering a foreign key located on another schema.

But I haven't tried. I'll give it a shot tomorrow. But I doubt it will be succesful

Didn't realise you had cross-schema reference, but yes, that might be an issue :grimacing:

By the way, what happens currently when you do that? Does it create a foreign key to a table, but not the referenced table itself it is not in the default schema?