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 :
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.
However there are some points that are not relevant to my issue.
1. the SQL is not complex:
SELECT TeamType as `Team cat`, `Team pos`, `Pos in team`, `Team name`, Name, Gender, ClubName as `Club`, `Overall Pos`, `Finish Time`, `Team time` FROM `Showground Relays 2024 Results` ORDER BY TeamType, `Team Pos`, cast(`Pos in team` as int)
Even a very simple parser should expect these very common operations.
2. The above SQL worked in 2023 which is what I cloned it from. The only difference was the table name. The table itself contains the same structure.
3. It happens with almost every table that I try to create.
...and has been happening for years.
4. I can make it work,by changing the SQL slightly, then changing it back, or closing the page and re-opening. It can take minutes or hours and drives me crazy. Sometimes I get the above error but it worked anyway!
If the issue was consistent (i.e. the same input produced the same output, every time) then none of the above would be true. I would be able to use a clone of the table and it would work as before.
Scroll down to Showground Relays 2024 Results (Individuals)
and you will see that it is finally working. However it has taken many frustrating hours to make it work, and I still do not know why it was not working, or why it now is working. There is a bug somewhere.
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.
This has been an occasional issue for years but lately I can't get around it:
I usually circumvent it by cloning another table and starting again but no good any more.
The table runs a query on MySql. The SLQ parses OK on PhpMyAdmin so it is
OK. Could you please also make your error messages more helpful?
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:
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:
And line 100:
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.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Miloš.
Thank you for your thorough reply.
However there are some points that are not relevant to my issue.
1. the SQL is not complex:
SELECT TeamType as `Team cat`, `Team pos`, `Pos in team`, `Team name`, Name, Gender, ClubName as `Club`, `Overall Pos`, `Finish Time`, `Team time`
FROM `Showground Relays 2024 Results`
ORDER BY TeamType, `Team Pos`, cast(`Pos in team` as int)
Even a very simple parser should expect these very common operations.
2. The above SQL worked in 2023 which is what I cloned it from. The only difference was the table name. The table itself contains the same structure.
...and has been happening for years.
4. I can make it work,by changing the SQL slightly, then changing it back, or closing the page and re-opening. It can take minutes or hours and drives me crazy. Sometimes I get the above error but it worked anyway!
If the issue was consistent (i.e. the same input produced the same output, every time) then none of the above would be true. I would be able to use a clone of the table and it would work as before.
The above error is happening right now! However the table produces the correct output. See:
https://jcracetiming.uk/showground-relays-2024-results/
Scroll down to
and you will see that it is finally working. However it has taken many frustrating hours to make it work, and I still do not know why it was not working, or why it now is working. There is a bug somewhere.
Kind regards
Joe
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.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables