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.
