Errors converting YaBB 2.1 to SMF 1.1.5

[JMB]

Hi,

I'm trying to convert a YaBB 2.1 forum to SMF 1.1.5. I'm running it all locally on Mac OS 10.5.4. I'm using the latest convert.php and yabb21_to_smf.php files from the sticky on this board.

Every 'Converting..." page has the following error at the top:

Notice: Undefined variable: command_line in /Library/WebServer/Documents/smf/convert.php on line 1071

Right at the start of the conversion, at cstep=2, while converting members, I get pages full of the following error:

Notice: Undefined index: bday in /Library/WebServer/Documents/smf/yabb21_to_smf.php on line 325

The conversion continues and then bombs out at cstep=8, while converting topics (part 2):

Converting topics (part 2)... Unsuccessful!
This query:
ALTER TABLE `smf`.smf_log_topics
ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER),
DROP COLUMN tempID;

Caused the error:

Duplicate entry '0-0' for key 1

Anyone have any ideas on these problems?

[ThorstenE]

try the yabb21_to_smf.php from here:
http://www.simplemachines.org/community/index.php?topic=248353.msg1609777#msg1609777

[JMB]

Hi,

I've just tried again using the version of yabb21_to_smf.php in that post. No change. All the same errors as before.

[SleePy]

Open the yabb21_to_smf.php file

Find:

Code Select Expand

            convert_query("
                DELETE FROM {$to_prefix}log_topics
                WHERE ID_TOPIC OR ID_MEMBER");


Replace:

Code Select Expand

            convert_query("
                DELETE FROM {$to_prefix}log_topics
                WHERE ID_TOPIC = 0
                    OR ID_MEMBER = 0");


You could rerun the converter file. or if you wanted you could quickly run this single command in phpMyAdmin (
What is phpMyAdmin?).
If you do run it in phpMyAdmin just run this query

Code Select Expand


                DELETE FROM smf_log_topics
                WHERE ID_TOPIC = 0
                    OR ID_MEMBER = 0

It appears your prefix is smf_ so you won't need to worry about changing that

[JMB]

OK, I tried that and now the error has changed to:

Notice: Undefined variable: command_line in /Library/WebServer/Documents/smf/convert.php on line 1071
Converting topics (part 2)... Unsuccessful!
This query:

ALTER TABLE `smf`.smf_log_topics
ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER),
DROP COLUMN tempID;

Caused the error:

Duplicate entry '49-639' for key 1

[SleePy]

OK,

Give this one a try.
I added another substep in the converter that attempts to detect the duplicate ids in the logs table and removes them before doing that step.

You shouldn't need to start it over.

[JMB]

Hi SleePy,

OK, tried that, now I'm getting this error:

Notice: Undefined variable: command_line in /Library/WebServer/Documents/smf/convert.php on line 1071
Converting topics (part 2)... Unsuccessful!
This query:

DELETE `smf`.smf_log_topics
SET ID_TOPIC = 1;

Caused the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET ID_TOPIC = 1' at line 1

[JMB]

So, after changing

DELETE `smf`.smf_log_topics
SET ID_TOPIC = 1;

to

DELETE FROM {$to_prefix}log_topics
WHERE ID_TOPIC = $row[ID_TOPIC]");

in cstep 8, all is well until converting poll votes in cstep 18.

I keep getting the following error:

Converting poll votes... Unsuccessful!
This query:

ALTER TABLE `smf`.smf_poll_choices
ADD PRIMARY KEY (ID_POLL, ID_CHOICE);

Caused the error:

Duplicate entry '1-0' for key 1

Looks similar to the error I got when converting topics (part 2), but I don't know much about PHP or MySQL, so I'm not sure what to do about it!

Any ideas on this one guys?

[SleePy]

Yea it is similar to the first error.

Do you mind if you go to your SMF database. Look in the smf_log_topics table. Is there anything in there?

I wonder if for some reason the temp_id wasn't being set right.

As for the polls, If I Remember tonight I can write the same code I did for the log topics for the polls to remove duplicates.

btw, that command line error I have a fix for it. I just need to get to my home computer and upload the fixed convert.php

[JMB]

Do you mind if you go to your SMF database. Look in the smf_log_topics table. Is there anything in there?

No problem. There are five items there. Here's a dump from phpMyAdmin:

INSERT INTO `smf_log_topics` (`ID_MEMBER`, `ID_TOPIC`, `ID_MSG`) VALUES
(4, 7595, 0),
(1, 621, 0),
(1, 1041, 0),
(1, 7482, 0),
(1, 435, 0);

As for the polls, If I Remember tonight I can write the same code I did for the log topics for the polls to remove duplicates.

That would be great, thank you! 

btw, that command line error I have a fix for it. I just need to get to my home computer and upload the fixed convert.php

[SleePy]

Heres the updated file.

Let me know if you have luck.
Its the last step in the converter, so you are very close

[JMB]

Thanks SleePy.

I'm using your new updated convert.php now along with the yabb21_to_smf.pl attached in your last post.

First, I made a slight change on line 319 of your attached yabb21_to_smf.php file. On previous conversion attempts, I had noticed that the time since registration for members wasn't being converted. I found a post on the forum with the following fix:

Find:

Code Select Expand

'dateRegistered' => (int) parse_time($data['regdate']),

Replace:

Code Select Expand

'dateRegistered' => $data['regtime'],


I then started to run a conversion. That worked!

It stopped at cstep 8 with:

Converting topics (part 2)... Unsuccessful!
This query:

DELETE `smf`.smf_log_topics
SET ID_TOPIC = 1
LIMIT 1;

Caused the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET ID_TOPIC = 1
LIMIT 1' at line 1

This look familiar, so I changed:

Code Select Expand

while ($row = mysql_fetch_assoc($result))
convert_query("
DELETE {$to_prefix}log_topics
SET ID_TOPIC = $row[ID_TOPIC]
LIMIT 1");


to:

Code Select Expand

while ($row = mysql_fetch_assoc($result))
convert_query("
DELETE FROM {$to_prefix}log_topics
WHERE ID_TOPIC = $row[ID_TOPIC]
LIMIT 1");


and then got the following error:

Converting topics (part 2)... Unsuccessful!
This query:

ALTER TABLE `smf`.smf_log_topics
ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER),
DROP COLUMN tempID;

Caused the error:

Duplicate entry '49-107' for key 1

As this step had worked before, I checked the code in the previous version of yabb21_to_smf.php. There wasn't the 'LIMIT 1' statement before, so I deleted that and tried again. That seemed to work and the converter carried on until cstep 13, here is the error:

Converting attachments (if the mod is installed)... Unsuccessful!
This query:

SELECT ID_MSG, temp_filename
FROM `smf`.smf_messages
WHERE temp_filename != ''
LIMIT 0, 100;

Caused the error:

Unknown column 'temp_filename' in 'field list'

As I don't have attachments turned on in my YaBB forum, I probably don't need this step. So I added a return statement at the start of the convertStep13 function. That got me past that step.

Then in cstep 18 I got this error:

Converting poll votes... Unsuccessful!
This query:

DELETE `smf`.smf_poll_choices
SET ID_POLL = 1
LIMIT 1;

Caused the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET ID_POLL = 1
LIMIT 1' at line 1

Same problem as cstep 8, so I changed the code from:

Code Select Expand


while ($row = mysql_fetch_assoc($result))
convert_query("
DELETE {$to_prefix}poll_choices
SET ID_POLL = $row[ID_POLL]
LIMIT 1");


to this:

Code Select Expand


while ($row = mysql_fetch_assoc($result))
convert_query("
DELETE FROM {$to_prefix}poll_choices
WHERE ID_POLL = $row[ID_POLL]
LIMIT 1");


And then at:

convert.php?step=1&substep=17075&cstep=18&start=0

I get a 'server is not responding' error from the browser. I checked the MySQL server using MySQL Administrator. The server is busy doing something and there is a connection active:

id     |User  |Host                   |DB     |Command   |Time   |State                             |Info
59    root    localhost:51894             Query           7328    Copying to tmp table      SELECT t.ID_TOPIC, COUNT(m.ID_MSG) AS numMsg

It's been stuck like that for hours. I tried refreshing the page in the browser and it doesn't even time out this time, just sits there trying to load the page.

Any ideas?

I've attached the yabb21_to_smf.php file in it's current edited state, maybe that'll help.

[SleePy]

Ouch, copying to temp table.

Thats most likely due to a missing index in the conversion.

I emailed myself a big notice and when I get time in the next couple days will take a look at the query to see which index may be missing.

[JMB]

Thanks SleePy. I really appreciate all the work you put in to help make this work! 

[JMB]

Hi SleePy, are you making any progress on this problem?

[SleePy]

I haven't had much time but the query shouldn't be that big.

The only issue I can see is miss linked topics without a correct message linked to them.

[JMB]

OK. So I started with a new backup of the live YaBB forum, after I'd run all the maintenance routines. Hoping that would fix any miss linked topics.

The browser still timed out and MySQL Administrator is showing the same 'Copying to tmp table' as before, but this time the conversion got past converting the polls and got to 'Recalculating forum statistics':

convert.php?step=2&substep=6&cstep=19&start=0

I killed the connection to the MySQL server with MySQL Administrator and refreshed the browser. This time the browser didn't time out but it didn't progress either. So I killed the connection again and the converter gave the error:

Recalculating forum statistics... Unsuccessful!
This query:

SELECT t.ID_TOPIC, COUNT(m.ID_MSG) AS numMsg
FROM `smf`.smf_topics AS t
LEFT JOIN `smf`.smf_messages AS m ON (m.ID_TOPIC = t.ID_TOPIC)
GROUP BY t.ID_TOPIC
HAVING numMsg = 0
LIMIT 0, 200;

Caused the error:

Lost connection to MySQL server during query

[SleePy]

If you go to the Structure for the topics and messages table.
What does it show for the indexes?

[JMB]

Topics:

Keyname    Type    Cardinality   Action   Field
PRIMARY     PRIMARY    7630          ID_TOPIC
isSticky     INDEX    2           isSticky
ID_BOARD    INDEX    18           ID_BOARD

Messages:

Keyname     Type    Cardinality   Action   Field
PRIMARY    PRIMARY    254586           ID_MSG
ipIndex    INDEX    127293          posterIP    15
                                                                ID_TOPIC
participation    INDEX    7714            ID_MEMBER
                                                                ID_TOPIC
showPosts          INDEX    19           ID_MEMBER
                                                                 ID_BOARD

[SleePy]

Ok.. Topics table is missing a few indexes

You will need to create a few indexes

It should work if you run this query:

Code Select Expand

ALTER TABLE smf_messages
ADD UNIQUE INDEX topic (ID_TOPIC, ID_MSG)
ADD UNIQUE ID_BOARD topic (ID_BOARD, ID_MSG)
ADD UNIQUE ID_MEMBER topic (ID_MEMBER, ID_MSG)

and then this query:

Code Select Expand

ALTER TABLE smf_topics
ADD UNIQUE INDEX poll (ID_POLL, ID_TOPIC)
ADD UNIQUE INDEX ID_BOARD (ID_BOARD, ID_MSG)
ADD UNIQUE INDEX lastMessage (ID_LAST_MSG, ID_BOARD)
ADD UNIQUE INDEX firstMessage (ID_FIRST_MSG, ID_BOARD)