5

I am currently working on a Joomla website on a Unix server. There are no users connected at the moment because I'm the only one who can access it (so no heavy traffic).

During testing, I proceed to make several MySQL INSERTs or UPDATEs on a table in my DB. When the Ajax is triggered, data is sent to a php file which connects using jFactory::getDBO(), executes a query, and returns a success response.

Everything works perfectly, until I run several tests in rapid succession. After a while, even though I still get a success response in Ajax, there are no affected rows (not on this table, not on any other table).

It's like MySQL blocks all the connections (and yet I don't get any error message). I tried opening a different browser and magically the queries execute perfectly/successfully from there. I think I also noticed that it starts working again when I close the original browser and open it again.

What could the issue be? And how can I solve it? I was looking for a way to close the getDBO() connections after each query, but all I found online is that on a Unix server, this type of connection is automatically closed after each query.

This is the Ajax Call:

jQuery.ajax({
          type:"POST",
          url:"/models/ajax.php",
          data:{"sid":sid, "steid":steid, "answer":answer,   "function_to_call":21},
          dataType: "json",
          cache: false,
          success: function(data){
              console.log(JSON.stringify(data));
          },
          error: function(msg){
            console.log(JSON.stringify(msg));   
          }     
});

This is how the insert/updates are performed:

if(isset($step) && !empty($step) && trim($step) != ""){

  $sql = "UPDATE #__thamatho_student_mistakes SET   
  date_mistake='".$date."', answer='".addslashes($answer)."' WHERE 
  student=".$userid." and step=".$steid;
  $db->setQuery($sql);
  $db->query();

}else{  

  $sql = "Insert into #__thamatho_student_mistakes (`student`, 
  `step`, `answer`, `date_mistake`) values (".$userid.", ".$steid.", 
  '".addslashes($answer)."', '".$date."')";
  $db->setQuery($sql);
  $db->query();

}
  • Can you confirm that your browser is Chrome and with Firefox works?, maybe it's related to the max-connections-per-server limit in Chrome (6) – José Sánchez Martín Jun 21 '18 at 16:15
1

Probably the browser gets a response from the browser cache, try to add a time variable to the link or otherwise disable the cache

  • 2
    Please elaborate on your answer a little with a possible example too – Lodder Mar 12 '15 at 12:02
  • I am not sure I understood what it means to add a time variable to the link. What link ? I have a page where students perform exercises and for each exercise, if the student gives the wrong answer, I have to insert his answer into the DB and that's what I do through an Ajax request on the user's click. So there's no link, but simple actions on DOM elements – user3716433 Mar 12 '15 at 16:42
  • He probably means the url in the open method for the request. The example is like "xmlhttp.open("GET","demo_get.asp?t=" + Math.random(),true);". t is not used for anything on the server side, but it tells the browser it can't use cached result. I think you could tell browser the response is non-cacheable in php with "header("Cache-Control: no-cache, must-revalidate");" or similar. – Ville Niemi Mar 13 '15 at 10:37
  • For the moment, I added "cache: false" to my Ajax call, but I haven't had the chance to perform an intensive test to see if the problem presents itself. Just in case, where should I add the header("Cache-Control: no-cache, must-revalidate") ? In the php page that is called by the Ajax function and that contains the Insert/Update sql ? Thanks for your help ! – user3716433 Mar 14 '15 at 9:35
0

My first concern is always security, add_slashes() is an inadequate function to call to protect your query. I would urge you to implement the Joomla query methods to build your queries and read about Joomla's Security Recommendations.

I agree with the other volunteers that this is likely to be a matter of caching and that MySQL is not likely the culprit in your issue. If MySQL was having trouble you, could find some evidence in the error logs or write your php file to return some informative feedback.

To offer greater specificity on the commented advice.

You can add a random or time-based querystring to the url/page that is receiving your submitted data so that never will you have two duplicate urls:

url:"/models/ajax.php?unique=" + Math.random(),

or

url:"/models/ajax.php?unique=" + +new Date();

Note, you don't actually need to use $_GET['unique'] on the receiving page, this is merely being used as a unique identifier to prevent caching.

If that doesn't work or you wish to "double-down" on cache-prevention, then you can also write header("Cache-Control: no-cache, must-revalidate") at the very top of your receiving php file.

Beyond all of that, Joomla has some guides on developing an ajax interface which are intended to improve security and make coding simpler.

And as far as your raw php validation condition:

if(isset($step) && !empty($step) && trim($step) != ""){

That can be rewritten as if (!empty($step) && trim($step) != "") { because !empty() checks for existence (making isset() redundant) AND checks for a non-false-y value. If you have a more narrow expectation of the $step value, then I would advise that you perform stronger validation at this location in your code.

I don't know the capabilities of your project interface, but if several subsequent submissions (ajax calls) are likely, perhaps you could re-design the form to allow multiple sets of data to be delivered in a single batch. By minimizing the total ajax calls, you minimize the number of times that a db connection will have to be opened and closed -- this is best practice.

For greater peace-of-mind, you could return a more detailed explanation of the values sent and how many rows were affected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.