Transferring a large phpbb2 database to new server

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

Ok thanks for the quick reply ric323. So in order to restore the search tables I omitted, should I just follow the guide you linked me to?

How about the session tables?

Thanks for your help.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 »

MrGibbonz wrote:Ok thanks for the quick reply ric323. So in order to restore the search tables I omitted, should I just follow the guide you linked me to?
Yes
How about the session tables?
They will rebuild themselves. It just forces everyone to log in again if they are blank.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

I reinstated the forum on the new server and configured it to connect to the new database (still without search tables present)

Before I edited the config file I got the could not connect to database message as expected, but after updating the config file with my new database details I now get the following displayed when I load the forum index....
message_die() was called multiple times. This isn't supposed to happen. Was message_die() used in page_tail.php?
Should I expect to see this? I've read through the search mod guide and I can see it's accessed from a menu within the forum admin cp and I can't access it as the site doesnt work because of this new problem.

I have no idea what's wrong now. Somebody PLEASE help me! :cry:

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 »

I suspect your profile is trying to select a theme which hasn't been installed on the new server.
If so, upload the files for your theme also, or this article shows how to force all users back to the default subSilver: Knowledge Base - Set subSilver as the default template


or, there is a MOD do display the individual error messages. I'll try to find a link to it.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

ric323 wrote:I suspect your profile is trying to select a theme which hasn't been installed on the new server.
I reuploaded every single folder from the old server. Including the alternative themes. I gave the MOD you suggested a try. It ran the script ok and returned the data as well as the general status message of "All queries have been run successfully."

Upon returning to the forum index I still get the message die notification as posted in my previous post.

I'm starting to worry that I might not ever resolve this painful transfer!
or, there is a MOD do display the individual error messages. I'll try to find a link to it.
Thanks once again for your help. Much appreciated at this stressful time.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 »

Here's the MOD to see the individual error messages:
http://www.phpbb.com/community/viewtopic.php?t=233542
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

ric323 wrote:Here's the MOD to see the individual error messages:
http://www.phpbb.com/community/viewtopic.php?t=233542
Thank you so much. From the error it appears that it's due to the fact that my board was in disabled mode when I exported the database.
Critical Error!
message_die() was called multiple times.

--------------------------------------------------------------------------------
Error #1
Information
Board_disable


--------------------------------------------------------------------------------
Error #2
Error creating new session

Line : 189
File : /home/sapower/public_html/forum/includes/sessions.php
SQL : INSERT INTO phpbb_sessions (session_id, session_user_id, session_start, session_time, session_ip, session_page, session_logged_in, session_admin) VALUES ('b9cf9cf310f197d03b863b5fcbd82211', -1, 1226612889, 1226612889, '5c06b653', 0, 0, 0)
I'm assuming I just need to re-enable the board. How can I perform this without access to admin cp? Thanks for the excellent help.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 »

The admin toolkit (link in my sig) can do that. Try it, it's useful for much more also.

The second error probably means your sessions table doesn't exist at all. I'll find the SQL query to recreate it.

Here you go.

Code: Select all

CREATE TABLE phpbb_sessions (
   session_id char(32) DEFAULT '' NOT NULL,
   session_user_id mediumint(8) DEFAULT '0' NOT NULL,
   session_start int(11) DEFAULT '0' NOT NULL,
   session_time int(11) DEFAULT '0' NOT NULL,
   session_ip char(8) DEFAULT '0' NOT NULL,
   session_page int(11) DEFAULT '0' NOT NULL,
   session_logged_in tinyint(1) DEFAULT '0' NOT NULL,
   session_admin tinyint(2) DEFAULT '0' NOT NULL,
   PRIMARY KEY (session_id),
   KEY session_user_id (session_user_id),
   KEY session_id_ip_user_id (session_id, session_ip, session_user_id)
);

Code: Select all

CREATE TABLE phpbb_sessions_keys (
  key_id varchar(32) DEFAULT '0' NOT NULL,
  user_id mediumint(8) DEFAULT '0' NOT NULL,
  last_ip varchar(8) DEFAULT '0' NOT NULL,
  last_login int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (key_id, user_id),
  KEY last_login (last_login)
);
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

wow. I didn't think i'd see the day, but my forum seems to be back online!

I take it now I must run the search mod?

One problem I have now is how can I ensure that nothing really did get lost in the database during the transfer? I mean i'm paranoid that something was lost and I won't realise it until a few weeks or even months down the line....

Thanks for your help ric. You've been brilliant - I really appreciate it.

User avatar
ric323
Former Team Member
Posts: 22909
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Transferring a large phpbb2 database to new server

Post by ric323 »

MrGibbonz wrote:I take it now I must run the search mod?
yes
One problem I have now is how can I ensure that nothing really did get lost in the database during the transfer? I mean i'm paranoid that something was lost and I won't realise it until a few weeks or even months down the line....
Just make sure you can read posts that were made a long time ago, and also recently.
check the profiles of an old and a new member.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

I've installed the search mod but when trying to begin the process in admin cp I get the following error:
General Error
Error in deleting post

DEBUG MODE

SQL Error : 1146 Table 'sapower_rhcp.phpbb_search_wordmatch' doesn't exist

DELETE FROM phpbb_search_wordmatch WHERE post_id IN (13)

Line : 419
File : functions_search.php
I'm assuming its because i need the actual search tables added back in the database like the session table queries you gave me? I'm not sure of the paramaters required to run the mysql query to reinstate the three tables? I deleted all three search tables during the transfer.

phpbb_search_results
phpbb_search_wordlist
phpbb_search_wordmatch

I found the following three queries to run in order to resinstate these three tables... are they correct?
# Table: 'phpbb_search_results'
CREATE TABLE phpbb_search_results (
search_key varchar(32) DEFAULT '' NOT NULL,
search_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
search_keywords mediumtext NOT NULL,
search_authors mediumtext NOT NULL,
PRIMARY KEY (search_key)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;


# Table: 'phpbb_search_wordlist'
CREATE TABLE phpbb_search_wordlist (
word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
word_text varchar(255) DEFAULT '' NOT NULL,
word_common tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
word_count mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
PRIMARY KEY (word_id),
UNIQUE wrd_txt (word_text),
KEY wrd_cnt (word_count)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;


# Table: 'phpbb_search_wordmatch'
CREATE TABLE phpbb_search_wordmatch (
post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
word_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
title_match tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
UNIQUE unq_mtch (word_id, post_id, title_match),
KEY word_id (word_id),
KEY post_id (post_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

User avatar
Lumpy Burgertushie
Registered User
Posts: 67051
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Transferring a large phpbb2 database to new server

Post by Lumpy Burgertushie »

no, those are for phpbb3 I believe. here are the ones for phpbb2:

Code: Select all

#
# Table structure for table `phpbb_search_results`
#
CREATE TABLE phpbb_search_results (
  search_id int(11) UNSIGNED NOT NULL default '0',
  session_id char(32) NOT NULL default '',
  search_time int(11) DEFAULT '0' NOT NULL,
  search_array mediumtext NOT NULL,
  PRIMARY KEY  (search_id),
  KEY session_id (session_id)
);


# --------------------------------------------------------
#
# Table structure for table `phpbb_search_wordlist`
#
CREATE TABLE phpbb_search_wordlist (
  word_text varchar(50) binary NOT NULL default '',
  word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
  word_common tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY (word_text),
  KEY word_id (word_id)
);

# --------------------------------------------------------
#
# Table structure for table `phpbb_search_wordmatch`
#
CREATE TABLE phpbb_search_wordmatch (
  post_id mediumint(8) UNSIGNED NOT NULL default '0',
  word_id mediumint(8) UNSIGNED NOT NULL default '0',
  title_match tinyint(1) NOT NULL default '0',
  KEY post_id (post_id),
  KEY word_id (word_id)
);
luck,
robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

Uh oh... looks like i've made a major boo boo then as I've run those queries I posted! :?

I also started the rebuild search process last night after adding the 3 tables like that! It's taken me half a day and the progress is not even at 50% yet! theres over 682,648 posts for it to go through.

Should I stop what I'm doing and reinstate the tables with the queries you've given me? :oops:

User avatar
Lumpy Burgertushie
Registered User
Posts: 67051
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Transferring a large phpbb2 database to new server

Post by Lumpy Burgertushie »

MrGibbonz wrote:Uh oh... looks like i've made a major boo boo then as I've run those queries I posted! :?

I also started the rebuild search process last night after adding the 3 tables like that! It's taken me half a day and the progress is not even at 50% yet! theres over 682,648 posts for it to go through.

Should I stop what I'm doing and reinstate the tables with the queries you've given me? :oops:
well, if you created those tables in the database, then the rebuild search should not even be working so I am not sure what is going on. can you access phpmyadmin and see if there is any data in any of those search tables yet?

robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
MrGibbonz
Registered User
Posts: 66
Joined: Thu Feb 02, 2006 9:45 pm
Location: London

Re: Transferring a large phpbb2 database to new server

Post by MrGibbonz »

Lumpy Burgertushie wrote:
MrGibbonz wrote:Uh oh... looks like i've made a major boo boo then as I've run those queries I posted! :?

I also started the rebuild search process last night after adding the 3 tables like that! It's taken me half a day and the progress is not even at 50% yet! theres over 682,648 posts for it to go through.

Should I stop what I'm doing and reinstate the tables with the queries you've given me? :oops:
well, if you created those tables in the database, then the rebuild search should not even be working so I am not sure what is going on. can you access phpmyadmin and see if there is any data in any of those search tables yet?

robert
In the end I reinstated them with the correct queries. It does seem strange, but rebuild search was working with them! well the progress page was updating the data etc. I didn't check the tables directly though before deleting them.

I'd just like to thank everyone who helped me with the database nightmare I've had over the past few days, and thank you for being patient with me throughout. Your help is very much appreciated and won't be forgotten.

You guys are a credit to mankind.

Locked

Return to “2.0.x Support Forum”