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

Home » FUDforum Development » Bug Reports » Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql)
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
Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166227] Sat, 15 October 2011 05:09 Go to next message
djzort is currently offline  djzort   
Messages: 30
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user
Hello, i am attempting to restore a backup from v3.0.3 (mysql 5.1.49) to v3.0.3 (postgresql 8.4). Im not sure if backups are supposed to be database neutral, however my goal is to move all my applications from mysql to postgresql including my forums.

the fudforums install properly as a new install on postgresql, then i attempt to restore a backup file from the other installation...

here is the output...

Quote:


Restore progress
Restore forum files...
Drop and recreate database tables...
Start loading database tables...
Process table fud30_action_log:
...59 rows loaded.
Process table fud30_ann_forums:
...149 rows loaded.
Process table fud30_announce:
...11 rows loaded.
Process table fud30_attach:
...713 rows loaded.
Process table fud30_avatar:
...84 rows loaded.
Process table fud30_bookmarks:
...1 rows loaded.
Process table fud30_buddy:
...91 rows loaded.
Process table fud30_calendar:
...1 rows loaded.
Process table fud30_cat:
...3 rows loaded.
Process table fud30_custom_tags:
...15 rows loaded.
Process table fud30_fc_view:
...14 rows loaded.
Process table fud30_forum:
...35 rows loaded.
Process table fud30_forum_notify:
...7 rows loaded.
Process table fud30_forum_read:
...2506 rows loaded.
Process table fud30_geoip:
...10000 rows
...20000 rows
...30000 rows
...40000 rows
...50000 rows
...60000 rows
...70000 rows
...80000 rows
...90000 rows
...100000 rows
...110000 rows
...111799 rows loaded.
Process table fud30_group_cache:
...77 rows loaded.
Process table fud30_group_members:
...77 rows loaded.
Process table fud30_group_resources:
...36 rows loaded.
Process table fud30_groups:
...38 rows loaded.
Process table fud30_level:
...5 rows loaded.
Process table fud30_mime:
...43 rows loaded.
Process table fud30_mod:
...10 rows loaded.
Process table fud30_msg:
...10000 rows
Warning: pg_query(): Query failed: ERROR: syntax error at or near "'staffNULL status in rego list'" LINE 1: ...21.44.125.133',NULL,'1211710597','0','0',NULL,NULL'staffNULL... ^ in /var/www/sites/forums.fragfest.com.au/FUDforum/include/theme/default/db.inc on line 108 Fatal error: Uncaught exception 'Exception' with message '(/var/www/sites/forums.fragfest.com.au/FUDforum/include/theme/default/db.i nc:108
/var/www/sites/forums.fragfest.com.au/htdocs/adm/admimport.php:199
) : ERROR: syntax error at or near "'staffNULL status in rego list'" LINE 1: ...21.44.125.133',NULL,'1211710597','0','0',NULL,NULL'staffNULL... ^
Query: INSERT INTO fud30_msg VALUES ('17425','1532','4','0','121.44.125.133',NULL,'1211710597','0','0',NULL,NUL L'staffNULL status in rego list','0','0','138797','324','130000','0','0','0',NULL,NULL,NULL,'1','1','a u','AUSTRALIA')
_POST: array ( 'SQ' => '3c7f284369032ff2f1cc18cf34f776b9', 'path' => '/tmp/FUDforum_15_10_2011_10_17.fud.gz', 'btn_submit' => 'Import Data', )
Database version: 8.4.8
[Referring Page] http://forums.fragfest.com.au/adm/admimport.php?&SQ=3c7f284369032ff2f1c c18cf34f776b9
' in /var/www/sites/forums.fragfest.com.au/FUDforum/include/core.inc:256 Stack trace: #0 /var/www/sites/forums.fragfest.com.au/FUDforum/include/theme/default/db.inc (108): fud_sql_error_handler('INSERT INTO fud...', 'ERROR: syntax ...', '', '8.4.8') #1 /var/www/sites/forums.fragfest.com.au/htdocs/adm/admimport.php(199): q('INSERT INTO fud...') #2 {main} thrown in /var/www/sites/forums.fragfest.com.au/FUDforum/include/core.inc on line 256

Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166228 is a reply to message #166227] Sat, 15 October 2011 05:20 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3631
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
We don't test cross database restores as it's strictly speaking a migration and not a restore.

That said, if you can mail me the dump or give me access to your site, I can try to prepare a patch for you.
Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166261 is a reply to message #166228] Mon, 17 October 2011 22:29 Go to previous messageGo to next message
djzort is currently offline  djzort   Australia
Messages: 30
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user
hi naudefi, i would be happy to send you the dump - i will send you a link in a PM
Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166262 is a reply to message #166261] Tue, 18 October 2011 04:03 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3631
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Please try this fix:
http://fudforum.svn.sourceforge.net/fudforum/?rev=5397&view=rev
Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166285 is a reply to message #166262] Thu, 27 October 2011 04:20 Go to previous messageGo to next message
djzort is currently offline  djzort   
Messages: 30
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user
closer...


Drop and recreate database tables...
Start loading database tables...
Process table fud30_action_log:
...82 rows loaded.
Process table fud30_ann_forums:
...149 rows loaded.
Process table fud30_announce:
...11 rows loaded.
Process table fud30_attach:
...713 rows loaded.
Process table fud30_avatar:
...84 rows loaded.
Process table fud30_bookmarks:
...1 rows loaded.
Process table fud30_buddy:
...91 rows loaded.
Process table fud30_calendar:
...1 rows loaded.
Process table fud30_cat:
...3 rows loaded.
Process table fud30_custom_tags:
...15 rows loaded.
Process table fud30_fc_view:
...14 rows loaded.
Process table fud30_forum:
...35 rows loaded.
Process table fud30_forum_notify:
...7 rows loaded.
Process table fud30_forum_read:
...2489 rows loaded.
Process table fud30_geoip:
 ...10000 rows
 ...20000 rows
 ...30000 rows
 ...40000 rows
 ...50000 rows
 ...60000 rows
 ...70000 rows
 ...80000 rows
 ...90000 rows
 ...100000 rows
 ...110000 rows
...111799 rows loaded.
Process table fud30_group_cache:
...77 rows loaded.
Process table fud30_group_members:
...77 rows loaded.
Process table fud30_group_resources:
...36 rows loaded.
Process table fud30_groups:
...38 rows loaded.
Process table fud30_index:
 ...10000 rows
 ...20000 rows
 ...30000 rows
 ...40000 rows
 ...50000 rows
 ...60000 rows
 ...70000 rows
 ...80000 rows
 ...90000 rows
 ...100000 rows
 ...110000 rows
 ...120000 rows
 ...130000 rows
 ...140000 rows
 ...150000 rows
 ...160000 rows
 ...170000 rows
 ...180000 rows
 ...190000 rows
 ...200000 rows
 ...210000 rows
 ...220000 rows
 ...230000 rows
 ...240000 rows
 ...250000 rows
 ...260000 rows
 ...270000 rows
 ...280000 rows
 ...290000 rows
 ...300000 rows
 ...310000 rows
 ...320000 rows
 ...330000 rows
 ...340000 rows
 ...350000 rows
 ...360000 rows
 ...370000 rows
 ...380000 rows
 ...390000 rows
 ...400000 rows
 ...410000 rows
 ...420000 rows
 ...430000 rows
 ...440000 rows
 ...450000 rows
 ...460000 rows
 ...470000 rows
 ...480000 rows
 ...490000 rows
 ...500000 rows
 ...510000 rows
 ...520000 rows
 ...530000 rows
 ...540000 rows
 ...550000 rows
 ...560000 rows
 ...570000 rows
 ...580000 rows
 ...590000 rows
 ...600000 rows
 ...610000 rows
 ...620000 rows
 ...630000 rows
 ...640000 rows
 ...650000 rows
 ...660000 rows
 ...670000 rows
 ...680000 rows
 ...690000 rows
 ...700000 rows
 ...710000 rows
 ...720000 rows
 ...730000 rows
 ...740000 rows
 ...750000 rows
 ...760000 rows
 ...770000 rows
 ...780000 rows
 ...790000 rows
 ...800000 rows
 ...810000 rows
 ...820000 rows
 ...830000 rows
 ...840000 rows
 ...850000 rows
 ...860000 rows
 ...870000 rows
 ...880000 rows
 ...890000 rows
 ...900000 rows
...904808 rows loaded.
Process table fud30_level:
...5 rows loaded.
Process table fud30_mime:
...43 rows loaded.
Process table fud30_mod:
...10 rows loaded.
Process table fud30_msg:
 ...10000 rows
 ...20000 rows
 ...30000 rows
...32437 rows loaded.
Process table fud30_plugins:
...6 rows loaded.
Process table fud30_pmsg:
Warning: pg_query(): Query failed: ERROR: null value in column "subject" violates not-null constraint in /var/www/sites/forums.fragfest.com.au/FUDforum/include/theme/default/db.inc on line 108 Fatal error: Uncaught exception 'Exception' with message '(/var/www/sites/forums.fragfest.com.au/FUDforum/include/theme/default/db.inc:108
/var/www/sites/forums.fragfest.com.au/htdocs/adm/admimport.php:201
) : ERROR: null value in column "subject" violates not-null constraint
Query: INSERT INTO fud30_pmsg VALUES ('3142','DjZoRt!','131','131','0','203.8.80.18','203.8.80*','1169158462','1169158462',NULL,NULL,'0','116732','1368',NULL,'4','33')
_POST: array ( 'SQ' => 'e82e18997e7cb809e4a9dc1720ce1d83', 'path' => '/tmp/FUDforum_27_10_2011_09_35.fud.gz', 'btn_submit' => 'Import Data', )
Database version: 8.4.8
[Referring Page] http://forums.fragfest.com.au/adm/admimport.php?&SQ=50ca5dc759218c31b17aeaaa7feb9d38
' in /var/www/sites/forums.fragfest.com.au/FUDforum/include/core.inc:256 Stack trace: #0 /var/www/sites/forums.fragfest.com.au/FUDforum/include/theme/default/db.inc(108): fud_sql_error_handler('INSERT INTO fud...', 'ERROR: null va...', '', '8.4.8') #1 /var/www/sites/forums.fragfest.com.au/htdocs/adm/admimport.php(201): q('INSERT INTO fud...') #2 {main} thrown in /var/www/sites/forums.fragfest.com.au/FUDforum/include/core.inc on line 256 

Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166291 is a reply to message #166285] Mon, 31 October 2011 22:42 Go to previous messageGo to next message
djzort is currently offline  djzort   Australia
Messages: 30
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user
Im willing to put a bounty on this?
Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166294 is a reply to message #166291] Wed, 02 November 2011 05:39 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3631
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
A bounty would be great! Smile

BTW, this is a data problem and not a FUDforum problem. You have a message with empty subject, MySQL allowed it (bad database), but PostgreSQL won't.
Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166308 is a reply to message #166294] Tue, 08 November 2011 20:40 Go to previous messageGo to next message
djzort is currently offline  djzort   
Messages: 30
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user
hmmm luckily for me, unluckily for you i guess - the updated admimport.php file worked and after finding a few other inappropriate NULL values i was able to restore the mysql backup into a fresh install using postgresql.

take aways are...

- the forum consistency checker may want to look for NULL values in columns which are set NOT NULL or are otherwise inappropriate
- the admimport.php script may also want to filter NULL values to perhaps 'NULL' where they are inappropriate?
- the backup script may also want to filter as as well

thanks for your help !!!!!!
Re: Restore data from 3.0.3 (mysql) to 3.0.3 (postgresql) [message #166315 is a reply to message #166308] Wed, 09 November 2011 01:16 Go to previous message
djzort is currently offline  djzort   
Messages: 30
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user
also fyi, after install in the GLOBALS.php the DBHOST_DBTYPE was 'mysql' though the forum was working fine. i guess php was smart enough to work it out?

not really sure, but i manually changed that value to pgsql and restarted apache just in case.
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: feeds (RDS or others) NOT working for IE and Google Chrome
Next Topic: plugin settings not maintained when backup and restored
Goto Forum:
  

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

Current Time: Tue Dec 12 01:46:57 EST 2017

Total time taken to generate the page: 0.00753 seconds