MySQL temporary tables immediately dropped

dev
1

Running Lucee 6.2.0.321 under Ubuntu 22 on an Azure VM against MySQL 8.x.

This is driving me batty. I’m migrating from CF to Lucee and some configurations, scripts and components have needed modification, as one might expect. But this one is tough.

I have a script that calls a component to do some threaded work. The component creates a MySQL temp file with engine=memory and then tries to fill it, only to have the INSERT fail because the temporary file “doesn’t exist”. Check out this snippet from a log:

“INFO”,“pool-15-thread-1”,“05/26/2025”,“18:31:20”,“1vox”,“Created table mempost230”

“ERROR”,“pool-15-thread-1”,“05/26/2025”,“18:31:20”,“1vox”,“Fill of mempost230 failed: {”“Extended_Info”“:”“”“,”“Message”“:”“Table ‘vox.mempost230’ doesn’t exist”"

As you can see, the CREATE TABLE and INSERT are executed in order (note timestamps). I’ve tried many variants on the code (don’t use ENGINE=MEMORY, etc.) but nothing seems to help. I’ve changed the datasource timeouts, turned verifications on and off…everything I can think of.

I sure would appreciate some help. Have you encountered this before?

2

There’s an option for the datasource to use exclusive connections, on my phone so can’t find it?

Lucee uses a shared pool and borrows a connection for each jdbc request, returning it after each query

Otherwise, wrapping it all in a transaction may also work?

3

Hi Zackster - thanks for the quick reply.

I saw that (experimental?) option but it said the exclusivity is per “request”. Would that not have the effect of seeing the CREATE TABLE and the INSERT as separate requests and therefore drop the temp table?

4

Request in this context means the http request / page

5

Aha :slight_smile: I’ll try it! Thanks again.

6

Feedback is always appreciated! (positive and negative)

1 Like
7

Yessir