Simple Machines Community Forum

SMF Support => Converting to SMF => YaBB/YaBB SE => Topic started by: evgenydeep on May 20, 2008, 06:53:08 AM

Title: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 20, 2008, 06:53:08 AM
   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.
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 20, 2008, 09:48:58 AM
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?
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on May 20, 2008, 05:37:05 PM
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
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 24, 2008, 04:21:54 AM
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.

Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 24, 2008, 04:41:22 AM
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?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 26, 2008, 03:23:31 PM
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?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 26, 2008, 03:29:24 PM
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?
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on May 26, 2008, 06:38:12 PM
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.

Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on May 28, 2008, 06:00:36 AM
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')
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on May 28, 2008, 12:45:58 PM
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
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on June 20, 2008, 05:16:00 AM
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?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on June 20, 2008, 05:40:28 AM
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;
         }
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on June 24, 2008, 09:43:43 AM
? :-(
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on June 24, 2008, 12:43:15 PM
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?
Title: Re: Converting big forum from Yabb to SMF
Post by: JayBachatero on June 24, 2008, 01:32:22 PM
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 :(.
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on July 07, 2008, 01:39:53 PM
Unfortunately, I can't catch Jay online since 25th June... :-(
Probbaly he is too much busy with his job and studying.
Title: Re: Converting big forum from Yabb to SMF
Post by: JayBachatero on July 07, 2008, 02:11:07 PM
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.
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on July 31, 2008, 07:50:12 PM
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...
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on July 31, 2008, 08:04:40 PM
Was this for yabb 2.2 or 2.1?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on July 31, 2008, 08:53:10 PM
For YaBB 2.1. Do you remember 1.5mln posts forum(look above at begin of thid topic)?
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on July 31, 2008, 09:38:46 PM
Give this one a try.

I believe I fixed some issues in it. The good and bad karma should of be handled it looks like.
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 03, 2008, 02:58:05 PM
SleePy, thanks, I saw you added block_size. Something other?
I have tried your version, but it strangely not ALTER tables sometimes, which produce errors on next steps. I'm not trying to investigate why and where... I simple return to old one version, which works almost well. Almost becasue i still not converted my forum :) But now I'm serious, and i want to fight and finish it finaly, becasue if it will be Yabb forum, my server will not survive on autmn, when many users will come to my forum...


I have other issue.  I looked on the convertion process and realize that on step 8(function convertStep8), it become unexpected slowly on the query like
UPDATE `forum_smf`.smf_log_topics\n                    SET ID_TOPIC = 38\n                    WHERE tempID = 1111015939
I have almost 4mln rows in smf_log_topics and around 30 000 topics in smf_topics.
So, to really speed up the process, is only needed to add index to the tempID column, and then drop it.
Otherwise this 8 step can lasts months :)

How do you think?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 03, 2008, 05:12:44 PM
Other one issue/bug:
On STEP 4 find next code:

      if ($_GET['substep'] >= -4)
      {
         $knownColumns = array(
            'temp_toName' => 'DROP temp_toName',
         );
         alterTable('personal_messages', '', $knownColumns, false, true);

         pastTime(-5);
      }

I suppose it should be 'DROP COLUMN temp_toName' instead 'DROP temp_toName'
?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 03, 2008, 05:32:16 PM
3) On step 8(function convertStep8), during query
         $knownKeys = array(
            'PRIMARY' => 'ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER)',
         );
         $knownColumns = array(
            'tempID' => 'DROP COLUMN tempID',
         );
         alterTable('log_topics', $knownKeys, $knownColumns, '', true, true);

I got following error:


<br />
<b>Notice</b>:  Undefined variable: endl in <b>/home/forumgr/public_html/smf102/yabb21_to_smf.php</b> on line <b>800</b><br />
Duplicate entry '20706-8050' for key 1
                ALTER TABLE `forumgr_smf102`.smf_log_topics
                ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER),
                DROP COLUMN tempIDhosting#


Why to not check by aditional one sql-query - is there duplicates or not?
Can You tell me correct SQL query(-ies), 1) to check for duplicates and then 2) delete duplicates, but let one recored will remain only one from duplicates.
?
Thanks.
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 03, 2008, 11:05:52 PM
Well there should hopefully not be duplicate topics :|
It does happen though, the flat file converters dont make it very fun because they remove key during conversion that is supposed to help against this.
This may work to show all duplicate topics in the log_topics table. Replace smf_ with your prefix

SELECT ID_TOPIC
FROM smf_log_topics
WHERE ID_TOPIC != 0
GROUP BY ID_TOPIC, ID_MEMBER
HAVING ID_TOPIC > 1


But it should show the id of a topic that has more than 1 ID_TOPIC and ID_MEBER (both are the same).
The next part would be to find and delete those.
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 02:40:17 AM
Moreover, I have the same bug with poll_choices:
<b>Notice</b>:  Undefined variable: command_line in <b>/home/forumgr/public_html/smf102/convert.php</b> on line <b>1148</b><br />
Duplicate entry '275-0' for key 1
                ALTER TABLE `forumgr_smf102`.smf_poll_choices
                ADD PRIMARY KEY (ID_POLL, ID_CHOICE)hosting#

And moreover, I saw such bugs have not me only. Try to search string 'Duplicate entry' on SMF community forum and you will notice that many users have the same bug. So, it would be good before adding any PRIMARY KEY(UNIQUE KEY) check - is there duplicates or not, and if there - then delete them.

BTW:  your sql is not working, I used

SELECT ID_TOPIC, ID_MEMBER, COUNT(*)
FROM smf_log_topics
GROUP BY ID_TOPIC, ID_MEMBER
HAVING COUNT(*) > 1

but on 2mln rows table without index it working VEEEEEEEERY :-) slow

and for polls:
SELECT ID_POLL, ID_CHOICE, COUNT(*)
FROM forumgr_smf102.smf_poll_choices
GROUP BY ID_POLL, ID_CHOICE
HAVING COUNT(*) > 1
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 03:53:21 AM
18 step

      if ($_GET['substep'] >= -2)
      {
         $knownKeys = array(
            'poll' => 'ADD UNIQUE INDEX poll (ID_POLL, ID_TOPIC)',
         );
         alterTable('messages', $knownKeys, '', true);

         pastTime(-3);
      }

Is it error? Is it should be messages table? But in messages table there is no field ID_POLL
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 03:59:16 AM
it looks that on 18 step should be not messages, should be topics, becasue only in topics exist ID_POLL, ID_TOPIC
Yeah... and on clean install poll index exist in topics only.

Guys... :-) How many users already converted Yabb to SMF? :)
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 04:10:29 AM
Another thing: during Cleaning up steps(14 and 15), no indexes creating. May be problems with the substeps again?
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 04:19:10 AM
I decided to remove duplicates by following SQL

CREATE TABLE forumgr_smf102.smf_poll_choices_temp AS SELECT * FROM forumgr_smf102.smf_poll_choices WHERE 1 GROUP BY ID_POLL, ID_CHOICE;
DROP TABLE forumgr_smf102.smf_poll_choices;
RENAME TABLE forumgr_smf102.smf_poll_choices_temp TO forumgr_smf102.smf_poll_choices;

and Indexes should be added manually also. It will be good to add this into converting script. At fiest - check, is there duplicates or not, and then if yes - remove them.

Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 04:27:49 AM
On step 8

         $knownKeys = array(
            'PRIMARY' => 'ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER)',
         );
         $knownColumns = array(
            'tempID' => 'DROP COLUMN tempID',
         );
         alterTable('log_topics', $knownKeys, $knownColumns, '', true, true);


Here ADD PRIMARY KEY (ID_TOPIC, ID_MEMBER) , but I look, on clean installed SMF
PRIMARY KEY (ID_MEMBER, ID_TOPIC)

Is it important?
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 04, 2008, 12:57:18 PM
evgenydeep,

Many users have converted to SMF from Yabb :P
Just not all of them have been big boards where these minor issues come poping out.

Thanks for the info on the query for finding duplicates. Did that table have a index for                  ID_TOPIC itself? If this is missing it would explain why it took a while. Its not a unique index or anything so adding it shouldn't cause issues but would speed up that query. As well a limiter on it would help.

As for step 18, are you sure you where using the .php file I gave you?
Lots of those steps got changed because of the inequality worked better the opposite way preventing the columns from being dropped to early.

Edit,
Attaching the working copy I have from SVN.
If you find bugs let me know, The hardest part is getting these flat file converters to work right especially from big boards where we don't go through 10 steps in a second :P
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 04, 2008, 02:47:41 PM
Quote from: SleePy on August 04, 2008, 12:57:18 PM
Thanks for the info on the query for finding duplicates. Did that table have a index for                  ID_TOPIC itself? If this is missing it would explain why it took a while. Its not a unique index or anything so adding it shouldn't cause issues but would speed up that query. As well a limiter on it would help.
About what table you discuss? smf_log_topics? Sorry, not understand.

Quote
As for step 18, are you sure you where using the .php file I gave you?
Lots of those steps got changed because of the inequality worked better the opposite way preventing the columns from being dropped to early.
Yes, i look into your file also, here is the same bug :-)
On 18 step should be not messages, should be topics, becasue only in topics exist ID_POLL, ID_TOPIC
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 04, 2008, 05:58:18 PM
yes sorry, I was asking about the smf_log_topics. It should have a primary index and another index of id_topic.

What line you seeing that on?
In fact in that file I gave you, its slightly different.
This is what I am seeing:

        if ($_GET['substep'] <= -3)
        {
            $knownKeys = array(
                'poll' => 'ADD UNIQUE INDEX poll (ID_POLL, ID_TOPIC)',
            );
            alterTable('messages', $knownKeys, '', true);

            pastTime(-4);
        }


If you look it is now substep -3 in step 18. The inequality is going the other way as well (This seemed to fix issues with changes to the table being done before the conversion was complete).
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 05, 2008, 03:38:21 AM
Should be as quoted.  Sholuld be not messages table, must be topics table. Becasue only in topics table exist fields ID_POLL, ID_TOPIC.

$knownKeys = array(
'poll' => 'ADD UNIQUE INDEX poll (ID_POLL, ID_TOPIC)',
);
alterTable('topics', $knownKeys, '', true);



Quote
If you look it is now substep -3 in step 18. The inequality is going the other way as well (This seemed to fix issues with changes to the table being done before the conversion was complete).
Aha, thanks, I see. I'll look on it.
Title: Re: Converting big forum from Yabb to SMF
Post by: evgenydeep on August 05, 2008, 03:44:04 AM
One another bug. Serious bug.
In "Converting membergroups" step function convertStep1().
I notice that NoPost Groups has not been converted.

How it now:
elseif (preg_match('~\$NoPost\[(\d+)\] = [\'|"]([^|]*)\|(\d*)\|([^|]*)\|([^|]*)~', $group, $match) != 0)

How it should be:
elseif (preg_match('~\$NoPost\[(\d+)\} = [\'|"]([^|]*)\|(\d*)\|([^|]*)\|([^|]*)~', $group, $match) != 0)
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 05, 2008, 02:00:13 PM
Ahh, I thought I had fixed the id_poll index issue. I fixed it

Thanks for the suggestion in the regex. I see from the other regex in membergroup conversion it looks right :)
Title: Re: Converting big forum from Yabb to SMF
Post by: Gilrod on August 05, 2008, 02:40:27 PM
Didn't you see my post :)
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 05, 2008, 02:57:15 PM
Which one? :P
Title: Re: Converting big forum from Yabb to SMF
Post by: Gilrod on August 05, 2008, 03:30:40 PM
The one with the error in the 2.2 thread  :P

this one (http://www.simplemachines.org/community/index.php?topic=214612.msg1643257#msg1643257)
Title: Re: Converting big forum from Yabb to SMF
Post by: JMB on August 06, 2008, 10:48:14 AM
Quote from: evgenydeep on August 05, 2008, 03:44:04 AM
One another bug. Serious bug.
In "Converting membergroups" step function convertStep1().
I notice that NoPost Groups has not been converted.

How it now:
elseif (preg_match('~\$NoPost\[(\d+)\] = [\'|"]([^|]*)\|(\d*)\|([^|]*)\|([^|]*)~', $group, $match) != 0)

How it should be:
elseif (preg_match('~\$NoPost\[(\d+)\} = [\'|"]([^|]*)\|(\d*)\|([^|]*)\|([^|]*)~', $group, $match) != 0)

I hadn't noticed the non post groups not being imported!

I tried this change but it didn't work for me. I got it working with this:


elseif (preg_match('~\$NoPost\{\'(\d+)\'\} = [\'|"]([^|]*)\|(\d*)\|([^|]*)\|([^|]*)~', $group, $match) != 0)


Although the minPosts count is wrong on all of them...
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 06, 2008, 01:01:32 PM
Do you have a sample of a post group?

Just wanted to know where the number was coming from.
Title: Re: Converting big forum from Yabb to SMF
Post by: JMB on August 06, 2008, 01:50:09 PM
It's only the post dependant groups that have the wrong value for minPosts. Mods/Admins and NoPost groups are ok.

Here is a line from my membergroups.txt file:

$Post{'50'} = 'Junior|2|stargold.gif|#9acd32|0|0|0|0|0|0|Blue';


This group imports with a minPosts value of 2. Looks like the 2 comes from between the name and the graphic. That value is the number of stars for the group.
Title: Re: Converting big forum from Yabb to SMF
Post by: SleePy on August 06, 2008, 06:26:44 PM
Was the minPosts supposed to be 50?
The other numbers there are 0 so I don't think thats it :P

And this is really Yabb 2.1? I can't believe something like that went missing for a while. I guess not many Yabb users customize their post groups :P
Title: Re: Converting big forum from Yabb to SMF
Post by: JMB on August 06, 2008, 06:38:53 PM
Quote from: SleePy on August 06, 2008, 06:26:44 PM
Was the minPosts supposed to be 50?
The other numbers there are 0 so I don't think thats it :P

And this is really Yabb 2.1? I can't believe something like that went missing for a while. I guess not many Yabb users customize their post groups :P

Yep, minPosts is 50 for that one. I have no idea what all the 0's are about!

And yes, it's YaBB 2.1!

Maybe people just went in to SMF after they converted and changed them back by hand.  :o
Title: Re: Converting big forum from Yabb to SMF
Post by: mtagoon on December 28, 2018, 02:40:40 PM
Hello all,

I want to convert my YaBB 2.5.2 Forum to SMF with this thread instructions [ http://www.simplemachines.org/community/index.php?topic=457735.0 ] and plan to go from YaBB 2.5.2 > YaBB 2.4 > SMF 1.1.14 > SMF 2.0 > SMF 2.0.15 in my computer. I've downloaded the convert.php from here [ http://www.simplemachines.org/community/index.php?topic=140741.0 ] and yabb22_to_smf.php from here [http://www.simplemachines.org/community/index.php?topic=249777.0 ].

I am using Windows 10 Pro, XAMPP for Windows 5.6.39, YaBB 2.4 (downgraded from YaBB 2.5.2), a fresh copy of SMF 1.1.14. Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls. I uploaded/copied the yabb22_to_smf.php and convert.php to my SMF 1.1.14 installation and ran it...  I received an error though as displayed in the results below:

- - - - - -
Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in C:\xampp\htdocs\forum\convert.php on line 380
Unsuccessful!
This query:

    SELECT @@SQL_BIG_SELECTS, @@SQL_MAX_JOIN_SIZE;

Caused the error:

    Unknown system variable 'SQL_MAX_JOIN_SIZE'

Try again
- - - - - - -

Exist:
My YaBB 2.4 Forum with
- 25,443 members
- 11,176 total topics
- 137,611 total posts


Thanks for any advice!

Title: Re: Converting big forum from Yabb to SMF
Post by: vbgamer45 on December 28, 2018, 02:45:31 PM
Can you attach the convert.php file you are using
Title: Re: Converting big forum from Yabb to SMF
Post by: mtagoon on December 28, 2018, 03:00:16 PM
No, I can not attach the convert.php file in this thread. Only this link -> https://www.simplemachines.org/community/index.php?action=dlattach;topic=140741.0;attach=128919

Thx.
Title: Re: Converting big forum from Yabb to SMF
Post by: vbgamer45 on December 28, 2018, 03:04:51 PM
Try this file
Title: Re: Converting big forum from Yabb to SMF
Post by: mtagoon on December 28, 2018, 03:19:15 PM
Thank you vbgamer45

After run your new convert.php. I received an error :


Fatal error: Call to undefined function loadDatabase() in C:\xampp\htdocs\forum\convert.php on line 418


Thx.
Title: Re: Converting big forum from Yabb to SMF
Post by: mtagoon on January 10, 2019, 11:56:08 PM
Hi,

I tried using the converter again from here [ http://www.simplemachines.org/community/index.php?topic=344465.0 ] which user mrtoner paid someone and made a change to convert.php and yabb24_to_smf.php to get it to work. I've attached the converter here for your use. (convert.php and yabb24_to_smf.php are included.)

convert.php :
   - Replace '@@SQL_MAX_JOIN_SIZE' to '@@MAX_JOIN_SIZE' ( line 428 and 437 )
   - Replace '@set_time_limit(300);' to '@set_time_limit(600);' ( line 1979 )

yabb24_to_smf.php
   - Replace 'YaBB 2.2' to 'YaBB 2.4' ( line 26 )

Exist: Windows 10 Pro, XAMPP for Windows 5.6.39
My YaBB 2.4 Forum with
   - 25,443 members
   - 11,176 total topics
   - 137,611 total posts
   - 34,150 pictures (YaBB's Attachments directory)
   - no polls

I've also tried to convert YaBB 2.4 to SMF 1.1.14 :
   1. without picture in YaBB's Attachments directory [Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls.] Database Table prefix : smf_db_00
     The converter took about 5 minutes and the conversion has completed successfully.

   2. with all of 34,150 pictures in YaBB's Attachments directory [Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls.]
     The converter took about 12 hours with no error or successful and the screen didn't show Continue(0) button in the bottom right. It showed " Converting... Converting attachments (if the mod is installed)... " . It seemed that the converter's converted about 10,900 pictures in SMF's attachments directory and my computer did nothing after 12 hours. LoL

   3. with pictures 0 - 10,000 in YaBB's Attachments directory [Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls.] Database Table prefix : smf_db_01
    The converter took about 1.5 hours and the conversion has completed successfully. Attachments all showed up almost fine.

   4. with pictures 10,001 - 20,000 in YaBB's Attachments directory [Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls.] Database Table prefix : smf_db_02
    The converter took about 1.5 hours and the conversion has completed successfully. Attachments all showed up almost fine.

   5. with pictures 20,001 - 30,000 in YaBB's Attachments directory [Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls.] Database Table prefix : smf_db_03
    The converter took about 1.5 hours and the conversion has completed successfully. Attachments all showed up almost fine.

   6. with pictures 30,001 - 34,150 in YaBB's Attachments directory [Put my YaBB 2.4 site into Maintenence Mode. Run all the YaBB Maintenence Controls.] Database Table prefix : smf_db_04
    The converter took about 40 minutes and the conversion has completed successfully. Attachments all showed up almost fine.

I tried converting my YaBB 2.4 to the SMF 1.1.14 and it succeeded in 1, 3, 4, 5 and 6. How to add only the posts with new attachments from database table prefix smf_db_04, smf_db_03 and smf_db_02 to database table prefix smf_db_01?  If I can do.. this migration will be successful with all attachments.

Any ideas?
mtagoon
Title: Re: Converting big forum from Yabb to SMF
Post by: vbgamer45 on January 11, 2019, 09:12:58 AM
I like how creative you were in getting the job done.

After the files are converted I would move them all back to the attachments directory in your main folder for SMF that ran completely.

Then take the entries from smf_attachments table in each database prefix and add them to your main SMF database install. Insert/Replace the smf_attachments table with those entries. They should be unique with the attachment id.
Title: Re: Converting big forum from Yabb to SMF
Post by: mtagoon on January 17, 2019, 01:55:24 AM
Eureka!!!

Thank you vbgamer45; modified, updated and inserted all smf_attachments tables as per your suggestions, and successfully migrating from YaBB 2.4 with all attachments to SMF 1.1.14 forum. Rebuilt/moved/modified boards, categories, membergroups, attachments that duplicated copies or didn't import properly and ran the SMF 1.1.14 maintenence tasks before successfully large upgrade the local copy of SMF 1.1.14 to SMF 2.0.15 forum in 10 minutes! Thank you very much indeed - I spent a lot of time looking for a solution and was getting nowhere  ...and also thanks for the other relative posts, of course.

mtagoon
Title: Re: Converting big forum from Yabb to SMF
Post by: vbgamer45 on January 17, 2019, 08:59:40 AM
Excellent news glad you have it working!