Cannot upgrade from SMF 2.0 RC1 to RC2 or clean install RC2 (PostgreSQL)

Started by TheStupidOne, November 10, 2009, 03:48:44 PM

Previous topic - Next topic

TheStupidOne

I originally posted this in the SMF 2.0 support forum, crossposting here to try to get help.

I am currently using SMF 2.0 RC1 on a community forum I administer, and I am completely unable to upgrade to RC2 from RC1, nor can I perform a clean install to RC2.  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 and syntax errors but the script will fail at step 27 with the following error message:
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:
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


Now, trying to see if it's due to manual patches I have applied to the database, I attempted to make a clean install and the installer will fail after the initial database setup with the following error message.


Fatal error: The database value you're trying to insert does not exist: value(install.php-1140) in /var/www/forum/Sources/Subs-Db-postgresql.php on line 747


I have reproduced this installer failure on another machine using Debian using the same exact software setup, LigHTTPd + PostgreSQL.

Attached is the output of phpinfo() as well as the diff for the corrections made to upgrade_2-0_postgresql.sql

TheStupidOne

Not to be impatient, but I'd at least like to have the problem acknowledged.  As it stands now RC2 is COMPLETELY unusable on PostgreSQL despite being one of the big key features added in from SMF 1.1.x to 2.x.

RC1 had some bugs and broken behavior, but at least I was able to patch out some of those issues and make it usable.  This is completely unacceptable, as a key support feature is completely broken, not just on an upgrade install but on a clean one as well.

Oldiesmann

Thanks for your report. I'm not sure how the PostgreSQL upgrade file got so broken, as it worked fine when I last tested it a few weeks ago (mid-October or so). I will look at the changes you've made and implement them to fix the existing thing, and will also keep on top of the PostgreSQL changes more closely to ensure things don't get broken again.

The "value you're trying to insert doesn't exist" error is related to the option to allow us to collect basic stats. For now just uncheck that option and you should be able to proceed without any problems.
Michael Eshom
Christian Metal Fans

TheStupidOne

We did have that option unchecked in both the upgrade and new install setup.  We've managed to fix the upgrade script for the most part, the only thing that it dies on now is changing the version number to 2.0 RC2 in the database, so we just commented that out and did it by hand, the rest of the script executes properly.  I've attached updated diff of the SQL script.  We executed it via commandline, no idea if it works via browser but it should give a good clue where to start looking to fix it properly.

The problem now is with the handling code in Sources/Subs-Db-postgresql.php, and this is the error that produces "The database value you're trying to insert does not exist: value" on both the upgrade and new installs.  By replacing the file with the RC1 version the forum is usable, although I'm not sure what functionality is lost as this goes a little beyond my area of expertise.  With some debugging we were able to determine where the fault occurs.  Here's the error text with our more "verbose" error output.

The database value you're trying to insert does not exist: log_time<br/>VALUES: Array
(
[0] => 127.0.0.1
[1] => 1258020057
[2] => login
)


This and a few other similar errors occur when trying to update any settings or perform any operation that involves adding a log entry.  As you can tell from the output the query itself is structured properly.  Looking at the diff the regexes used to build the queries are the only significant difference between RC1 and RC2, and since the forum more or less works with the RC1 version dropped in we assume the error is there.  These database errors that make it impossible to even login, let alone manipulate any settings. 

I've attached a diff between the RC1 and RC2 version of Subs-Db-postgresql.php as well to give some reference to anyone willing to help.

TheStupidOne

Actually, it looks like we've solved that error too in Subs-Db-postgresql.php.

In between RC1 and RC2 you made a change to the code that reformats replace calls to PostgreSQL compatible calls.  Here is the relevant diff section.

*** 579,600 ****
  // Setup an UPDATE template.
  $updateData = '';
  $where = '';
- $count = 0;
  foreach ($columns as $columnName => $type)
  {
  // Are we restricting the length?
  if (strpos($type, 'string-') !== false)
! $actualType = sprintf($columnName . ' = SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $count);
  else
! $actualType = sprintf($columnName . ' = {%1$s:%2$s}, ', $type, $count);
 
  // Has it got a key?
  if (in_array($columnName, $keys))
  $where .= (empty($where) ? '' : ' AND ') . substr($actualType,0, -2);
  else
  $updateData .= $actualType;
-
- $count++;
  }
  $updateData = substr($updateData, 0, -2);
 
--- 613,631 ----
  // Setup an UPDATE template.
  $updateData = '';
  $where = '';
  foreach ($columns as $columnName => $type)
  {
  // Are we restricting the length?
  if (strpos($type, 'string-') !== false)
! $actualType = sprintf($columnName . ' = SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName);
  else
! $actualType = sprintf($columnName . ' = {%1$s:%2$s}, ', $type, $columnName);
 
  // Has it got a key?
  if (in_array($columnName, $keys))
  $where .= (empty($where) ? '' : ' AND ') . substr($actualType,0, -2);
  else
  $updateData .= $actualType;
  }
  $updateData = substr($updateData, 0, -2);


That removal of $count and changing the sprintfs to have $columnName rather than $count as the last argument breaks any operation that results in an SQL update.  By reverting it back to the RC1 changes, it works perfectly, even with the new regexes.  Attached is a diff for the exact patch to be made.

The Subs-Db-postgresql.php patch will fix the "The database value you're trying to insert does not exist: value" error on both upgrades and new installs using PostgreSQL, and the upgrade_2-0_postgresql.sql patch will fix upgrade issues from RC1 to RC2 using PostgreSQL.  Apply with "patch -p1 < /path/to/diff" while in the SMF folder.

Everything else seems to be working at this point, I will mark this solved once I am able to confirm all functionality is working.

TheStupidOne

Edit: nevermind filed my own bug reports.

N3RVE

Sorry, it's taken so long to get back to you. Thanks for the reports, we'll certainly look into the issue.

-[n3rve]
Ralph "[n3rve]" Otowo
Former Marketing Co-ordinator, Simple Machines.
ralph [at] simplemachines [dot] org                       
Quote"Somewhere, something incredible is waiting to be known." - Carl Sagan

Advertisement: