jOOQ: MySQL "TEMPORARY TABLE" does not exist during transaction?

Question

I have code like the following:

fun <T> withTempTableTxn(
    ctx: DSLContext,
    tempTableSelectAs: org.jooq.Select<*>,
    txnBlock: (DSLContext, org.jooq.Name) -> T,
): T {
    val uuid = UUID.randomUUID().toString().replace("-", "_")

    val tempTableName = DSL.name("temp_table_$uuid")
    val createTempTable = DSL.createGlobalTemporaryTable(tempTableName).`as`(tempTableSelectAs)

    return ctx.transactionResult { txn ->
        txn.dsl().execute(createTempTable)
        val res = txnBlock(txn.dsl(), tempTableName)
        txn.dsl().execute(DSL.dropTemporaryTableIfExists(tempTableName))
        res
    }
}

private fun updateMutation(
    mutationRequest: MutationRequest,
    op: UpdateMutationOperation,
    ctx: DSLContext,
): MutationOperationResult {
    // The rows that the temporary table will be populated with
    val tempTableRows = DSL
        .selectFrom(DSL.table(DSL.name(op.table.value)))
        .where(/* ... */)

    return withTempTableTxn(ctx, tempTableRows) { txn, tempTableName ->
        // ...
    }
}

When this runs, it seems like the temporary table can't be found.

Note that if I replace temporaryTable with a regular table, it all works fine

If I add an .also { println(ctx.render(it)) } to the createTempTable, we get:

Creating temp table:
create temporary table `temp_table_e2fd79a5_4589_41df_9972_8ff9b216300a`
as
select *
from `Chinook`.`Artist`
where `Artist`.`Name` = 'foobar'

13:48:02 ERROR traceId=, parentId=, spanId=, sampled= [io.ha.ap.ExceptionHandler] (executor-thread-0) Uncaught exception: org.jooq.exception.DataAccessException: SQL [update `temp_table_e2fd79a5_4589_41df_9972_8ff9b216300a`
set
  `Name` = 'foobar new'
where `temp_table_e2fd79a5_4589_41df_9972_8ff9b216300a`.`Name` = 'foobar'];
Table 'Chinook.temp_table_e2fd79a5_4589_41df_9972_8ff9b216300a' doesn't exist

Answer 1

From the docs:

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name.

Other RDBMS have temporary tables whose meta data is shared between sessions / transactions, truncating only data on commit, but not in MySQL.

As far as jOOQ is concerned, the most likely explanation is that you're creating and dropping the table using txn.dsl() (transaction 1), but you're running your txnBlock using ctx, which is also in scope, but is running a different transaction. As such, it can't see your txn's temporary tables.