PostgreSQL incompatible with Storage API

Thanks for the report.

Feel free to submit patches or identify why it isn't incompatible. I myself don't use Postgre so it isn't something I can fix.

jm.federico: What did you do to provoke the crash, and what error message(s) did you get from the crash, or from the logs after the crash?

I took a look at the code you pointed to, and I see nothing that stands out as PostgreSQL-incompatible. Without the particular error messages produced by the crash, and in particular the PostgreSQL error messages, it's going to be hard to figure out what's going on.

I haven't had a chance to try to reproduce this, but a PDOException error normally displays the error message from the SQL server, and the query sent to the SQL server that produced the error. Do you have that detail at this point?

Also, upgrade to the latest dev and report the error back. The line numbers have changed in the code.

I've reproduced the problem here, using jm.federico's steps in #5. The full error message produced is

An AJAX HTTP request terminated abnormally.
Debugging information follows.
Path: /file/ajax/field_image/und/0/form-bJDakysCK2X5d0SH8qllSWYEZ4glTf7Z0F7MrrP0NA8
StatusText: n/a
ResponseText:
Error
Error
PDOException: in _storage_file_id() (line 655 of /home/drupal7/public_html/sites/all/modules/storage_api/storage.module).
The website encountered an unexpected error.  Please try again later.

ReadyState: undefined

I upgraded to 7.x-1.x-dev, and got essentially the same message, with the line number changed to 661 (which points to the same line of code (the ->execute() part of the db_select call in _storage_file_id).

The above error message not being too helpful, I checked the PostgreSQL logs, and there I get a 'invalid byte sequence for encoding "UTF8": 0xa4' error message. This looks like the generated query contains a character which is not a valid UTF8 character. Trying to upload a different image produces an 'invalid byte sequence for encoding "UTF8"' message with a different character (in one particular case, 0xb1).

I suspect the problem is the comparison value being passed for the 'whirlpool' condition (line 659 in 7.x-1.x). This value is binary, but is probably being passed to the generated SQL SELECT as a normal string constant, rather than as a binary constant (which in PostgreSQL is either bytea hex format, or bytea escape format, see The Postgresql docs on this). The PostgreSQL server is probably tagging this error even before trying to parse the SELECT statement.

The issue is that there needs to be some way of indicating to the condition method that the value is to be taken as binary data, not as a normal string. I'm not yet sure if such a way exists, or if DBTNG handles it. This may take a bit more research.

Note that it is quite common for PostgreSQL to be more picky than MySQL. This is probably why MySQL isn't crashing.

Sounds like you guys have identified it. This may be an issue found in Drupal across the board because that is a simple query. I can only imagine others have found the problem.

Let me know how I can help.

My own digging hasn't found how to handle this, so I've posted an issue over in Core (#2635756: How do you indicate to ->condition that a value is binary?) about how this is handled.

Hi, I just found related issues which can be helpful.

Adding my name to tracking this. Running into this issue myself

So with a bit of hackery, I was able to get this to work with a Postgres back end.

First off, I just modified the $whirlpool to be an md5 of the original whirlpool to ensure I get valid Postgres happy UTF-8 data.

That got me by the first part, but then I hit the 2nd part.

The StorageLock class (around line 841 in storage.module) has MySQL specific GET_LOCK and RELEASE_LOCK queries. So I took the step of detecting what driver is being used and a simple if/else to use postgres advisory lock when postgres.

Not really sure of best way to get changes to maintainer, or even if maintainer would like to incorporate them, but I will include my modified storage.module file here

I would need a patch file to consider it.

I grabbed coopers98's storage.module.txt, got rid of a bunch of extraneous white-space changes, and generated a patch off of 7.x-1.8. Patch attached.

Thanks! I'll take a look at it. I was just doing a few minor updates to get testing working with Travis CI.

Patch applied cleanly since storage.module hasn't changed since 7.x-1.8. Just FYI, always patch against dev.

About to push the changes.

Pushed changes. Don't know why they are not showing in the issue here.

No idea why tests are failing.

Tests are passing on Travis CI: https://travis-ci.org/taz77/drupal_storage_api

The patch is failing to apply because the patch expects storage.module to have permissions of 100755, but it has permissions of 100744. This is probably because I generated the patch on a Windows box. Attached is a modified patch which fixes this.

I had already fixed it and applied the patch. The test bots often fail here on this module.

Ah. Then shouldn't the status be 'Fixed'?

Yeah adding the hook would be good.

Committed an addition to the hook_requirements to look for MySQL or PgSQL

Should the following text from the community docs page https://www.drupal.org/node/420786 be updated to include Postgres as well?

Prerequisites

Database: MySQL
Other databases are not supported and could result in unexpected behaviour. MariaDB being a form fit and function replacement for MySQL seems to work fine.

I see the update was already made. Thanks for working on the documentation!