datatables 1.10.12 fails to compose Ajax request properly at random intervals with no apparent cause

JordanCapitalIT@gmail.com · October 2016

The following DataTables implementation fails randomly and unpredictably. The Table will be correctly rendered about half the time, and then fail with no change in parameters by simply refreshing the same page in the browser. Further refresh of the page after it has failed can also result in a successfully rendered table.

The Misbehavior is the same on all major browsers. When the failure occurs, unminified jquery.dataTables.js throws an error at line 4108 (for ( var i=0, ien=data.length ; i<ien ; i++ ) ) with 'cannot read property 'length' of undefined'. This failure causes the request string to be malformed (6 elements present in the request rather than the expected 7 (the 'draw' array is not present)) which in turn causes the server-side ssp.class.php to generate an invalid query and the Db server to throw an error which is echoed back to the client browser and spawns a dialogue with the following text:

"DataTables warning: table id=MyDataTable - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 you have an error your SQL syntax...near 'LIMIT 0,10' at line..."

The datatables implementation is nearly identical to the 'Server Side' example with no significant deviations from that example beyond the DataSource and Column definitions being altered. DataSource is ~110K rows table on MYSQL 5.7.10 server (running local to the machine), datasource PHP (v5.6). I have tried various versions of JQuery (1.21 through 3.1.1) multiple versions of DataTables with no change.

I have experimented with all the suggestions (character set, datasource and Ajax data properties explicitly defined (see commented out lines below) which I can find in this forum and other sources to deal with similar errors to no effect.

Anyone have any ideas why the jquery.dataTables.js would succeed one moment, and then fail another moment, and then succeed again after simply refreshing the same page?

```
JSONClient.php

Id	Date	Order Type	Item	Price	Quantity	Fee

$(document).ready(function () { $('#myDataTable').DataTable({ "responsive": true, "processing": true, "serverSide": true, "paging": true, "ajax": "/TestArea/JSONSource.php" //"sAjaxDataProp": "data", //"dataSrc": "data" }); });

JSONSource.php

<?php
//Db Table to execute against
$table = 'ObscuredTableName';
// Table's primary key
$primaryKey = 'Id';
//Table Columns to Db Fields mapping
$columns = array(
array('db' => 'Id','dt' => 0 ),
array('db' => 'Date','dt' => 1 ),
array('db' => 'OrderType','dt' => 2 ),
array('db' => 'Item','dt' => 3 ),
array('db' => 'Price','dt' => 4 ),
array('db' => 'Quantity','dt' => 5 ),
array('db' => 'Fee','dt' => 6)
);
// SQL server connection information
$sql_details = array(
'user' => 'ObscuredUserName',
'pass' => 'ObscuredPassword',
'db' => 'ObscuredSchemaName',
'host' => 'localhost');

require('ssp.class.php');
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

<?php > ``` ?>

allan · October 2016

I would recommend saving the SQL that the server-side script is creating into a debug file - about this point do something like:

file_put_contents(
  "/tmp/sql",
  "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit",
  FILE_APPEND
);

Then you can inspect the SQL that is being used to see why it thinks it is invalid. It might be something to do with the bindings.

If you can identify the parameters being submitted for the case when the error occurs, that would also help.

Allan

JordanCapitalIT@gmail.com · October 2016

What follows is the difference I find between to successive loads of my Ajax client page - nothing changes between one load and the next, I simply press F5 to refresh my browser and the same code that just succeeded then fails on refresh.

The value that the $_GET global variable (which is passed to the ssp.class.php script for query construction) resolves to changes from one load of the page to the next. The XHR request URL (which is what the $_GET variable is supposed to represent) is essentially identical from the successful page load to the failed one (see request URLs bottom of this post).

Good parms in $_GET :
https://1drv.ms/i/s!AuLPATBaON6ZjZct3OwA7eQokm4NMA

Invalid parms in $_GET:
https://1drv.ms/i/s!AuLPATBaON6ZjZcuZpFM4Q1yE_1q5Q

What could explain this? Thanks in advance for any ideas or advice.

Request URLs:

allan · October 2016

The fact that the two URLs are basically identical suggests to me that its a server-side resource issue. Perhaps it can't connect to the database because a connection pool has been fully used? Does the server's error logs not indicate anything?

Allan

JordanCapitalIT@gmail.com · October 2016

Changed the server side code to call SSP with the $_REQUEST superglobal instead of $_GET and everything works perfectly since then.

allan · November 2016

Super - glad to hear you've got it working now and thanks for posting back.

Allan

datatables 1.10.12 fails to compose Ajax request properly at random intervals with no apparent cause

datatables 1.10.12 fails to compose Ajax request properly at random intervals with no apparent cause

Answers

Howdy, Stranger!

Categories

DataTables

datatables 1.10.12 fails to compose Ajax request properly at random intervals with no apparent cause

datatables 1.10.12 fails to compose Ajax request properly at random intervals with no apparent cause

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables