Unable to drop table which has circular dependency to other table

General
#1

Hi, we’re just starting to use CockroachDB and really like i so far. But today we discovered a problem we were unable to fix. We are having two tables (users and files) which have circular dependencies (but where one of them is nullable)

We tried to drop the table (we’re in active development and changes happen all the time) to migrate new changes and have been completely stopped in our tracks by the following error;

root@cockroachdb-public:26257/wasder> show tables;
table_name
±---------------+
files
sequelize_meta
users
(3 rows)

Time: 4.17725ms

root@cockroachdb-public:26257/wasder> drop table users, files cascade;
pq: invalid fk backreference table=files index=2: index in GC mutations list
root@cockroachdb-public:26257/wasder>

Locally we can delete the data directory and start from scratch, but this littel nine-node cluster of three GCP regions work a little differently :slight_smile:

Any suggestion on how to force a clean slate warmly welcome!

Cheers,
PS

#2

Hey Peter,

What version of CockroachDB are you running?

Thanks,
Matt

#3

Mi Matt, we’re running version 19.1, set up in accordance with the GKE Multi-Cluster deployment (https://www.cockroachlabs.com/docs/stable/orchestrate-cockroachdb-with-kubernetes-multi-cluster.html)

Cheers,
PS

#4

Thanks for the quick reply.

Just to confirm, v19.1.0? or a specific release of 19.1.

Can you provide some steps to reproduce this behavior? (i.e examples of your circular dependencies with the nullable one as well)

Thanks,
Matt

#5

Thanks the same!

Actually, I think right now that what we practically need is a way to force drop and wipe a database regardless of what it feels about it, if possible.

I have tried to get you the information needed, and we’ll see if it is enough.
We use node.js and Sequelize and the following files are used to define the relationship that borked the database;

‘use strict’

module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable(‘files’, {
id: {
type: Sequelize.STRING,
primaryKey: true,
allowNull: false,
defaultValue: Sequelize.UUIDV4,
},
url: {
type: Sequelize.STRING(1024),
allowNull: false,
},
file_path: {
type: Sequelize.STRING(1024),
allowNull: false,
},
is_public: {
type: Sequelize.BOOLEAN,
defaultValue: true,
},
created_at: {
type: Sequelize.DATE(6),
defaultValue: Sequelize.literal(‘CURRENT_TIMESTAMP’),
},
updated_at: {
type: Sequelize.DATE(6),
defaultValue: Sequelize.literal(‘CURRENT_TIMESTAMP’),
onUpdate: Sequelize.literal(‘CURRENT_TIMESTAMP’),
},
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable(‘files’)
},
}

‘use strict’

module.exports = {
up: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.createTable(‘users’, {
id: {
type: Sequelize.STRING,
primaryKey: true,
allowNull: false,
defaultValue: Sequelize.UUIDV4,
},
username: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
},
user_search: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
},
email: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
},
avatar_id: {
type: Sequelize.STRING,
references: {
model: ‘files’,
key: ‘id’,
},
onUpdate: ‘CASCADE’,
onDelete: ‘SET NULL’,
},
cover_id: {
type: Sequelize.STRING,
references: {
model: ‘files’,
key: ‘id’,
},
onUpdate: ‘CASCADE’,
onDelete: ‘SET NULL’,
},
bio: {
type: Sequelize.STRING(2048),
},
notifications_token: {
type: Sequelize.STRING,
},
metadata: {
type: Sequelize.JSON,
},
created_at: {
type: Sequelize.DATE(6),
defaultValue: Sequelize.literal(‘CURRENT_TIMESTAMP’),
},
updated_at: {
type: Sequelize.DATE(6),
defaultValue: Sequelize.literal(‘CURRENT_TIMESTAMP’),
onUpdate: Sequelize.literal(‘CURRENT_TIMESTAMP’),
},
}),
])
},
down: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.dropTable(‘users’),
])
},
}

‘use strict’

module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.addColumn(‘files’, ‘user_id’, Sequelize.STRING, {
after: ‘id’,
})

await queryInterface.addConstraint('files', ['user_id'], {
  type: 'foreign key',
  name: 'user_files',
  references: {
    table: 'users',
    field: 'id',
  },
  onDelete: 'cascade',
  onUpdate: 'cascade',
})

return true

},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeConstraint(‘files’, ‘user_files’)
await queryInterface.removeColumn(‘files’, ‘user_id’)

return true

},
}

The issue was that at the beginning this was not there

await queryInterface.removeConstraint(‘files’, ‘user_files’)
await queryInterface.removeColumn(‘files’, ‘user_id’)

4:04 PM

I guess this is the constraint making it hard to remove

await queryInterface.addConstraint(‘files’, [‘user_id’], {
type: ‘foreign key’,
name: ‘user_files’,
references: {
table: ‘users’,
field: ‘id’,
},
onDelete: ‘cascade’,
onUpdate: ‘cascade’,
})

Cheers,
PS

#6

What about dropping the database?

https://www.cockroachlabs.com/docs/stable/drop-database.html

#7

Yes, good point. I didn’t mention that in detail, but we get the same error and it was actually the beginning of our problem, when we started trying to understand what was happening;

drop database wasder;
pq: rejected: DROP DATABASE on non-empty database without explicit CASCADE (sql_safe_updates = true)

And to answer you other question, we’re running 19.1.3.

Cheers,
PS

#8

Hi Peter, Matt,

I have looked into this a bit further and this may be a bug in CockroachDB.
Note that the “simple” scenario of dropping cyclically referencing tables that were just created succeeds without error.

The problem seems specific to the case where a table has undergone multiple DDL, including adding and dropping indexes. If my understanding is corrrect, the error occurs if you attempt to drop tables referencing each other while an index on one of them is still in the process of being deleted (in the background) That’s what the “GC” reference is about.

I believe this is a bug and I would encourage either of you to file an issue with the full details:

  1. cockroachdb full version number
  2. SHOW CREATE for all the tables in that db
  3. a copy of the event log (can be copy-pasted from the admin UI) so we can see the exact sequence of DDL
  4. the result of BACKUP of the system.descriptors table, so we can check the particular data structure that is blocking the DROP.

I am not 100% sure but it’s possible that dropping the database will fail with the same error (Peter you can check this by forcing the DROP DATABASE after setting sql_safe_updates = false).

Thank you,