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

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

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

[Comandante G]

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

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

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

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.

[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?

[Author]

Posts