Not working for Big Database

Maxxxel

I got a database with 738.600 Rows and 20 ( + 1 uniqueID) columns.
This is my app.js and the error is "invalid JSON response".
If i manually set the LIMIT in the simple function from ssp.class.php to e.g. 0, 10000 i get data displayed, non paginated and non filterable, 10000 on one site.

If i manually open my webiste + getData.php i see blank page.

$(document).ready(function() {
  $('#ItemList').DataTable({
    "processing": true,
    "serverSide": true,
    "paging": true,
    "ajax": {
      "url": '../getData.php',
      "dataSrc": 'data', //data because ssp.class.php adds data to it
      "type": 'POST'
    },
    "columns": [
        { "data": 'Held' },
        { "data": 'Waffe' },
        { "data": 'Schild' },
        { "data": 'Ring' },
        { "data": 'Amulett' },
        { "data": 'Mantel' },
        { "data": 'Off' },
        { "data": 'OffT' },
        { "data": 'Def' },
        { "data": 'DefT' },
        { "data": 'Hybrid' },
        { "data": 'HybridT' },
        { "data": 'DefA' },
        { "data": 'DefAT' },
        { "data": 'OffA' },
        { "data": 'OffAT1' },
        { "data": 'OffAT2' },
        { "data": 'HybridA' },
        { "data": 'HybridAT1' },
        { "data": 'HybridAT2' },
        { "data": 'ExtraTruppen' }
      ]
  });
});

And here my simple getData.php

<?php
    require 'ssp.class.php';

    $table = 'TABLENAME';
    $primaryKey = 'ID';
    $columns = array(
        array( 'db' => 'Held', 'dt' => 'Held' ),
        array( 'db' => 'Waffe', 'dt' => 'Waffe' ),
        array( 'db' => 'Schild', 'dt' => 'Schild' ),
        array( 'db' => 'Ring', 'dt' => 'Ring' ),
        array( 'db' => 'Amulett', 'dt' => 'Amulett' ),
        array( 'db' => 'Mantel', 'dt' => 'Mantel' ),
        array( 'db' => 'Off', 'dt' => 'Off' ),
        array( 'db' => 'OffT', 'dt' => 'OffT' ),
        array( 'db' => 'Def', 'dt' => 'Def' ),
        array( 'db' => 'DefT', 'dt' => 'DefT' ),
        array( 'db' => 'Hybrid', 'dt' => 'Hybrid' ),
        array( 'db' => 'HybridT', 'dt' => 'HybridT' ),
        array( 'db' => 'DefA', 'dt' => 'DefA' ),
        array( 'db' => 'DefAT', 'dt' => 'DefAT' ),
        array( 'db' => 'OffA', 'dt' => 'OffA' ),
        array( 'db' => 'OffAT1', 'dt' => 'OffAT1' ),
        array( 'db' => 'OffAT2', 'dt' => 'OffAT2' ),
        array( 'db' => 'HybridA', 'dt' => 'HybridA' ),
        array( 'db' => 'HybridAT1', 'dt' => 'HybridAT1' ),
        array( 'db' => 'HybridAT2', 'dt' => 'HybridAT2' ),
        array( 'db' => 'ExtraTruppen', 'dt' => 'ExtraTruppen' )
    );

    $sql_details = array(
        'user' => 'user',
        'pass' => 'pass',
        'db'   => 'DATABASE',
        'host' => 'localhost'
    );
    
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );
?>

allan

This is my app.js and the error is "invalid JSON response".

What is the server returning if it isn't valid JSON. The tech note that the error message links to will tell you how to find that out.

Allan

Maxxxel

Technote 1: did all the steps..

In console on firefox it does the ajax and nothing more no errors etc.
If i click on the ajax post i can see the send informations, if i check recieve its just empty. The request takes 5000 - 21000ms till the browser stops loading and showing a white page. I had the same issue on ngTable because it want to get all data at once. Another 8year old pagination table script loaded all the data.

EDIT: It seems to stop at ssp.class.php:236-242 at $data generation in simple()
i echo'ed $limit, $order and $where and all 3 are NULL so the SQL operation would be a simple get ALL from database or a FAIL cuz of the NULL.
MySQL: get 738,760 data in 0.0024 secs.
So this is the bottleneck i guess.

Echo'ed SQL command:

`SELECT `Held`, `Waffe`, `Schild`, `Ring`, `Amulett`, `Mantel`, `Off`, `OffT`, `Def`, `DefT`, `Hybrid`, `HybridT`, `DefA`, `DefAT`, `OffA`, `OffAT1`, `OffAT2`, `HybridA`, `HybridAT1`, `HybridAT2`, `ExtraTruppen` FROM `HobbitItemList` null `

Try yourself: sanctuments.16mb.com/getData.php
here the head of the recieve:

HTTP/1.1 200 OK
Date: Fri, 03 Feb 2017 13:14:36 GMT
Server: ApacheX-Powered-By: PHP/5.6.21
Access-Control-Allow-Origin: *
Content-Length: 0
Content-Type: text/html; charset=UTF-8
Connection: keep-alive

Request:

POST /getData.php HTTP/1.1
Host: sanctuments.16mb.com
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
Accept: application/json, text/javascript, */*; q=0.01
Accept-Language: de,en-US;q=0.7,en;q=0.3
Accept-Encoding: gzip, deflate
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
X-Requested-With: XMLHttpRequest
Referer: http://sanctuments.16mb.com/MegaItemListe.php
Content-Length: 4633
Cookie: PHPSESSID=42ce19a522df954bd2bd4010b78b981c
Connection: keep-alive
Cache-Control: max-age=0

POST

draw=1
columns[0][data]=Held
columns[0][name]
columns[0][searchable]=true
columns[0][orderable]=true
columns[0][search][value]
columns[0][search][regex]=false
columns[1][data]=Waffe
columns[1][name]
columns[1][searchable]=true
columns[1][orderable]=true
columns[1][search][value]
columns[1][search][regex]=false
columns[2][data]=Schild
columns[2][name]
columns[2][searchable]=true
columns[2][orderable]=true
columns[2][search][value]
columns[2][search][regex]=false
columns[3][data]=Ring
columns[3][name]
columns[3][searchable]=true
columns[3][orderable]=true
columns[3][search][value]
columns[3][search][regex]=false
columns[4][data]=Amulett
columns[4][name]
columns[4][searchable]=true
columns[4][orderable]=true
columns[4][search][value]
columns[4][search][regex]=false
columns[5][data]=Mantel
columns[5][name]
columns[5][searchable]=true
columns[5][orderable]=true
columns[5][search][value]
columns[5][search][regex]=false
columns[6][data]=Off
columns[6][name]
columns[6][searchable]=true
columns[6][orderable]=true
columns[6][search][value]
columns[6][search][regex]=false
columns[7][data]=OffT
columns[7][name]
columns[7][searchable]=true
columns[7][orderable]=true
columns[7][search][value]
columns[7][search][regex]=false
columns[8][data]=Def
columns[8][name]
columns[8][searchable]=true
columns[8][orderable]=true
columns[8][search][value]
columns[8][search][regex]=false
columns[9][data]=DefT
columns[9][name]
columns[9][searchable]=true
columns[9][orderable]=true
columns[9][search][value]
columns[9][search][regex]=false
columns[10][data]=Hybrid
columns[10][name]
columns[10][searchable]=true
columns[10][orderable]=true
columns[10][search][value]
columns[10][search][regex]=false
columns[11][data]=HybridT
columns[11][name]
columns[11][searchable]=true
columns[11][orderable]=true
columns[11][search][value]
columns[11][search][regex]=false
columns[12][data]=DefA
columns[12][name]
columns[12][searchable]=true
columns[12][orderable]=true
columns[12][search][value]
columns[12][search][regex]=false
columns[13][data]=DefAT
columns[13][name]
columns[13][searchable]=true
columns[13][orderable]=true
columns[13][search][value]
columns[13][search][regex]=false
columns[14][data]=OffA
columns[14][name]
columns[14][searchable]=true
columns[14][orderable]=true
columns[14][search][value]
columns[14][search][regex]=false
columns[15][data]=OffAT1
columns[15][name]
columns[15][searchable]=true
columns[15][orderable]=true
columns[15][search][value]
columns[15][search][regex]=false
columns[16][data]=OffAT2
columns[16][name]
columns[16][searchable]=true
columns[16][orderable]=true
columns[16][search][value]
columns[16][search][regex]=false
columns[17][data]=HybridA
columns[17][name]
columns[17][searchable]=true
columns[17][orderable]=true
columns[17][search][value]
columns[17][search][regex]=false
columns[18][data]=HybridAT1
columns[18][name]
columns[18][searchable]=true
columns[18][orderable]=true
columns[18][search][value]
columns[18][search][regex]=false
columns[19][data]=HybridAT2
columns[19][name]
columns[19][searchable]=true
columns[19][orderable]=true
columns[19][search][value]
columns[19][search][regex]=false
columns[20][data]=ExtraTruppen
columns[20][name]
columns[20][searchable]=true
columns[20][orderable]=true
columns[20][search][value]
columns[20][search][regex]=false
order[0][column]=0
order[0][dir]=asc
start=0
length=10
search[value]
search[regex]=false

allan

if i check recieve its just empty

That's fine. An empty response is invalid JSON. The next step would be to check the server's error logs.

: It seems to stop at ssp.class.php:236-242 at $data generation in simple()

With what error?

Allan

Maxxxel

no errors, its simply the last thing he does, if i do an echo after the function the echo wont be printed. maybe timeout from server and script keeps waiting? because for 10k data it takes 5-10sec if he tries to get the ~800.000 it would take minutes..

server error logs where to find im new to web

allan

server error logs where to find

Depends on the server. For apache on *nix it would normally be /var/log/apache2/error_log. But you'd need to check your server's configuration.

It might be worth trying to echo out the SQL that is being run to make sure it looks sensible.

Allan

Maxxxel

In my 2nd Post you can see the SQL that is echo'ed.
the NULL at the end makes problems also that there is no limit, i try check server log..

EDIT: i made 2 requests and its always the same error.
AS i thought the Scripts requests to much at once because the $LIMIT is unset

[03-Feb-2017 18:53:26 UTC] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in /home/u773581450/public_html/ssp.class.php on line 434
[03-Feb-2017 18:53:26 UTC] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 65536 bytes) in Unknown on line 0
[03-Feb-2017 18:54:40 UTC] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in /home/u773581450/public_html/ssp.class.php on line 434
[03-Feb-2017 18:54:40 UTC] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 65536 bytes) in Unknown on line 0

I can give you full Access to DB/Scripts or TeamViewer if u want to its 0 sensible xD

allan

Sorry I missed the SQL. That isn't a server-side processing statement since there is no limit.

'../getData.php'

Does that actually implement server-side processing.

I can give you full Access to DB/Scripts or TeamViewer if u want to its 0 sensible xD

That would be covered under the priority support options.

Allan

Maxxxel

My first post contains the getData.php tell me what is wrong as i cant find anything

Or has it something to do with the php memory limit of my hoster. It is set to 128Mb and the complete databse got 235MB? But that cant be as sone 8 year old script can handle it to create a sinple table with 3400 sites of paginating.

tangerine

Clearly your PHP script is running out of memory.
Either use a smaller dataset for debugging, or increase available memory with ini_set().
Also (A) try your query in PhpMyAdmin, and (B) check your error log as already advised.

allan

SSP::simple( $_GET,

But:

"type": 'POST'

You are POSTing the data, and then not using it! Change the $_GET to $_POST.

Allan

Maxxxel

Omg thank you that was the problem now i need to get style in it and custom filtering and it will be perfect.

pagolina

Have being battling with this for days, my ssp return fine if the record in my table is within 10, but for a table of 500 and above the following line return blank

SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )

I suspected the post parameters but i return it and they where completely ok,
I also echo $request ($request['start']) and it was live on ssp_class.php, so in my own case i dont think limit or where should be null, please i need help, what could be responsible?

allan

Sounds like you have error reporting disabled in PHP. I'd suggest checking the server's error logs.

You could possibly also use:

error_reporting(E_ALL);
ini_set('display_errors', '1');

to enable error reporting in your file.

Allan

pagolina

Resolved, json_encode return blank because of invalid json, i just added charset=utf8 to my pdo connection and all was fine

carlito27

Hey @pagolina, can you please explain exactly where you added the line? I believe I'm having the exact same issue but I'm not sure where to add the charset=utf8. I'm guessing it's somewhere near the following line..

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, )
);

Let me know thanks

colin

This page might help, it's showing where to set it.

Colin