Not unique table/alias: <tablename> for temporary table

[ChrisBattersby]

I’m converting an application from Paradox to MySQL (first time I’ve used this) and am replacing Paradox local tables with MySQL temporary tables – I understand each user will have a unique copy of the table. Basically I issue the following two commands :

'drop temporary table if exists <tablename>'

'create temporary table <tablename> like <tablename>'

where <tablename> is a table structure in the database with 0 records. This strategy was successful while I was working with a local copy of MySQL on my development PC, but when I run the system from a server on the office network I get the following error :

#42000Not unique table/alias: <tablename>

The environment I work in is Windows XP Pro SP2, Delphi 5 and CoreLabs MyDAC data access components. I’ve searched this site and Googled to no avail. Hoping someone has seen this before.

 

[ChrisBattersby]

Important information I omitted : MySQL version is 5.0.45 and all tables are InnoDB

 

[ChrisBattersby]

Solved. The problem was naming the temporary table the same as the permanent table, as described in 'MySQL Cookbook' by Paul DuBois, chapter 4.3.

 

[r937]

well, yeah

did the book actually use this --

create temporary table <tablename> like <tablename>

that's misleading, it should have said --

create temporary table <tablename[blue]2[/blue]> like <tablename[blue]1[/blue]>'



r937.com | rudy.ca

 

[ChrisBattersby]

I quote from MySQL Cookbook :

'Another property of temporary tables is that they can be created with the same name as a permanent table. In this case, the temporary table "hides" the permanent table for the duration of its existence, which can be useful for making a copy of a table that you can modify without affecting the original by mistake.'

There follows an example illustrating this behaviour. As I originally mentioned, this seems to work OK on a local copy of MySQL but not on a network.

Thanks for the reply in any case.

 

[r937]

wow, thanks for the clarification -- that sure is weird

r937.com | rudy.ca

 

[towerbase]

What is the version of MySQL on the office server?

It might be an earlier version of MySQL which simply ignored the TEMPORARY keyword which, I think, would result in the behaviour you've observed.

This is only a guess!

Andrew
Hampshire, UK

 

[ChrisBattersby]

Good thought, Andrew. The version on my local PC is 5.0.27-community-nt, and that on the server is 5.0.45-community-nt. Maybe the difference.

 

[towerbase]

It's unlikely to be the slightly different versions that is causing the problem.

Maybe you don't have CREATE TEMPORARY TABLE privilege on the server? This is a different privilege to the CREATE TABLE privilege.





Andrew
Hampshire, UK

 

[ChrisBattersby]

Both my local PC and the server MySQL have full privileges granted

Chris