MySQL Bugs: #510: UPDATE infinite loop?

Bug #510	UPDATE infinite loop?
Submitted:	26 May 2003 13:36	Modified:	27 May 2003 4:50
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Microsoft Windows (MS Windows)
Assigned to:		CPU Architecture:	Any

Description:
Using the console, I entered the query:

UPDATE table SET xxx="yyy" WHERE criteria1 > 19 AND criteria2 != "zzz" order by rand() limit 10000;

Believe it or not, but I would have needed this to work. It parses OK, starts to run. The problem is that it never ends and that it does not do anything. I let it run for 20 minutes, stopped it and then checked the table to see if it had done anything. It had not. This table is quite large, 350 mb or so and has around 40 columns.

The SET column referred to a DATE column, and the criterions were INTs and TEXTs, respectively.

This might be a parser error (Is rand() allowed on UPDATE queries? The manual doesn't mention it, but it does not give an error)

How to repeat:
UPDATE table SET xxx="yyy" WHERE criteria1 > 19 AND criteria2 != "zzz" order by rand() limit 10000;

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

It is rather expected behaviour as this query must first order your whole table BY RAND() then pick up 10000 and just then perform UPDATE on them.

> I let it run for 20 minutes, stopped it and...

With large table it can take hours and even days.

To estimate how long it will take simply run SELECT with same WHERE, ORDER BY and LIMIT clauses.

SELECT COUNT(*) WHERE criteria1 > 19 AND criteria2 != "zzz"
order by rand() limit 10000;

This type of questions belongs to general list at mysql@lists.mysql.com not to Bugs Database. Please post here only if you have fully repeatable test case.