Why does this MySQL query not work?

Question

mysql

create temporary table t2 
select min(id) 
from mycontent 
group by download_link;   

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Answer 1

Maybe try doing them separately.

1) create temporary table t2;

2) select min(id) from mycontent group by download_link;   

It's worth a shot.

Answer 2

As I understand group_by, you need to select the grouped fields for the query to make sense. What is it that you try to accomplish?

Imagine this table:

download_link, id
-----------------
''           , 3
'foo'        , 3

This, using your query, would select

3
3

and therefor explain your locking problem because row 1 is locked by the transaction already but needs to be replaced by row 2.

Answer 3

Your mycontent table may be locked by some other transaction when you trying to select from it.