The current implementation of certain database queries in the node_access_rebuild_progressive module uses raw SQL that is not compatible with PostgreSQL. Specifically, the LIMIT clause syntax and subqueries in the DELETE statement need to be updated to ensure compatibility across both MySQL and PostgreSQL databases.
1. Set up a Drupal site with PostgreSQL as the database.
2. Install and enable the node_access_rebuild_progressive module.
3. Run drush node-access-rebuild-progressive
4. Observe the SQL errors related to the LIMIT clause:
SQLSTATE[42601]: Syntax error: 7 ERROR: LIMIT #,# syntax is not supported
LINE 1: SELECT nid FROM "node" ORDER BY nid DESC LIMIT 0,1
^
HINT: Use separate LIMIT and OFFSET clauses.: SELECT nid FROM "node" ORDER BY nid DESC LIMIT 0,1;Rewrite DB queries to use Drupal's Database API, which abstracts the differences between MySQL and PostgreSQL:
None.
None.
None.
Fixed
2.0
Code
Particularly affects sites running on the PostgreSQL database.
Automatically closed - issue fixed for 2 weeks with no activity.
I was receiving a similar error with MariaDB 10.4
This patch fixes the error.
If this helps anyone.