When i try to update apachesolr module on Drupal 6.19 with a postgres 8.4 database i have some warnings :

warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « ON » at character 479 in /var/www/html/gesteau/includes/database.pgsql.inc on line 139.
user warning: query: INSERT INTO drupal_apachesolr_search_page (page_id, label, description, env_id, search_path, page_title, settings) VALUES ('core_search', 'Core Search', 'Core Search', 'solr', 'search/site', 'Site', 'a:6:{s:29:"apachesolr_search_search_type";s:6:"custom";s:26:"apachesolr_search_per_page";i:10;s:24:"apachesolr_search_browse";s:6:"browse";s:28:"apachesolr_search_spellcheck";b:1;s:31:"apachesolr_search_not_removable";b:1;s:28:"apachesolr_search_search_box";b:1;}') ON DUPLICATE KEY UPDATE page_id = 'core_search', label = 'Core Search', description = 'Core Search', env_id = 'solr', search_path = 'search/site', page_title = 'Site', settings = 'a:6:{s:29:"apachesolr_search_search_type";s:6:"custom";s:26:"apachesolr_search_per_page";i:10;s:24:"apachesolr_search_browse";s:6:"browse";s:28:"apachesolr_search_spellcheck";b:1;s:31:"apachesolr_search_not_removable";b:1;s:28:"apachesolr_search_search_box";b:1;}' in /var/www/html/gesteau/sites/all/modules/contrib/apachesolr/apachesolr_search.module on line 393.

warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « ON » at character 465 in /var/www/html/gesteau/includes/database.pgsql.inc on line 139.
user warning: query: INSERT INTO drupal_apachesolr_search_page (page_id, label, description, env_id, search_path, page_title, settings) VALUES ('taxonomy_search', 'Taxonomy Search', 'Search all items with given term', '', 'taxonomy/term/%', '%value', 'a:5:{s:29:"apachesolr_search_search_type";s:3:"tid";s:26:"apachesolr_search_per_page";i:10;s:24:"apachesolr_search_browse";s:7:"results";s:28:"apachesolr_search_spellcheck";b:0;s:28:"apachesolr_search_search_box";b:0;}') ON DUPLICATE KEY UPDATE page_id = 'taxonomy_search', label = 'Taxonomy Search', description = 'Search all items with given term', env_id = '', search_path = 'taxonomy/term/%', page_title = '%value', settings = 'a:5:{s:29:"apachesolr_search_search_type";s:3:"tid";s:26:"apachesolr_search_per_page";i:10;s:24:"apachesolr_search_browse";s:7:"results";s:28:"apachesolr_search_spellcheck";b:0;s:28:"apachesolr_search_search_box";b:0;}' in /var/www/html/gesteau/sites/all/modules/contrib/apachesolr/apachesolr_search.module on line 393.

CommentFileSizeAuthor
#7 mysql-specific-queries-1866768-7.patch4.41 KBkevin.dutra
#4 mysql-specific-queries-1866768-4.patch4.01 KBkevin.dutra
#1 mysql-specific-queries-1866768-1.patch4.03 KBkevin.dutra

Comments

kevin.dutra’s picture

kevin.dutra commented
Title: upgrade apachesolr 6.x-1.2 to 6.x-3.0-rc1 on postgres » Multiple INSERT queries are MySQL specific
Version: 6.x-3.0-rc1 » 6.x-3.x-dev
Priority: Minor » Major
Status: Active » Needs review
StatusFileSize
new mysql-specific-queries-1866768-1.patch4.03 KB

Indeed. "ON DUPLICATE KEY UPDATE" is a MySQL specific clause for insert. To be valid for a non-MySQL database, the code should first check for the existence of the row and insert or update accordingly.

Here's a patch that utilizes drupal_write_record() to properly do any insertions or updates.

pwolanin’s picture

pwolanin commented

might be better to SELECT 1 instead of SELECT COUNT(*)?

pwolanin’s picture

pwolanin commented

Also, maybe this should be ported/applied to 7.x too so as to bring the 2 versions into closer alignment?

kevin.dutra’s picture

kevin.dutra commented
StatusFileSize
new mysql-specific-queries-1866768-4.patch4.01 KB

In 7.x-1.x, the equivalent spots use db_merge(), rather than a direct db_query(). My $0.02 is that it would be better to stick to the D7 database layer standards than to try and make those segments look similar between the versions.

Here's an updated patch using SELECT 1.

acbramley’s picture

acbramley commented

Can confirm that patch #4 fixes all errors on postgres upon installing module, was also getting them on the apachesolr settings page which are all gone too! Thanks

acbramley’s picture

acbramley commented

Woops, I lied, on my index admin page (/admin/settings/apachesolr/settings/solr/index) I get the following errors:

warning: pg_query(): Query failed: ERROR: column "aie.entity_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 5: ....changed <= 0) AND (aie.entity_id > 0))) ORDER BY aie.entity... ^ in /home/adam/projects/drupal-6/includes/database.pgsql.inc on line 138.
user warning: query: SELECT count(*) FROM apachesolr_index_entities_node aie WHERE (aie.bundle IN ('page','story')) AND (aie.status = 1) AND ((aie.changed > 0) OR ((aie.changed <= 0) AND (aie.entity_id > 0))) ORDER BY aie.entity_id ASC in /home/adam/projects/drupal-6/sites/all/modules/contrib/apachesolr/apachesolr.index.inc on line 145.
kevin.dutra’s picture

kevin.dutra commented
StatusFileSize
new mysql-specific-queries-1866768-7.patch4.41 KB

The error message is a little odd, considering there is no GROUP BY clause in that query, but that aside why don't you try this patch. This removes the ORDER BY clause in that query, which doesn't add any value since it's just a count query.

pwolanin’s picture

pwolanin commented

looks like a reasonable change, but needs verification