FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum » FUDforum Installation Issues » Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168753] Mon, 15 July 2013 07:50 Go to next message
now_what is currently offline  now_what
Messages: 6
Registered: July 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
upgrade 2.8 -> 3.0.0 ran without trouble.

upgrade.php 3.0.0 -> 3.0.4 complains about postgres 9.1.9 not being newer than the requested version 8.1.0
After fixing include dbadmin.inc, this output:

Create new database table /var/www/FUDforum//sql/fud_custom_fields.tbl.
Create new database table /var/www/FUDforum//sql/fud_pages.tbl.
Add new database column ann_opt to table fud28_announce.
Alter database column ip_addr in table fud28_pmsg.
Alter database column subject in table fud28_pmsg.
Drop unused database column id from table fud28_thread_notify.
Drop unused database index fud28_thread_notify_i_ut from table fud28_thread_notify.
Create composite primary key on fud28_thread_notify(user_id,thread_id).

Warning: pg_query(): Query failed: ERROR: column i.oid does not exist LINE 1: ...D i.thread_id = fud28_thread_notify.thread_id AND i.oid < fu... ^ in /var/www/FUDforum/sql/pgsql/db.inc on line 108

Fatal error: Uncaught exception 'Exception' with message ': ERROR: column i.oid does not exist LINE 1: ...D i.thread_id = fud28_thread_notify.thread_id AND i.oid < fu... ^ @ DELETE FROM fud28_thread_notify WHERE EXISTS (SELECT 1 FROM fud28_thread_notify i WHERE i.user_id = fud28_thread_notify.user_id AND i.thread_id = fud28_thread_notify.thread_id AND i.oid < fud28_thread_notify.oid)' in /var/www/byteplant/forum/upgrade.php:158 Stack trace: #0 /var/www/FUDforum/sql/pgsql/db.inc(108): fud_sql_error_handler('DELETE FROM fud...', 'ERROR: column ...', '', '9.1.9') #1 /var/www/FUDforum/include/dbadmin.inc(589): q('DELETE FROM fud...') #2 /var/www/FUDforum/include/dbadmin.inc(379): delete_duplicate_rows('fud28_thread_no...', 'user_id,thread_...') #3 /var/www/byteplant/forum/upgrade.php(978): create_primary_key('fud28_thread_no...', 'user_id,thread_...') #4 {main} thrown in /var/www/byteplant/forum/upgrade.php on line 158

Thanks for any help!
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168765 is a reply to message #168753] Fri, 19 July 2013 10:49 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3632
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Looks as if OID (Object identifiers) support was removed from PostgresQL?

UPDATE: Solution is to recreate your tables with OIDs turned on.
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168766 is a reply to message #168765] Fri, 19 July 2013 11:13 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3632
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Also look at http://www.postgresql.org/message-id/40AA5C56(dot)2030105(at)archonet(dot)com -

"I believe in the next version, the default setting will be not to have OIDs and you will have to specify WITH OIDS if you do want them."
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168973 is a reply to message #168765] Mon, 12 August 2013 12:24 Go to previous messageGo to next message
now_what is currently offline  now_what   Germany
Messages: 6
Registered: July 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Thank you naudefj!

To proceed with the update, I removed all offending DELETE statements with OIDs in the update script.
The DELETE statements looked like they were meant prevent other SQL statements from complaining about index violations.

There were no index violations in the tables that mattered, though there was some complaints involving fud28_search,
but they looked as if they could safely be ignored.

As the rest of FUDforum does not use OIDs, I suggest you guys should stop using them in the update script.

No problems encountered for 4 weeks:
- my Chinese spammer friends were still able to register and post their dribble.
- I slapped them with Email and IP filters, now they can still register, but they are unable to post.

The whole point of updating was to get rid of spammers. The Captchas did not seem to help at all.
Also, I would have liked if the Email and IP filters had been able to block registration as well as posting.

There is another serious issue that came to my attention today, but it is unrelated to the upgrade process.
I will start a separate thread for this.

Cheers!
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169037 is a reply to message #168973] Sat, 17 August 2013 14:20 Go to previous messageGo to next message
naudefj is currently offline  naudefj   
Messages: 3632
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
I suspect that one can still use OID for dictionary tables. Can you please test this SQL statement on pgsql. If it runs fine, we only have one query with OID that needs to be fixed.

SELECT pg_catalog.pg_get_indexdef(i.indexrelid) 
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i 
WHERE c.relname = 'fud28_search'
  AND c.oid= i.indrelid 
  AND i.indexrelid = c2.oid

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169055 is a reply to message #169037] Mon, 19 August 2013 03:33 Go to previous messageGo to next message
now_what is currently offline  now_what   Germany
Messages: 6
Registered: July 2013
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Yes, it works without error. "No rows found."
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169142 is a reply to message #169055] Sat, 24 August 2013 13:42 Go to previous messageGo to next message
cpreston is currently offline  cpreston   United States
Messages: 160
Registered: June 2012
Location: Oceanside
Karma: 6
Senior Member
add to buddy list
ignore all messages by this user
HAve you enabled the botscout plugin? It checks against the botscout.com database for known bots.
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169208 is a reply to message #169142] Sat, 07 September 2013 04:00 Go to previous messageGo to next message
naudefj is currently offline  naudefj   
Messages: 3632
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Great, that leaves us with one query to fix in delete_duplicate_rows() in dbadmin.inc:

DELETE FROM tableX WHERE EXISTS (SELECT 1 FROM tableX i WHERE i.colX = tableX.colX AND i.oid < tableX.oid;


Any suggestions?
Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169209 is a reply to message #169208] Sat, 07 September 2013 04:39 Go to previous message
naudefj is currently offline  naudefj   
Messages: 3632
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Possible solution from http://postgres.cz/wiki/PostgreSQL_SQL_Tricks
DELETE FROM tableX WHERE NOT ctid = (SELECT ctid FROM tableX i WHERE colX=i.colX LIMIT 1);

Can you please test this query for us to confirm:
DELETE FROM fud28_thread_notify WHERE NOT ctid = (SELECT ctid FROM fud28_thread_notify i WHERE i.user_id = fud28_thread_notify.user_id AND i.thread_id = fud28_thread_notify.thread_id LIMIT 1)

Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Register + Activate Account
Next Topic: Error upgrade from 3.0.0 to 3.0.5
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 14 12:17:59 EST 2017

Total time taken to generate the page: 0.00787 seconds