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?

There is an

IdTable

you can use instead of

Table

. That likely won't fix your issue, but just as an FYI

You can just do

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

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/.

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.

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"
)

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 🤦‍♂️