Foreign Key Constraints being recreated

[Togrias]

Hi,

When using uniqueIndex() or init { index(true, ...) }, createMissingTablesAndColumns seems to add additional constraints each time it is run. How do we avoid this?

[Tapac]

@Togrias , could you please share your Table mapping and which database do you use?

[platky]

I am also running into this. Here is a quick example that triggers it:

object UserTable : Table() {
    val id = integer("id").primaryKey().autoIncrement()
    val email = varchar("email", 128).index()
    val isVerified = bool("isVerified")
}

object UserInfoTable : Table() {
    val userId = integer("userId").references(UserTable.id, ReferenceOption.CASCADE, ReferenceOption.RESTRICT)
    val firstName = varchar("firstName", 128).nullable()
    val lastName = varchar("lastName", 128).nullable()
    val signUpDate = datetime("signUpDate")
}

I am using MySQL 5.7

[Tapac]

@platky , could you also specify which jdbc driver version do you use?

[Tapac]

@platky , @Togrias , could you test your code with the latest master?
You have to add that to your gradle/maven to do so:

repositories {
   ...
   maven { url 'https://jitpack.io' }
}

dependencies {
   ...
   compile 'com.github.JetBrains:Exposed:-SNAPSHOT'
}

[platky]

I'm using the MySQL Connector which seems to have JDBC version 4.2.

Using the above dependency instead of just creating duplicate fk's, it throws an exception and crashes:

Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-1190_110'
SQL: [ALTER TABLE userinfo ADD CONSTRAINT fk_userinfo_userid_id FOREIGN KEY (userid) REFERENCES `user`(id) ON DELETE CASCADE ON UPDATE RESTRICT]
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:61)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:128)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:122)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:101)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:92)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:201)
	at database.table.Tables$create$1.invoke(Tables.kt:15)
	at database.table.Tables$create$1.invoke(Tables.kt:9)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:58)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:58)
	at database.table.Tables.create$backend_main(Tables.kt:11)
	at database.DbSetup.<init>(DbSetup.kt:17)
	at MainKt.main(Main.kt:25)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-1190_110'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1046)
	at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:105)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:59)
	... 14 more

Also just in case, I am triggering it like this:

transaction {
            createMissingTablesAndColumns(
                UserTable, UserInfoTable
            )
        }

[Tapac]

Still can't reproduce on clean mysql 5.7 with jdbc connector 8.0.13.
Do you have any specific connection parameters? Maybe your database has multiple schemas with User/UserInfo tables?

[Togrias]

I haven't gotten a chance to test extensively, but I'm using postgresql.

Could this be due to case-sensitivity issues with the generated constraint names? IIRC postgres names are case sensitive but mysql names aren't (?).

alter table books drop constraint "books_authorId_fkey5" works, but alter table books drop constraint books_authorId_fkey5 doesn't, because case-sensitive names require quotation marks. The latter is the raw sql query being generated by createMissingTablesAndColumns(). Apparently, the sql query completes without throwing an error, but does nothing.

Also, uniqueIndex(...) throws an error when updating tables vis createMissingTablesAndColumns(), because it will attempt to create the constraint a second time, which fails due to naming conflicts.

I'll update when I get a chance.

[Tapac]

Fixed in master, will be released soon.

[Togrias]

Hi,

I can confirm that the case-sensitivity issue is causing it. Renaming columns to lower-case fixed the issue for Postgresql 9.1.

[Tapac]

Please, check release 0.11.1.

[Togrias]

Hi,

I don't think the issue has been resolved for Postgresql. The function now throws an error rather than creating duplicate keys.

[StefanLiebig]

Hi,

I think I am running into the same problem.

With:

object Customers : Table() {
    val id = integer("id").primaryKey().autoIncrement()
    val name = varchar("name", 50)
}

object Assignments : Table() {
    val id = integer("id").primaryKey().autoIncrement()
    val customFK = integer("customFK").uniqueIndex().references(Customers.id)
    val name = varchar("name", 50)
}

fun main() {
    database(Config.database)

    println("Before first createAnd..")
    transaction {
        SchemaUtils.createMissingTablesAndColumns(Customers, Assignments)
    }
    println("After first createAnd.. and before second")
    transaction {
        SchemaUtils.createMissingTablesAndColumns(Customers, Assignments)
    }
    println("After second createAnd..")

}

the second createMissingTables...() fails with

10:26:30.303 [main] WARN Exposed - Transaction attempt #0 failed: java.sql.SQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-2798_102'. Statement(s): ALTER TABLE assignments ADD CONSTRAINT fk_assignments_customfk_id FOREIGN KEY (customfk) REFERENCES customers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-2798_102'
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:61)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:131)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:125)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:101)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:92)
	at org.jetbrains.exposed.sql.SchemaUtils.createMissingTablesAndColumns(SchemaUtils.kt:201)
	at timebox.problem.ProblemKt$main$2.invoke(Problem.kt:29)
	at timebox.problem.ProblemKt$main$2.invoke(Problem.kt)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:104)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:75)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:58)
	at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:58)
	at timebox.problem.ProblemKt.main(Problem.kt:28)
	at timebox.problem.ProblemKt.main(Problem.kt)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-2798_102'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:975)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1114)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1062)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1383)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1047)
	at org.jetbrains.exposed.sql.Transaction$exec$2.executeInternal(Transaction.kt:106)
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:59)
	... 13 common frames omitted

Before running all tables have been droped.
DB: MySql 5.7.25
JDBC: MySQL Connector/J 5.1.46 (Intellij IDEA provided)

[joserobjr]

I'm having the same issue, after debugging I noticed that it is being caused by a case-sensitive check here:

Exposed/exposed/src/main/kotlin/org/jetbrains/exposed/sql/vendors/Mysql.kt

Line 98 in 26aa93e

if (fromTableName !in tableNames) continue

The indexes are created correctly by the createStatements() method but exposed attempts to recreate it at addMissingColumnsStatements() because it's ignoring the references when the table have capital letters

@StefanHUB has a work around, name all your tables manually with lower cased names

object Customers : Table("customers") {
//...
}

[StefanLiebig]

@joserobjr Thanks!