Hardcoded MySQL function in path.inc [#1791366]

Error on every page of the website:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function substring_index(character varying, unknown, integer) does not exist LINE 1: SELECT DISTINCT SUBSTRING_INDEX(source, '/', 1) AS path FROM... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.: SELECT DISTINCT SUBSTRING_INDEX(source, '/', 1) AS path FROM {url_alias}; Array ( ) in drupal_path_alias_whitelist_rebuild() (line 380 of /var/www/projects/petrol/webdir/includes/path.inc).

While importing taxonomies with taxonomy_csv the batch process failed with this error. I disabled and removed the module but this error is still blocking my site.

Function in question: http://api.drupal.org/api/drupal/includes!path.inc/function/drupal_path_...

The SQL query includes a MySQL specific function ... and if we're supporting multiple databases this can not be done this way.

Drupal 7.14
PostgreSQL 9.1.3
PHP 5.3.6
Nginx 1.0.5

Comments

Comment #1

Putr CreditAttribution: Putr commented 21 September 2012 at 12:32

The folowing is a Quick Fix:

Replace {{SCHEMA}} with your schema name and run in DB:

CREATE OR REPLACE FUNCTION {{SCHEMA}}.substring_index(str text, delim text, count int)
RETURNS text AS $$
SELECT CASE WHEN $3 > 0 
THEN array_to_string((string_to_array($1, $2))[1:$3], $2)
ELSE array_to_string(ARRAY(SELECT unnest(string_to_array($1,$2))
                             OFFSET array_upper(string_to_array($1,$2),1) + $3),
                     $2)
END
$$ LANGUAGE sql;

Soruce: http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html

Edit: includes/path.inc
Change line 380 to (do not forget the schema):
$result = db_query("SELECT DISTINCT {{SCHEMA}}.SUBSTRING_INDEX(source, '/', 1) AS path FROM {url_alias}");

And reload your page.

Comment #3

22 September 2012 at 05:24

Version:

8.0.x-dev

» 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Comment #4

attiks CreditAttribution: attiks at Attiks commented 26 August 2016 at 12:15

Component:	postgresql database	» postgresql db driver
Priority:	Normal	» Critical
Issue summary:	View changes

I'm unable to run some updates on a drupal 7 site

Comment #5

amateescu CreditAttribution: amateescu for Pfizer, Inc. commented 26 August 2016 at 12:37

Status:	Active	» Postponed (maintainer needs more info)
Issue tags:	-Needs backport to D7

The substring_index() function is added to the Postgres driver in http://cgit.drupalcode.org/drupal/tree/includes/database/pgsql/install.i... (7.x) and http://cgit.drupalcode.org/drupal/tree/core/lib/Drupal/Core/Database/Dri... (8.x)

Comment #6

attiks CreditAttribution: attiks at Attiks commented 26 August 2016 at 13:03

Priority:	Critical	» Normal
Status:	Postponed (maintainer needs more info)	» Active

You're right!

Added to my update function, and all is fine, so probably a bad restore, not sure if this can be closed.

db_query('CREATE OR REPLACE FUNCTION "substring_index"(text, text, integer) RETURNS text AS
\'SELECT array_to_string((string_to_array($1, $2)) [1:$3], $2);\'
LANGUAGE \'sql\''
);