Please include the full query you're running, the table structure, and the number of rows in the table. A dump of 'show variables;' would be helpful too.
On Wed, 2004-11-10 at 21:44, foo bar wrote: > Hi Everyone, > > I've been Googling unsuccessfully for specific issues > relating to queries run on MySQL version 4.0.16 > against "tmp" tables. I have witnessed several > occurrences where queries running on various platforms > hang in a "Copying to tmp table" state for hours or > days at a time. When the same query is manually run > from the MySQL command line client, the query returns, > even on very large tables. Could someone please give > me a hint as to possible tuning (or upgrade?) ideas to > fix this situation? I can't find anything else on > each problem system that would give me a hint as to > why this problem randomly occurs. I've made attempts > at adjusting the "tmp_table_size" limits to have the > system try to do "order by" queries in memory, but > this does not seem to help the situation. > > Any help would be appreciated! > > -Kevin > > "show full processlist" output (notice that the query > has been running for 7.9 days!!!): > | 33 | someuser | localhost:34329 | sometable | Query > | 687465 > | Copying to tmp table | select > date_add( date_format(time, '%Y-%c-%d 00:00:00'), > INTERVAL 0 HOUR) time, > ... > group by 1 > order by 1 > > > > __________________________________ > Do you Yahoo!? > Check out the new Yahoo! Front Page. > www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]