5.10 -> 6.4 upgrade warning with PostgreSQL 8.1 [#296082]

the following warning is printed when upgrading a Drupal 5.10 installation using a PostgreSQL 8.1 database to Drupal 6.4 with all contributed modules disabled before performing the update:

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "t" at character 25 in /var/www/drupal-6.4/includes/database.pgsql.inc on line 138.
user warning: ERROR: syntax error at or near "t" at character 25 query: UPDATE drupal_term_node t SET vid = (SELECT vid FROM drupal_node n WHERE t.nid = n.nid) in /var/www/drupal-6.4/modules/system/system.install on line 1154.

The correct SQL syntax is:

UPDATE drupal_term_node set vid=(select vid from drupal_node n where drupal_term_node.nid=n.nid)

I manually used this statement to upgrade the database.

Comment	File	Size	Author
#4	postgres-system-upgrade-fix.patch	576 bytes	stefanor
#2	postgres-system-upgrade-fix.patch	574 bytes	nigel
#1	update.patch	684 bytes	stefanor

Comments

Comment #1

stefanor commented 20 September 2008 at 17:03

Component:	update system	» system.module
Status:	Active	» Needs review

Status	File	Size
new	update.patch	684 bytes

Also obtained, upgrading from 5.10 -> 6.4. The problem is that whereas in MySQL "AS" is optional, in PostgreSQL it isn't.

Patch attached.

Comment #2

nigel commented 26 September 2008 at 01:28

Status	File	Size
new	postgres-system-upgrade-fix.patch	574 bytes

Hi. I also have this problem, but prepared a different patch. The first patch doesn't work in PostgreSQL 8.1, as per the following:

mahara-site=# update term_node t set vid = (select vid from node n where t.nid = n.nid);
ERROR:  syntax error at or near "t" at character 18
LINE 1: update term_node t set vid = (select vid from node n where t...
                         ^
mahara-site=# update term_node as t set vid = (select vid from node n where t.nid = n.nid);
ERROR:  syntax error at or near "as" at character 18
LINE 1: update term_node as t set vid = (select vid from node n wher...
                         ^
mahara-site=# update term_node set vid = (select vid from node n where term_node.nid = n.nid);
UPDATE 0
mahara-site=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

Attached is a patch that works for postgres 8.1. I can't test it in mysql or postgres 8.2/3 at this stage, would appreciate it if someone else could.

Comment #3

nigel commented 26 September 2008 at 01:37

Priority:

Normal

» Critical

Bumping this to critical (after discussion on #drupal-support). If the patch is not applied (and the query fails), then all data from term_node seems to be deleted :(