Database Query Builder cannot do paged query on MySQL

[jellederijke]

Hi there Wappler support,

I recently found an issue with the simple query builder. When I want to do a paged record it creates this query:

SELECT *
FROM aansluitingen
FETCH NEXT 5 ROWS ONLY

But I get this error:

If I do custom query and change the FETCH NEXT 5 ROWS ONLY
to LIMIT 5 I get no error and it just works.

This is using the latest Wappler version and connected to a DO managed db. MySQL, using a legacy user account for the db.

Happy to provide with more details, but I suspect this is easily reproducable.

Thanks for your support!

bg Jelle

[Teodor]

@jellederijke

recently found an issue with the simple query builder. When I want to do a paged record it creates this query:

SELECT *
FROM aansluitingen
FETCH NEXT 5 ROWS ONLY

But I get this error:

Could you please show a screenshot of the query you created in the query builder?

[jellederijke]

Thank you for the swift response, @Teodor.

[Teodor]

Have you added any parameters in the Parameters tab?

[jellederijke]

No this is a brand new query I just added for this bug/issue report. So nothing else except the screenshot I made.

[patrick]

I assume the error you get is when trying to run it within the Wappler UI. Can you test if you also getting the error when running the server action.

[jellederijke]

Thank you for chiming in, @patrick.

This is the message I get on running the server action

[jellederijke]

This is a php project, btw. I found this Stackoverflow

PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client

I also get this error when I run the server action with the custom query, so with LIMIT 5 instead of FETCH x rows only.

This custom query does not give an error in the UI.

Server actions that I created earlier on tis project that have queries on the same managed database instance and same credentials (with a different db name) give no errors. Haven’t tried chaning those queries, because I don’t want to mess up something that is working right now.

EDIT: I noticed that my credentials in Workflows>globals>Database connections where not the same. So I changed those. This did fix the error in executing the server action. But I still get the SQL syntax error in the UI.

[patrick]

The parser in Wappler generates SQL according to the ISO standard and not database specific. We will improve this in an upcoming Wappler update to generate correct SQL based on your database connection.

[George]

have you entered manually the offset and limit values in the pages action, like:

those usually point to $_GET values are not counted with the query

[Teodor]

Fixed in Wappler 5.4.3