Hi Joseph,

Our developers are doing their best to keep improving the error handling messages, but I will pass on this feedback; thank you for reporting the issue.

When it comes to SQL Query based Tables, it can happen that some specific Query can work directly in the Database but not in our Plugin;

our Plugin is not the same as a DB Management Tool and is not meant to be used as one, since all our Queries go through our PHP&SQL Parser.

It also depends if you use our server-side processing enabled or disabled, that makes a big difference for how the Query gets sent and returned.

I will do my best to advise you in full details on this subject :

Our SQL Feature is not the same as a Database Management Tool, so for example - a specific Custom Query might work in your SQL Database directly but not in our SQL Feature of the Plugin, because we run everything through our PHP & SQL Parser, which has certain limitations.

We will do our best to advise.

On this documentation, there are more details which explain how our server-side processing works :

https://wpdatatables.com/documentation/table-features/server-side-processing/.

Basically, when the server-side option in a Table is enabled, the wpDataTables will give the search results through the Query;

So, our Plugin sends the Query to the database.

If that Query is formatted as

 "SELECT ...

 FROM ... 

WHERE... "

but after the "FROM" if it has any complex Query, as in your case, there can be errors;

as we mentioned, our SQL Query feature does not work in the same way as a Database Management Tool ( such as PHPMyAdmin and similar),

and is not meant to be used as one;

it is based on an SQL Parser which has limitations;

so it can happen that a specific complex Query might work in PhpMyAdmin but struggles to work through our server-side processing ( and the SQL Parser);

So when the Server-Side is enabled, our plugin sends a more complex SQL Query which in this case is too complicated for our Parser to handle,

instead of when the server-side is disabled, it just simply filters the data just by the values already seen in the column.

I hope that this helps to clarify everything, did my best to pass our developer Team's explanations.

So to solve this particular issue, you would need to either try and somehow simplify your SQL Query in order for our server-side processing to work;

or if you can make it work without server-side processing, if your data/number of rows of  the table does not become too large, let's say above 4, 5, 6 thousand rows,  and if the hosting server performs well,

you can 'get away' with disabling server-side on the table and see if that helps.

Here is how you can 'raise' the limit of rows for "server-side processing" toggle.

 If your SQL query based tables are not bigger than 2.000 rows, 

you can disable server-side on SQL tables, and it will work like it does for Excel tables. ( loads all rows regardless of pagination)

If you need to increase the row count while still having the "toggle" to disable server-side,

Please go to ../wp-content/plugins/wpdatatables/source/class.wpdatatable.php and around line 2176 you'll see this:

if (count($res_dataRows) > 2000) {

You can change that number to a value bigger than the number of rows in your table.

Same should be applied in ../wp-content/plugins/wpdatatables/source/class.wdtconfigcontroller.php on lines 53:

if (count($wpDataTable->getDataRows()) > 2000) {

And line 100:

if (count($wpDataTable->getDataRows()) > 2000) {

That will increase the server-side automatic limit.

Let us know if you manage to simplify the Query for our Parser, or if disabling Server-Side processing helps;

If none of that helps,

you can try preparing a MySQL view (which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like "SELECT * FROM view1″.

 If you need help with that, you can see our video, where we show an example of using View in our plugin.

I hope one of these methods might solve the issue.

Hi Joe,

Yes, I can see it works on the Page you sent, and I can completely understand what you mean.

Unfortunately, our PHP & SQL Parser can have an issue when a Query does not seem complex, but if you have some specific custom Query, there can be some operations or symbols in the syntax which can cause issues and also the major difference can happen when we compare if you enable server-side processing or disable it.

As mentioned earlier, the server-side adds an additional layer of processing and then our Plugin is dynamically changing/adding some syntax to the Query before it gets sent to the database and return the data.

Such as, for example the accent grave is being dynamically added to the table name as well as the semi colon symbol at the end of the Query, and so on...

It is very strange, though, as you wrote, if you are using exactly the same Query and it is intermittent like that, sometimes throws the parser error and sometimes not.

I believe that is not a bug per se, but our SQL Parser has certain limitations, which our developers plan to improve, but we can't say an ETA due to the possible compexity and changing of logic for it, etc.

- Try to avoid adding the accent graves ~ around the table name;

- See if you can modify calling of the columns, if it's possible to avoid using the alias (AS);

- If you can try removing the ORDER BY statement - If you need to have it, then it's best to disable sorting in our Table;

Or it is better to avoid ORDER BY from the Query, but you can rather set the "Use as default sorting column" on the Column you need.

- Also, if you use spaces in the Table name, I believe that could cause issues.

For example, in our new Manual table feature where we can set custom DB table name for them;

our devs did this tooltip which advises the DB table name can only contain letters, numbers and underscores and should not start with a number unless the DB prefix is included.

Not sure about that point, but just thought it is worth a shot to consider all possible 'variables' and eliminate as many that could potentially cause issues as possible.

Best is if you use underscores for Table or VIEW names, if possible.

- Check if it makes a difference when you toggle between server-side processing disabled or enabled.

- And lastly, if this Query runs in the DB management Tool without issues, did you try making an SQL VIEW, then calling it on our Table with a super simple Query just like : SELECT * FROM VIEWname?

I hope one of these details could help to potentially isolate what could be that 'difference' which causes those intermittent errors like that.