This version doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

EthanElijah — Thu, 07 Jan 2021 08:22:45 GMT

One of our customers is running an old MySQL, 5.5 or 5.7. We have a very long query 300+ lines which is too long to read, what it does essentially gets orders that need to be renewed. We need to run it 24/7 and if renewal fails due to a lack of funds, the query needs to wait 6 hours before trying to renew it again.

Each new order has a new and unique orderid, if an order is generated by a renewal attempt of another order, the orderid of the old order would be inserted in generated_by field of the new order. If renewal fails due to lack of funds, it will have is_paid=-6

The subquery is below

AND orderid NOT IN (

SELECT generated_by FROM wtb_orders where is_paid=-6 AND created >= DATE_SUB(NOW(), INTERVAL 6 HOUR ) ORDER BY created DESC LIMIT 1 )

That gives this error

This version doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

And if we don't use ORDER BY created DESC LIMIT 1, then the renewal system will always try to renew an order because I guess each time the renewal is failing it's always creating a new entry that has an interval that doesn't fit within the rules, but I could be wrong

essentially what I want to do is to stop the renewal script from selecting the same order twice within less than 6 hours duration.

topic This version doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery in Integration and Testing

This version doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery