Drupal7, PostgreSQL, bytea and unserialize

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
svetachu's picture

Hi!

My attempts to get working Drupal7 with PostgreSQL db has no success.
Drupal7 and PostgreSQL db are installed.
The first request to my site gives an error message:
"Parameter of unserialize function should be string"
at the line:
$cache->data = unserialize($cache->data);
This is reasonable. PDO_pgsq returns a resource for the bytea type, data is of bytea type.

The following steps helped me:
I added the following function:

function newunserialize ($param) {
if (is_resource($param)) {
$ret = unserialize(stream_get_contents($param));
}
else {
$ret = unserialize($param);
}
return $ret;
}

and changed the unserialize function calls to calls of my newunserialize function.

The unserialize function is used in many Drupal7 modules (e.g., dblog, field, sustem, profile...)
Clearly, it's bad to change the core files...

Reading the Drupal 7 documentation and forum, I understood that there are working applications on Drupal7 and PostgreSQL db.
How do you solve the problem of unserialize for bytea type? Could anybody share successful experience, please?

Svetlana

Comments

bytea_output configuration is my guess.

sbuttgereit's picture

I'd need more details to know for sure, but it sounds like you're running into the BYTEA escaping issue. This cropped up with a change in (I believe) PostgreSQL 9.0 where the default storage for BYTEA data was changed.

If that's the issue, then you need to execute the following on your Drupal database:

ALTER DATABASE drupal_database SET bytea_output = 'escape';

More on this can be found at: http://drupal.org/node/926636

Cheers,
Steven C. Buttgereit

Steven C. Buttgereit
Managing Director
Muse Systems
www.musesystems.com

Thank you for your help, it

svetachu's picture

Thank you for your help, it took me a while to try your suggestion. Unfortunately my problem still is not solved.

I use the following tools:
OS:Windows 7 Professional
Drupal 7.9
xampp 1.7.7
Apache 2.2.21
php 5.3.8

I have a working Drupal 7 application on MySql ( MySql 5.5.16). I manually converted database data structures from MySql to PostgreSql and copied data.
I executed on my Drupal database the following command:
ALTER DATABASE d7 SET bytea_output = 'escape';
and changed the default bytea_output ='hex' in postgresql.conf file to
bytea_output='escape', but it didn't change the error message.
Then I updated PostgreSQL to version 9.1 and repeated all manipulations for bytea_otput. Again, the error message did not change. In other words, when I type http://localhost/d7 in a browser, I always get the following message:

Warning: unserialize() expects parameter 1 to be string, resource given in DrupalDatabaseCache->prepareItem() (line 407 of C:\xampp\htdocs\d7\includes\cache.inc).
Warning: unserialize() expects parameter 1 to be string, resource given in variable_initialize() (line 924 of C:\xampp\htdocs\d7\includes\bootstrap.inc).
[......]
Warning: unserialize() expects parameter 1 to be string, resource given in DrupalDatabaseCache->prepareItem() (line 407 of C:\xampp\htdocs\d7\includes\cache.inc).
[......]
Warning: unserialize() expects parameter 1 to be string, resource given in DrupalDatabaseCache->prepareItem() (line 407 of C:\xampp\htdocs\d7\includes\cache.inc
Warning: unserialize() expects parameter 1 to be string, resource given in menu_unserialize() (line 379 of C:\xampp\htdocs\d7\includes\menu.inc).

I also tried to re-create the database structures in a different way, i.e. create a new database d7 and click the 'Save and continue' button on the 'Database configuration' page (http://localhost/d7/install.php?profile=standard&locale=en). This gives the following message.
The bytea_output setting is currently set to 'hex', but needs to be 'escape'. Change this by running the following query: ALTER DATABASE "d7" SET bytea_output = 'escape';

Any further suggestions and insights are greatly appreciated.

Conversion vs. New Installation

sbuttgereit's picture

Data conversion between database vendors can be tricky and data may not be directly convertible, especially when dealing with data types like BYTEA which are intended for binary data. I strongly recommend that you get a plain vanilla Drupal 7 up and working with PostgreSQL first before trying to deal with converted data.

I recommend that you follow these steps.

  • Create a new database in your PostgreSQL cluster (i.e drupal_new) with the following commands, assuming the PostgreSQL role drupal_user owns the database:

CREATE DATABASE drupal_new
  WITH OWNER = drupal_user
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

ALTER DATABASE drupal_new SET bytea_output='escape';
  • Try re-running the plain vanilla D7 installer pointed at this new database.

Don't do anything else or change the order, etc. This really should be sufficient. Naturally change the collation and ctype as appropriate; I can't remember if there are any Drupal limitations on these, but I know mine are working. The postgresql.conf bytea_output deals with client connections. I've not played with that setting: in my known working Drupal 7/PostgreSQL 9.0 instance bytea_output is at it's default setting of 'hex'... so I'm pretty sure the alter database statement above is much more important. The postgresql.conf setting is likely more relevant when running the initdb routine that creates the template databases... but I'm speculating on that point.

If that works, you've gotten a good start and know your database and Drupal instances are configured well enough for at least being able to run.

Regarding your larger problem of data conversion between MySQL and PostgreSQL. As I said binary data types are not directly convertible most of the time. There are other issues you'll have to handle, too, with migrating a Drupal site... things like clearing certain cache's and the like. It can be a bit tricky.

Thanks,
Steven C. Buttgereit

Steven C. Buttgereit
Managing Director
Muse Systems
www.musesystems.com

Unserialize bytea problem

svetachu's picture

Thank you for a help. Still I can not get a working Drupal7 with PostgreSQL database and bytea data.

In the begining, PostgreSQL 9.1 was installed on my computer (OS: Window7 Professional) with
locale=Finnish_Finland.1252. This locale was installed by default.
I couldn't create db with

LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'

because psql gives the following error message:
Invalid locale name
I succeeded to create the following databases:
CREATE DATABASE d1
  WITH OWNER = usr
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Finnish_Finland.1252'
       LC_CTYPE = 'Finnish_Finland.1252'
       CONNECTION LIMIT = -1;
ALTER DATABASE d1 SET bytea_output='escape';

CREATE DATABASE d2
  WITH OWNER = usr
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       TEMPLATE = template0
       LC_COLLATE = 'american_us'
       LC_CTYPE = 'american_us'
       CONNECTION LIMIT = -1;
ALTER DATABASE d2 SET bytea_output='escape';

So, I have (Windows7 OS, PostgreSQL 9.1):
C:\Users\Svetlana>psql
psql (9.1.2)
postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding |       Collation        |         Ctype          |   Access privileges
-----------+----------+----------+------------------------+------------------------+-----------------------
d1        | usr      | UTF8     | Finnish_Finland.1252   | Finnish_Finland.1252   |
d2        | usr      | UTF8     | american_us            | american_us            |
...
postgres=#

After that I have used your advice and started to install Drupal7 from the begining according to
'plain vanilla D7 installer' (http://localhost/d7/install.php?profile=standard&locale=en&op=start&id=1)
For both d1 and d2 I got the following error message:

Warning: unserialize() expects parameter 1 to be string, resource given in install_verify_completed_task()
(line 783 of C:\xampp\htdocs\d7\includes\install.core.inc).

Table variable already exists.

Despite the warning, many (31) tables were created, but the errors about the unserialize()
function were again issued.

Then I re-installed PostgreSQL 9.1, selecting english(United States) locale for
the installation prompt that suggests to select the locale: locale = 'English, United States'.
Then I created two databases (with or without LC_COLLATE and LC_CTYPE lines). Now they look as follows.

postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding |       Collation        |         Ctype          |   Access privileges
-----------+----------+----------+------------------------+------------------------+-----------------------
d1        | usr      | UTF8     | English, United States | English, United States |
d2        | usr      | UTF8     | american_us            | american_us            |
...

Executing the http://localhost/d7/install.php?profile=standard&locale=en&op=start&id=1) gives the
same unserialize() error message in all cases.

As I understood, your advice uses the locale name in Linux style.
Do you know corresponding names for Windows7? Are the ones I used above OK?
Are you sure the error is connected to locale?
What else can I try?

Thanks,
Svetlana

PS. Most of the data is already converted. When the unserialize() is not breaking too hard, web pages are shown.

Thank you for a help in my problem decision.

svetachu's picture

Thak you very much for your help.
I stated installation from downloading the last version of Drupal (Drupal 7.10) and now looks like work properly.

Best regards,
Svetlana

I know this post is rather

DHL's picture

I know this post is rather old. But I trap in this problem too.
Error occur when perform a full database restore.
No problem if just restore the records only.
Using Drupal 7.36, PostgreSQL 9.3.3, Linux

Postgresql

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: