Bug #6588 LOCK TABLES - UNION - TABLE WAS NOT LOCKED
Submitted: 12 Nov 2004 9:28 Modified: 12 Nov 2004 13:19
Reporter: Tore Van Grembergen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.17 OS:Linux (Linux - Mandrake 9.2)
Assigned to: CPU Architecture:Any

[12 Nov 2004 9:28] Tore Van Grembergen
Description:
When executing the following : 

LOCK TABLES openstaandefifolijnen WRITE, fifolijnen WRITE, levbestellijnen READ, levbestellijnenpicking READ, producten READ 

INSERT INTO openstaandefifolijnen ( uvnotanr, ulvnotanr, ivnotanr, ilvnotanr, prodnr ) SELECT    uvnotanr as uvnotanr, 0000000000000000000 as ulvnotanr, fifolijnen.ivnotanr, fifolijnen.ilvnotanr, fifolijnen.prodnr 
FROM      fifolijnen 
WHERE     ulvnotanr = 3478 
UNION     ALL SELECT    fifolijnen.uvnotanr, fifolijnen.ulvnotanr, ivnotanr as ivnotanr, 0000000000000000000 as ilvnotanr, fifolijnen.prodnr 
FROM      fifolijnen 
WHERE     ilvnotanr = 3478

I get the following error :

Table 'fifolijnen' was not locked with LOCK TABLES

How to repeat:
cf supra
[12 Nov 2004 13:19] MySQL Verification Team
Hi,

Thank you for the report, but this is not a bug.

From the MySQL manual:

When you use LOCK TABLES, you must lock all tables that you are going to use in your queries. While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any tables that were not locked by the statement. Also, you cannot use a locked table multiple times in one query - use aliases for that. Note that in that case you must get a lock for each alias separately.
[12 Nov 2004 13:37] Tore Van Grembergen
This used to work in previous releases. (eg 4.0.17)
It stopped working when I installed release 4.1.7

However the suggested solution appears to work.

Thanks for answering so quickly