Question

If I need to change a concat value for a VirtualAs column, Skipper generates a migration that fails: 'Changing the STORED status' is not supported for generated columns'

Instead this column should be deleted and added?

If not, how do I recover from this without having to break my earlier migrations?

Comments

Hello Frode,

unfortunately, it seems that Laravel doesn't support changing it at all. So probably the only way would be to manually remove the column and add it new one, to simulate this behavior.

You will have to remove migrations in Skipper until this is broken migration, remove the field, create new migration, and then add a correct field to fix it.

We can check Laravel documentations to see more info about this, but it's probably some inner limitation of Laravel what will have to be discussed/solved by Laravel team.

But Skipper can't do this automatically, because this could cause deleting all data from such field. So until Laravel will fix this issue it will be necessary to create also manual migrations to migrate data correctly.

---

this is a SQL server issue, not Laravel. The Error is SQL server that gives.
I understand your workaround will do it like this.

BTW. a virtual column, can NOT delete any data, since its generated automatically onSave by SQL server and it can not contain data from outside table-row. ita an expression from other columns in the same row. What`s needed is to "touch" all records to trigger the virtualAs function.

---

Thanks for the explanation about the virtual column. I'm not Laravel expert.
But still, if there is needed some recalculation, this has to be handled/triggered by ORM layer in Laravel during migrations.

Unfortunately, I don't see any way how can Skipper do that (other than simulating removing and adding columns).

Unfortunately forcing column removal&creation after some property change is big change in the whole migration logic and I'm not sure if we would be even able to do that.

---

VirtualAs is a MYSQL only feature. and Mysql states:

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/alter-table-generated-columns.html

Virtual generated columns cannot be altered to stored generated columns, or vice versa. To work around this, drop the column, then add it with the new definition.

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);
ALTER TABLE t1 DROP COLUMN c2;
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

This means that I would have done the following in Skipper:

• If a user changes the virtualAS field (meaning there is prior migrations). You should warn user, Do you really want to change this value. This will drop the column and recreate it, just as MYSQL documentation is saying..

Until MYSQL supports otherwise, that is better than how it is today.. Today it breaks the migrations.

At least that is my vote.

---

Unfortunately, it's not such easy. Skipper isn't "Database" tool, Skipper is ORM tool and because of that Skipper doesn't know anything if the project is deployed on MySQL, SQL Server or anything else.

This what you're describing is a work of ORM, not Skipper. Skipper is exporting ORM migrations, not database migrations.

Laravel should know that MySQL doesn't support such change and Larave should "fix it" by creating a temporary column, migrating data, destroying the column, and renaming the column.

This is the primary purpose of ORM. To hide implementation details of every DB and offer a single API.

So as I wrote earlier, Laravel has to fix it (or MySQL), not Skipper, which knows nothing about target DB.