Error 1066: 'Not unique table/alias' in a join or HANDLER statement
ERROR 1066 (42000): Not unique table/alias: 'orders'Short answer
Error 1066 means a table name or alias appears twice in the same statement and the server cannot tell which one you mean. In a SELECT, the same table is listed twice without distinct aliases. With HANDLER, a second table of the same name is opened. Give each one its own alias.
Fix it fast
1Find out which cause you have
Take the name in the error message and count how many times it appears after FROM, JOIN and in HANDLER ... OPEN. Two or more is the cause.
-- the name in the message is 'orders': find every use of it grep -n -i "orders" query.sql2Match what you see, then jump to its fix
- 1
The name in the message appears in two FROM or JOIN items with no distinct alias.The same table, or the same alias, is listed twice in a SELECTFix - 2
The failing statement is HANDLER ... OPEN, often for a table of the same name in another database.HANDLER opens a second table that has the same nameFix
- 1
What does the error look like?
ERROR 1066 (42000): Not unique table/alias: 'orders'ERROR 1066 (42000): Not unique table/alias: 't1'
Cause 1 of 2
The same table, or the same alias, is listed twice in a SELECT
1Check
A table name or alias must identify one object in the statement. A self-join, or a table joined twice for two different roles, therefore needs an alias on each side. The manual's form is tbl_name AS alias_name, or tbl_name alias_name.
-- fails with 1066: 'orders' is listed twice, no aliases
SELECT orders.id
FROM orders
JOIN orders ON orders.id = orders.parent_id;2Fix
Alias each occurrence once, with a different name, and refer to the column through that alias.
SELECT child.id, parent.id AS parent_id
FROM orders AS child
JOIN orders AS parent ON parent.id = child.parent_id;3Verify
The statement runs and returns the pairs you expect.
EXPLAIN SELECT child.id FROM orders AS child JOIN orders AS parent ON parent.id = child.parent_id;Confirmed by dev.mysql.com
Cause 2 of 2
HANDLER opens a second table that has the same name
1Check
HANDLER statements refer to an open table by its unqualified name. Opening db_new.t1 while another t1 is open collides; MariaDB's reference for this error shows exactly that case.
HANDLER db_old.t1 OPEN;
HANDLER db_new.t1 OPEN;2Fix
Open the second table with an alias and use that alias in later HANDLER statements. The manual says references must use the alias, not the table name.
HANDLER db_new.t1 OPEN AS db_new_t1;
HANDLER db_new_t1 READ NEXT LIMIT 3;
HANDLER db_new_t1 CLOSE;3Verify
READ returns rows and no 1066 appears.
HANDLER db_new_t1 READ FIRST;Confirmed by mariadb.com
How do I stop it coming back?
- Give every table in a multi-table query a short alias from the start, and use only the alias after that.
- Keep aliases in one query distinct from every real table name used in it.
- Close HANDLER tables when you finish with them.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix