FUDforum: FUDforum Installation Issues » Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9

Home » FUDforum » FUDforum Installation Issues » Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9

Show: Today's Messages :: Polls :: Message Navigator

Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168753]

Mon, 15 July 2013 11:50

now_what
Messages: 6
Registered: July 2013

Karma: 0

Junior Member

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!

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168765 is a reply to message #168753]

Fri, 19 July 2013 14:49

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

Looks as if OID (Object identifiers) support was removed from PostgresQL?

UPDATE: Solution is to recreate your tables with OIDs turned on.

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168766 is a reply to message #168765]

Fri, 19 July 2013 15:13

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

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."

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #168973 is a reply to message #168765]

Mon, 12 August 2013 16:24

now_what

Messages: 6
Registered: July 2013

Karma: 0

Junior Member

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!

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169037 is a reply to message #168973]

Sat, 17 August 2013 18:20

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

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

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169055 is a reply to message #169037]

Mon, 19 August 2013 07:33

now_what

Messages: 6
Registered: July 2013

Karma: 0

Junior Member

Yes, it works without error. "No rows found."

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169142 is a reply to message #169055]

Sat, 24 August 2013 17:42

cpreston

Messages: 160
Registered: July 2012
Location: Oceanside

Karma: 6

Senior Member

HAve you enabled the botscout plugin? It checks against the botscout.com database for known bots.

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169208 is a reply to message #169142]

Sat, 07 September 2013 08:00

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

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?

Report message to a moderator

Re: Upgrade 3.0.0 -> 3.0.4 postgres 9.1.9 [message #169209 is a reply to message #169208]

Sat, 07 September 2013 08:39

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

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)

Report message to a moderator

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) ] [

]

Current Time: Thu Nov 28 08:01:20 GMT 2024

Total time taken to generate the page: 0.02741 seconds