Temporary MySQL tables disappearing in Web project

Here is my new “web” challenge…

Platform 1: CentOS running MariaDB 5.5.52
Platform 2: macOS running MySQL 5.7.16

I’m creating Temporary tables via the MySQLCommunity Plugin. The tables are created & populated without error. However, when queried later, they are empty. Is it not correct that these tables should be kept around until the database connection is dropped? It is only dropped when the Session is done.

If I remove the TEMPORARY keyword when the tables are being created it works as expected… except they are not temporary. I have to go back through and delete the tables, so it probably won’t work once in a multi-user environment.

There was a MySQL bug regarding Temporary tables disappearing but my MySQL version is past that. I’m assuming this is either a Web issue or a plugin issue. Suggestions?

That would really surprise me. Are you using SQLExecute? All the plugin in does is handing whatever SQL you have in there to the server and setting the error properties if the server doesn’t like it.

Does your sequence of commands work if you execute them via the command line?

Also note:

CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.


…so maybe you’ll have to commit manually.

If you have multiple connections to your MySQL Database I think only the one that creates the temporary table has access to it. This means you need to either share this connection to the session and make sure you deadlock so that two javascript callbacks to XOJO don’t happen at the same time

[quote]A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed.
This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name.[/quote]

I am using the built-in .Commit call. I suppose I could switch to a SQLExecute statement.

I will double-check the connections. There are two. I’ll get rid of the second one and see what happens.

I’ve removed the second connection. Same problem. “Create Table” works. “Create Temporary Table” does not work.

And I’m SQLExecuting START TRANSACTION and SQLExecuting COMMIT instead of using the .Commit. Still no go with the Temporary Tables.

same user can log in and issue a create temporary using some other tool ?
maybe the user has no permission to do this ?

[quote=304982:@Norman Palardy]same user can log in and issue a create temporary using some other tool ?
maybe the user has no permission to do this ?[/quote]

Yes. I’m connecting as root. Logged in via cmd line, made a temp table, did a Describe on it. Exited, logged back in, table gone, as expected.

Cant say I’ve ever had issues with temporaries

Absolutely certain the db connection doesn’t close or get replaced somehow ?
Thats the only reason I can think you’d have issues because of course the connection would close, the temporary would disappear, and then you;d be reconnected with the same user but no temporaries.

Yeah Norman. The only .Close is in Session.Close. And the only .Connect is in Session.Open.

I really don’t know what is going on. I’m now working on converting the temp tables to an in-memory SQLite database. I really didn’t want to do that. :\

very weird

Switched to SQLite in-memory database and everything works fine now.