snitz convertor error

[telf]

SMF Version: SMF 1.1 RC3
I have converted a snitz/MS Access based forum to Snitz/MySql using the procedure at hxxp:forum.snitz.com/forum/topic.asp?TOPIC_ID=42438 [nonactive] (other people have asked for the procedure so there it is).

When I then tried to convert it to SMF with the snitz->SMF converter I get the following errors -



The 'Parse Error' goes on ~100 times (yes I counted them), then you get -



This is followed by all the data from our posts then this at the end-



Help, I'm not really a coder so I'm stuck.

Thanks in advance.

[Oldiesmann]

Try this one. A couple of lines were missing a " at the end of them.

[telf]

Thanks for that, Michael. It now produces this -



There are many pages of the 'Parse error' before the end -



If we then go to the forum, you get this -



Notice that there is no 'Started by' entry and all the dates are 31 December 1969 16:00:00

When you go into a post, there is no Author, no body and all dates are as above.



I was looking around the snitz database yesterday and noticed that all the data is there, ie, in the  fields you can see authors, times, replys and body, etc.

Any further help is much appreciated.

[kgrimsby]

Hi, Telf,

I'm at about the same stage of the Snitz conversion process as you. However, using the Snitz converter on the download page, I'm not encountering any parse errors.

The first problem I encountered is that an empty birthdate in Snitz is not a valid value for insertion into a date column in SMF. That problem was easily resolved by updating the Snitz Members table with dummy birthdates of '00010101'.

The real problems began with the Messages table. I can see that the conversion code consolidates posts from the separate Topics and Replies tables of Snitz for eventual insertion into the single Messages table of SMF. However, as you can see in your screenshot above, the converter then attempts to insert the consolidated data into columns of the Messages table that do not exist. The first such nonexistent column encountered is TPosterTime, which raises the error you see displayed in your screenshot above.

I'm looking forward to any guidance we can get from others who have been through this process already.

By the way, as someone new to SMF, I am simply amazed at the level of support offered on these forums, and by Oldiesmann's support in particular.

Ken

[Oldiesmann]

Try this one.

[kgrimsby]

I'm still getting...

Unknown column 'TposterTime' in 'field list'

Ken

[Oldiesmann]

What's the exact error that you're getting?

[telf]

Hi again Michael.
I tried your new script and got the same error as before except its on line 18 -
Parse error: syntax error, unexpected ';' in /home/asra03/public_html/smf/convert.php(814) : eval()'d code on line 18

The forum still shows exactly the same problems as yesterday.

Hi Ken, yes this guy's level of support is amazing as seen by his helping of others. I don't know how to say thanks to people like this but they are definitely appreciated.

Thanks again in advance from me 'down under'.

[kgrimsby]

Here's the error (with sensitive data snipped):

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

    INSERT INTO `smf`.smf_messages
    (ID_TOPIC, ID_BOARD, subject, TposterTime, TID_MEMBER, TposterIP, TposterName, Tbody, TposterEmail, TmodifiedName, TmodifiedTime, RposterTime, RID_MEMBER, RposterIP, RposterName, Rbody, RposterEmail, RmodifiedName, RmodifiedTime)
    VALUES ('74', '20', 'Aspiration', '0', '14', ... , '', '');

Caused the error:

    Unknown column 'TposterTime' in 'field list'

[telf]

Michael, Ken,
I used to get that Tposter error before I asked for support.
I played around with the smf database a bit and it went away.
Should I totally clear my database and start again just in case I did something before that has fixed the error, ie, if I start from a clean database and start getting the error again, then there is definitely still a Tposter error with the script?

[Oldiesmann]

Found a missing ) in my code. Try this one.

[telf]

Thanks Michael.
I now get -



These errors on lines 5, 9, 14 and 18 repeat for quite a while then this happens -



A heap of forum posting data then appears then -



BTW-
Server (hosted) is linux 2.6.9-42.0.2.ELsmp
PHP is 4.4.4
Mysql is 4.1.21-standard
Apache is 1.3.37
Perl is 5.8.7

Thanks again,
telf.

[Oldiesmann]

Try this one.

[telf]

Mate, you are fantastic.
It is almost all in order!!!!
The only thing that I can't find are attachments and images.
I have found out that the old snitz image directory (called 'uploaded') wasn't uploaded to the webserver.
Is it as simple as just uploading all the images and attachments to a directory and SMF will link to them or is it harder than that?
If so, where should it be uploaded to?

Again, many thanks for your work so far.

telf

[kgrimsby]

Telf,

I made a couple of modifications to Michael's latest .sql file and I've gotten past all the error messages. I have about 60,000 messages to convert, so it will be a while before the conversion is complete. I'll report back about the outcome when it is.

In the meantime, you might want to try the files I'm using at the moment. I'm using the convert.php file for Snitz from the download page. I'm using Michael's latest snitz_to_smf.sql file with the following modifications:

At line 95, change

Code Select Expand

$row['TmodifiedName'] = 'NULL';

to

Code Select Expand

$row['TmodifiedName'] = "''";

At line 108, change

Code Select Expand

$row['RmodifiedName'] = 'NULL';

to

Code Select Expand

$row['RmodifiedName'] = "''";

Good luck!

Ken

[kgrimsby]

Although the conversion completed without reporting any error, the resulting database is quite a mess. Among the many problems: At most, only one post shows up per topic, even though reply counts are displayed correctly. Post counts and topic counts are shown as being the same, no matter how may replies a topic has received. Some topics don't display any posts at all. All dates are displayed as "December 31, 1969, 04:00:00 pm." No user is able to log in.

I haven't had time to investigate what may have happened. However, I promised a report, so here it is. At the moment, I am very discouraged.

Ken

[telf]

I had that at one stage but the last file Michael posted worked for me apart from attachments.
All my posts are there and in order.

Haven't heard from Michael for a day or so but hey, he can have a day or 3 off.
His help has been awesome so far.

Hope he gets you sorted to at least where I'm at.
Let me know if you ever get your attachments working.

telf

[kgrimsby]

I figured out why the memberName column ended up empty for each row of smf_members. It's because Snitz allows M_USERNAME to be NULL. So change line 34 of snitz_to_smf.sql from

Code Select Expand

SUBSTRING(IF(M_USERNAME = '', M_NAME, M_USERNAME), 1, 80) AS memberName,

to

Code Select Expand

SUBSTRING(IF(IFNULL(M_USERNAME, '') = '', M_NAME, M_USERNAME), 1, 80) AS memberName,

Also, in order to get SHA256 encryption to work on my Windows installation of PHP, I found that I needed to add a new environment value, PHPRC, and set its value to the root PHP directory. Otherwise, PHP is unable to locate libmhash.dll.

Finally, the failure of dates to transfer correctly was due to a problem with the ODBC import of data into MySQL from MS SQL Server using the native SQL Server provider:

In some cases, text and varchar data in SMF end up with a null character ('\0') replacing the final character in the original data. I've never seen it happen with PHP 4.x, but it does happen with PHP 5.x. A workaround is to import first into an Access database and then export from Access into your MySQL database.

Ken

[Oldiesmann]

I figured out why the memberName column ended up empty for each row of smf_members. It's because Snitz allows M_USERNAME to be NULL. So change line 34 of snitz_to_smf.sql from

Code Select Expand

SUBSTRING(IF(M_USERNAME = '', M_NAME, M_USERNAME), 1, 80) AS memberName,

to

Code Select Expand

SUBSTRING(IF(IFNULL(M_USERNAME, '') = '', M_NAME, M_USERNAME), 1, 80) AS memberName,

This is much simpler:

Code Select Expand

SUBSTRING(IFNULL(M_USERNAME, M_NAME), 1, 80) AS memberName

I'll wait until I hear back from you before making any changes to the sql file

[kgrimsby]

Michael,

I'll wait until I hear back from you before making any changes to the sql file

The conversion last night worked very well!   So far, the only problems I have discovered are some incorrect post counts, which were probably carried over from Snitz. Running the SMF Admin cleanup utilities seems to have resolved these minor problems.

So I feel confident that my changes to the .sql file are ready for inclusion in the live copy. At line 95:

Code Select Expand

$row['TmodifiedName'] = "''";

At line 108:

Code Select Expand

$row['RmodifiedName'] = "''";

At line 34, we have two options, my original modification,

Code Select Expand

SUBSTRING(IF(IFNULL(M_USERNAME, '') = '', M_NAME, M_USERNAME), 1, 80) AS memberName,

and your simplification,

Code Select Expand

SUBSTRING(IFNULL(M_USERNAME, M_NAME), 1, 80) AS memberName,

You might actually prefer my version, because it deals with the fact that Snitz columns often include both NULL and zero-length string data. This complication occurs because Snitz changed its database design several times without cleaning up data created with earlier releases.

I'm submitting my converted forums to a group of testers today. I'll let you know if we discover any further anomalies.

Thank you very much for all of your help!

Ken