Upgrading to 9.4.0 fails when applying shares

[keesvanbemmel]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

Upon first startup after upgrading to Directus 9.4.0 I'm getting this error:
10:34:15 ✨ Running migrations...
10:34:15 ✨ Applying Add Shares...
10:34:15 🚨 error alter table "directus_sessions" drop column "data" - column "data" of relation "directus_sessions" does not exist

This will prevent our container from starting.

To Reproduce

I did the regular upgrade steps, yarn upgrade directus and then create a snapshot of the schema. Only change in the schema was directus version.

Errors Shown

No response

What version of Directus are you using?

9.4.0

What version of Node.js are you using?

16 lts

What database are you using?

Postgres 13

What browser are you using?

na

What operating system are you using?

MacOS

How are you deploying Directus?

Docker

[joselcvarela]

Hello @keesvanbemmel
Can you tell what was the version you had before?
I ran the migrations without any problem.

[keesvanbemmel]

9.3.0

…

On Fri, 24 Dec 2021, 15:11 José Varela, ***@***.***> wrote: Hello @keesvanbemmel <https://github.com/keesvanbemmel> Can you tell what was the version you had before? I ran the migrations without any problem. — Reply to this email directly, view it on GitHub <#10669 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABDLKFHZ47CZCAQAC4H7KQ3USR5SBANCNFSM5KWNVHQA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[joselcvarela]

This is odd.
You should had data column on directus_sessions, because it was added here:

directus/api/src/database/migrations/20210903A-add-auth-provider.ts

Lines 18 to 20 in 5e698a7

	await knex.schema.alterTable('directus_sessions', (table) => {
	table.json('data');
	});

And it seems this was added in v9.0.0-rc95.

I think this migration ran but it failed in another place and the data column was removed anyways.
Although, if some migration fails, the whole process should rollback.

Do you, by any chance, have changed this table manually?

As a workaround you can simple add a data JSON column to directus_sessions in order to upgrade.

[keesvanbemmel]

Weird... I'll add it manually and will try to figure out why this data column is missing. Can't recall doing anything manually though. Can close this issue!

…

On Fri, 24 Dec 2021, 15:22 José Varela, ***@***.***> wrote: This is odd. You should had data column on directus_sessions, because it was added here: https://github.com/directus/directus/blob/5e698a77237f0ce80e4d69c7404ba21a34053bab/api/src/database/migrations/20210903A-add-auth-provider.ts#L18-L20 And it seems this was added in v9.0.0-rc95. I think this migration ran but it failed in another place and the data column was removed anyways. Although, if some migration fails, the whole process should rollback. Do you, by any chance, have changed this table manually? As a workaround you can simple add a data JSON column to directus_sessions in order to upgrade. — Reply to this email directly, view it on GitHub <#10669 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABDLKFHL2N5OXNHZJZWNWLDUSR6ZFANCNFSM5KWNVHQA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[keesvanbemmel]

I'm thinking that applying my schema snapshot before doing the migrations somehow removed the data column (or the entire sessions table perhaps?).

…

On Fri, 24 Dec 2021, 15:23 Kees van Bemmel, ***@***.***> wrote: Weird... I'll add it manually and will try to figure out why this data column is missing. Can't recall doing anything manually though. Can close this issue! On Fri, 24 Dec 2021, 15:22 José Varela, ***@***.***> wrote: > This is odd. > You should had data column on directus_sessions, because it was added > here: > > https://github.com/directus/directus/blob/5e698a77237f0ce80e4d69c7404ba21a34053bab/api/src/database/migrations/20210903A-add-auth-provider.ts#L18-L20 > > And it seems this was added in v9.0.0-rc95. > > I think this migration ran but it failed in another place and the data > column was removed anyways. > Although, if some migration fails, the whole process should rollback. > > Do you, by any chance, have changed this table manually? > > As a workaround you can simple add a data JSON column to > directus_sessions in order to upgrade. > > — > Reply to this email directly, view it on GitHub > <#10669 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ABDLKFHL2N5OXNHZJZWNWLDUSR6ZFANCNFSM5KWNVHQA> > . > Triage notifications on the go with GitHub Mobile for iOS > <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> > or Android > <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. > > You are receiving this because you were mentioned.Message ID: > ***@***.***> >

[rijkvanzanten]

I'm thinking that applying my schema snapshot before doing the migrations
somehow removed the data column (or the entire sessions table perhaps?).

Very odd!

I'll close this for now, as it seems to be a specific issue in your particular database, rather than a migration problem in this release. Happy to keep discussing / debugging though 🙂

[keesvanbemmel]

@rijkvanzanten still think something is wrong, albeit not with the migrations of this release. For some reason the snapshot that I have will delete directus_sessions.data see screenshot:

I was under the impression that a snapshot never touches the directus_ collections? There is also no reference to directus_sessions in my schema.yaml.

Any thoughts?

MERRY CHRISTMAS ALL!!!

[rijkvanzanten]

Not sure! That being said, the snapshot should never include system collections/fields, so I something must be afoot there..

MERRY CHRISTMAS ALL!!!

You too! 🎄 🎅🏻

[keesvanbemmel]

When applying the snapshot, this is what is returned for the data field in get-snapshot:

{
    collection: 'directus_sessions',
    field: 'data',
    type: 'json',
    schema: {
      name: 'data',
      table: 'directus_sessions',
      data_type: 'json',
      default_value: null,
      generation_expression: null,
      max_length: null,
      numeric_precision: null,
      numeric_scale: null,
      is_generated: false,
      is_nullable: true,
      is_unique: false,
      is_primary_key: false,
      has_auto_increment: false,
      comment: null,
      schema: 'public',
      foreign_key_schema: null,
      foreign_key_table: null,
      foreign_key_column: null
    },
    meta: null
  }

The fact that meta === null will also make sure this field is not a system field (as system: true is not in this meta object).

Will try to figure out why meta === null, but not sure if I'll be able to tackle that one.

[keesvanbemmel]

Found it. Pretty sure it has something to do with removing the "data" field from sessions.yaml in this commit:
dbf35a1

Not sure why it has been removed there though. Seems deliberate as it's also in the add shares migration.

@rijkvanzanten can't really assess the impact of this data field missing from that yaml file, but I thought I'd tag you here so you could assess that :)

Something to consider: our container applies schema snapshots upon startup, so the database is always up to date with the code. The order of operation is:

• schema apply (with the --yes flag, so changes aren't logged, might be smart!), this effectively removes the data column, since it's not present anymore in sessions.yaml
• Directus bootstrap, which will run migrations. Will try to drop the data column, which fails
• start directus

The first step applies our schema, but due to the error above, it completely removed the data column from our directus_sessions table. If I put it back manually, our schema with the error above will remove it again. Luckily this happened on staging :) But perhaps we can add multiple measures to make sure a schema apply does not touch any directus_ system collections or fields whatsoever, ever?

[Prochy20]

I got this error:

create table `directus_shares` (`id` char(36), `name` varchar(255), `collection` varchar(64), `item` varchar(255), `role` char(36), `password` varchar(255), `user_created` char(36), `date_created` timestamp default CURRENT_TIMESTAMP, `date_start` timestamp, `date_end` timestamp, `times_used` int default '0', `max_uses` int) - ER_INVALID_DEFAULT: Invalid default value for 'date_start

in the very same situation

[keesvanbemmel]

I got this error:

create table `directus_shares` (`id` char(36), `name` varchar(255), `collection` varchar(64), `item` varchar(255), `role` char(36), `password` varchar(255), `user_created` char(36), `date_created` timestamp default CURRENT_TIMESTAMP, `date_start` timestamp, `date_end` timestamp, `times_used` int default '0', `max_uses` int) - ER_INVALID_DEFAULT: Invalid default value for 'date_start

in the very same situation

That seems like a completely different issue. I'd create a new issue out of it if I were you. be sure to describe the exact environment (DB version etc) because it seems like a DB specific thing.

Cheers!

[Prochy20]

I got this error:

create table `directus_shares` (`id` char(36), `name` varchar(255), `collection` varchar(64), `item` varchar(255), `role` char(36), `password` varchar(255), `user_created` char(36), `date_created` timestamp default CURRENT_TIMESTAMP, `date_start` timestamp, `date_end` timestamp, `times_used` int default '0', `max_uses` int) - ER_INVALID_DEFAULT: Invalid default value for 'date_start

in the very same situation

That seems like a completely different issue. I'd create a new issue out of it if I were you. be sure to describe the exact environment (DB version etc) because it seems like a DB specific thing.

Cheers!

Thanks. I have opened an issue: #10693

[rijkvanzanten]

The data column was dropped completely from the latest release @keesvanbemmel:

directus/api/src/database/migrations/20211211A-add-shares.ts

Lines 22 to 24 in 98cf134

	await knex.schema.alterTable('directus_sessions', (table) => {
	table.dropColumn('data');
	});

I'm a little confused why it shows up in your schema.yaml. Are you applying the schema from / to the same version? Could it be the snapshot was created on a different version of the platform?

Lets move this to a Q&A discussion, and continue there 🙂