News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Converting big forum from Yabb to SMF

Started by evgenydeep, May 20, 2008, 06:53:08 AM

Previous topic - Next topic

evgenydeep

   Sorry to create separate thread, but... I really need your help with converting my average big forum.
I already tried to convert my forum, but unsucessfully, main problem was: very small speed of converting(~ 100 rows each 20-30 seconds).
   You may see all my posts in this community and this thread also:
http://www.simplemachines.org/community/index.php?topic=122338.340
   JayBachatero kindly agree to help me with convertion, I prepeared special account for him, but it looks that he is too much busy :-( So, I ask everyone to help me with this convertion process.

It was 2 months ago, so i even began to forget where and which problems appear.
So, I decide to run convertion from scratch, from 0.

Exist: 
Forum with
~ 1 500 000 messages in threads
~ 30 000 threads
~ 13 000 users
(~ 400 000 personal messages, but I'm unsure)


Process and problems: 
Using webisnstall I successfully set up at first russian interface package, then SMF 1.1.5.

I downloaded last convertion files from these topics:
http://www.simplemachines.org/community/index.php?topic=140741.0
http://www.simplemachines.org/community/index.php?topic=122338.0

I run convert.php

Problem 1.
During converting members, on each step I see error:
Notice: Undefined variable: command_line in /home/forumgr/public_html/smf001/convert.php on line 1148

Problem 2.
During converting members, almost on each step I one or several notice-errors like:
Notice: Undefined index: realname in /home/forumgr/public_html/smf001/yabb21_to_smf.php on line 303
Notice: Undefined index: email in /home/forumgr/public_html/smf001/yabb21_to_smf.php on line 304
Notice: Undefined index: postcount in /home/forumgr/public_html/smf001/yabb21_to_smf.php on line 308
Notice: Undefined index: userpic in /home/forumgr/public_html/smf001/yabb21_to_smf.php on line 316
Notice: Undefined index: regdate in /home/forumgr/public_html/smf001/yabb21_to_smf.php on line 317

Problem 3.
I see strange things in table "members". When count of inserted rows close to number of regstered members, table become empty, and "Converting members..." began again.

If Problem 1 and 2 looks not like a big problems, but Problem 3 looks serious.
Any ideas?

PS: i gies through all community forum, and found a few persons with similiar problmes.

evgenydeep

#1
I have another small forum on YaBB 2.1, I tried to convert it.
It converted quickly, becasue it is VERY, VERY small and have only 300 messages :-)

But during converting I got the same "error", like on big forum:
Notice: Undefined variable: command_line in /home/dorogush/public_html/smf02/convert.php on line 1148

Is this important notice-error or not?

SleePy

Try this for the convert.php

The second problem looks like it isn't able to find data it needs from the members information
Do you got any members that where deleted or something but still reside in the file database?

The third one I think I might know why it happens. It looks like it would be a little work to fix this as all steps need to be renamed almost to add a new step in.

We can try something though.
Open the .php script
Find:
        // Part 2: Now we get to resort the members table!
        if ($_GET['substep'] >= -1)
        {
            convert_query("
                ALTER TABLE {$to_prefix}members
                ORDER BY ID_MEMBER, dateRegistered");
            pastTime(-2);
        }
        if ($_GET['substep'] >= -2)
        {
            $knownKeys = array(
                'PRIMARY' => 'ADD PRIMARY KEY (ID_MEMBER)',
            );
            $alterColumns = array(
                'ID_MEMBER' => 'CHANGE COLUMN ID_MEMBER ID_MEMBER mediumint(8) unsigned NOT NULL auto_increment',
            );
            alterTable('members', $knownKeys, '', $alterColumns, true);

            pastTime(-3);
        }
        if ($_GET['substep'] >= -3)
        {
            convert_query("
                ALTER TABLE {$to_prefix}members
                ORDER BY ID_MEMBER");
        }


And replace it with:
        // Part 2: Now we get to resort the members table!
        if ($_GET['substep'] >= $file_n)
        {
            convert_query("
                ALTER TABLE {$to_prefix}members
                ORDER BY ID_MEMBER, dateRegistered");
            pastTime(++$file_n);
        }
        if ($_GET['substep'] >= $file_n + 1)
        {
            $knownKeys = array(
                'PRIMARY' => 'ADD PRIMARY KEY (ID_MEMBER)',
            );
            $alterColumns = array(
                'ID_MEMBER' => 'CHANGE COLUMN ID_MEMBER ID_MEMBER mediumint(8) unsigned NOT NULL auto_increment',
            );
            alterTable('members', $knownKeys, '', $alterColumns, true);

            pastTime(++$file_n);
        }
        if ($_GET['substep'] >= $file_n + 2)
        {
            convert_query("
                ALTER TABLE {$to_prefix}members
                ORDER BY ID_MEMBER");
        }


Lets see if that will get it going.
What I am seeing is the timeout protecting is kicking in right there and since it did it starts back over and for some reason the substep is 0 to its emptying the table again and starting over.

Hopefully making it not need to put those to a negative would fix it.
Otherwise I think splitting it out of the function might be the only way
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

evgenydeep

Sleepy, thanks a lot!

With help of Jay I runned local converting from command line on production server.
Here is output:

hosting# php -f convert.php (... all needed options was here...)

Array
(
   
=> convert.php
(...skipped...)
)
Converting membergroups... Successful.<br />
Converting members............................................ Successful.<br />
Converting settings... Successful.<br />
Converting personal messages...............................................
................................................ Successful.<br />
Converting boards and categories.... Successful.<br />
Converting mark read data... Successful.<br />
Converting topics (part 1)... Successful.<br />
Converting topics (part 2)...<br />

in step "Converting topics (part 2)" convertion process become VERY Slow
I looked into phpmyadmin for actual processes(SHOW PROCESSLIST)
and found that converting make SQL requests like:

Id=1209
User=smfgrod_smfuser
Host=localhost
Db=NULL
Command=Query
Time=0
State=Updating
Info=UPDATE `smfgrod_smf`.smf_log_topics\n                    SET ID_TOPIC = 708\n                    WHERE tempID = 1114842221

I noticed that quesries changes very slow, value of ID_TOPIC increases +1 approximately each second.
In table smf_log_topics I have 3,110,212 rows...

Imagine how long convertion process will take... 3,110,212 seconds? :-) (that's a 36 days)

this table have index
Name:ID_TOPIC
Type:INDEX
Number of elements: 10724

Anyway, I tried to wait

FIRST TRY: after half day convertion falling with following errors:
<b>Notice</b>:  Undefined variable: command_line in <b>/home/smfgrod/public_html/smf/convert.php</b> on line <b>1148</b><br />
<br />
<b>Notice</b>:  Undefined index:  email in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>306</b><br />
<br />
<b>Notice</b>:  Undefined index:  userpic in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>318</b><br />
<br />
<b>Notice</b>:  Undefined index:  email in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>306</b><br />
<br />
<b>Notice</b>:  Undefined index:  regdate in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>319</b><br />
<br />
<b>Notice</b>:  Undefined index:  realname in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>305</b><br />
<br />
<b>Notice</b>:  Undefined index:  email in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>306</b><br />
<br />
<b>Notice</b>:  Undefined index:  postcount in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>310</b><br />
<br />
<b>Notice</b>:  Undefined index:  userpic in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>318</b><br />
<br />
<b>Notice</b>:  Undefined index:  regdate in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>319</b><br />
<br />
<b>Notice</b>:  Undefined index:  teenagers in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>694</b><br />
<br />
<b>Notice</b>:  Undefined variable: endl in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>793</b><br />
Duplicate entry '22931-8027' for key 1
                ALTER TABLE `smfgrod_smf`.smf_log_topics
                ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER),
                DROP COLUMN tempIDhosting#

SECOND TRY: After more than 24 hours converting I stopped this process.
In update query  SET ID_TOPIC value was is about 25 000.


evgenydeep

PS:
looking on nearest topic http://www.simplemachines.org/community/index.php?topic=240691.0
I ran SELECT *  FROM `smf_log_topics`  WHERE ID_TOPIC = '22931' AND ID_MEMBER = '8027';
MySQL returned empy result.

Other question: if speed of convertion with this table can't be invreased or it may took some time to fix, + if this table is just mark read/unread topics - it is not a big problem, and I think I could leave this step of convertion. How to do that?

evgenydeep

My runned convertion can't process all data, converion takes too much time.
More than 24 hours on convertStep8 (Converting topics (part 2)...)  and it wasn't finished,  i had to break it :-(
and more than 24 hours on convertStep11 (Converting posts (part 2)...) and it wasn't finished,  i had to break it too.

How much time it could take?

To continue convertion I had to comment
convertStep8 (Converting topics (part 2)...)
convertStep11 (Converting posts (part 2)...)

What mean this steps? What doing convert on this steps?
Is it so important or not?

Question 1. Is it possible to speed up, to optimize convertion? Somehow...
Question 2. Is it possible to run these steps separetley?

evgenydeep

So, 3 questions:
Question 0: What mean this steps(Step8, Step11)? What doing convert on this steps? Is it so important or not?
Question 1. Is it possible to speed up, to optimize convertion? Somehow...
Question 2. Is it possible to run these steps separetely?

SleePy

A1. The converter script can be altered to tell it to handle more data at once. As a big board user you should know that more data you are attempting to handle at once the more resources you need to accommodate that. So it would be up to you if you wanted to attempt to process more data at once.
To be honest with 1.5 million posts, it might take a while to convert to SMF. Its a lot of data.

A2. Are you using the convert.php that was linked to the first post of YaBB converter script in this board? I think it may offer an option, but am not 100% sure if the .php scripts offer options of what to convert.

B0. It looks like Step8 is updating the log_topics table to have the correct ids. The log topics table keeps track of what user has read what topic and up to what point. So if you just have it skip it, then the worse thing is users need to mark posts read again.
Step 11 looks important. It is correcting the messages in the messages table that do not have a valid id_msg.

Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

evgenydeep

Thanks for the answers!

During conversion another error appear:

Converting poll votes....................<br />
<b>Notice</b>:  Undefined variable: command_line in <b>/home/smfgrod/public_html/smf/convert.php</b> on line <b>1148</b><br />
<br />
<b>Notice</b>:  Undefined index:  teenagers in <b>/home/smfgrod/public_html/smf/yabb21_to_smf.php</b> on line <b>698</b><br />
Column count doesn't match value count at row 47
                        INSERT  IGNORE  INTO `smfgrod_smf`.smf_log_polls
                                (ID_POLL, ID_MEMBER, ID_CHOICE)
                        VALUES ('148', '5866', '0'),
                                ('148', '1474', '1'),
                                ('148', '3218', '1'),
                                ('148', '5843', '1'),
                                ('148', '6440', '1'),
                                ('148', '5892', '2'),
                                ('148', '5865', '2'),
                                ('148', '6752', '1'),
                                ('148', '5852', '1'),
                                ('148', '724', '2'),
                                ('148', '5570', '1'),
                                ('148', '5837', '1'),
                                ('148', '5897', '2'),
                                ('148', '7464', '1'),
                                ('816', '5060', '4'),
                                ('816', '2976', '2,6'),
                                ('816', '1613', '0,6'),
                                ('816', '0', '0'),
                                ('816', '5897', '5'),
                                ('816', '0', '0,5'),
                                ('816', '5837', '1,6'),
                                ('816', '7464', '0'),
                                ('816', '1287', '0,6'),
                                ('816', '1474', '0,6'),
                                ('816', '1022', '1'),
                                ('816', '5866', '6'),
                                ('816', '5570', '0,5'),
                                ('816', '6440', '0'),
                                ('816', '3218', '5'),
                                ('213', '3221', '2'),
                                ('213', '1619', '1'),
                                ('213', '4091', '0'),
                                ('213', '6551', '0'),
                                ('213', '456', '2'),
                                ('213', '3561', '2'),
                                ('213', '3183', '2'),
                                ('213', '0', '2'),
                                ('213', '4702', '2'),
                                ('213', '298', '1'),
                                ('213', '837', '2'),
                                ('213', '1088', '0'),
                                ('213', '281', '0'),
                                ('213', '3649', '2'),
                                ('213', '1273', '1'),
                                ('213', '505', '2'),
                                ('213', '0', '1'),
                                ('0', '7090', '0', '11123574660'),
                                ('0', '0', '0', '11123574660'),
                                ('0', '3309', '0', '11123574660'),
                                ('0', '1369', '0', '11123574660'),
                                ('0', '461', '0', '11123574660'),
                                ('0', '1076', '2', '11123574660'),
                                ('0', '462', '0', '11123574660'),
                                ('0', '1899', '1', '11123574660'),
                                ('0', '1873', '0', '11123574660'),
                                ('0', '1180', '0', '11123574660'),
                                ('0', '1096', '1', '11123574660'),
                                ('0', '2244', '1', '11123574660'),
                                ('0', '1068', '1', '11123574660'),
                                ('0', '345', '2', '11123574660'),
                                ('0', '570', '2', '11123574660'),
                                ('0', '418', '0', '11123574660'),
                                ('0', '1298', '0', '11123574660'),
                                ('0', '1794', '0', '11123574660'),
                                ('0', '1111', '1', '11123574660'),
                                ('0', '1889', '0', '11123574660'),
                                ('0', '1474', '0', '11123574660'),
                                ('0', '1051', '0', '11123574660'),
                                ('0', '65', '0', '11123574660'),
                                ('0', '411', '0', '11123574660'),
                                ('0', '1726', '0', '11123574660'),
                                ('0', '52', '0', '11123574660'),
                                ('0', '317', '1', '11123574660'),
                                ('0', '174', '0', '11123574660'),
                                ('0', '1319', '0', '11123574660'),
                                ('0', '2252', '2', '11123574660'),
                                ('0', '319', '2', '11123574660'),
                                ('0', '1446', '2', '11123574660'),
                                ('0', '1894', '1', '11123574660')

SleePy

Lets try this.
Open the yabb21_to_smf.php

Find:

                // Lets unset the remaining memberNames


Add Before:

                // Attempt to prevent TempID from still being set.
                foreach ($pollVotesBlock as $key => $value)
                {
                    if (!empty($pollVotesBlock[$key]['tempID']) && empty($pollVotesBlock[$key]['ID_POLL']))
                    {
                        $pollVotesBlock[$key]['ID_POLL'] = $row['tempID'];
                        unset($pollVotesBlock[$key]['tempID']);
                    }
                }

That may display a little weird, but I am in class using windows and not my normal computer :P

Note the only line that changes is the tempID.
Hopefully this will let it work by detecting if the poll is empty (as it is for some of them) and force a id at least
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

evgenydeep

Hello Sleepy and Jay!
At first, thanks for your previous help.

My convertion almost working :-) ... almost :-)
I use local convertion cli.

I found very strange problem and as I'm not guru at php, I can't find core of problem, but I try to explain...

1) when I use web-convertion, then during convertion each step began repeat...
I noticed that some of table alteration doesn't work. This proplem appear on several steps.

2) When I try CLI, repeated bug disappear, but some steps worked soooooooooo slow.
A few days ago, I installed 2 clear smf, then on one of them I runned converting. Then after each step I began to compare tables and indexes in 1(clear) and 2(converted) smf. + even if I'm not php guru, I mainly understand SQL, base PHP, since I'm ex-programmer. I saw that some of ALTER tables, where some columns must be DROPped or/and ADDed some indexes - not worked.

First of them, which I saw, is function convertStep4(), where at begin of function added new field "ADD COLUMN temp_toName tinytext", and at end of this function, this field should be dropped:
      if ($_GET['substep'] >= -4)
      {
         $knownColumns = array(
            'temp_toName' => 'DROP temp_toName',
         );
         alterTable('personal_messages', '', $knownColumns, false, true);

         pastTime(-5);
      }

But it has not been dropped   :-(

The same with next steps .... 7,8, and especially on step14 (Cleaning up (part 1)), where indexes in messages  table should be restored.

The most strange thing that some of alterations works, some - not... :(
That's a bug. But how can I found where is a problem?
Can you advise me?

evgenydeep

#11
One another question.

I looked on step2, where fetch of vars from .vars file.
But number of lines not corresponds to the array numbers. Is this not problem?
Second, inf I'll add  'reputation' => $userData[28], and then    'karmaGood' => $data['reputation'],
this will be correct for? (that will be not correct, becasue some users have not positive carma :-( )

Also, below is part of code for convertion carma, but it not correponds my carma mod
(Yet another Reputation System  http://www.boardmod.org/yabb/YaBB.pl?board=newsy2;action=display;num=1169204866)
I think this is not too big problem to parse such file, can you write a cycle for parse file, to get all - and + for carma?


Below is code for Step2, and examples how look my .vars and .reputation files


Step2 code -------------------------------------------------------------------------------------------
         if (strrchr($entry, '.') != '.vars')
         {
            $userData = array_pad($userData, 31, '');
            $data = array(
               'password' => $userData[0],
               'realname' => $userData[1],
               'email' => $userData[2],
               'webtitle' => $userData[3],
               'weburl' => $userData[4],
               'signature' => $userData[5],
               'postcount' => $userData[6],
               'position' => $userData[7],
               'icq' => $userData[8],
               'aim' => $userData[9],
               'yim' => $userData[10],
               'gender' => $userData[11],
               'usertext' => $userData[12],
               'userpic' => $userData[13],
               'regdate' => $userData[14],
               'location' => $userData[15],
               'bday' => $userData[16],
               'hidemail' => $userData[19],
               'msn' => $userData[20],
               'lastonline' => $userData[23],
               'im_ignorelist' => $userData[26],
               'im_notify' => $userData[27],
               // !!! 'cathide' => $userData[30],
               // !!! 'postlayout' => $userData[31],
            );
         }

My .vars file look like: ----------------------------------------------------

### User variables for ID: DDD ###

'password',"JbRlurgxaUH0mdXvY42sLQ"
'realname',"Alex"
'email',"[email protected]"
'regdate',"03/16/05 :: 22:51:13"
'webtitle',""
'weburl',""
'signature',"bmw is best"
'postcount',"142"
'position',""
'addgroups',""
'icq',""
'aim',""
'yim',""
'gender',"Male"
'usertext',"УНВЕРЯЪ ОЕПЕЛЕМ"
'userpic',"http://www.avatars.com/avatars/P1010034_a1_ready120x120.jpg"
'regtime',"1111013473"
'location',"цПНДМН"
'bday',"06/07/1905"
'timeselect',"2"
'timeoffset',"2"
'timeformat',"MM D+ YYYY @ HH:mm:ss*"
'hidemail',"checked"
'msn',""
'gtalk',""
'template',"Forum default"
'language',"Russian"
'lastonline',"1203876731"
'lastpost',"1203876731"
'lastim',"1203859634"
'im_ignorelist',""
'im_notify',"1"
'im_popup',""
'im_imspop',""
'cathide',""
'postlayout',""
'session',"TljfSaReSB4n+xfwP2oOhg"
'sesquest',""
'sesanswer',""
'favorites',"1141242360,1187678025,1168356317,1175127785,1173949326,1190840281,1192175685,1174485528,1133422105,1196654175,1200059256,1200946098,1200926884,1183971005,1170444668,1202566093,1203237666,1202052371"
'dsttimeoffset',"1"
'pageindex',"1|1|1"
'reputation',"254"


My .reputation file looks like ------------------------------------
1207429024.55169|7floor|profile|спасибо ;)|+1|0||
1207551170.06982|AckiySotona|1195302636/499#499| ;D|+1|0||
1207779753.94772|Nofret|1207770626/1#1| ;)|+1|0||


part of code for convertion carma ---------------------------------
         if (file_exists($yabb['memberdir'] . '/' . substr($entry, 0, -9) . '.reputation'))
         {
            $karma = (int) implode('', file($yabb['memberdir'] . '/' . substr($entry, 0, -9) . '.reputation'));
            $row['karmaGood'] = $karma > 0 ? $karma : 0;
            $row['karmaBad'] = $karma < 0 ? -$karma : 0;
         }


SleePy

Did jay not contact you? He said he was when I asked him.

That column above will be dropped at the end of the conversion. It is used while ids and stuff are corrected incase their is duplicates and such.

Did it add the table at all?
Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

JayBachatero

evgenydeep, please contact me tonight when I get on ICQ.  I've been extremely busy the past few days and haven't had time to look into this :(.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

evgenydeep

Unfortunately, I can't catch Jay online since 25th June... :-(
Probbaly he is too much busy with his job and studying.

JayBachatero

Sorry.  I've been extremely busy the past 2 weeks and haven't had much time to be online.  Work and RL stuff has kept me busy.  Things are starting to slow down a bit more.  I'll try and get online some time this week.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert

evgenydeep

I not saw Jay online in ICQ last month! :-)
I return to convertion topic. And in my plan, I want try to move finally from my YaBB to the latest stable SMF 1.5.
I'll be almost online all time next 3 days...

SleePy

Jeremy D ~ Site Team / SMF Developer ~ GitHub Profile ~ Join us on IRC @ Libera.chat/#smf ~ Support the SMF Support team!

evgenydeep

For YaBB 2.1. Do you remember 1.5mln posts forum(look above at begin of thid topic)?

Advertisement: