from YaPP to SMF

[JayBachatero]

Yes the table structure for both sites.

[Poasters]

OK, there are a couple problems.

On our live site, we don't have phpMyAdmin, so I can't see the table structure. I would guess this info could be obtained via telnet, but I don't know how to construct the query, since I am a database n00b.

On our test site, I can see the yabbse_members table. There is a warning near the bottom, not sure if it is significant:
! PRIMARY and INDEX keys should not both be set for column `ID_MEMBER`

[JayBachatero]

Run this query.

SHOW COLUMNS FROM TABLE_PREFIX_HERE_members;

[Poasters]

OK, change of plan. I managed to install phpMyAdmin on our live site's server. 

The yabbse_members table on the test site has an extra column called "Collation" in between "Type" and "Attributes".

[JayBachatero]

Check the version of phpMyAdmin that you are running.  They might be different.  But that doesn't affect the converter.

[Poasters]

live site has the latest version 2.9.1.1

test site has 2.8.0.2

[JayBachatero]

that might be it.  In regard to the tables.  What is the structure of the live site members table?

[Poasters]

The yabbse_members table at the live site looks like (sorry for the unformatting):

         Field     Type     Attributes     Null     Default     Extra     
   ID_MEMBER    int(10)    UNSIGNED    No       auto_increment    
   memberName    varchar(80)       No          
   realName    tinytext       Yes    NULL       
   passwd    tinytext       No          
   emailAddress    tinytext       Yes    NULL       
   dateRegistered    bigint(20)       Yes    NULL       
   personalText    tinytext       Yes    NULL       
   memberGroup    tinytext       Yes    NULL       
   gender    tinytext       Yes    NULL       
   birthdate    date       No    0000-00-00       
   websiteTitle    tinytext       Yes    NULL       
   websiteUrl    tinytext       Yes    NULL       
   location    tinytext       Yes    NULL       
   ICQ    tinytext       Yes    NULL       
   AIM    tinytext       Yes    NULL       
   YIM    tinytext       Yes    NULL       
   hideEmail    tinyint(4)       Yes    NULL       
   timeFormat    tinytext       Yes    NULL       
   signature    text       Yes    NULL       
   posts    int(11)       Yes    NULL       
   timeOffset    float       Yes    NULL       
   avatar    tinytext       Yes    NULL       
   im_ignore_list    text       Yes    NULL       
   im_email_notify    tinyint(4)       No    0       
   lastLogin    bigint(20)       Yes    NULL       
   karmaBad    tinyint(4)       No    0       
   karmaGood    tinyint(4)       No    0       
   usertitle    tinytext       Yes    NULL       
   lngfile    tinytext       Yes    NULL       
   notifyAnnouncements    tinyint(4)       No    1       
   notifyOnce    tinyint(4)       No    1       
   MSN    tinytext       Yes    NULL       
   memberIP    tinytext       Yes    NULL       
   secretQuestion    tinytext       No          
   secretAnswer    tinytext       No          
   astatus    text       No          
   bio    text       No          
   muteduser    tinyint(4)       No    0       
   mood    varchar(75)       No          
   mood_filename    varchar(200)       No          
   theme    text       No          
   showAvatars    tinyint(1)       No    1       
   showSigs    tinyint(1)       No    1       
   showLocked    tinyint(1)       No    1       

[JayBachatero]

Umm now that I look into it, it seems that the converter is picking up the SMF info and not the YaPP Settings.  Check the db prefix on Settings.php for YaPP.

[Poasters]

On the live site, the db prefix in Settings.php is yabbse_

On the test site, YaPP is not actually installed - it's just the backup db tables merged with the smf db tables from the new smf install.

[JayBachatero]

Umm try installing yapp there.  It needs the Settings.php from yapp for it to work.

[Poasters]

OK, I didn't realize that. I will try it and report back in a while.

Update: There is no YaPP installer, so I attempted to install YaBBSE. The installer didn't seem to like that there was already data in the db, but let the install proceed anyway. The site displays this error:

Database Error

Unknown column 'lb.ID_MEMBER' in 'on clause'
File: /home/xpoaster/public_html/steve2/yabbse/Sources/BoardIndex.php
Line: 151

I attempted the YaBBSE --> SMF converter anyway, but it was unsuccessful:

Converting members... Unsuccessful!
This query:

    UPDATE `xpoaster_steve2`.smf_members
    SET im_ignore_list = ''
    WHERE ID_MEMBER = 12
    LIMIT 1;

Caused the error:

    Unknown column 'im_ignore_list' in 'field list'

[JayBachatero]

Try this one.

[Poasters]

It seems that one almost worked.

It looked like the members converted correctly, then it took a long time for further conversions, then finally stopped with this error:

Converting...
Converting polls... Successful.
Converting poll options... Successful.
Converting poll votes... Successful.
Converting personal messages (step 1)... Successful.
Converting personal messages (step 2)... Successful.
Converting topic notifications... Unsuccessful!
This query:

    SELECT ID_MEMBER, ID_TOPIC, notificationSent AS sent
    FROM `xpoaster_steve2`.yabbse_log_topics
    WHERE notificationSent != 0
    LIMIT 0, 500;

Caused the error:

    Unknown column 'ID_MEMBER' in 'field list'

[JayBachatero]

What's the structure for the YaPP log_topics table?

[Poasters]

yabbse_log_topics looks like:

   Field        Type        Collation  Attributes     Null     Default     Extra     
   memberName tinytext    latin1_swedish_ci    No          
   ID_TOPIC    int(11)             No    0       
   logTime    bigint(20)             Yes    NULL       
   notificationSent    tinyint(4)          No    0       

[JayBachatero]

Damn.  This one is going to need a sub select.  Ill update it tom so that you can give it a shot.

[Poasters]

Thank you for your efforts. 

I am optimistic that we will finally be able to upgrade our forum. It has been about 3 years since our last upgrade (from Snitz forums - brrrrr!)

[JayBachatero]

Give this one a shot.

[Poasters]

Media Center mod?