Mysql – use a permenent table instead of temporary but I still need the table’s data to be private to the connection

MySQLreplicationtemporary-tables

I currently have an issue whereby an application which I maintain uses temporary tables. This is an issue as it's causing replication errors like found in this mariadb issue.

I'm trying to think of a way that I can use permanent tables instead however I have the following dilemma.

The application in question is used concurrently by many users (not huge volume but enough for this to be a problem). An example temp table's data is populated with data specific to that user.

If I make the table permanent and truncate it at the beginning of every execution of the action in question then it has the potential to cause conflicts with other users on the system at the time.

Is there a way I can use transaction(s) and their isolation levels to be able to use a permanent table in the same manner as a temporary table?

Best Answer

Dynamically create a table name of 

CONCAT("T_", CONNNECTION_ID())

With that, create the table. (You cannot use "binding" to do so; you must create the entire CREATE TABLE string.)

Related Question