I have two servers, one runs PostgreSQL 8.2.7 and the Update 6009 failed on it with the following two errors:

  • warning: pg_query() [function.pg-query]: Query failed: ERROR: cannot cast type bytea to text LINE 1: ...play SET display_options = CAST(display_options_old AS text) ^ in /usr/clients/www/drupal/includes/database.pgsql.inc on line 185.
  • user warning: query: UPDATE views_display SET display_options = CAST(display_options_old AS text) in /usr/clients/www/drupal/includes/database.pgsql.inc on line 960.

The CAST() fails... and the data is lost. (no worries, I had a backup, but some other people might not...)

I have no clue how you could do that translation but by hand (i.e. with a while() loop reading the old data and inserting it in the new table.)

Thank you.
Alexis

CommentFileSizeAuthor
#8 765352-pgsql-data-loss.patch3.03 KBmerlinofchaos
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

AlexisWilke’s picture

AlexisWilke CreditAttribution: AlexisWilke commented

P.S. my other server has PostgreSQL 8.3.9, that's the one that works properly and accepts the CAST() as is.

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Active » Closed (duplicate)

http://drupal.org/node/765236

Is this so hard to find?

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented

Sorry, for my behavior.

I'm not sure, whether its the same problem for pgsql and for mysql.

javiarauz’s picture

javiarauz CreditAttribution: javiarauz commented
Status: Closed (duplicate) » Active

Same two errors on pgsql 8.2.16

Greetings,

Javier

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented
Status: Active » Closed (duplicate)

lets mark this as duplicate of the other issue.

I think its the same.

AlexisWilke’s picture

AlexisWilke CreditAttribution: AlexisWilke commented
Status: Closed (duplicate) » Active

The following works a lot better for my old PostgreSQL server:

function views_update_6009() {
  $ret = array();

  $schema = views_schema_6009();
  //db_change_field($ret, 'views_display', 'display_options', 'display_options', $schema['views_display']['fields']['display_options']);
  $ret[] = update_sql('ALTER TABLE {views_display} RENAME "display_options" TO "display_options_old"');
  db_add_field($ret, 'views_display', 'display_options', $schema['views_display']['fields']['display_options']);

  $sql = "SELECT vid, id, display_options_old FROM {views_display}";
  $result = db_query($sql);
  while ($row = db_fetch_array($result)) {
    $row['display_options_old'] = db_decode_blob($row['display_options_old']);
    $sql = "UPDATE {views_display} SET display_options = '%s' WHERE vid = %d AND id = '%s'";
    db_query($sql, $row['display_options_old'], $row['vid'], $row['id']);
  }

  db_drop_field($ret, 'views_display', 'display_options_old');

  return $ret;
}

Thank you.
Alexis

dawehner’s picture

dawehner
Primary language German
CreditAttribution: dawehner commented

It should be just be done with non-sql stuff.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Version: 6.x-3.0-alpha3 » 6.x-2.x-dev
Priority: Normal » Critical
Status: Active » Needs review
FileSize
765352-pgsql-data-loss.patch3.03 KB

Ok, this integrates #6 into a patch that checks for pgsql and uses that code instead. Needs urgent testing.

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented

Is this someplace we should use http://api.drupal.org/api/function/db_change_column/6 instead of db_change_field() since it has extras for PostgreSQL?

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Not according to comments by the pgsql maintainer on http://api.drupal.org/api/function/db_change_field/head

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented

It has usefulness in a Pgsql-only context for exactly this purpose it seems. Otherwise yes, db_dchange_field() should always be used.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Well. I would need someone with pgsql to help with this. As soon as possible. We have to get this fix out before more pgsql users get their data destroyed.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Needs review » Fixed

Committed and hoping for the best.

AlexisWilke’s picture

AlexisWilke CreditAttribution: AlexisWilke commented

dereine,

I'm seeing your answers now... 8-) I don't think that it was the same as that other issue. The other issue talked about the PRIMARY KEY, whereas this one is the CAST that creates a problem. (P.S. I had not even noticed that you marked the issue as a duplicate until now!)

I tried the equivalent of db_change_column() in one of my database and it does not work.

ERROR: column "display_options" is of type text but expression is of type bytea

Thank you for the quick fix Merlin. I hope my fix helped you well. 8-)

I wasn't sure whether you'd want to use a condition or use that code for MySQL as well. It would work for both, although a little slower, it's just for the views definitions so it is relatively limited!

Thank you.
Alexis

gmh04’s picture

gmh04 CreditAttribution: gmh04 commented

Hi

I use postgres 8.1 and had the problem upgrading to 6.x-2.9. I restored the display_options table from backup (not the whole site as the backup it was 10 hours old) and installed the 6.x-2.10. The update went well but when I went into the modules page I got the following error:

* warning: Invalid argument supplied for foreach() in /disk/mirror-0/homes/aqmen/distributions/aqmen/modules/views/includes/view.inc on line 1747.
* warning: Invalid argument supplied for foreach() in /disk/mirror-0/homes/aqmen/distributions/aqmen/modules/views/includes/view.inc on line 1747.
* warning: Invalid argument supplied for foreach() in /disk/mirror-0/homes/aqmen/distributions/aqmen/modules/views/includes/view.inc on line 1747.
* warning: Invalid argument supplied for foreach() in /disk/mirror-0/homes/aqmen/distributions/aqmen/modules/views/includes/view.inc on line 1747.
* warning: Invalid argument supplied for foreach() in /disk/mirror-0/homes/aqmen/distributions/aqmen/modules/views/includes/view.inc on line 1747.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near ")" at character 43 in /disk/mirror-0/homes/aqmen/distributions/drupal-6.16/includes/database.pgsql.inc on line 139.
* user warning: query: SELECT * FROM views_display WHERE vid IN () ORDER BY vid, position in /disk/mirror-0/homes/aqmen/distributions/aqmen/modules/views/includes/view.inc on line 1369.

I re-enabled the view module and my views were restored and everything seems to be fine. This error nagging me however.

Thanks for the quick patch!

George

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

I've seen that error, it usually means bad cached schema. Try flushing cache?

almalino’s picture

almalino CreditAttribution: almalino commented

For you information. I have similar issue as in comment #15 for MySQL database. I created a separate bug report for it since it is different from original bug in this thread.
http://drupal.org/node/769780

Clearing cache didn't help.

AlexisWilke’s picture

AlexisWilke CreditAttribution: AlexisWilke commented

Merlin,

I just noticed that you switched this issue from 3.x to 2.x... I have 3.x and it failed in 3.x (and probably it would do so in 2.x too.)

I just hope that you'll also fix 3.x! 8-)

Thank you.
Alexis

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Any fixes in 2.x will always go forward into 3.x and beyond. I committed this in both branches.

Status: Fixed » Closed (fixed)

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