I was upgrading 6.26 -> 7.14 with postgresql 9.1.3 when the following error occured during the core upgrade:

Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: bytea ~~* unknown LINE 4: WHERE (uid > '-1') AND (data ILIKE '%s:5:"block";%') ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT u.uid AS uid, u.data AS data FROM {users} u WHERE (uid > :db_condition_placeholder_0) AND (data ILIKE :db_condition_placeholder_1) ORDER BY uid ASC LIMIT 100 OFFSET 0; Array ( [:db_condition_placeholder_0] => -1 [:db_condition_placeholder_1] => %s:5:"block";% ) in update_fix_d7_block_deltas() (line 426 of /usr/local/www/drupal6-dev/includes/update.inc).

This is the actual SQL Query from the pg log:

SELECT u.uid AS uid, u.data AS data FROM  users u WHERE  (uid > '-1') AND (data ILIKE '%s:5:"block";%')  ORDER BY uid ASC
LIMIT 100 OFFSET 0

If i change ILIKE to LIKE it works, but i don't understand what needs to be changed in the drupal upgrade code.
There was a similar case: http://drupal.org/node/1202690.

Files: 
CommentFileSizeAuthor
#21 0001-1575790-by-greg.1.anderson-and-sun-cast-fields-to-te.patch1.4 KBgreg.1.anderson
PASSED: [[SimpleTest]]: [MySQL] 39,324 pass(es).
[ View ]
#18 drupal8.postgres-bytea-ilike.16.patch1.19 KBsun
PASSED: [[SimpleTest]]: [MySQL] 39,743 pass(es).
[ View ]
#17 drupal8.postgres-bytea-ilike.15.patch1.19 KBsun
PASSED: [[SimpleTest]]: [MySQL] 39,745 pass(es).
[ View ]
#13 drupal-1575790-13.patch1.26 KBtim.plunkett
FAILED: [[SimpleTest]]: [MySQL] 36,996 pass(es), 62 fail(s), and 37 exception(s).
[ View ]
#9 0001-1575790-8.x-by-greg.1.anderson-cast-fields-to-text-when.patch1.64 KBgreg.1.anderson
PASSED: [[SimpleTest]]: [MySQL] 37,258 pass(es).
[ View ]
#8 0001-1575790-7.x-by-greg.1.anderson-cast-fields-to-text-when.patch1.56 KBgreg.1.anderson
PASSED: [[SimpleTest]]: [MySQL] 39,082 pass(es).
[ View ]
#7 0001-1575790-by-greg.1.anderson-cast-fields-to-text-when-.patch1.55 KBgreg.1.anderson
PASSED: [[SimpleTest]]: [MySQL] 39,084 pass(es).
[ View ]

Comments

hixster’s picture

I just experienced a similar issue upgrading form 7.01:

Failed: PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: bytea ~~* unknown LINE 4: WHERE (uid > '-1') AND (data ILIKE '%s:5:"block";%') ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT u.uid AS uid, u.data AS data FROM {users} u WHERE (uid > :db_condition_placeholder_0) AND (data ILIKE :db_condition_placeholder_1) ORDER BY uid ASC LIMIT 100 OFFSET 0; Array ( [:db_condition_placeholder_0] => -1 [:db_condition_placeholder_1] => %s:5:"block";% ) in update_fix_d7_block_deltas() (line 426 of /Applications/MAMP/htdocs/xxxxxx/includes/update.inc).

How can I fix this?

ij’s picture

I just hit this issue today as well when upgrading a site from d6 to d7. I already migrated other sites weeks before (with 7.12 and prior) and didn't hit this issue.

Anyway, after editing /usr/share/drupal7/includes/database/pgsql/database.inc and replaced ILIKE by LIKE in line 158 the update.php ran just fine:

   // Function calls not allowed in static declarations, thus this method.
    if (!isset($specials)) {
      $specials = array(
        // In PostgreSQL, 'LIKE' is case-sensitive. For case-insensitive LIKE
        // statements, we need to use ILIKE instead.
        'LIKE' => array('operator' => 'ILIKE'),
        'NOT LIKE' => array('operator' => 'NOT ILIKE'),
      );
    }

The line beginning with LIKE needs to be changed. Definitely only a bad workaround and not a good solution.

hixster’s picture

@ij - thanks for that suggestion, it fixed my issue :-)

frega’s picture

The problem seems rooted to the automatic mapping of the LIKE operator to ILIKE (which intends to harmonize "LIKE"-behaviour across db-engines, includes/database/pgsql/database.inc:150, DatabaseConnection_pgsql::mapConditionOperator).

ILIKE fails on bytea fields (blobs?). The failing query "does an ILIKE" on the data column of the users-table. Relevant line in includes/update.inc:423

->condition('data', '%' . db_like(serialize('block')) . '%', 'LIKE')

Which gets rewritten to ILIKE in case of Postgres.

As DatabaseConnection_pgsql::mapConditionOperator uses a static variable it's - afaics - easily overriden; instead probably there needs to be a switch update_fix_d7_block_deltas for pgsql.

christoph’s picture

Hi,

I'm experiencing a similar issue on an upgrade of the menu block (menu_update_7002 which calls update_fix_d7_block_deltas). This generates the query:-
SELECT u.uid AS uid, u.data AS data FROM users u WHERE  (uid > '-1') AND (data ILIKE '%s:5:"block";%') ORDER BY uid ASC LIMIT 100 OFFSET 0;

What is strange is that when running this query inside pgsql (Postgres command line) there is no error, but when running from Drupal upgrade there is. I think the query is the same hitting the database - that is what appears in the logs. The 'data' column is of type 'text' - switching it to varchar didn't work.

The fix above worked (#2 and #4 changing ILIKE back to LIKE in includes/database/pgsql/database.inc Line 158). I note there are a number of other mentions of this issue (but seemingly not exactly the same)
http://drupal.org/node/1538638
http://drupal.org/node/1202690

Not sure what to suggest as the comment in the code is "In PostgreSQL, 'LIKE' is case-sensitive. For case-insensitive LIKE statements, we need to use ILIKE instead." - perhaps that code needs to check if ILIKE is supported by PostgreSQL for that particular type - or do some sort of casting.

jaredsmith’s picture

I too am seeing this on an upgrade from 6.x to 7.14, but didn't encounter it before on earlier test upgrades to 7.12.

A less hackish way of making this work is to actually convert the bytea field (the data field in this query) using the convert_to string function.

SELECT u.uid AS uid, u.data AS data
FROM
users u
WHERE (uid > '-1') AND (convert_from(data,'UTF8') ILIKE '%s:5:"block";%')
ORDER BY uid ASC
LIMIT 100 OFFSET 0

I'm not sure how to do this in the database layer in Drupal, however. In essence the logic would be something like "If the column being evaluated by ILIKE is of type bytea, then wrap it in convert_from".

Thoughts? Concerns? Can someone help me get that logic into includes/database/pgsql/database.inc?

greg.1.anderson’s picture

Status:Active» Needs work
StatusFileSize
new1.55 KB
PASSED: [[SimpleTest]]: [MySQL] 39,084 pass(es).
[ View ]

convert_from(data,'UTF8') did not work for me; however, typecasting to text via data::text seems to do the trick. I am working on a patch, but it is not perfect yet, as it fails on NOT LIKE / NOT ILIKE. Posting a work-in progress for reference; will update this with a working version when ready.

Note also that Drupal does not have a good mechanism for checking the data type of the field being operated on in the LIKE expression; this patch invariantly casts to text, which should be innocuous (e.g. for fields that are already in text format).

greg.1.anderson’s picture

Status:Needs work» Needs review
StatusFileSize
new1.56 KB
PASSED: [[SimpleTest]]: [MySQL] 39,082 pass(es).
[ View ]

ILIKE, or NOT ILIKE, that is the question that this patch addresses. The updated regex allows a postgres d6->d7 upgrade to complete without problem.

greg.1.anderson’s picture

Version:7.14» 8.x-dev
StatusFileSize
new1.64 KB
PASSED: [[SimpleTest]]: [MySQL] 37,258 pass(es).
[ View ]

The Drupal-8.x patch is the same, save for the fact that the modified class moved.

greg.1.anderson’s picture

c.f. #1518506: Normalize how case sensitivity is handled across database engines - might be better to go that route, and skip preg_replace-ing the query.

ogi’s picture

I hit the issue by upgrading an old Drupal 7.x-dev to Drupal 7.14 with PostgreSQL. After applying the patch in comment 8, the update has finished its job.

greg.1.anderson’s picture

Priority:Normal» Critical
Status:Needs review» Reviewed & tested by the community
Issue tags:+7x upgrade

I did look into what it would take to do #1518506: Normalize how case sensitivity is handled across database engines; Drupal does not yet have the hooks needed to implement some of these adjustments. Adding these to d8 and then backporting to d7 would take some time. While the preg_replace solution above is not ideal, it is my hope that we could consider committing it as an interim solution, so that the postgres upgrade path does not remain broken while the final solution is being implemented.

Upgrading to critical since it affects the upgrade path, and setting rtbc per #11.

tim.plunkett’s picture

StatusFileSize
new1.26 KB
FAILED: [[SimpleTest]]: [MySQL] 36,996 pass(es), 62 fail(s), and 37 exception(s).
[ View ]

Rewrapped the comments to 80 characters.

Status:Reviewed & tested by the community» Needs work

The last submitted patch, drupal-1575790-13.patch, failed testing.

sun’s picture

Status:Needs work» Reviewed & tested by the community
Issue tags:-7x upgrade+PostgreSQL, +upgrade path, +D7 upgrade path
StatusFileSize
new1.19 KB
PASSED: [[SimpleTest]]: [MySQL] 39,745 pass(es).
[ View ]

I've rewritten and clarified the comment. No other changes.

This is a major hack, to be honest. But at the same time, it doesn't look like we're able to resolve this in any other way, since we don't have ORM, so essentially we don't know which queries are affected and need to be adjusted.

Therefore, this looks good to go.

sun’s picture

StatusFileSize
new1.19 KB
PASSED: [[SimpleTest]]: [MySQL] 39,743 pass(es).
[ View ]

Sorry, fixing the past-tense error.

catch’s picture

Version:8.x-dev» 7.x-dev
Status:Reviewed & tested by the community» Patch (to be ported)

OK this is a serious hack but I also don't remotely have a better idea how to fix it, and we shouldn't hold it up on #1518506: Normalize how case sensitivity is handled across database engines since that's very unlikely to be backportable. Committed/pushed to 8.x, moving to 7.x for backport.

drumm’s picture

7002's LIKE actually would be better as case-sensitive. (Case-insensitive is okay. The LIKE check is to cut down on the number of rows that need to be unserialized for the real check, is_string($menu_link['options']['query']). Another approach might be to allow turning off this rewriting.

greg.1.anderson’s picture

Status:Patch (to be ported)» Needs review
StatusFileSize
new1.4 KB
PASSED: [[SimpleTest]]: [MySQL] 39,324 pass(es).
[ View ]

Here is a backport of #18 for Drupal 7, basically #8 re-rolled for 7.x-dev HEAD and updated with the change thru #18.

This problem cannot be fixed by changing 7002's ILIKE to LIKE, as Drupal will still rewrite the LIKE to ILIKE for Postgres, even if you do not need case-insensitivity for the given operation. If there was some other way to write 7002, even if it was somewhat slower (i.e., just take out the ILIKE and and process all rows), that certainly would be an option for this particular symptom. However, since Drupal does in general rewrite LIKE to ILIKE for Postgres, and since that will in general cause problems with queries on certain field types, this fix has a potentially larger scope than just the 7002 menu update. I would therefore recommend that we stick with this workaround (and I agree, it is a serious hack) until a better solution can be implemented in #1518506: Normalize how case sensitivity is handled across database engines. Otherwise, I think it too likely that this bug would keep cropping up in other areas as LIKE/ILIKE operations on blobs are introduced in Drupal.

drumm’s picture

Agreed.

I haven't run the numbers to know how much slower it is, but for Drupal.org's DB, the difference is running unserialize() 10,440 times vs once.

Status:Needs review» Needs work

The last submitted patch, 0001-1575790-by-greg.1.anderson-and-sun-cast-fields-to-te.patch, failed testing.

greg.1.anderson’s picture

Casual inspection of the simpletest log leads me to suspect that the test failure in #21 is unrelated to this patch.

drumm’s picture

Status:Needs work» Needs review
aspilicious’s picture

I'm going to retest this again... Other tests have more than 40 000 assertions.

EDIT: ow this is only postgres stuff... Strange...

aspilicious’s picture

dgv’s picture

Otherwise, I think it too likely that this bug would keep cropping up in other areas as LIKE/ILIKE operations on blobs are introduced in Drupal

The root cause of this problem is that ILIKE on a blob should be a non-starter. Why would anyone introduce that? ILIKE is an operator that implies character interpretation and the whole point of a blob is to be a bunch of bytes without any interpretation/validation of the contents.

On a D6/postgres install, I see that {users}.data used to be of text type, which is what one would expect for serialized contents.
Now at some point with D7 the type has become bytea, which leads to this specific query failing. No idea why this type-switch decision was made, but this is inconsistent with still trying to apply text operators to this field. Maybe the implications of this were overlooked at the time? Could this decision be reviewed and reconsidered?

In the queries, technically, it may work to convert bytea on the fly to text with convert_from(...,'UTF8') or by a CAST, but only in the cases where it contains cleanly encoded text, which strikes me as contradicting the decision to switch it to bytea in the first place.

greg.1.anderson’s picture

Re: #19, I think this is already recognized, and is being worked on in D8 at #1518506: Normalize how case sensitivity is handled across database engines.

andypost’s picture

Status:Needs review» Reviewed & tested by the community

It's hack but it works. Let's get this in and close the critical.
Not sure that follow-up #1518506: Normalize how case sensitivity is handled across database engines could make it better

webchick’s picture

Status:Reviewed & tested by the community» Fixed

Ick. :\ But as catch said, we're unlikely to figure out a better fix that's also back-portable.

Committed and pushed to 7.x. Thanks a lot; great to see this closed out.

We should probably mention this in the release announcement.

dgv’s picture

What is not so great is to not recognize that text pattern matching should not be used to search inside binary fields in the database. Binary is bytes, if you want find something in there, search for bytes, not characters.

i.e. this comparison did not make much sense in the first place:
(data ILIKE :db_condition_placeholder_1)
given that data is of binary type, because there is no guarantee that it can be interpreted as a valid sequence of characters.

The clean/correct comparison for binary contents that will always work would be this with PostgreSQL:
position('somebytes'::bytea in data)>0

But it's not even clear that's what this issue is really about. Because when the caller code searches for the substring s:5:"block"; in data, it somehow considers that this column is of text type, which it was at some point, but no longer, and this datatype confusion looked more like the original cause of the problem (see comment #28)

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

David_Rothstein’s picture

Issue tags:+7.17 release notes

Drupal 7.16 was a security release only, so this issue is now scheduled for Drupal 7.17 instead.

Fixing tags accordingly.

Also, I invented the "release announcement" tag for issues that require special mention even for non-technical users (i.e., in the release announcement itself, rather than just in the technical release notes).... So the idea is that's mainly for very rare issues that might have social/community implications (such as #1036780: Drupal.org should collect stats on enabled sub-modules and core modules). Of course, I never said that anywhere except in a comment buried in that issue, so no one but me understood what that tag is intended for :) Given that, though, I'm moving this one over to the more normal "release notes" tag as well.