[SMF 2.0 RC2] Syntax errors when upgrading from RC1 on PostgreSQL DB

[TheStupidOne]

Unable to upgrade from RC1 to RC2 using upgrade.php and the large upgrade package.  I am hosting it on a stock Debian Stable distro using LigHTTPd and PHP5 in FastCGI, software versions are as follows:

LigHTTPd 1.4.19
PHP 5.2.6-1+lenny3
PostgreSQL 8.3.8
XCache 1.2.2

All straight from stable respositories, no custom compilations.

Several things are wrong with the upgrade script, notably a lot of bad or invalid SQL in upgrade_2-0_postgresql.sql.  I have corrected all the incorrect SQL queries and syntax errors but the script will fail at step 27 with the following error message:

Code Select Expand

Error!
Error retrieving information on step: Enable the buddy and ignore lists if we have not done so thus far... (Item: 106)

But... checking the database, dont_repeat_buddylists is present in {$db_prefix}settings and set to 1, so the SQL did complete.  Commenting out/removing the code for that step will just cause the script to die on the next step, Installing theme settings, with the same error code.  That step however does NOT complete.

If I comment out both offending steps, the script will continue on and then reach the end, but not actually advance.  The script will complete the final SQL queries but hang and the upgrade script will time out.

Running from commandline produces the following output:

Code Select Expand

sh-3.2$ php5 upgrade.php
* Updating Settings.php... Successful.
*  +++  Adding Open ID Assocation table...Error: pg_query(): Query failed: ERROR:  relation "motk_openid_assoc" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
Successful.
*  +++  Adding search ability to custom fields.Error: pg_query(): Query failed: ERROR:  column "can_search" of relation "motk_custom_fields" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Enhancing privacy settings for custom fields. done.
+++  Changing default_values column to a larger field type... done.
+++  Adding new custom fields columns. done.
Successful.
*  +++  Implementing board redirects.Error: pg_query(): Query failed: ERROR:  column "redirect" of relation "motk_boards" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
Successful.
*  +++  Creating spider sequence.Error: pg_query(): Query failed: ERROR:  relation "motk_spiders_seq" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Creating spider table.Error: pg_query(): Query failed: ERROR:  relation "motk_spiders" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_spiders_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Removing a spider. done.
+++  Sequence for table log_spider_hits.Error: pg_query(): Query failed: ERROR:  relation "motk_log_spider_hits_seq" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Creating spider hit tracking table.Error: pg_query(): Query failed: ERROR:  relation "motk_log_spider_hits" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  relation "motk_log_spider_hits_id_spider" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  relation "motk_log_spider_hits_log_time" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  relation "motk_log_spider_hits_processed" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Creating spider statistic table.Error: pg_query(): Query failed: ERROR:  relation "motk_log_spider_stats" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
Successful.
*  +++  Enable cache if upgrading from 1.1 and lower. done.
Successful.
*  +++  Converting "log_online".Error: pg_query(): Query failed: ERROR:  constraint "motk_log_online_log_time" does not exist File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  constraint "motk_log_online_id_member" does not exist File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Adding guest voting - part 1...Error: pg_query(): Query failed: ERROR:  column "guest_vote" of relation "motk_polls" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Adding guest voting - part 2...Error: pg_query(): Query failed: ERROR:  constraint "motk_log_polls_pkey" does not exist File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  relation "motk_log_polls_id_poll" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Adding admin log...Error: pg_query(): Query failed: ERROR:  column "id_log" of relation "motk_log_actions" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Adding search ability to custom fields.Error: pg_query(): Query failed: ERROR:  column "passwd_flood" of relation "motk_members" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
. Successful.
*  +++  Adding weekly maintenance task...Error: pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "motk_scheduled_tasks_pkey" File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Setting the birthday email template if not set... done.
Successful.
*  +++  Adding pruning option... done.
Successful.
*  +++  Adding private to mail queue... done.
Successful.
*  +++  Adding multiple attachment path functionality.Error: pg_query(): Query failed: ERROR:  column "id_folder" of relation "motk_attachments" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Adding file hash. done.
Successful.
*  +++  Adding restore topic from recycle feature...Error: pg_query(): Query failed: ERROR:  column "id_previous_board" of relation "motk_topics" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430Error: pg_query(): Query failed: ERROR:  column "id_previous_topic" of relation "motk_topics" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
Successful.
*  +++  Changing URL to SMF package server... done.
Successful.
*  +++  Adding index member_started...Error: pg_query(): Query failed: ERROR:  relation "motk_topics_member_started" already exists File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 430 done.
+++  Adding index last_message_sticky... done.
+++  Adding index board_news... done.
Successful.
*  +++  Adding index on total_time_logged_in... done.
+++  Adding index on id_theme... done.
+++  Adding index on real_name... done.
Successful.
*  +++  Adding index id_member_msg... done.
. +++  Adding index current_topic... done.
. +++  Adding index related_ip... done.
. Successful.
*  +++  Adding index on attachment_type... done.
Successful.
*  +++  Changing ignore_boards column to a larger field type... done.
Successful.
*  +++  Adding default value for pending_details column done.
+++  Adding default value for vendor_ref column done.
Successful.
*  +++  Changing 'aim' to varchar to allow using email... done.
Successful.
*  +++  Changing 'ip' from char to varchar done.
+++  Changing 'error_type' from char to varchar done.
Successful.
*  +++  Changing event title column to a larger field type... done.
+++  Changing holiday title column to a larger field type... done.
Successful.
*  +++  Changing field_options column to a larger field type... done.
Successful.
*  +++  Adding reset poll timestamp and guest voters counter. done.
+++  Fixing guest voter tallys on existing polls...Error: pg_fetch_assoc(): Unable to jump to row 0 on PostgreSQL result index 91 File: /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php Line: 568 done.
. Successful.
*  +++  Changing inet_aton function to use bigint instead of int... done.
Successful.
*  +++  Adding an IFNULL to handle 8-bit integers returned by inet_aton done.
+++  Changing ip column in log_online to int8 done.
Successful.
*  +++  Removing index on hits... done.
Successful.
*  +++  Adding column that stores the PM receiving setting... done.
+++  Enable the buddy and ignore lists if we have not done so thus far... done.
Successful.
*  +++  Installing theme settings...
Parse error: syntax error, unexpected T_STRING in /var/www/shrinemaiden.org/testing/forum/upgrade.php(2764) : eval()'d code on line 93
Error in upgrade script upgrade_2-0_postgresql.sql on line 979!
done.
Successful.
*  +++  Installing new smiley sets... done.
Successful.
*  +++  Adding instr() done.
. +++  Adding daty() done.
+++  Adding IFNULL(varying, varying) done.
+++  Adding IFNULL(varying, bool) done.
+++  Adding IFNULL(int, bool) done.
+++  Adding bool_not_eq_int() done.
+++  Creating operator bool_not_eq_int() done.
Successful.

Fatal error: The database value you're trying to insert does not exist: value<em>(upgrade.php-1499)</em> in /var/www/shrinemaiden.org/testing/forum/Sources/Subs-Db-postgresql.php on line 747

The fatal error at the end is a separate bug, I have filed a bug report for it here as well.  Eventually found a missing ") at line 905, and that fixed the rest of the script.  Only thing that does not execute now is the code changing the value of smfVersion in {$db_prefix}settings to 2.0 RC2.  I just did that by hand, it seems to be an error in the upgrade.php that I can't track down. 

Attached is a diff with the corrections to the upgrade_2-0_postgresql.sql script.  Apply patch with "patch -p1 < /path/to/diff" in your SMF install location.  I executed the script via commandline, have not tested if it works via browser but willing to test if necessary.