When setting up a query using a temporary lookup table, I got this error:

[text]ERROR 1137 (HY000): Can't reopen table: 'tmp_journals'[/text]

It transpires that since 4.1 the way MySQL handles temporary tables has changed. This affects joins, unions and subqueries. There is an obvious fix:

[sql]mysql> CREATE TEMPORARY TABLE tmpjournals2 LIKE tmp_journals; Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmpjournals2 SELECT * FROM tmp_journals;
Query OK, 3228659 rows affected (2.01 sec)
Records: 3228659 Duplicates: 0 Warnings: 0 [/sql]

Then the query is easy:

[sql]SELECT COUNT(1) cnt, journalinvoiceref FROM tmpjournals
GROUP BY journal
HAVING cnt > 10000</pre>


SELECT COUNT(1) cnt, journalinvoiceref
FROM tmpjournals2
GROUP BY journalinvoiceref
HAVING cnt < 10[/sql]