MySQL error trying to delete a constraint #722

bottlehall · 2021-04-08T18:29:45Z

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.

The text was updated successfully, but these errors were encountered:

0xTim · 2021-04-12T09:42:30Z

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 · 2021-04-13T18:10:58Z

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 · 2022-03-09T15:36:24Z

Same issue here

kevinzhow · 2022-03-09T17:01:48Z

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 · 2022-03-10T06:06:27Z

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	[email protected] 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	[email protected] 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	[email protected] 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	[email protected] 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	[email protected] 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	[email protected] 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 · 2022-03-10T06:26:46Z

@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 · 2022-03-10T07:06:10Z

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 · 2022-03-10T07:23:50Z

I made a breakpoint in FluentMySQLDatabase#48

Looks like it convert deleteConstraint into

ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`

kevinzhow · 2022-03-10T08:22:06Z

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 · 2022-03-10T08:26:01Z

@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

So why we are using KEY instead of CONSTRAINT?

@bottlehall

0xTim · 2022-03-10T10:39:40Z

@gwynne any idea?

kevinzhow · 2022-03-10T12:09:07Z

I leave some new invesgation on PR vapor/fluent-kit#492 (comment)

@gwynne I would like to know your oppion.

gwynne · 2023-07-28T15:46:59Z

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

gwynne · 2023-07-30T02:11:03Z

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

bottlehall · 2023-07-31T19:40:20Z

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 <#722> as completed. — Reply to this email directly, view it on GitHub <#722 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABK5DRI5JAR3YND4EE5XBPDXSW7EHANCNFSM42TNSK4A>. You are receiving this because you were mentioned.

bottlehall added the bug Something isn't working label Apr 8, 2021

This was referenced Mar 10, 2022

MySQL CONSTRAINT/KEY logic result in error when trying to delete a constraint vapor/fluent-kit#491

Closed

Fix mysql SQLDropConstraint CONSTRAINT serialize vapor/fluent-kit#492

Closed

gwynne mentioned this issue Jul 28, 2023

Fully work around MySQL 5.7's lack of the DROP CONSTRAINT statement vapor/fluent-kit#575

Merged

gwynne closed this as completed Jul 30, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL error trying to delete a constraint #722

MySQL error trying to delete a constraint #722

bottlehall commented Apr 8, 2021

0xTim commented Apr 12, 2021

bottlehall commented Apr 13, 2021 •

edited

Loading

kevinzhow commented Mar 9, 2022

kevinzhow commented Mar 9, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

0xTim commented Mar 10, 2022

kevinzhow commented Mar 10, 2022

gwynne commented Jul 28, 2023

gwynne commented Jul 30, 2023

bottlehall commented Jul 31, 2023 via email

MySQL error trying to delete a constraint #722

MySQL error trying to delete a constraint #722

Comments

bottlehall commented Apr 8, 2021

0xTim commented Apr 12, 2021

bottlehall commented Apr 13, 2021 • edited Loading

kevinzhow commented Mar 9, 2022

kevinzhow commented Mar 9, 2022 • edited Loading

kevinzhow commented Mar 10, 2022 • edited Loading

migration

revert

kevinzhow commented Mar 10, 2022 • edited Loading

kevinzhow commented Mar 10, 2022 • edited Loading

kevinzhow commented Mar 10, 2022 • edited Loading

kevinzhow commented Mar 10, 2022 • edited Loading

kevinzhow commented Mar 10, 2022 • edited Loading

0xTim commented Mar 10, 2022

kevinzhow commented Mar 10, 2022

gwynne commented Jul 28, 2023

gwynne commented Jul 30, 2023

bottlehall commented Jul 31, 2023 via email

bottlehall commented Apr 13, 2021 •

edited

Loading

kevinzhow commented Mar 9, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading

kevinzhow commented Mar 10, 2022 •

edited

Loading