Hello!
Recently we had a tricky problem with Drupal 7 and manipulating content. Our content types and content in general for a particular Drupal site has grown significantly.
Ajax queries in Drupal
Adding and removing content such as images or gallery items requires an Ajax query that polls the database. These queries, once your database grows on a drupal site, can also grow. You might not notice much of a difference (when its working) but it does require more overhead when manipulating data via Drupal’s administration area.
When attempting to add or delete an item within a content type , we received this error :
Why Ajax was returning an error
Further investigation provided some evidence of this error. What we found was that many different elements , configuration settings or permissions could be causing these types of Ajax Errors in Drupal.
What happened was that we noticed there were many potential bottlenecks on the systems level that caused these types of Ajax queries to fail. I’ll run through each of the potential bottlenecks or configuration root causes that contribute or cause these types of problems.
PHP.INI Configuration
There are configuration directives in php.ini that contribute to the upload maximum file size, POST maximum size, maximum memory, maximum execution time for php scripts, input time and a few other things. Here is what I would consider an ideal configuration for a testing server. Obviously for a production server you would have to fine tune it a bit more to balance efficiency with the types of constraints mentioned already:
max_execution_time = 600 ; Maximum execution time of each script, in seconds max_input_time = 600 ; Maximum amount of time each script may spend parsing request data memory_limit = 1024M ; Maximum amount of memory a script may consume (128MB) upload_max_filesize = 80M ; Maximum allowed size for uploaded files. post_max_size = 200M ; Maximum size of POST data that PHP will accept. max_input_vars = 10000 suhosin.request.max_vars = 10000 suhosin.post.max_vars = 10000 max_input_nesting_level = 10000
Some would argue that some of the above variables are excessive. Remember that this is intended for a testing server and we were ruling out potential root causes. You might want to lower some of these timeout and maximum file size variables.
NGINX Configuration
If you dont use nginx, you can probably find an equivalent directive for apache or lighttpd. Nginx can define a “client max body size” which is similar to the “post_max_size” php.ini directive above. Most likely the two related directives should be similar or the same. The shorter variable will likely be the one that is enacted for obvious reasons.
client_max_body_size 200m; client_body_buffer_size 10m;
MySQL Configuration
I’ve saved the best for last. This was the actual culprit for our particular problem. We were still experiencing an issue even after implementing all of the above configuration directives, unfortunately. What I already alluded to was that some of these directives between php, nginx and mysql overlap such as the max post size / client body size directives.
In MySQL there is a similar directive (in the context of MySQL) called max_allowed_packet. This was the culprit. The default variable for this setting out of the box for MySQL is 1mb. This proved to be far too small for the large ajax queries when manipulating large amounts of data / content within Drupal. Once we set it to the following variable , the Ajax problems went away!
max_allowed_packet=16M
Now instead of just giving the above configuration setting, I’ve decided to run through the actual reasons and potential root cause analysis as to why this happens. Everyone’s environment is a bit different. During our research into this problem, we found many many suggestions from other people that resolved it from all the different methods (including ensuring the default file folder has proper write permissions).
It took almost 3 days for us to resolve this. In the end it was an (excruciatingly) simple fix. The real problem (I think) is that mysql just cut off the response to drupal’s AJAX request and neither service knew actually what happened. Perhaps MySQL could have logged this, or perhaps AJAX should have interpreted the empty response more intelligently. Either way, because the issue was so generic it took a long time to run through the long list of potential root causes until a fix was found.
Hope this helps somebody out there!
