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:
QuoteNotice: 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:
QuoteNotice: 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):
QuoteConverting 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?
try the yabb21_to_smf.php from here:
http://www.simplemachines.org/community/index.php?topic=248353.msg1609777#msg1609777
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.
Open the yabb21_to_smf.php file
Find:
convert_query("
DELETE FROM {$to_prefix}log_topics
WHERE ID_TOPIC OR ID_MEMBER");
Replace:
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? (http://www.simplemachines.org/community/index.php?topic=21919.0)).
If you do run it in phpMyAdmin just run this query
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 :)
OK, I tried that and now the error has changed to:
QuoteNotice: 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
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.
Hi SleePy,
OK, tried that, now I'm getting this error:
QuoteNotice: 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
So, after changing
QuoteDELETE `smf`.smf_log_topics
SET ID_TOPIC = 1;
to
Quote
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:
Quote
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?
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 :)
Quote from: SleePy on July 15, 2008, 01:03:43 PM
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:
QuoteINSERT 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);
Quote from: SleePy on July 15, 2008, 01:03:43 PM
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! :)
Quote from: SleePy on July 15, 2008, 01:03:43 PM
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 :)
:D
Heres the updated file.
Let me know if you have luck.
Its the last step in the converter, so you are very close :)
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:
'dateRegistered' => (int) parse_time($data['regdate']),Replace:
'dateRegistered' => $data['regtime'],
I then started to run a conversion. That worked!
It stopped at cstep 8 with:
QuoteConverting 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:
while ($row = mysql_fetch_assoc($result))
convert_query("
DELETE {$to_prefix}log_topics
SET ID_TOPIC = $row[ID_TOPIC]
LIMIT 1");
to:
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:
Quote
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:
Quote
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:
Quote
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:
while ($row = mysql_fetch_assoc($result))
convert_query("
DELETE {$to_prefix}poll_choices
SET ID_POLL = $row[ID_POLL]
LIMIT 1");
to this:
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.
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.
Thanks SleePy. I really appreciate all the work you put in to help make this work! :)
Hi SleePy, are you making any progress on this problem?
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.
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:
Quote
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
If you go to the Structure for the topics and messages table.
What does it show for the indexes?
Topics:
Quote
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 7630 ID_TOPIC
isSticky INDEX 2 isSticky
ID_BOARD INDEX 18 ID_BOARD
Messages:
Quote
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
Ok.. Topics table is missing a few indexes
You will need to create a few indexes
It should work if you run this query:
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:
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)
I copied and pasted that into phpMyAdmin and got the following:
Quote
Error
SQL query:
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
)
MySQL said:
#1064 - 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 'ADD UNIQUE ID_BOARD topic (ID_BOARD, ID_MSG)
ADD UNIQUE ID_MEMBER topic (ID_MEM' at line 3
OK, worked it out. That should have been:
ALTER TABLE smf_messages
ADD UNIQUE INDEX topic (ID_TOPIC, ID_MSG)
ADD UNIQUE INDEX ID_BOARD (ID_BOARD, ID_MSG)
ADD UNIQUE INDEX ID_MEMBER (ID_MEMBER, ID_MSG)
Is that right?
Scratch what I just posted..
Did that work?
What do the indexes does it show currently?
I mucked it up, so I've had to start the conversion from scratch again! :(
With over 8000 members and 250000 messages, it'll take a while! I'll report back in a few hours and let you know!
OK, it timed out while converting poll votes again. :(
I killed the connection. Added the missing indexes and refreshed the browser. No luck. Will try again in the morning.
As an experiment, I restarted the converter at cstep14, Cleaning up (part1).
Got an error for Duplicate key name 'topic' in smf_messages. So I dropped 'topic' and hit 'Try again'. It added 'topic' back.
Next I got an error for Duplicate key name 'ID_BOARD', so I dropped that one too and hit 'Try again'. It advanced to giving me an error for Duplicate key name 'ID_TOPIC', but it didn't add ID_BOARD back.
The same happened for ID_TOPIC and ID_MEMBER.
The same thing happens in the next cstep too. It's all the indexes you got me to add.
With my limited understanding of PHP and MySQL I'd have thought that cstep should have added those indexes back, hence the errors that they where already there. So why are they not getting added? Or am I missing something?
My only thought could be that for some reason the script isn't being allowed to do the index dropping or additions that it does.
I guess drop them for now. Then after that is done with topics and posts, add them back in.
Hurrah! It worked! It says Conversion Complete! Woo Hoo!!
The only problem is that polls didn't convert properly. All the poll topics and posts are there but none of the polls themselves have any options to choose. Also the 'Maximum votes per user' is set to 0 for all polls.
OK, I got the vote options to come up. Had to run:
ALTER TABLE smf_messages
ADD KEY ID_TOPIC (ID_TOPIC)
Seemed that one wasn't getting added in cstep14.
As for the Maximum votes per user issue, it seems that only about 35 polls out of almost 300 have the right number for max votes (1). All the rest have 0.