Migrating a phpBB Forum

[Comandante G]

We are trying to move a phpBB forum to a different host.

We try two methods with similar results, none was successfull.

1) Export DB as a dump

2) Copy DB to a different Host

All data bases generate a dump file of about 57.8Mb and I wonder if there is any restriction regarding DB size or having same MySQL level at both Hosts.

While SQLyog says the operation was successfull, posts have disapeared in the new site, only a portion of total members seems to be in the new forum, and so on.

Input forum: http://www.dolaraldia.com/phpBB2/

Output (copy): http://www.foromercado.com.ar/phpbb2/index.php

[CalEvans]

IMHO,

If you are dumping from one version of mysql and importing into another, I would use sqlyog instead of mysqldump. (Which sounds like what you are doing but I felt the need to state the obvious)

Run record counts on all your tables before and after you do either the copy or the dump/restore and compare them. I've never done the copy to a different host but I've used dump/restore with much larger phpBB installs that you are describing and it worked without a problem.

=C=

[Comandante G]

OK, while I tried different options, what I'm describing here were both (back up/restore or Copying to a different host) made using SQLyog.

While last copy was made several days ago, and thus live (INPUT) data base continue to be updated, there are big differences between current Forum and the copy in some databases:

INPUT (current may have some increment, but in this case looks excesive)

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment

---

---

---

---

---

---

---

---

---

---

---

phpbb_posts MyISAM 7 Dynamic 21335 33 721560 801792 0 23156

phpbb_posts_text MyISAM 7 Dynamic 21335 681 14532068 154624 0 (NULL)

phpbb_privmsgs MyISAM 7 Dynamic 2169 46 100380 54272 0 5448

phpbb_privmsgs_text MyISAM 7 Dynamic 4107 389 1603936 31744 4452 (NULL)

phpbb_search_wordlist MyISAM 7 Dynamic 83322 20 1689036 1505280 0 90456

phpbb_search_wordmatch MyISAM 9 Fixed 1108571 8 8868568 17165312 0 (NULL)

phpbb_sessions HEAP 9 Dynamic 42 311 176720 28560 157988 (NULL)

phpbb_users MyISAM 9 Dynamic 437 185 80932 11264 60 (NULL)

OUTPUT (New Copy)

Name Engine Version Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment

---

---

---

---

---

---

---

---

---

---

phpbb_groups MyISAM 9 Dynamic 437 24 10544 9216 0 455

phpbb_posts MyISAM 9 Dynamic 755 33 25056 40960 0 8284

phpbb_posts_text MyISAM 9 Dynamic 15543 704 10949364 155648 0 (NULL)

phpbb_privmsgs MyISAM 9 Dynamic 872 47 41012 25600 0 5421

phpbb_privmsgs_text MyISAM 9 Dynamic 369 359 132688 5120 0 (NULL)

phpbb_ranks MyISAM 9 Dynamic 1 24 24 2048 0 2

phpbb_search_results MyISAM 9 Dynamic 2 5352 10704 3072 0 (NULL)

phpbb_search_wordlist MyISAM 9 Dynamic 991 20 19904 25600 0 4958

phpbb_search_wordmatch MyISAM 9 Fixed 1374 8 10992 27648 0 (NULL)

phpbb_sessions HEAP 9 Dynamic 2 311 15600 1200 0 (NULL)

phpbb_users MyISAM 9 Dynamic 303 183 55572 9216 0 (NULL)

I wonder why Copy function does not provided identical databases.

Do you mean you moved databases from different hosts using dump and restore functions?

[Ritesh]

Are you sure that your live server didnt have so many updation after the backup?

Many of customers like CalEvans have used it to backup/restore their data without any problem.

BTW: Have you tried our Data Sync Tool? A nice tutorial/article can be found at:

Quote:

http://www.sitepoint.com/article/mysql-data-sqlyog-job-agent

An addtional article on how to use it effectively can be found at http://www.webyog.com/forums/index.php?act…st=0&#entry6118

[peterlaursen]

some questions:

1) are there som “mods” with that forum. Some “mods” may alter the config tables of the DB. Other “mods” may alter the php-code. Porting a “modded” forum can be very complicated. Mods and data import have to be applied in the right order.

2) Did you copy your old php-code to new server (phpbb2-library) ?

3) MySQL and php-version on the servers ?

If SQLyog says that everything was imported it could be that not everything was exported ?

Data also may be there but not used by the code ?

Did you try the forum at http://www.phpbb.com/ ??

[Comandante G]

Let me answer

1) Both systems (actual and new) are using same phpBB level (copied from one site to the other)

2) It is now clear that data bases are not the same. I checked qty of records thanks to CalEvans recommendation and discovered that record counts are different, as shown above. But we suspect that MySQL and php levels should be different since the different hosts is due we intend to use different vendors.

3) We read phpBB forums and used many of the ideas proveded. For instance, we used StarFosTj toolkit because phpBB forums include URL identification inside the Data Base, so once the data bases are copied, we run tookit to update the URL for the new DB.

3) While we started using standard phpBB back up and restore functions, and thus having problems, we were advised of SQLyog program as a vehicle to move data bases, and if we are successfull to use it as a back up restore tool in the future.

4) We used both back up and restore and DB Copy DB to diferent Host, both with bad results.

5) We stop the live database during the backup or copy database function using a phpBB administrative function

6) Yes, data copied from actual forum is being used by the new forum. The problem is that there are missing data in the new forum (only part of the forums is visible at the new forum) because the problem to get both DBs equal.

7) I am reading the referenced documentation regarding SJA, but I understand that this may be a way of keeping the databases at both hosts at the same level once you have copied the original and want to update the new DB while the current is still being updated. But perhaps I may try to use it to let SQLyog program to check if DBs at both hosts and give it a second chance to live the equal.

[peterlaursen]

Quote:

… discovered that record counts are different, as shown above

I can't explain this.

(BTW: where is above ?)

If could be a problem to copy the Forum php-code from one host to antother if MySQL and php versions are “too much different”, but it is not the reason why rows are missing with the data import. That could cause problems when running the BB-software …

[peterlaursen]

well ..

we must start finding out whether it is export or import that fails!

I propose you do this:

Exporting with SQLyog you should try to uncheck the “Create Bulk Insert Statements” in the export dialogue.

Then SQLyog will only put one row of data in each stmnt – each on its own line in the .sql-file.

Then open file in an editor with line-numbers and check the number of insert statements and compare with count(*) on source server.

Actually unchecking the “Create Bulk Insert Statements” could prove to be a solution itself …

[peterlaursen]

I don't know if this bug

http://www.webyog.com/forums/index.php?act…t=ST&f=6&t=1504

could have influenced ..

(it might have been in existence with SQLyog for a very long while)

and with 4.07 RC1 there's another bug

http://www.webyog.com/forums/index.php?act…�entry6208

I believe that 4.07 will be “solid as rock” when that last bug is fixed. It probably will be tomorrow.

[Comandante G]

OK, then I will do:

1) Connect to input (current) db to SQLyog and click DB. Check and save record counts for future control

2) Go to:

DB

Export DatabBase as SQL Dump

Checked:

-Structure and data

-Include USE…..

-Add Create DB

-Inlcude Drop Table

-Lock all tables for read

Unchecked:

-Flush logs…

-Set FOREIGN KEY

-Add lock Arround Insert

-Create Bulk Insert Statements

Having a good copy of data sonds a good start

Thank you

[Comandante G]

OK, I did a Export from the current forum and then I made ain import into the new host

Some error messages were obtained, but since error report may mix wiht older error messages, I compared both structures

For instance (those are not the unique differences) there were differencies in the categories database. Then I take all records from the dump.sql file to this database (drop, create and input statements until next database) and I run this import statements again.

No error message was obtained

The records in the new host was almost the same as previous import, and different from current database (from export):

_______Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

_______

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

INPUT phpbb_categories MyISAM 7 Dynamic 3 26 80 4294967295 3072 0 10 2002-12-17 18:31:17 2005-05-20 14:48:43 2005-04-11 13:15:36 latin1_swedish_ci (NULL)

OUTPUT phpbb_categories MyISAM 9 Dynamic 3 25 76 4294967295 3072 0 10 2005-07-01 11:22:31 2005-07-01 11:22:33 (NULL) latin1_swedish_ci (NULL)

[Ritesh]

If the data is not confidential then can you mail me the exported .SQL file?

[Comandante G]

OK, complete dump takes 90 Mb, so I will split and send those with different records in DB output and small size.

[peterlaursen]

@commandante:

are you sure that your sql-dump is consistent with the source DB (that everything is there) ?

[Comandante G]

Tanks to your responses I started to make a more detailed review.

The problem may be in something with the new host. When you use SQLyog to make a copy to a different host, it may say that everything is “OK” when really it is not ok if there were a problem that perhaps the program could not detect.

I made a complete dump (export) from the current Forum using SQLyog and also take the details of the data bases (record counts, etc…) and saved also this picture.

Then I made a restore (import) in the new host. A problem of SQLyog is that errors are written in a file stored in the Program Files folder, and errors appended to others and it is not easy to know when an error was written.

Also, if you look to the “/*Database Information For – phpBB2*/” it may show an old picture if you made a new restore.

I took the whole SQL dumpl file and restored again one of the data bases with inconsistent record count at new host.

I checked the INSERT INTO records from the export from the current database and find with excel that there were just as insert records as rows in the current forum. But substantially less records were shown as stored in the new host database and no error messages were written at SQLyog.err file. In a previous import there were a message saying that there was a lost connection to SQL.

Then I made a dump from the new database, I found the new dump got exactly the same qty of records form the current forum. Then I disconnect the new host database and connected again, and for this specific database (posts) it showed the same qty of records from the current database and both dumps.

THEN:

1) It may be problems documented and not easily discovered.

2) sometimes may be a problem when restoring a database, and that this problem is not documented. Then you may assume according to SQLyog messages that everything is OK when it is not OK.

Only the record counts may show to you that everything is OK.

[Ritesh]

Quote:

When you use SQLyog to make a copy to a different host, it may say that everything is “OK” when really it is not ok if there were a problem that perhaps the program could not detect.

Very strange. What kind of error you suspect that might occur?

Quote:

A problem of SQLyog is that errors are written in a file stored in the Program Files folder, and errors appended to others and it is not easy to know when an error was written.

Whenever SQLyog comes across an error, it will display a MessageBox notifying you about it. Starting from v4.07, the error dialog will also have a button to open SQLyog.err.

Quote:

Then I disconnect the new host database and connected again, and for this specific database (posts) it showed the same qty of records from the current database and both dumps.

You mean to say that just reconnecting to the new hosts solved the problem?

Is it possible that you upload the dump at one of your server from where we can download it? 90MB download should not be a problem.

[peterlaursen]

For my part I must say that I step down from this question.

I'm not able to understand the exact nature of your problem.

[Comandante G]

You know how this business is. You believe you have done……..

What I could remember, but I could not assure is that I started making a full copy of all databases (export and Import). When importing I think I received a duplicate record error message. Please refer to a duplicate error message later on.

Then I went to copy from host to host and it seems everything was OK, but it wasn't.

Then I started this forum and started to be more carefull of what's going on.

Then I made a new dump (export), take record counts and split dump for each DB

It seems that everithing was going OK DB trou DB, but suddendly with the search_wordlist data base (the second largest) I got an error message “Error Code: 2013 – Lost connection to MySQL server during query” and “Error Code: 1053 – Server shutdown in progress”

It seems that something happend to MySQL at my new host provider.

I think that perhaps during initial copy from host to host, SQLyog may have experienced this problem without reporting an error. I am not sure, but not error message that I could remember (I was happy at that time)

I started to import that DB again and I received in the middle an error message “Line no.:22269 Error Code: 1062 – Duplicate entry 'migrar' for key 1” (import again means to drop database and create a new one again and insert again all records). This does not mean it was an SQLyog problem

I started to import that DB again and that point was overpassed without duplicate record message. Unfortunatelly the MySQL has gone away other time due to systems maintenance.

[Ritesh]

Looks like there is a problem with your new server or your data.

“Error Code: 2013 – Lost connection to MySQL server during query” and “Error Code: 1053 – Server shutdown in progress” and “Line no.:22269 Error Code: 1062 – Duplicate entry 'migrar' for key 1”

[Comandante G]

Yes, I think that perhaps sometimes it may be a SQL service down for same time (perhaps only seconds), but this may affect a long import.

For instance, in the last event that I've received this message, I got in touch with host service support and they said there was and SQL update in progress.

But it is not yet clear to me the duplicate record message.

I have already successfully imported all databases but search_wordmatch wich is far the largest. It contains 1114032 records and I think it will take more than one full day to be imported.

Since copy DB to different host is much faster, I've tried to use this functions instead of import from previous dump.

Then I used copy to different host, unselect all databases, select this one, SQLyog said it was doing it, and half one hour later I received a “succesfull copied” , message from SQLyog. Then I disconect DB, connect again (remember that record counts are not dinamically updated) and the DB has only 2654 records. I do not know what it happends, but it is clear that something happends and DB was not successfully copied.

Now I went back to import to get a copy. The problem is the time it requires to have the work done.

[Author]

Posts