We recently came across an issue on a client’s server where one of the SELECT queries from WooCommerce Booking & Appointment Plugin was failing. The query was related to the Global timeslots feature.
The strange thing was that the query was failing only for 1 product & it was working for rest of the products.
Since we usually only have wp-admin access, it was difficult to check the database entries.
But what we found was that when $wpdb->get_results() was replaced with mysql_query(), the query used to work fine.
We asked for the database access or mysql dump from the client.
The query for the product that was failing was trying to fetch about 21000 records from the wp_booking_history table. Though that number is not alarming, but it was big.
We added “LIMIT 0,1000” at the end of the query and that seemed to work. We increased the limit to 5000, that failed.
After modifying the query to only fetch a limited set of results, the issue was fixed.
However, after doing some R & D, I found that the failure is due to the memory_limit setting in php.ini. The amount of memory consumed was too much for the big result set.
So it’s not really the number of records, but the amount of memory consumed that caused $wpdb->get_results() to fail.
So next time if $wpdb->get_results() fail, remember to do 2 things:
1. Fix your query so it fetches a smaller result set or add a LIMIT clause.
2. Increase your memory_limit setting.