PHPBB2 - problem with MySQL

[jacky]

I don't know too much in sql, which would be the code?

[Oldiesmann]

There are two files that you need for the converter - convert.php and phpbb2_to_smf.sql. Download the SQL file I attached to my last post in this topic and upload it to your SMF directory, overwriting the existing one.

[jacky]

Sorry I had not seen the file   

It's good, thanks you 

[Iehova]

I, like many others, am having trouble converting from phpBB2. I've tried every converter in this topic and I'm still getting the same (apparently common) problem:

Code Select Expand

Converting ranks... Successful.
Converting groups... Successful.
Converting smileys... Successful.
Converting members... Successful.
Converting additional member groups... Successful.
Converting categories... Successful.
Converting boards... Successful.
Converting topics... Unsuccessful!
This query:

    INSERT INTO `iehova_smf1`.smf_topics
    (ID_TOPIC, isSticky, ID_FIRST_MSG, ID_LAST_MSG, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_BOARD, ID_POLL, locked, numReplies, numViews)
    VALUES ('530', '0', '20682', '24773', '138', '138', '29', '0', '0', '268', '4812'),
    ('531', '0', '20683', '24096', '2', '9', '11', '0', '1', '25', '78'),
    ('532', '0', '20684', '21335', '2', '115', '30', '0', '0', '26', '59'),
    ('533', '0', '20696', '21281', '2', '115', '30', '0', '0', '26', '59'),
    ('534', '0', '20774', '20875', '2', '115', '30', '0', '0', '1', '11'),
    ('535', '0', '20828', '22015', '34', '34', '22', '0', '0', '8', '20'),

[...]

    ('1008', '0', '34081', '34081', '83', '83', '37', '0', '0', '0', '3'),
    ('1009', '0', '34082', '34085', '83', '83', '37', '0', '0', '3', '5'),
    ('1010', '0', '34089', '34091', '83', '83', '9', '0', '0', '2', '9'),
    ('1011', '0', '34094', '34094', '2', '2', '9', '0', '0', '0', '3'),
    ('1012', '0', '34095', '34099', '2', '2', '3', '0', '0', '2', '6');

Caused the error:

    Duplicate entry '6410-15' for key 2

Any ideas?

[mycroft]

Same here, same error.  ANY help would be appreciated; if I can't move my board from phpBB to SMF, I will have to stick with phpBB, which I'd rather *not* do.

I've tried the conversion routine on several phpBB boards I administer; sometimes I get the duplicate key for TOPICS and sometimes for MESSAGES, but it ALWAYS is a duplicate key error.

Help.

[Oldiesmann]

phpbb2_to_smf.sql

Find

Code Select Expand

/* // !!! WHERE t.topic_moved_id = 0 */

Replace

Code Select Expand

WHERE t.topic_moved_id = 0

I believe that will fix the problem.

[mycroft]

The version of phpbb2_to_smf.sql I have already has that particular change applied.  Is there a comparable analog to the actual message conversion routine?

Many thanks for your reply.

[Oldiesmann]

What specific error message are you getting for the messages?

[mycroft]

I have the conversion script running again now; it's the same duplicate key message, only during the post conversion.  I note that the script does bulk inserts; I know that's faster, but more difficult to recover from if there's an error.  What happens is that the post conversion section gets to a place where it fancies it has a duplicate post (same id_msg) and bombs out with the error message noted.

[Oldiesmann]

Hmmm... Try this one and see if it works.

[mycroft]

Running now; I have 326,000 posts to convert (at 120K now), and I'll post the results.  Thanks!

[mycroft]

Barfaroony at around 216000 while converting posts...

Converting...
Converting posts (this may take some time)... Unsuccessful!
This query:

    INSERT INTO `tbforum_prod`.smf_messages
    (ID_MSG, ID_TOPIC, posterTime, ID_MEMBER, posterEmail, subject, posterName, smileysEnabled, modifiedTime, posterIP, body, ID_BOARD, modifiedName, icon)
    VALUES ('241748', '17974', '1125459032', '7066', '[email protected]', 'Odd, harmless thing about you?', 'Thomas Matta', '1', '0', '68.255.26.132', 'I use my cell phone alarm, too! And sometimes I pay $1.99 for a wake up call!
    <br />
    <br />I\'m out of control!', '6', '', 'xx'),

...many other values deleted...

Caused the error:

    Duplicate entry '241748' for key 1

[mycroft]

Oldiesmann:

I ran your updated version of the .SQL last night with the added DISTINCT in the topics conversion; same tune, basically (see below).  Suggestions?

---

Converting posts (this may take some time)... Unsuccessful!
This query:

    INSERT INTO `tbforum_prod`.smf_messages
    (ID_MSG, ID_TOPIC, posterTime, ID_MEMBER, posterEmail, subject, posterName, smileysEnabled, modifiedTime, posterIP, body, ID_BOARD, modifiedName, icon)
    VALUES ('17931', '1087', '1051876440', '3324', '', 'Trombone ensemble names', '', '0', '0', '151.203.195.149', '</font><blockquote><font size=\"1\" face=\"Verdana, Geneva, sans-serif\">quote:</font><hr /><font
... miles of SQL deleted...

Caused the error:

    Duplicate entry '17931' for key 1

[mycroft]

Bit more info:

The post in question in the error above, ID 17931, does NOT exist twice in the phpBB installation.

When looking at the data already moved across to the smf_messages table, ID_MSG 17931 already DOES exist in the table.

It would *seem* that for some reason the converter attempted to move this one message from phpBB --> SMF twice.  I've also noticed that in every single Duplicate Key error I've seen, the key that is duplicated is *always* the first one in the bulk insert statement, never one of the IDs in the middle or at the end. 

I'm wondering if that is indicative of the query accidentally "double grabbing" that one row in phpBB.phpbb_posts...

[Oldiesmann]

Let me look at this some more and see if I can figure out what's going on. The messages table is cleared before anything is inserted, so I'm not sure why it's doing that.

[mycroft]

Many thanks.  Please let me know if you need any more details from my end.  Understandably, I am at a loss.  I've look at the code myself, and can see no logical reason why this would only happen *sometimes*.

R

[Oldiesmann]

Can you send me a copy of the data from your phpbb_posts and phpbb_topics tables? I've looked at the code as well and can't figure it out.

[mycroft]

Working....it's going to be pretty large, as I have about 326,000 posts...

[mycroft]

Data as SQL statements, CSV, or XML?

And do you want the posts_text table as well?

[mycroft]

In the interest of time, I've dumped the CSV files out and zipped them.  Location in a PM.