Hi! I am attempting to use Exposed in my first Kto...

# exposed

[Tom Truyen]

Hi! I am attempting to use Exposed in my first Ktor server project, but unfortunately I get the exception below.

A little more information:
• Exposed version: 0.44.0
• JDBC connector version: 8.0.33
• Database used: MariaDB (running in docker)
Exception seems to happen on the SchemaUtils.create method

// UserService.kt
init {
    transaction(database) {
        SchemaUtils.create(Users)
    }
}

// UserModel.kt
object Users : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", length = 50)
    val age = integer("age")

    override val primaryKey = PrimaryKey(id)
}

// Databases.kt
val database = Database.connect(
    url = "jdbc:<mysql://localhost:3306/mydatabase>",
    user = "user",
    driver = "com.mysql.cj.jdbc.Driver",
    password = "password"
)

I am unsure where the Unknown column 'RESERVED' in 'where clause' comes from. I guess it is something that happens internally in Exposed.

Any idea what could be going wrong here?

[Luis Arcos]

There is an IdTable you can use instead of Table. That likely won't fix your issue, but just as an FYI

[Ruben Holen]

You can just do

object Users : IntIdTable() {
    val name = varchar("name", length = 50)
    val age = integer("age")
}

[Tom Truyen]

I tried both, the error persists. I am also just using Table because this User object is generated by a project created with: https://start.ktor.io/.

[Luis Arcos]

you will probably need to put a breakpoint on the create and step through the code to see where it errors. i had to do that a few times w/ my project. the good news is JetBrains has been very good about fixing issues quickly.

[Chantal Loncle]

Based on the setup mentioned, the cause of the syntax exception originates from this function:

com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getSQLKeywords(DatabaseMetaDataUsingInfoSchema.java:1194)

// specifically this line in the driver source code
ResultSet rs = stmt.executeQuery("SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED=1 ORDER BY WORD");

When Exposed is tasked to generate an SQL statement, part of the underlying process is checking if any of table identifiers are keywords that need to be quoted. It does so on first access by requesting a result set of reserved keywords from the JDBC driver, using getSQLKeywords().

So the MySQL connector executes the query above, which throws. This is because the INFORMATION_SCHEMA.KEYWORDS table in MariaDB does not contain the RESERVED column. Whereas this table in MySQL does have the 2 columns referenced.

The generated sample project runs without an issue if setup is changed to match the driver with the database: either the database used is switched to MySQL or the MariaDB connector is used instead.

val database = Database.connect(
    url = "jdbc:<mariadb://localhost:3306/mydatabase>",
    user = "user",
    driver = "org.mariadb.jdbc.Driver",
    password = "password"
)

[Tom Truyen]

That works! Thank you for you detailed explanation on the problem. It did give me a lot of insights on how it works 🙂

No idea why I didn't just use the MariaDB JDBC Driver from the start it makes so much sense 🤦‍♂️