1

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?

  • why just not change replication format to RAW? it resolve replication problem with temporary tables, plus prevent a lot of other – a_vlad Jun 20 '17 at 9:45
2

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

1

If it was Microsoft SQL Server, which it isn't, then I'd consider giving each user their own named schema and creating the table in that schema e.g. user "adam" has a table named "adam"."table_name", and whenever Adam executes a statement referring to "table_name" it is fulfilled using"adam"."table_name". But Robert runs the application and uses the table "robert"."table_name". However, I also would try quite hard to find another way around the problem. That's what I'd do.

Another approach (which also may be unavailable)... use the table with a view, which is,

SELECT * FROM table_name WHERE table_user = USER_NAME() -- the current user

Then Adam only sees rows in the table which belong to user "adam".

  • You could probably mimic the same behaviour with what MySQL calls a database (which is equivalent to an SQL schema). – joanolo Jun 20 '17 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.