search

vapor / fluent

Vapor ORM (queries, models, and relations) for NoSQL and SQL databases
https://docs.vapor.codes/4.0/fluent/overview/
MIT License
1.32k stars 172 forks source link

MySQL error trying to delete a constraint #722

Closed bottlehall closed 1 year ago

bottlehall commented 3 years ago

I've got a Migration that adds a foreign key field to a Model:

struct UpdateUnitTable1: Migration {
    func prepare(on database: Database) -> EventLoopFuture<Void> {
        database.schema("Unit")
            .field("createdByUserId", .uuid)
            .foreignKey("createdByUserId", references: "User", "id", name: "created_user_id")
            .update()
    }

    func revert(on database: Database) -> EventLoopFuture<Void> {
        database.schema("Unit")
            .deleteConstraint(name: "created_user_id")
            .deleteField("createdByUserId")
            .update()
    }
}

The prepare works, but revert errors with:

caught error: "previousError(MySQL error: Server error: Cannot drop column 'createdByUserId': needed in a foreign key constraint '7b75a8aae1173c6c1f04cb626cadff7a5c321cf0')"

Thinking it might be that it can't be done in a single update to the schema, I tried:

    func revert(on database: Database) -> EventLoopFuture<Void> {
        database.schema("Unit")
            .deleteConstraint(name: "created_user_id")
            .update()
            .flatMap { _ in
            return database.schema("Unit")
                .deleteField("createdByUserId")
                .update()
            }
    }

However, this just gives a different error:

caught error: "previousError(MySQL error: Server error: Cannot drop index '7b75a8aae1173c6c1f04cb626cadff7a5c321cf0': needed in a foreign key constraint)"

Reverting to the original, single update and reversing the order of deleting the field and constraint doesn't help either.

Looking through the fluent source code, it looks like it doesn't take account of the existence of the index that is created with the constraint so doesn't try to. delete it.

0xTim commented 3 years ago

It looks like on MySQL it's creating an index as well as the constraint which needs to be deleted in the revert as well. You'll probably need to drop down to raw SQL for this

bottlehall commented 3 years ago

Thank you, @0xTim. I have done some more investigation.

If I use MySQL client on the table created by the prepare above and try to delete the index manually, then I get the same error about it being needed in the foreign key constraint. However, if I use:

ALTER TABLE Unit DROP FOREIGN KEY 7b75a8aae1173c6c1f04cb626cadff7a5c321cf0

Then it works and deletes the index as well. So, the raw query will have to delete the foreign key rather than the index, which suggests that there is a problem in the deleteConstraint approach. Looking at the MySQL reference, it says you can't delete a foreign key using the generic constraint syntax, you need to use the specific syntax of deleting the foreign key as above. However, I've just tried:

ALTER TABLE Unit DROP CONSTRAINT 7b75a8aae1173c6c1f04cb626cadff7a5c321cf0

And this deletes the foreign key but leaves the index of the same name untouched.

Postgres does let you delete a foreign key via the constraint syntax.

I think there is a (second) issue that although I have attempted to name the constraint in the prepare, this isn't showing up in MySQL - the foreign key/index name is auto-generated as above. If we can fix the missing name then it is fairly trivial to do the raw SQL to delete the foreign key manually using the statement above, but if not then it will take an initial raw SQL query to identify auto-generated name and then use this in the raw query to delete the foreign key.

kevinzhow commented 2 years ago

Same issue here

kevinzhow commented 2 years ago

After some research, I sloved this problem by writing raw sql

import Foundation
import Fluent
import Vapor
import SQLKit

struct CreateArticleHeadCommit: AsyncMigration {
    func prepare(on database: Database) async throws {
        try await database.schema(ArticleEntry.schema)
            .field("head_commit_id", .uuid)
            .update()

        let sqlDB = database as! SQLDatabase

        let _ = try await sqlDB.raw(
"""
ALTER TABLE \(ArticleEntry.schema)
ADD CONSTRAINT FK_head_commit_id
FOREIGN KEY (head_commit_id)
REFERENCES \(ArticleCommitEntry.schema)(id);
"""
        ).all()

    }

    func revert(on database: Database) async throws {
        let sqlDB = database as! SQLDatabase

        let _ = try await sqlDB.raw(
"""
ALTER TABLE \(ArticleEntry.schema)
DROP CONSTRAINT FK_head_commit_id;
"""
        ).all()

        try await database.schema(ArticleEntry.schema)
            .deleteField("head_commit_id").update()
    }
}
kevinzhow commented 2 years ago

Here is my new approach @0xTim 

struct CreateArticleHeadCommit: AsyncMigration {
    func prepare(on database: Database) async throws {
        try await database.schema(ArticleEntry.schema)
            .field("head_commit_id", .uuid)
            .foreignKey("head_commit_id",
                        references: ArticleCommitEntry.schema,
                        "id",
                        onDelete: .setNull,
                        name: "created_head_commit_id")
            .update()

    }

    func revert(on database: Database) async throws {
        try await database.schema(ArticleEntry.schema)
                    .deleteConstraint(name: "created_head_commit_id")
                    .update()

        try await database.schema(ArticleEntry.schema).deleteField("head_commit_id")
            .update()
    }
}

This approach result in the same as this issue

MySQL error: Server error: Cannot drop index '2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14': needed in a foreign key constraint
Swift/ErrorType.swift:200: Fatal error: Error raised at top level: MySQL error: Server error: Cannot drop index '2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14': needed in a foreign key constraint

to debug this problem, I traced the mysql log

migration

12 Connect  vapor_username@172.25.0.1 on vapor_database using TCP/IP
            12 Prepare  CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
            12 Execute  CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
            12 Close stmt   
            12 Prepare  SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN (? , ? , ?)
            12 Execute  SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN ('CreateArticleCommitEntry' , 'CreateArticleHeadCommit' , 'CreateArticleEntry')
            12 Close stmt   
            13 Connect  vapor_username@172.25.0.1 on vapor_database using TCP/IP
            13 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
            13 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
            13 Close stmt   
220310  6:00:47     14 Connect  vapor_username@172.25.0.1 on vapor_database using TCP/IP
            14 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
            14 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
            14 Close stmt   
            14 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
            14 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
            14 Close stmt   
            14 Prepare  CREATE TABLE `article_entry`(`id` VARBINARY(16) PRIMARY KEY, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6))
            14 Execute  CREATE TABLE `article_entry`(`id` VARBINARY(16) PRIMARY KEY, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6))
            14 Close stmt   
            14 Prepare  INSERT INTO `_fluent_migrations` (`name`, `id`, `created_at`, `batch`, `updated_at`) VALUES (?, ?, ?, ?, ?)
            14 Execute  INSERT INTO `_fluent_migrations` (`name`, `id`, `created_at`, `batch`, `updated_at`) VALUES ('App.CreateArticleEntry', '{7
    \?L\?\?\?$x', TIMESTAMP'2022-03-10 06:00:47.954614', 1, TIMESTAMP'2022-03-10 06:00:47.954614')
            14 Close stmt   
            14 Prepare  CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
            14 Execute  CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
            14 Close stmt   
            14 Prepare  INSERT INTO `_fluent_enums` (`id`, `name`, `case`) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)
            14 Execute  INSERT INTO `_fluent_enums` (`id`, `name`, `case`) VALUES ('\?M\?\?-!E\?\ZŶ\?7\?', 'article_type', 'novel'), ('\?\?\?S\?Hx\?\"5;    \?\?\?', 'article_type', 'news'), (':N\?o\?K\?6_\?SXT', 'article_type', 'manga')
            14 Close stmt   
            14 Prepare  DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ? AND 1 = 0
            14 Execute  DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type' AND 1 = 0
            14 Close stmt   
            14 Prepare  SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ?
            14 Execute  SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type'
            14 Close stmt   
            14 Prepare  CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
            14 Execute  CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
            14 Close stmt   
            14 Prepare  DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ? AND 1 = 0
            14 Execute  DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type' AND 1 = 0
            14 Close stmt   
            14 Prepare  SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ?
            14 Execute  SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type'
            14 Close stmt   
            14 Prepare  CREATE TABLE `article_commit_entry`(`id` VARBINARY(16) PRIMARY KEY, `article_id` VARBINARY(16) NOT NULL, `author` JSON NOT NULL, `title` JSON NOT NULL, `article_type` ENUM('manga', 'news', 'novel') NOT NULL, `content` VARCHAR(255) NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6), CONSTRAINT `0c5b0271fa7fa8d07357c01c906eeb90abce93b5` FOREIGN KEY (`article_id`) REFERENCES `article_entry` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
            14 Execute  CREATE TABLE `article_commit_entry`(`id` VARBINARY(16) PRIMARY KEY, `article_id` VARBINARY(16) NOT NULL, `author` JSON NOT NULL, `title` JSON NOT NULL, `article_type` ENUM('manga', 'news', 'novel') NOT NULL, `content` VARCHAR(255) NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6), CONSTRAINT `0c5b0271fa7fa8d07357c01c906eeb90abce93b5` FOREIGN KEY (`article_id`) REFERENCES `article_entry` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
            14 Close stmt   
            14 Prepare  INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (?, ?, ?, ?, ?)
            14 Execute  INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (1, TIMESTAMP'2022-03-10 06:00:47.988511', 'җ\?\?\?\?Hn\?Ε.\?\?\?\?', 'App.CreateArticleCommitEntry', TIMESTAMP'2022-03-10 06:00:47.988511')
            14 Close stmt   
            14 Prepare  ALTER TABLE `article_entry` ADD `head_commit_id` VARBINARY(16) , ADD CONSTRAINT `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14` FOREIGN KEY (`head_commit_id`) REFERENCES `article_commit_entry` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
            14 Execute  ALTER TABLE `article_entry` ADD `head_commit_id` VARBINARY(16) , ADD CONSTRAINT `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14` FOREIGN KEY (`head_commit_id`) REFERENCES `article_commit_entry` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
220310  6:00:48     14 Close stmt   
            14 Prepare  INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (?, ?, ?, ?, ?)
            14 Execute  INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (1, TIMESTAMP'2022-03-10 06:00:48.003450', '\?/eXk\?G\?\?<\?\?o\?i', 'App.CreateArticleHeadCommit', TIMESTAMP'2022-03-10 06:00:48.003450')
            14 Close stmt   
            13 Quit 
            14 Quit 
            12 Quit

revert

220310  6:03:30     15 Connect  vapor_username@172.25.0.1 on vapor_database using TCP/IP
            15 Prepare  CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
            15 Execute  CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
            15 Close stmt   
            15 Prepare  SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN (? , ? , ?)
            15 Execute  SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN ('CreateArticleCommitEntry' , 'CreateArticleHeadCommit' , 'CreateArticleEntry')
            15 Close stmt   
            16 Connect  vapor_username@172.25.0.1 on vapor_database using TCP/IP
            16 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
            16 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
            16 Close stmt   
            16 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = ?
            16 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = 1
            16 Close stmt   
            17 Connect  vapor_username@172.25.0.1 on vapor_database using TCP/IP
            17 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
            17 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
            17 Close stmt   
            17 Prepare  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = ?
            17 Execute  SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = 1
            17 Close stmt   
            17 Prepare  ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
            17 Execute  ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
            17 Close stmt   
            17 Quit 
            15 Quit 
            16 Quit
kevinzhow commented 2 years ago

@0xTim just wrote a new unit test to debug deleteConstraint

final class DatabaseSQLTests: XCTestCase {
    func testDatabaseSQLTests() throws {
        let db = DummyDatabaseForTestSQLSerializer()
        try db.schema(ArticleEntry.schema)
                    .deleteConstraint(name: "created_head_commit_id")
                    .update().wait()

        print(db.sqlSerializers)
    }
}

the sql looks normal

[SQLKit.SQLSerializer(sql: "ALTER TABLE \"article_entry\" DROP CONSTRAINT \"created_head_commit_id\"", binds: [], database: AppTests.DummyDatabaseForTestSQLSerializer)]

so why mysql recieved these sql?

Prepare ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
            17 Execute  ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`

I'll keep digging

kevinzhow commented 2 years ago

According to MySQL's reference Conditions and Restrictions

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. 
In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. 
Such an index is created on the referencing table automatically if it does not exist. 
This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

So yes, adding foregin key reference will also produce an index.

But when we want to delete Foregin Key Reference field, there is no need to delete index first

Seems Fluent tries to delete the index first.

kevinzhow commented 2 years ago

I made a breakpoint in FluentMySQLDatabase#48

image

Looks like it convert deleteConstraint into 

ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
kevinzhow commented 2 years ago

Okey, I find out whats the problem

https://github.com/vapor/fluent-kit/blob/9d47c328bf83999968c12a3bc94ead1d706ad4a9/Sources/FluentSQL/SQLSchemaConverter.swift#L280

    public func serialize(to serializer: inout SQLSerializer) {
        if serializer.dialect.name == "mysql" {
            serializer.write("KEY ")
        } else {
            serializer.write("CONSTRAINT ")
        }
        let normalizedName = serializer.dialect.normalizeSQLConstraint(identifier: name)
        normalizedName.serialize(to: &serializer)
    }

Here we can find out that at runtime, SQLDropConstraint was converted to KEY instead of CONSTRAINT

kevinzhow commented 2 years ago

@0xTim just wrote a new unit test to debug deleteConstraint

final class DatabaseSQLTests: XCTestCase {
    func testDatabaseSQLTests() throws {
        let db = DummyDatabaseForTestSQLSerializer()
        try db.schema(ArticleEntry.schema)
                    .deleteConstraint(name: "created_head_commit_id")
                    .update().wait()

        print(db.sqlSerializers)
    }
}

the sql looks normal

[SQLKit.SQLSerializer(sql: "ALTER TABLE \"article_entry\" DROP CONSTRAINT \"created_head_commit_id\"", binds: [], database: AppTests.DummyDatabaseForTestSQLSerializer)]

so why mysql recieved these sql?

Prepare   ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
          17 Execute  ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`

I'll keep digging

So here is the truth, when db is mysql, it produces KEY which failed the deleteConstraint

image

So why we are using KEY instead of CONSTRAINT?

@bottlehall

0xTim commented 2 years ago

@gwynne any idea?

kevinzhow commented 2 years ago

I leave some new invesgation on PR https://github.com/vapor/fluent-kit/pull/492#issuecomment-1063959265

@gwynne I would like to know your oppion.

gwynne commented 1 year ago

This should have been fixed by vapor/fluent-kit#522

gwynne commented 1 year ago

This should now be actually fixed, see the release notes for details on how to update your code.

bottlehall commented 1 year ago

Hi

Sadly, I can’t reproduce the fix with my minimal example previous submitted. I’ve tried using:

            .field("imagesUploadedByUserId", .uuid, .references("User", "id"))
            .foreignKey("idkId", references: "User", "id", name: "waddyaKnow")

To create the foreign key fields. And:

            .deleteForeignKey(name:"waddyaKnow”)
    .deleteConstraint(name:”waddyaKnow”)

Attempting to delete the field.

In all cases, it reports that it is failing to delete the column/key using the normalised constraint/key name, such as:

0c2da0c13abec57e8c4ccd235ae7b4d070b2e686

I’ve also uncovered a similar issue with multiple-index ‘unique’ indices, which I have logged.

N

On 30 Jul 2023, at 03:11, Gwynne Raskind @.***> wrote:

Closed #722 https://github.com/vapor/fluent/issues/722 as completed.

— Reply to this email directly, view it on GitHub https://github.com/vapor/fluent/issues/722#event-9955893869, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABK5DRI5JAR3YND4EE5XBPDXSW7EHANCNFSM42TNSK4A. You are receiving this because you were mentioned.