Converting from Snitz Forums

[ssoltz]

Radianation and [Unknown],

Man am I glad that you two went through this whole shebang before I tried to do this or I have a feeling that I would have been seriously SOL!

Anyway, here's my story and I'm sticking to it:

I run a website for a company that organizes, runs and promotes a specific K9 event around the country. I won't be specific as of now, but...have any of you ever seen ESPN's the Great Outdoor Games? Well, they run two of those events and GOG is the Big Kahuna for all their events.

I originally created a Flash-based site for them. It was purely intended as a "wow" site for potential sponsors and that's about all. As it went, the web presence took off from there. Now it is seriously dated and it doesn't work very well mostly because many of the features were added AdHoc. One thing we did was to put up a forum. Well, it got pretty huge (not by some standards, but much bigger than we anticipated). We used the ISP's standard forum software - Snitz. What a mess. I hate ASP and am not versed in it at all and we have had many problems. Basically, the site is a mess. Even still we are a Google PR 6 and have done no SEO or promotion. Just word of mouth.

Long story a little shorter: I am moving ISPs, going to a Mambo-based site, switching from Windows to Unix servers, etc. SMF is the obvious choice for our forum, considering we are going Mambo (esp. because of the ability only to have to login once). The Snitz database is currently in...ehem...Access!

I have gone through this topic and I think I have the steps down as far as the switch-over. We shall see...I have a couple of initial questions. Bear in mind that I am not a very experienced programmer. I am just getting started learning PHP.

Keeping the login usernames and passwords is key. We have over 4200 members and are growing at about 25-50 a day.

1. [Unknown] - Should I use the last version of the snitz_to_smf.php script that you uploaded or was that a particular fix-it to a problem experienced by ukcwm? I have counted 7 versions in this topic

2. How difficult is it going to be for me to convert from Access to MySQL? Is there a converter out there that makes this seamless?

3. I checked with my service provider and their PHP has been compiled with support for mhash.  Radianation or [Unknown], should I use the 1.1 Beta or go with version 1.05? Given the mhash support, do you forsee any problems I may have with the whole SHA256 thingy-majigy?

Anyway, that's it for now...I'm sure I will have other questions shortly. I am mostly interested to see if anyone has tips, pointers, advice as to any issues, pitfalls, etc. I may be facing given my situation. I am going to try a test run with a backup of the Access database from the Snitz forum and see how it goes. I would like to migrate in the next couple of weeks.

I will probably be signing up for the Premium Support for SMF just because I will not have a lot of time to mess around with this to get it working. I have too much to do with the rest of the site.

Thanks in advance,
Steve

[[Unknown]]

Is it built in to 1.1?

Code Select Expand

// Snitz style - SHA-256.  Technically, this is a downgrade, but most PHP configurations don't support sha256 anyway.
elseif (strlen($user_settings['passwd']) == 64 && function_exists('mhash') && defined('MHASH_SHA256'))
$other_passwords[] = bin2hex(mhash(MHASH_SHA256, $_REQUEST['passwrd']));

Yes.

Long story a little shorter: I am moving ISPs, going to a Mambo-based site, switching from Windows to Unix servers, etc. SMF is the obvious choice for our forum, considering we are going Mambo (esp. because of the ability only to have to login once). The Snitz database is currently in...ehem...Access!

Good moves all around .

Bear in mind that I am not a very experienced programmer. I am just getting started learning PHP.

As the theory goes, you shouldn't have to be to convert.  That said, it's true that Snitz is (arguably) the hardest conversion.

Keeping the login usernames and passwords is key. We have over 4200 members and are growing at about 25-50 a day.

Okay, done - assuming you can get the mhash support in your installation of PHP.  Considering you seem to be reasonably high-profile, this shouldn't be difficult.  That's a key I think to many communities - and so far, such is possible in all of our converters (and there are other 30 of them!)

1. [Unknown] - Should I use the last version of the snitz_to_smf.php script that you uploaded or was that a particular fix-it to a problem experienced by ukcwm? I have counted 7 versions in this topic

Actually, this is the one you want:

http://www.simplemachines.org/community/index.php?action=dlattach;topic=34642.0;attach=8911

And, you may want to consider going with the 1.1 beta version.  If you plan to roll out your site in enough time, it should be in a stable release soon - and the converter is much improved from this one.  You won't have to worry about code modifications or anything else, either.

That said, it still is a beta version.  The next release will be an RC.  Only follow that course if you are comfortable with beta software, and/or plan on having it in testing for long enough that it won't matter.

2. How difficult is it going to be for me to convert from Access to MySQL? Is there a converter out there that makes this seamless?

I'm afraid that's really the most fun part :/.  From what I've seen, there are a few options:

http://snitz2phpbb.sourceforge.net/ (mentioned earlier - search for "access" on this page.)
http://www.mysqlfront.de/ (apparently free for the first 30 days only...)

I have never done such things myself, however, and can't estimate how difficult they may be.

3. I checked with my service provider and their PHP has been compiled with support for mhash.  Radianation or [Unknown], should I use the 1.1 Beta or go with version 1.05? Given the mhash support, do you forsee any problems I may have with the whole SHA256 thingy-majigy?

Either way should be fine.  As above, I would suggest using the 1.1 version if you are comfortable with it.  Otherwise, use the 1.0 line and install the package.  Both solutions are - as far as the SHA-256 issue - roughly equivalent.

Given said support, I doubt you will have any problems.  If you have any, and you also have the mhash support indeed, it is quite likely they can be easily resolved.

-[Unknown]

[NoRad]

I would avoid converting to PHPBB first. I think you might lose some passwords if you go that route. I can't recall if they are lost on conversion or if PHPBB simply didn't have the SHA256 support built in to recognize them.

As for the Access migration, your host should have Enterprise Manager. Ask them if they can use that to dump your access database to mySQL. Otherwise, there are several tools on the internet that will perform this conversion. It's a lot easier than what I had to go through (MSSQL to mySQL). The BLOB issue was a nightmare until I found the right program. I ended up paying $25 for some software that worked like a charm.

[[Unknown]]

I would avoid converting to PHPBB first. I think you might lose some passwords if you go that route. I can't recall if they are lost on conversion or if PHPBB simply didn't have the SHA256 support built in to recognize them.

I only meant that it mentioned ways of going from Access -> MySQL on that page, not that using that converter was desirable.

-[Unknown]

[NoRad]

Whew... You had me confused for a second there.

[ssoltz]

Thanks for the replies, gentlemen...

I think I will go ahead and try the beta, then. I have over amonth before this thing needs to be stable. And considering that Snitz NEVER was for me, I'm sure it will be a big improvement.

I'm going to get started today...I will keep you abreast of my progress.

Thanks again,

[ssoltz]

OK, I am finally ready to try this.

I have the converter, the SMF install files for 1.1 b4 and the SHA256 installer.

What I have done so far is this: I had the Snitz forum on another server, like I said it was linked to an Access database. I downloaded the .mdb file from that site and then I used a program called SQLyog. I set up an ODBC data source for the Access database on my local machine. Then I connected to my new site. Now, for kicks, I have a default installation of Mambo there...nothing I care about losing but I wanted to see if I could combine the tables. So, I connected to the database that underlies the Mambo install and then imported from an ODBC source (the Access database locally). I had to use this program because it offers something called HTTP Tunneling which tricks my ISPs database into letting me import tables to a database. They do not support it directly. It was slow, but 4 1/2 hours later, the database had the combined tables of Snitz and Mambo. Here's a question:

Do I need to also upload the actual .asp pages from the old Snitz install or do I only need the tables to run the converter? If I do need the .asp pages, should I put them in the same directory as the SMF installation?

An unrelated question:

I looked at the requirements page. My ISP does have all the features for the minimum installation but does not have some of the recommendations for maximum performance, such as:

In PHP.ini:
1. Max_input_time directive is set to -1 not 30
2. session.use_trans_sid directive is set to On
3. register_globals is set to On

MySQL version is only 3.23.43
Apache version is only 1.3.14

Will this cause any serious problems?

Thanks,

[ssoltz]

Another quick question or two:

1. Since I am going ahead with the 1.1 b4 release, should I be using the converters and instructions found here?

http://www.simplemachines.org/community/index.php?topic=38966.0

Or was the php script above sufficient?

2. How long should the conversion process take? Converting messages has been at it for over an hour now...just curious..

Thanks,

[ssoltz]

By the way, my Snitz forum has about 4,000 members and around 30,000 posts, including topics and replies.

Thanks,

[[Unknown]]

OK, I am finally ready to try this.

I have the converter, the SMF install files for 1.1 b4 and the SHA256 installer.

As I said a few posts above, you don't need the SHA-256 package if you have a 1.1 release - it already includes it.

I wanted to see if I could combine the tables.

You should be able to with SMF or Snitz, in fact .

Do I need to also upload the actual .asp pages from the old Snitz install or do I only need the tables to run the converter? If I do need the .asp pages, should I put them in the same directory as the SMF installation?

There's no need.  You only need the tables, and to know the name of the database.  That's all the converter will ask for.

1. Max_input_time directive is set to -1 not 30

That should be fine.  -1 means no limit, while 30 means 30 seconds.  If something goes horribly wrong, it's often best to have some limit, but none at all won't hurt you - especially if it's just SMF and Mambo (which are reasonably well written scripts, if I can say so myself, and won't need the limit.)

2. session.use_trans_sid directive is set to On

SMF will attempt to turn this off automatically, so it should be fine.  The only real danger with that setting is that search engines might see URLs with session ids in them if it is still on.

3. register_globals is set to On

This basically just means that PHP will waste its time on a compatibility feature SMF does not need.  It could also cause potential security holes (although again, Mambo and SMF should be safe.)

MySQL version is only 3.23.43
Apache version is only 1.3.14

Both of those are... very old.  How much control over this server do you have?  Is it shared?  Do you think your host would be willing to upgrade?

SMF works fine on those versions, but the most stable version of MySQL 3.23 is 3.23.58, if I remember correctly.  Apache should be at least 1.3.33.  There are newer major versions of both of these softwares, too, but I can understand your host not using them.  Still, using older versions of MySQL and especially Apache is potentially a security risk.  Many of the minor updates have been security patches... this is like running a Windows 2003 server with no service packs installed.

Still, it's unlikely that would cause any immediate problems.  For the purposes of testing and etc., you should be fine... but I would strongly recommend you ask your host if there's any possibility of using the latest and most secure versions of each line.

Additionally, SMF has been optimized for MySQL 4.0 and 4.1.  Even MySQL 3.23.58 is somewhat old, and much less efficient than MySQL 4.0 and above anyway.  You won't notice the difference with 30,000 posts, but once you get above 80,000 it will become more apparent.  While askihng your host about software versions, it may also be a good idea to ask if they have any intention of upgrading to a more recent version of MySQL.

1. Since I am going ahead with the 1.1 b4 release, should I be using the converters and instructions found here?

http://www.simplemachines.org/community/index.php?topic=38966.0

Yes.  Forgive me for not clarifying that... for 1.1, you want to use those (newer) converters.  I believe the 1.1 converter converts notifications, censored words, and moderators - none of which does the one found in this topic.

2. How long should the conversion process take? Converting messages has been at it for over an hour now...just curious..

For 30,000 posts and MySQL 3.23?  Hmm... shouldn't be much longer.  Considering you posted over 2 hours ago, it should definitely be done by now.  If it isn't, something is wrong.

-[Unknown]

[ssoltz]

[Unknown],

Thanks for the reply.

As I said a few posts above, you don't need the SHA-256 package if you have a 1.1 release - it already includes it.

Yes, I thought that's what you and Radination were talking about. I just figured I would get to that after I finished the convert. Thanks for clarifying, though.

Both of those are... very old.  How much control over this server do you have?  Is it shared?  Do you think your host would be willing to upgrade?

Hahaha...if you knew who my provider was, you wouldn't ask...let's just say they are a subdivision of one of the top three providers in the world. Nothing I say to them will make them flinch. If it's going to be a problem, I will just host this site elsewhere. That's part of the reason I am doing this little experiment, to see if things would work out OK. If not, I will go elsewhere where they have more current software and I have more direct access to the database (and probably someone with cPanel, since everyone at Mambo and Simple Machines talk about it so often).

Yes.  Forgive me for not clarifying that... for 1.1, you want to use those (newer) converters.  I believe the 1.1 converter converts notifications, censored words, and moderators - none of which does the one found in this topic.

D'oh, I ran it with the older script, the one you said to use...

For 30,000 posts and MySQL 3.23?  Hmm... shouldn't be much longer.  Considering you posted over 2 hours ago, it should definitely be done by now.  If it isn't, something is wrong.

Err...something's wrong then...it's still going. I can see the table increasing in size but it's been going for like...at least six or seven hours now...is this because of one of my "older versions" of software? It's working on the 25,000th message or so now....5,000 more to go

Should I start over again with the other script (the 1.1 version)? If I start over, do I need to dump all the tables first?

I think I better start looking for another provider...at least for this site.

[[Unknown]]

Top 3?  Sigh... I hate those "top 3" hosts, sorry.

The 1.1 converter will clear everything out before converting.  If it really took over 6 hours, that's horrible.  If you are willing to give me temporary access to the installation (and, if possible, phpMyAdmin or similar) I can try to find out why it's going that slowly... it shouldn't be.

That said, some of those top hosts do put you on a shared MySQL server, which is very bad for performance.

-[Unknown]

[ssoltz]

[Unknown],

Top 3?  Sigh... I hate those "top 3" hosts, sorry.

What's to like? The only thing at all good about them is that you can talk to someone 24/7. Since the knowledge varies incredibly, this isn't always a good thing. They also have great uptimes (but everyone should these days). I wrote you a PM last night. You can ignore it if you read this first.

Anyway, the converter finally finished...well, mostly (after well over ten hours)...I got an error when it was recalculating forum statistics:

Recalculating forum statistics... Unsuccessful!
This query:

    SELECT mem.ID_MEMBER, COUNT(pmr.ID_PM) AS realNum, mem.instantMessages
    FROM `sskcre`.smf_members AS mem
    LEFT JOIN `sskcre`.smf_im_recipients AS pmr ON (mem.ID_MEMBER = pmr.ID_MEMBER AND pmr.deleted = 0)
    GROUP BY mem.ID_MEMBER
    HAVING realNum != instantMessages;

Caused the error:

    Table 'sskcre.smf_im_recipients' doesn't exist

Well, I didn't have IM in Snitz...should I run the 1.1 converter? Should I just go and add an empty table of the above name?

Also, it appears that the passwords did not port successfully. At least my two accounts were not recognized.

By the way, thank you for the offer of going into my phpMYAdmin for me and checking it out. Unfortunately, my useless hosting company just told me that my database password is PERMANENT...nice, eh? I think I'll be looking for a new ISP Monday.

UPDATE:

I tried to run the 1.1 converter but it didn't seem to be doing anything. So, I am starting from scratch today: I dropped the tables and removed the install of SMF. I'll keep you posted.

Thanks for all the help,

[ssoltz]

It is stuck on the first page of the convert.php, where it asks for the location of SMF and the database info.

I looked at the DB and it looks to have ported the topics and forums but none of the messages.

Thanks,

[ssoltz]

OK...it wasn't frozen but it was unsuccessful. This not my cup of tea but it seemed to think that something in the messages was actually a query. I will snip the error message, but it basically contained the contents of around 50 posts!

Converting...
Converting members...Successful.
Converting categories...Successful.
Converting boards...Successful.
Converting posts (this may take some time)... Unsuccessful!
This query:

    INSERT INTO `sskcre`.smf_messages
    (ID_TOPIC, ID_BOARD, posterTime, ID_MEMBER, subject, posterName, posterEmail, posterIP, body)
    VALUES (2, 17, 1047032466, 2, 'Web Site', 'TADropik', 'tomatdockdogs.com', '206.10.75.243', 'Congratulations to all involved with the new web site. Job well done.

    Tom.'),
    (2, 17, 1047095602, 3, 'Re: Web Site', 'webmaster', 'ssoltzatsskcreatives.com', '24.54.51.225', 'Thank you.

    I hope everyone enjoys the site. Please report any errors and respond with your criticism.

    This site is young and still needs to grow. We can only do this through your participation!

    Thank you,

    Steve Soltz
    SSK Creatives

    dvd.web.video'),
    (4, 2, 1047122666, 2, 'Training in Minnesota', 'TADropik', 'tomatdockdogs.com', '206.10.75.233', 'I welcome anyone interested in Training in the Minnesota area to give me a call. Actually I invite anyone to call me anytime. Success in Big Air relies on team work. There's much more to it than getting your dog to jump off the end of the dock. The handler has plenty of responsiblity as well. Only you can add the distance to your dogs jumps.

    Feel free to call. I look forward to working with you.

    Tom A. Dropik
    tomatdockdogs.com
    612-247-0802
    '),

<snip>

<snip>

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 's much more to it than getting your dog to jump off the end of

Tell me if I'm wrong here, but shouldn't the name of the table concatenate right on the end of the name of my database without the extra single quote there( see red)? Maybe this is a bug, maybe I'm doing something wrong. I highlighted the section of the post that was indicated in the error, since it starts with that single quote, I figure thats probably the problem.

Thanks,

[NoRad]

All of this brings back the horrible memories of dealing with Snitz. You'll get through this and never look back. Hang in there. =)

[[Unknown]]

Recalculating forum statistics... Unsuccessful!

Well, I didn't have IM in Snitz...should I run the 1.1 converter? Should I just go and add an empty table of the above name?

Unfortunately, this error is because you were using the 1.0 converter.  Things in the database were changed in 1.1.

Also, it appears that the passwords did not port successfully. At least my two accounts were not recognized.

Hmm... that's strange.  None work at all?  That's not a good sign.  Can you post a link to a phpinfo.php?  What is phpinfo.php?

Sorry for the late response.  I have so many topics to deal with every day, it's hard to keep track of them all, or even a reasonable number thereof.

It is stuck on the first page of the convert.php, where it asks for the location of SMF and the database info.

I looked at the DB and it looks to have ported the topics and forums but none of the messages.

It's just staying there?  It's not showing any "paused" messages at all?

Hmm, I would swear I made sure it handled quotes properly, but I guess it didn't.  I really hated having to dance around Snitz' database structure... but, the database name is fine.

I've attached an updated version of the converter - only the snitz_to_smf.sql file is different, but I've attached both files for convenience.

-[Unknown]

[ssoltz]

Now I am getting this error at the end of the huge list of messages:

Caused the error:

    Unknown column 'TposterTime' in 'field list'

Sorry for the rigamarole on this. But at least once this has been solved, you will have one less unhappy Snitz ex-user to deal with!

EDIT:

It appears that there is an unexpected 'T' or 'R' in front of some of the field names. 'TPosterTime' should be 'posterTime' and all the fields have an unexpected 'T' or 'R' after this first one.

EDIT:

I have edited out what my newbie brain has deemed as typos in snitz_to_smf.sql. I'm thinking that the 'T's and 'R's are global find and replace errors since there are some T_'s and R_'s before some of the smf fields. Anyway, I'm giving my edit a go now. I'll let you know how it goes.

EDIT:

Well, it seems to now be populating the smf_messages table, which it wasn't doing before. Hopefully, this is a good sign.

EDIT:

It's going dirt slow, like 200 messages an hour right now....considering I have 40,000 posts, this might take a while.


Thanks again,

[[Unknown]]

That error doesn't make any sense, unless you're using an old convert.php.  I'll test it again.

The T and R thing is because of the way Snitz stores things.  In Snitz, you have the topic and the first post in the topic stored in one place, and replies stored in another.  But, in SMF, you have all posts (whether replies or not) stored in the same place, and the topic data is much more minimal.

Because of this, the converter has to convert both topic posts and replies, and moreover it has to do it in the right order.  This is exactly what the phpBB converter gets so wrong.

I'm working right now on maybe making it faster.

-[Unknown]

[[Unknown]]

Okay, the attached is fixed (sorry about that) and *should* be faster.  Unfortunately, I'm working with just test data, so it's hard to be accurate about speed.... and it's hard to make it fast and work around the problems.

If it's still slow, you might try this query in phpMyAdmin or similar:

ALTER TABLE FORUM_REPLY
ADD UNIQUE INDEX temp (TOPIC_ID, R_DATE);

What is phpMyAdmin?  But that may not help as much as could be desired.  You mentioned you were considering switching hosts - what has happened with that, thus far?  Would it be possible for me to try to optimize the converter with practical data using your phpMyAdmin, if it's still slow?

Attached.  The convert.php file changed, too.

-[Unknown]