Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Cleanup invalid relations row in foreign key migrations #6013

Closed
doodlemania2 opened this issue Jun 2, 2021 · 12 comments · Fixed by #6040
Closed

Cleanup invalid relations row in foreign key migrations #6013

doodlemania2 opened this issue Jun 2, 2021 · 12 comments · Fixed by #6040
Labels
🍰 Improvement
Milestone
v9.0.0-rc.72

Comments

@doodlemania2
Copy link

doodlemania2 commented Jun 2, 2021 •
edited by rijkvanzanten

edit by @rijkvanzanten: The migration is looping over directus_relations in order to setup the foreign key constraints. However, if for whatever reason you have a relationship configured in directus_relations, that doesn't actually map to existing tables in the database, the migration will fail

20:27:57 ✨ Initializing bootstrap...
2021-06-02T20:27:57.804348023Z 20:27:57 ✨ Database already initialized, skipping install
2021-06-02T20:27:57.805363229Z 20:27:57 ✨ Running migrations...
2021-06-02T20:27:57.814003478Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T20:28:02.197893647Z Error: ER_NO_SUCH_TABLE: Table 'directus.notes_sku' doesn't exist
2021-06-02T20:28:02.197957948Z at Query.Sequence._packetToError (/directus/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
2021-06-02T20:28:02.197965148Z at Query.ErrorPacket (/directus/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
2021-06-02T20:28:02.197969348Z at Protocol._parsePacket (/directus/node_modules/mysql/lib/protocol/Protocol.js:291:23)
2021-06-02T20:28:02.197973448Z at Parser._parsePacket (/directus/node_modules/mysql/lib/protocol/Parser.js:433:10)
2021-06-02T20:28:02.197977448Z at Parser.write (/directus/node_modules/mysql/lib/protocol/Parser.js:43:10)
2021-06-02T20:28:02.197981348Z at Protocol.write (/directus/node_modules/mysql/lib/protocol/Protocol.js:38:16)
2021-06-02T20:28:02.197985148Z at Socket.<anonymous> (/directus/node_modules/mysql/lib/Connection.js:88:28)
2021-06-02T20:28:02.197989748Z at Socket.<anonymous> (/directus/node_modules/mysql/lib/Connection.js:526:10)
2021-06-02T20:28:02.198009848Z at Socket.emit (events.js:376:20)
2021-06-02T20:28:02.198013348Z at addChunk (internal/streams/readable.js:309:12)
2021-06-02T20:28:02.198017048Z --------------------
2021-06-02T20:28:02.198021048Z at Protocol._enqueue (/directus/node_modules/mysql/lib/protocol/Protocol.js:144:48)
2021-06-02T20:28:02.198024748Z at Connection.query (/directus/node_modules/mysql/lib/Connection.js:198:25)
2021-06-02T20:28:02.198028448Z at /directus/node_modules/knex/lib/dialects/mysql/index.js:126:18
2021-06-02T20:28:02.198032448Z at new Promise (<anonymous>)
2021-06-02T20:28:02.198036348Z at Client_MySQL._query (/directus/node_modules/knex/lib/dialects/mysql/index.js:120:12)
2021-06-02T20:28:02.198040148Z at executeQuery (/directus/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
2021-06-02T20:28:02.198043948Z at Client_MySQL.query (/directus/node_modules/knex/lib/client.js:134:12)
2021-06-02T20:28:02.198047648Z at Runner.query (/directus/node_modules/knex/lib/execution/runner.js:130:36)
2021-06-02T20:28:02.198051348Z at ensureConnectionCallback (/directus/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
2021-06-02T20:28:02.198055048Z at Runner.ensureConnection (/directus/node_modules/knex/lib/execution/runner.js:272:20) {
2021-06-02T20:28:02.198058848Z code: 'ER_NO_SUCH_TABLE',
2021-06-02T20:28:02.198062448Z errno: 1146,
2021-06-02T20:28:02.198066048Z sqlMessage: "Table 'directus.notes_sku' doesn't exist",
2021-06-02T20:28:02.198070048Z sqlState: '42S02',
2021-06-02T20:28:02.198135849Z index: 0,
2021-06-02T20:28:02.198142349Z sql: "SHOW KEYS FROM `notes_sku` WHERE Key_name = 'PRIMARY'"
2021-06-02T20:28:02.198146149Z }
2021-06-02T20:28:06.941324950Z 20:28:06 ✨ Initializing bootstrap...
2021-06-02T20:28:07.180753581Z 20:28:07 ✨ Database already initialized, skipping install
2021-06-02T20:28:07.181792094Z 20:28:07 ✨ Running migrations...
2021-06-02T20:28:07.190021902Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T20:28:07.229793222Z Error: ER_NO_SUCH_TABLE: Table 'directus.notes_sku' doesn't exist
2021-06-02T20:28:07.229820322Z at Query.Sequence._packetToError (/directus/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
2021-06-02T20:28:07.229826823Z at Query.ErrorPacket (/directus/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
2021-06-02T20:28:07.229831323Z at Protocol._parsePacket (/directus/node_modules/mysql/lib/protocol/Protocol.js:291:23)
2021-06-02T20:28:07.229835423Z at Parser._parsePacket (/directus/node_modules/mysql/lib/protocol/Parser.js:433:10)
2021-06-02T20:28:07.229839723Z at Parser.write (/directus/node_modules/mysql/lib/protocol/Parser.js:43:10)
2021-06-02T20:28:07.229853423Z at Protocol.write (/directus/node_modules/mysql/lib/protocol/Protocol.js:38:16)
2021-06-02T20:28:07.229858123Z at Socket.<anonymous> (/directus/node_modules/mysql/lib/Connection.js:88:28)
2021-06-02T20:28:07.229863023Z at Socket.<anonymous> (/directus/node_modules/mysql/lib/Connection.js:526:10)
2021-06-02T20:28:07.229867423Z at Socket.emit (events.js:376:20)
2021-06-02T20:28:07.229871623Z at addChunk (internal/streams/readable.js:309:12)
2021-06-02T20:28:07.229875623Z --------------------
2021-06-02T20:28:07.229879623Z at Protocol._enqueue (/directus/node_modules/mysql/lib/protocol/Protocol.js:144:48)
2021-06-02T20:28:07.229883623Z at Connection.query (/directus/node_modules/mysql/lib/Connection.js:198:25)
2021-06-02T20:28:07.229887623Z at /directus/node_modules/knex/lib/dialects/mysql/index.js:126:18
2021-06-02T20:28:07.229891523Z at new Promise (<anonymous>)
2021-06-02T20:28:07.229895723Z at Client_MySQL._query (/directus/node_modules/knex/lib/dialects/mysql/index.js:120:12)
2021-06-02T20:28:07.229900224Z at executeQuery (/directus/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
2021-06-02T20:28:07.229904324Z at Client_MySQL.query (/directus/node_modules/knex/lib/client.js:134:12)
2021-06-02T20:28:07.229908124Z at Runner.query (/directus/node_modules/knex/lib/execution/runner.js:130:36)
2021-06-02T20:28:07.229912024Z at ensureConnectionCallback (/directus/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
2021-06-02T20:28:07.229916024Z at Runner.ensureConnection (/directus/node_modules/knex/lib/execution/runner.js:272:20) {
2021-06-02T20:28:07.229933324Z code: 'ER_NO_SUCH_TABLE',
2021-06-02T20:28:07.229937424Z errno: 1146,
2021-06-02T20:28:07.229940924Z sqlMessage: "Table 'directus.notes_sku' doesn't exist",
2021-06-02T20:28:07.229944624Z sqlState: '42S02',
2021-06-02T20:28:07.229948024Z index: 0,
2021-06-02T20:28:07.229951524Z sql: "SHOW KEYS FROM `notes_sku` WHERE Key_name = 'PRIMARY'"
2021-06-02T20:28:07.229955124Z }
2021-06-02T20:28:11.675260262Z 20:28:11 ✨ Initializing bootstrap...
2021-06-02T20:28:11.896724702Z 20:28:11 ✨ Database already initialized, skipping install
2021-06-02T20:28:11.897799208Z 20:28:11 ✨ Running migrations...
2021-06-02T20:28:11.906171655Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T20:28:11.934781215Z Error: ER_NO_SUCH_TABLE: Table 'directus.notes_sku' doesn't exist
2021-06-02T20:28:11.934824215Z at Query.Sequence._packetToError (/directus/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
2021-06-02T20:28:11.934875316Z at Query.ErrorPacket (/directus/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
2021-06-02T20:28:11.934900916Z at Protocol._parsePacket (/directus/node_modules/mysql/lib/protocol/Protocol.js:291:23)
2021-06-02T20:28:11.934904916Z at Parser._parsePacket (/directus/node_modules/mysql/lib/protocol/Parser.js:433:10)
2021-06-02T20:28:11.934908816Z at Parser.write (/directus/node_modules/mysql/lib/protocol/Parser.js:43:10)
2021-06-02T20:28:11.934912816Z at Protocol.write (/directus/node_modules/mysql/lib/protocol/Protocol.js:38:16)
2021-06-02T20:28:11.934916416Z at Socket.<anonymous> (/directus/node_modules/mysql/lib/Connection.js:88:28)
2021-06-02T20:28:11.934920716Z at Socket.<anonymous> (/directus/node_modules/mysql/lib/Connection.js:526:10)
2021-06-02T20:28:11.934924616Z at Socket.emit (events.js:376:20)
2021-06-02T20:28:11.934928316Z at addChunk (internal/streams/readable.js:309:12)
2021-06-02T20:28:11.934932116Z --------------------
2021-06-02T20:28:11.934935816Z at Protocol._enqueue (/directus/node_modules/mysql/lib/protocol/Protocol.js:144:48)
2021-06-02T20:28:11.934939816Z at Connection.query (/directus/node_modules/mysql/lib/Connection.js:198:25)
2021-06-02T20:28:11.934943616Z at /directus/node_modules/knex/lib/dialects/mysql/index.js:126:18
2021-06-02T20:28:11.934947416Z at new Promise (<anonymous>)
2021-06-02T20:28:11.934951216Z at Client_MySQL._query (/directus/node_modules/knex/lib/dialects/mysql/index.js:120:12)
2021-06-02T20:28:11.934954916Z at executeQuery (/directus/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
2021-06-02T20:28:11.934958616Z at Client_MySQL.query (/directus/node_modules/knex/lib/client.js:134:12)
2021-06-02T20:28:11.934962316Z at Runner.query (/directus/node_modules/knex/lib/execution/runner.js:130:36)
2021-06-02T20:28:11.934979916Z at ensureConnectionCallback (/directus/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
2021-06-02T20:28:11.934984016Z at Runner.ensureConnection (/directus/node_modules/knex/lib/execution/runner.js:272:20) {
2021-06-02T20:28:11.934987416Z code: 'ER_NO_SUCH_TABLE',
2021-06-02T20:28:11.934990716Z errno: 1146,
2021-06-02T20:28:11.934993916Z sqlMessage: "Table 'directus.notes_sku' doesn't exist",
2021-06-02T20:28:11.934997416Z sqlState: '42S02',
2021-06-02T20:28:11.935000716Z index: 0,
2021-06-02T20:28:11.935003916Z sql: "SHOW KEYS FROM `notes_sku` WHERE Key_name = 'PRIMARY'"
2021-06-02T20:28:11.935007416Z }

2021-06-02T20:28:13.204Z INFO - Stopping site ____ because it failed during startup.
@doodlemania2
Copy link
Author

I'm not sure where it's getting directus.notes_sku ... we have a table for nots and we have a table for sku, but they don't interact anywhere.

@rijkvanzanten
Copy link
Member

rijkvanzanten commented Jun 2, 2021 •
edited

It's trying to add foreign key constraints for your existing relations in Directus. Could it be that you have a stray row in directus_migrations directus_relations for a collection that doesn't exist?

@doodlemania2
Copy link
Author

directus_migrations shows this data:
20201028A,Remove Collection Foreign Keys,2021-01-31 22:48:04
20201029A,Remove System Relations,2021-01-31 22:48:04
20201029B,Remove System Collections,2021-01-31 22:48:05
20201029C,Remove System Fields,2021-01-31 22:48:05
20201105A,Add Cascade System Relations,2021-01-31 22:48:19
20201105B,Change Webhook URL Type,2021-01-31 22:48:19
20210225A,Add Relations Sort Field,2021-03-01 16:56:27
20210304A,Remove Locked Fields,2021-03-07 21:22:52
20210312A,Webhooks Collections Text,2021-03-13 21:17:09
20210331A,Add Refresh Interval,2021-04-11 01:40:33
20210415A,Make Filesize Nullable,2021-04-16 01:12:50
20210416A,Add Collections Accountability,2021-04-23 15:30:34
20210422A,Remove Files Interface,2021-04-23 15:30:34
20210506A,Rename Interfaces,2021-05-10 17:54:06
20210510A,Restructure Relations,2021-05-15 20:25:00

@rijkvanzanten
Copy link
Member

My bad, meant directus_relations

@doodlemania2
Copy link
Author

I do see some data regarding notes and sku in directus_relations regarding notes and sku but that doesn't strike a bell in my brain. notes and skus don't really interact. for our setup, it's more notes and features

@rijkvanzanten
Copy link
Member

@doodlemania2 Could you confirm the rows for any non-existing tables in many_collection and one_collection? Sounds like some old relational references didn't get cleaned up when deleting previous collections 🤔

@doodlemania2
Copy link
Author

doodlemania2 commented Jun 2, 2021 •
edited

perhaps this record:
59,notes,sku,sku,,,,,,nullify,id,id
I can send you a dump of that whole table (or my schema) privately if you like?
both notes and sku tables DO exist, but I don't think they should have a relationship.

@rijkvanzanten rijkvanzanten changed the title Failure bumping from .68 to .70 Cleanup invalid relations row in foreign key migrations Jun 2, 2021
@doodlemania2
Copy link
Author

This yielded LOTS of more errors :( I guess we ended up in a bad state. Any way to roll this back, clean, and then move forward?

021-06-02T21:22:31.151626106Z 21:22:31 ✨ Database already initialized, skipping install
2021-06-02T21:22:31.152678112Z 21:22:31 ✨ Running migrations...
2021-06-02T21:22:31.162312768Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T21:22:43.253995823Z 21:22:43 🚨 region.id contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.
2021-06-02T21:22:43.255013263Z 21:22:43 🚨 Items with illegal foreign keys: 1, 2, 3, 5, 8, 10, 11, 15, 16, 18, 37, 38, 39, 46, 47, 48, 49, 51, 53, 59, 63, 64, 65
2021-06-02T21:22:43.256185748Z Migration aborted
2021-06-02T21:22:47.608462341Z 21:22:47 ✨ Initializing bootstrap...
2021-06-02T21:22:47.812115114Z 21:22:47 ✨ Database already initialized, skipping install
2021-06-02T21:22:47.812984219Z 21:22:47 ✨ Running migrations...
2021-06-02T21:22:47.822151372Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T21:22:47.867321333Z 21:22:47 🚨 region.id contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.
2021-06-02T21:22:47.868479439Z 21:22:47 🚨 Items with illegal foreign keys: 1, 2, 3, 5, 8, 10, 11, 15, 16, 18, 37, 38, 39, 46, 47, 48, 49, 51, 53, 59, 63, 64, 65
2021-06-02T21:22:47.869341344Z Migration aborted
2021-06-02T21:22:52.071501768Z 21:22:52 ✨ Initializing bootstrap...
2021-06-02T21:22:52.265740880Z 21:22:52 ✨ Database already initialized, skipping install
2021-06-02T21:22:52.266623084Z 21:22:52 ✨ Running migrations...
2021-06-02T21:22:52.275485421Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T21:22:52.316560392Z 21:22:52 🚨 region.id contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.
2021-06-02T21:22:52.317349596Z 21:22:52 🚨 Items with illegal foreign keys: 1, 2, 3, 5, 8, 10, 11, 15, 16, 18, 37, 38, 39, 46, 47, 48, 49, 51, 53, 59, 63, 64, 65
2021-06-02T21:22:52.317863098Z Migration aborted

@doodlemania2
Copy link
Author

Cleared the first batch, now getting these:

021-06-02T21:36:11.589144976Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T21:36:14.948868067Z 21:36:14 🚨 features.id contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.
2021-06-02T21:36:14.955190204Z 21:36:14 🚨 Items with illegal foreign keys: 778, 779, 780, 781, 782, 783, 784, 785, 786, 4, 6, 7, 8, 9, 10, 11, 1149, 2389, 12, 13, 16, 17, 18, 19, 20 and 2146 others
2021-06-02T21:36:14.956108809Z Migration aborted
2021-06-02T21:36:21.373265650Z 21:36:21 ✨ Initializing bootstrap...
2021-06-02T21:36:21.614218548Z 21:36:21 ✨ Database already initialized, skipping install
2021-06-02T21:36:21.615192154Z 21:36:21 ✨ Running migrations...
2021-06-02T21:36:21.624049305Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T21:36:21.698133335Z 21:36:21 🚨 features.id contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.
2021-06-02T21:36:21.699290242Z 21:36:21 🚨 Items with illegal foreign keys: 778, 779, 780, 781, 782, 783, 784, 785, 786, 4, 6, 7, 8, 9, 10, 11, 1149, 2389, 12, 13, 16, 17, 18, 19, 20 and 2146 others
2021-06-02T21:36:21.700287847Z Migration aborted
2021-06-02T21:36:25.959126169Z 21:36:25 ✨ Initializing bootstrap...
2021-06-02T21:36:26.199655066Z 21:36:26 ✨ Database already initialized, skipping install
2021-06-02T21:36:26.200573271Z 21:36:26 ✨ Running migrations...
2021-06-02T21:36:26.211689436Z ✨ Applying Add Foreign Key Constraints...
2021-06-02T21:36:26.285610965Z 21:36:26 🚨 features.id contains illegal foreign keys which couldn't be set to NULL. Please fix these references and rerun this migration to complete the upgrade.
2021-06-02T21:36:26.286775072Z 21:36:26 🚨 Items with illegal foreign keys: 778, 779, 780, 781, 782, 783, 784, 785, 786, 4, 6, 7, 8, 9, 10, 11, 1149, 2389, 12, 13, 16, 17, 18, 19, 20 and 2146 others
2021-06-02T21:36:26.287595176Z Migration aborted

@doodlemania2
Copy link
Author

(discord seems to be messing up :( )

@doodlemania2
Copy link
Author

Gonna roll back to .68 and hopefully things will at least come back online while we tinker

@doodlemania2
Copy link
Author

okay - .68 did come back and things seem okay even though i've been removing a few rows from directus_relations. curious how we could clean up further to get to .70/.71?

rijkvanzanten added a commit that referenced this issue Jun 3, 2021
@rijkvanzanten
Handle illegal/corrupt relational rows better
e985143 
Fixes #6013
@rijkvanzanten rijkvanzanten mentioned this issue Jun 3, 2021
Merged
@rijkvanzanten rijkvanzanten added this to the v9.0.0-rc.72 milestone Jun 3, 2021
rijkvanzanten added a commit that referenced this issue Jun 3, 2021
@rijkvanzanten
Handle illegal/corrupt relational rows better (#6040)
1cdbc91 
Fixes #6013
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Feb 3, 2024
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
🍰 Improvement
Projects
None yet
Milestone
v9.0.0-rc.72
Development

Successfully merging a pull request may close this issue.

Handle illegal/corrupt relational rows better during foreign key migration directus/directus
2 participants
@doodlemania2 @rijkvanzanten