Reply
Highlighted
Member
Posts: 3
Registered: ‎06-26-2020

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

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.