Temp tables in mySQL stored procs not unique

[swamp]

Use a proper database.

[MrMark]

Interesting reading here, the comments may explain why things go astray on Unix/Linux-based op systems:
http://www.xaprb.com/blog/2007/05/11...bles-in-mysql/

[minestrone]

Strange thing is that it does not happen on my windows dev machine but happens on my ubuntu server.

If I was to use 'if not exists' then I would have to radically rework the procs and that could get messy.

I also 'think' I am creating a new transaction each time in the code but I suppose anything is possible with an ORM framework. I should double check that with some debug.

[DimPrawn]

According to the blurb temp tables are unique per connection.

[lilelvis2000]

Strange, they should be per session/connection unique. But there is the "if not exists" option.

Create temporary table if not exists blah.....

Hope that helps.

[minestrone]

Would anyone know of a fix to this problem? I have a small stored proc that can get called many times a second. The basics of this are..

BEGIN

    CREATE TEMPORARY TABLE
      TEMP_BLAHH_BLAHH ( BLAHH_ID int ) ;

    -- A BIT OF WORK

END $$

The problem is that if 2 of these SPs are running at the same time then I get the following error...

Table 'TEMP_BLAHH_BLAHH' already exists

I am used to Sybase there the temp tables are totally unique to the SP's transaction.

Does anyone know of a workaround/solution to this 'cos I have no idea?

Thanks